[Solved] Sum values on main form and in subform grid column
- martinbone
- Banned
- Posts: 8
- Joined: Wed Jun 07, 2017 2:45 pm
[Solved] Sum values on main form and in subform grid column
.
Last edited by Hagar Delest on Fri Nov 24, 2017 9:42 am, edited 3 times in total.
Reason: tagged solved
Reason: tagged solved
Openoffice 4 on Debian
-
- Volunteer
- Posts: 828
- Joined: Fri Nov 30, 2007 10:58 am
- Location: Scotland
Re: Macro to sum values on main form and in subform grid col
It would appear that sAmount2 in the lineplus all the values in the TRANSAMOUN column on the subform_grid (ie. not just for the current row - but for all rows in the subform_grid).
Code: Select all
sAmount2 = oForm.getByName("TRANSAMOUN").CurrentValue
When this issue has been resolved, it would help other users of the forum if you add the word - [Solved] - to the Subject line of your 1st post (edit button top right).
AOOo 4.1.5 & LO 6 on MS Windows 10 MySQL and HSQLDB
AOOo 4.1.5 & LO 6 on MS Windows 10 MySQL and HSQLDB
- martinbone
- Banned
- Posts: 8
- Joined: Wed Jun 07, 2017 2:45 pm
Re: Macro to sum values on main form and in subform grid col
.
Last edited by martinbone on Wed Nov 01, 2017 11:48 am, edited 1 time in total.
Openoffice 4 on Debian
-
- Volunteer
- Posts: 828
- Joined: Fri Nov 30, 2007 10:58 am
- Location: Scotland
Re: Macro to sum values on main form and in subform grid col
Without a better understanding of your actual form document, main form, or subform there could be a number of answers: it could well be that the macro is not required with a better database design. It would be quicker and hopefully more productive to suggest a solution if you were to upload a copy of your file with any sensitive data removed.OK ... So is there a way to get the aggregate value of a subform grid column into a field on a subform?
See here for how to upload a file to the forum.
Alternatively I suggest you examine DACM's excellent invoicing example which exploits the underlying strengths of Base while avoiding unnecessary resort to macros that tend to be less productive given the comprehensive but complex OpenOffice API.I'm trying to write a macro for an invoice form that will populate a field with a "balance" value.
When this issue has been resolved, it would help other users of the forum if you add the word - [Solved] - to the Subject line of your 1st post (edit button top right).
AOOo 4.1.5 & LO 6 on MS Windows 10 MySQL and HSQLDB
AOOo 4.1.5 & LO 6 on MS Windows 10 MySQL and HSQLDB
- martinbone
- Banned
- Posts: 8
- Joined: Wed Jun 07, 2017 2:45 pm
Re: Macro to sum values on main form and in subform grid col
.
Last edited by martinbone on Wed Nov 01, 2017 11:49 am, edited 2 times in total.
Openoffice 4 on Debian
-
- Volunteer
- Posts: 828
- Joined: Fri Nov 30, 2007 10:58 am
- Location: Scotland
Re: Macro to sum values on main form and in subform grid col
The file appears to reflect faulty database design in that few proper relationships are established between the source data tables in the database. An invoice database can be quite complicated. Typically, at the very least, it should reflect the one to many relationship between Suppliers and Invoices, similarly between an Invoice and the Lines it contains, and similarly between a purchase Item and invoice Lines. These are each quite distinct entities and will require individual, but referenced, tables if the database is to ensure integrity and avoid unnecessary redundancy.
An initial introduction to database design relevant to Base can be downloaded from
https://wiki.documentfoundation.org/ima ... torial.pdf. It is significant that many pages in that tutorial are devoted to design principles before addressing forms or queries.
Then again Base may not be a wholly suitable route if the intention is that the database is to be used for serious commercial purposes. The default database application embedded in Base is suitable for little more than learning and demonstration purposes: commercial reliability will inevitably require upgrading so that Base works with a split or separate database engine. It should also be recognised that starting an initial RDMS project by designing a commercial database is unlikely to be economically justifiable: an off-the-shelf purchase or hiring a professional may well be a cost that proves financially more rewarding. On the other hand it can indeed prove to be a challenging and enjoyable education exercise.
An initial introduction to database design relevant to Base can be downloaded from
https://wiki.documentfoundation.org/ima ... torial.pdf. It is significant that many pages in that tutorial are devoted to design principles before addressing forms or queries.
Then again Base may not be a wholly suitable route if the intention is that the database is to be used for serious commercial purposes. The default database application embedded in Base is suitable for little more than learning and demonstration purposes: commercial reliability will inevitably require upgrading so that Base works with a split or separate database engine. It should also be recognised that starting an initial RDMS project by designing a commercial database is unlikely to be economically justifiable: an off-the-shelf purchase or hiring a professional may well be a cost that proves financially more rewarding. On the other hand it can indeed prove to be a challenging and enjoyable education exercise.
When this issue has been resolved, it would help other users of the forum if you add the word - [Solved] - to the Subject line of your 1st post (edit button top right).
AOOo 4.1.5 & LO 6 on MS Windows 10 MySQL and HSQLDB
AOOo 4.1.5 & LO 6 on MS Windows 10 MySQL and HSQLDB
- martinbone
- Banned
- Posts: 8
- Joined: Wed Jun 07, 2017 2:45 pm
Re: Macro to sum values on main form and in subform grid col
.
Last edited by martinbone on Wed Nov 01, 2017 11:49 am, edited 1 time in total.
Openoffice 4 on Debian
Re: Macro to sum values on main form and in subform grid col
We have a whole subforum for tutorials and well designed database examples:martinbone wrote:Many thanks for your advice Arineckaig. I've now added relationships to my database ... I do find it a challenging and educational exercise creating a database. One of my aims to to help demonstrate how powerful Base is - and what it can do ...
https://sites.google.com/site/libreofficedatabases/
Board index ‹ Getting started ‹ Tutorials
Board index ‹ Getting started ‹ Tutorials ‹ Base
Board index ‹ Getting started ‹ Tutorials ‹ Base ‹ Database Examples
particularly:
[Example] Invoice Forms (without macros) (with excellent explanation on Base in general)
[Example] Relations reflected by list boxes in forms
[Tutorial] Forms in OpenOffice.org Base
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
-
- Volunteer
- Posts: 828
- Joined: Fri Nov 30, 2007 10:58 am
- Location: Scotland
Re: Macro to sum values on main form and in subform grid col
I have seen several enterprises fail when an unreliable Purchase Ledger caused loss of cash control. Any relational database that ignores the key principles of normalization sooner or later produces unreliable results. I fear all the care and effort already devoted to "Open PLA 5.3.2.2.g.odb" will be abortive if it is not grounded on a sound relational database design. Starting at the deep end is risky and better avoided. I suggest it will be difficult, if not impossible, to achieve your aim, as quoted above, without first gaining an initial understanding of the theoretical principles that underlie a relational database. An understanding of these principles is more readily proved and tested on the design of token or simple databases before embarking on the critical complexity of one that is intended for commercial accounting.martinbone:
One of my aims to to help demonstrate how powerful Base is - and what it can do ...
When this issue has been resolved, it would help other users of the forum if you add the word - [Solved] - to the Subject line of your 1st post (edit button top right).
AOOo 4.1.5 & LO 6 on MS Windows 10 MySQL and HSQLDB
AOOo 4.1.5 & LO 6 on MS Windows 10 MySQL and HSQLDB
- martinbone
- Banned
- Posts: 8
- Joined: Wed Jun 07, 2017 2:45 pm
Re: Macro to sum values on main form and in subform grid col
.
Last edited by martinbone on Wed Nov 01, 2017 11:49 am, edited 2 times in total.
Openoffice 4 on Debian
Re: Macro to sum values on main form and in subform grid col
It demonstrates how it shouldn't be done.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
-
- Volunteer
- Posts: 828
- Joined: Fri Nov 30, 2007 10:58 am
- Location: Scotland
Re: Macro to sum values on main form and in subform grid col
The issue is not to do with OpenOffice/LibreOffice but with relational databases. The relational database is founded on certain principles - e.g.mathematical set theory - and if the design is faulty the results will be unreliable.It would be nice to receive balanced feedback and receive at least one positive comment from people interested in OpenOffice / Libreoffice.
Base is merely a front end to many types of database. Base has its strengths, flexibility and limitations. It would be kinder to Base, and to intended users, that a demonstration of its potential should connect to a database of sound design.
When this issue has been resolved, it would help other users of the forum if you add the word - [Solved] - to the Subject line of your 1st post (edit button top right).
AOOo 4.1.5 & LO 6 on MS Windows 10 MySQL and HSQLDB
AOOo 4.1.5 & LO 6 on MS Windows 10 MySQL and HSQLDB
- Hagar Delest
- Moderator
- Posts: 32667
- Joined: Sun Oct 07, 2007 9:07 pm
- Location: France
Re: [Solved] Sum values on main form and in subform grid col
For the record, I've banned OP.
I can't stand users who go here to receive help and then delete all their posts. This place is to help users and build a knowledge base.
If another mod wants to reverse the ban, I'm fine also.
I can't stand users who go here to receive help and then delete all their posts. This place is to help users and build a knowledge base.
If another mod wants to reverse the ban, I'm fine also.
LibreOffice 7.6.2.1 on Xubuntu 23.10 and 7.6.4.1 portable on Windows 10
Re: [Solved] Sum values on main form and in subform grid col
I agree with Hagar. It is one thing to remove a sensitive example file, but antisocial to remove all posts.
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS