Macro to sum values on main form and in subform grid column

Creating a macro - Writing a Script - Using the API

Macro to sum values on main form and in subform grid column

Postby martinbone » Thu Aug 10, 2017 1:54 pm

I'm trying to write a macro for a purchase ledger invoice form that will populate a field with a "balance" value.
sAmount1 is taken from a field on the main form, while sAmount2 is taken from a column on a subform_grid (on a subform).
The macro will run after updating TRANSAMOUN in the subform grid.


Here's the macro:

Code: Select all   Expand viewCollapse view
Sub BALANCE_update (oEvent As Object)
SourceForm = ThisComponent.drawPage.Forms.getbyindex(0)
sAmount1 = SourceForm.getByName("fmtTRANSAMOUN").CurrentValue
oForm = oEvent.Source.Model.Parent
sAmount2 = oForm.getByName("TRANSAMOUN").CurrentValue
DestSubForm = SourceForm.GetByName("SubForm2")
sBalance = DestSubForm.getByName("Balance").Text   
IF sBalance = "" THEN DestSubForm.getByName("Balance").text = (sAmount1 + sAmount2)
DestSubForm.getByName("Balance").commit
End Sub


It works okay - however - only the value from one subform grid row is used in the calculation. But I need the calculation to sum the value on the main form - 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).

I would be very grateful for any help with this!
Last edited by martinbone on Wed Aug 16, 2017 6:40 pm, edited 1 time in total.
Openoffice 4 on Debian
martinbone
 
Posts: 6
Joined: Wed Jun 07, 2017 2:45 pm

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

Postby Arineckaig » Thu Aug 10, 2017 4:33 pm

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   Expand viewCollapse view
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).
OOo 4.1.1 on MS Windows XP MySQL and HSQLDB
Arineckaig
Volunteer
 
Posts: 825
Joined: Fri Nov 30, 2007 10:58 am
Location: Scotland

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

Postby martinbone » Tue Aug 15, 2017 2:26 pm

OK ... So is there a way to get the aggregate value of a subform grid column into a field on a subform?

(My macro has to look at fields on the form / subform grid - it can't look at Base tables or queries to get the aggregate value - since I need the balance calculated before the last record is saved)
Openoffice 4 on Debian
martinbone
 
Posts: 6
Joined: Wed Jun 07, 2017 2:45 pm

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

Postby Arineckaig » Tue Aug 15, 2017 7:27 pm

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).
OOo 4.1.1 on MS Windows XP MySQL and HSQLDB
Arineckaig
Volunteer
 
Posts: 825
Joined: Fri Nov 30, 2007 10:58 am
Location: Scotland

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

Postby martinbone » Wed Aug 16, 2017 10:58 am

I'm attaching a screenshot here. The fields highlighted by circles are the ones that I need the macro to sum. Here's a link to my .odb file (the form is called "UINV UK REGISTERED INVOICE"):

https://drive.google.com/file/d/0B_BCBRw_BLfxYzlCNmtzVVAtUGc1TDZaQ1M4b0VkMk5oMDZF/view?usp=sharing
Attachments
Screenshot from 2015-11-01 14-04-00.png
Screenshot - Open PLA (Invoice form)
Openoffice 4 on Debian
martinbone
 
Posts: 6
Joined: Wed Jun 07, 2017 2:45 pm

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

Postby Arineckaig » Thu Aug 17, 2017 5:37 pm

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/images/0/02/Base_tutorial.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).
OOo 4.1.1 on MS Windows XP MySQL and HSQLDB
Arineckaig
Volunteer
 
Posts: 825
Joined: Fri Nov 30, 2007 10:58 am
Location: Scotland


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 8 guests