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

[Hagar Delest]

.

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

[martinbone]

.

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

[martinbone]

.

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

[martinbone]

.

[Villeroy]

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

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

[martinbone]

.

[Villeroy]

It demonstrates how it shouldn't be done.

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

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

[RoryOF]

I agree with Hagar. It is one thing to remove a sensitive example file, but antisocial to remove all posts.