[Solved] Sum values in form; increment a table

[Grandapa Jim]

I would like to create a Form where I can enter up to 8 values, Sum the values and put the result in a table field. The values will not be put into a table and would be discarded. I then want to Add the sum to a total field in another table. Can this be done? If so how? Remember, I was looking for a Base for the Stupid Forum, and this was as close as I could get.

[Mountaineer]

... Can this be done? If so how? ...

If your values are not connected to the database there will be no "help" from the database. So create a button, wich you bind to a macro you have to write. This can access the fields on your form and calculate the sum and other things you like.

At the moment I'd do the SUM in Calc instead - seems easier for me... But, if you wish to do this in Base check the guide for LibreOffice Base. It shows several examples for macros accessing forms (but actually I use the german version, wich might have more on this side.)

...Remember, I was looking ...

I have not understood, what you are referring to, so I will forget about this.

[Grandapa Jim]

The last line was an attempt at humor, about my abilities with databases.
Can you give a link to Macros. I am quite comfortable writing macros and scripts, after being in the IT industry for 50 years.

So all I need to do is put a button on the form, and let the macro do its magic?

[Hagar Delest]

Can you give a link to Macros. I am quite comfortable writing macros and scripts, after being in the IT industry for 50 years.

See the forum section about macros with some documentation in the first topics: viewforum.php?f=20

[UnklDonald418]

Form where I can enter up to 8 values, Sum the values and put the result in a table field. The values will not be put into a table and would be discarded.

Calculating Sums in a database is relatively simple using Queries but this statement makes the assumption that there will never be an entry error!

Applying a few rules of good database design I put together an example, trying to keep it as simple as possible.
You didn't specify what type of numbers you will be using, so for my example I use Integer values in the table named VALUES.

There is one Form Document VALUES-SUMS containing 4 Forms

On the left is a Form for entering 8 values. When all eight values are entered then press the Button below and the row will be entered into the VALUES table and the form will clear Entries are required in all eight boxes, pad it out with 0 if that row has fewer than eight values.

If you want to see the newly entered data Press the Button below the center table which is on a separate Form. But also connected to the VALUES table.. The center table is where entry errors can be corrected. Updating this form is not necessary after each new entry, it is only for display and editing if needed.

The Right table is on a third Form and it gets the Sums for each row from Query1. Press the button below to make sure all the latest entries are displayed.

The Total is displayed below the Sums table. It is on a fourth Form and gets its data from Query2. It also has a button to ensure all the data is included in the Total.

Saving Sums in a database table can result in discrepancies in the data, so it is best to do the calculations at run time. Unless you have millions of rows the delay for completing the calculations is small.

In addition, this approach avoids all the brain damage of coding Macros.

Demo71.odb

(13.83 KiB) Downloaded 14 times

[Mountaineer]

...
So all I need to do is put a button on the form, and let the macro do its magic?

That's the answer to everything in software: Write the appropiate programm (macro, script, app ...) and call it from a button/icon or by name if you use shell/cli.

If your idea of not using the database is a good one? I'm not convinced.

For reference to start coding Base-macros, look in the guide for Base at LibreOffice. Most will be identical to OpenOffice. Main difference is they already integrated the OracleReportBuilder and FireBird as second option for embedded databases.
https://documentation.libreoffice.org/e ... mentation/

[UnklDonald418]

Can you give a link to Macros. I am quite comfortable writing macros and scripts, after being in the IT industry for 50 years.

As I mentioned in my previous post, solving your problem requires no Macros.
You might find this Base tutorial on database design useful
https://wiki.documentfoundation.org/ima ... torial.pdf

If you are still interested in Macro coding be aware that the API for OpenOffice is large, complex and rather opaque
https://www.openoffice.org/api/docs/com ... dex-1.html

So all I need to do is put a button on the form, and let the macro do its magic?

While Macros can sometimes be run from the Menu, Tools>Macros>Run Macro or a Push Button Control on a Form, most are triggered by Events.

The MRI tool is helpful/essential to shed some light on the dark side.
Introduction into object inspection with MRI

Other resources I use:

Andrew Piyonyak has a number helpful documents on general Macro Programming including his book.
https://www.pitonyak.org/oo.php

Most Macro programming is done in Star Basic but other languages such as Python, Java and C++ are also used.
Star Basic Programmers Guide is here
https://docs.oracle.com/cd/E19064-01/so ... 7-1826.pdf

I find “Database Programming with OpenOffice.org Base and Basic” by Roberto Benitez very helpful unfortunately is now out of print and can be difficult to find.