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

Creating a macro - Writing a Script - Using the API

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

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

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

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: 827
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

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

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: 827
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

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

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: 827
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 » Mon Oct 30, 2017 12:44 pm

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

Postby Villeroy » Mon Oct 30, 2017 3:18 pm

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 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 24663
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

Postby Arineckaig » Tue Oct 31, 2017 2:57 pm

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).
OOo 4.1.1 on MS Windows XP MySQL and HSQLDB
Arineckaig
Volunteer
 
Posts: 827
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 Oct 31, 2017 4:32 pm

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

Postby Villeroy » Tue Oct 31, 2017 4:35 pm

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 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 24663
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

Postby Arineckaig » Tue Oct 31, 2017 5:50 pm

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

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

Postby Hagar Delest » Fri Nov 24, 2017 9:45 am

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:
AOO 4.1.4 on Xubuntu 17.10, (Artful Aardvark) and 4.1.3 on Windows 7 (with winPenPack port).
User avatar
Hagar Delest
Moderator
 
Posts: 27662
Joined: Sun Oct 07, 2007 9:07 pm
Location: France

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

Postby RoryOF » Fri Nov 24, 2017 10:31 am

I agree with Hagar. It is one thing to remove a sensitive example file, but antisocial to remove all posts.
Apache OpenOffice 4.1.4 on Xubuntu 16.04.03 (mostly 64 bit version) and infrequently on Win2K/XP
14 October 2016 was Pooh's 90th birthday
User avatar
RoryOF
Moderator
 
Posts: 25518
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland


Return to Macros and UNO API

Who is online

Users browsing this forum: Villeroy and 10 guests