[Solved] Sum values on main form and in subform grid column

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
User avatar
martinbone
Banned
Posts: 8
Joined: Wed Jun 07, 2017 2:45 pm

[Solved] Sum values on main form and in subform grid column

Post by martinbone »

.
Last edited by Hagar Delest on Fri Nov 24, 2017 9:42 am, edited 3 times in total.
Reason: tagged solved
Openoffice 4 on Debian
Arineckaig
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

Post by Arineckaig »

plus 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).
It would appear that sAmount2 in the line

Code: Select all

sAmount2 = oForm.getByName("TRANSAMOUN").CurrentValue
is to refer to the aggregate sum of the column in the subform. It requires that aggregate value to have been included somewhere in the subform or its data source.
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
User avatar
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

Post by martinbone »

.
Last edited by martinbone on Wed Nov 01, 2017 11:48 am, edited 1 time in total.
Openoffice 4 on Debian
Arineckaig
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

Post by Arineckaig »

OK ... So is there a way to get the aggregate value of a subform grid column into a field on a subform?
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.

See here for how to upload a file to the forum.
I'm trying to write a macro for an invoice form that will populate a field with a "balance" value.
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.
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
User avatar
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

Post by martinbone »

.
Last edited by martinbone on Wed Nov 01, 2017 11:49 am, edited 2 times in total.
Openoffice 4 on Debian
Arineckaig
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

Post by Arineckaig »

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.
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
User avatar
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

Post by martinbone »

.
Last edited by martinbone on Wed Nov 01, 2017 11:49 am, edited 1 time in total.
Openoffice 4 on Debian
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Macro to sum values on main form and in subform grid col

Post by Villeroy »

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/
We have a whole subforum for tutorials and well designed database examples:
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
Arineckaig
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

Post by Arineckaig »

martinbone:
One of my aims to to help demonstrate how powerful Base is - and what it can do ...
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.
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
User avatar
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

Post by martinbone »

.
Last edited by martinbone on Wed Nov 01, 2017 11:49 am, edited 2 times in total.
Openoffice 4 on Debian
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Macro to sum values on main form and in subform grid col

Post by Villeroy »

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
Arineckaig
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

Post by Arineckaig »

It would be nice to receive balanced feedback and receive at least one positive comment from people interested in OpenOffice / Libreoffice.
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.

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
User avatar
Hagar Delest
Moderator
Posts: 32627
Joined: Sun Oct 07, 2007 9:07 pm
Location: France

Re: [Solved] Sum values on main form and in subform grid col

Post by Hagar Delest »

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.
:evil:
LibreOffice 7.6.2.1 on Xubuntu 23.10 and 7.6.4.1 portable on Windows 10
User avatar
RoryOF
Moderator
Posts: 34586
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: [Solved] Sum values on main form and in subform grid col

Post by RoryOF »

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
Post Reply