[Solved] Sum values in form; increment a table
-
- Posts: 4
- Joined: Sun Mar 24, 2024 9:14 pm
- Location: Florence AZ
[Solved] Sum values in form; increment a table
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.
Last edited by MrProgrammer on Tue Apr 30, 2024 4:42 am, edited 1 time in total.
Reason: Tagged ✓ [Solved] -- MrProgrammer, forum moderator
Reason: Tagged ✓ [Solved] -- MrProgrammer, forum moderator
Jim Openoffice 4.1.15 Win 11
-
- Posts: 318
- Joined: Sun Sep 06, 2020 8:27 am
Re: Calculations in Form
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.)
I have not understood, what you are referring to, so I will forget about this.
OpenOffice 3.1 on Windows Vista
-
- Posts: 4
- Joined: Sun Mar 24, 2024 9:14 pm
- Location: Florence AZ
Re: Calculations in Form
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?
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?
Jim Openoffice 4.1.15 Win 11
- Hagar Delest
- Moderator
- Posts: 32692
- Joined: Sun Oct 07, 2007 9:07 pm
- Location: France
Re: Calculations in Form
See the forum section about macros with some documentation in the first topics: viewforum.php?f=20Grandapa Jim wrote: ↑Thu Apr 18, 2024 10:45 pm Can you give a link to Macros. I am quite comfortable writing macros and scripts, after being in the IT industry for 50 years.
LibreOffice 24.2 on Xubuntu 24.04 and 7.6.4.1 portable on Windows 10
-
- Volunteer
- Posts: 1551
- Joined: Wed Jun 24, 2015 12:56 am
- Location: Colorado, USA
Re: Calculations in Form
Calculating Sums in a database is relatively simple using Queries but this statement makes the assumption that there will never be an entry error!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.
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.
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
-
- Posts: 318
- Joined: Sun Sep 06, 2020 8:27 am
Re: Calculations in Form
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.Grandapa Jim wrote: ↑Thu Apr 18, 2024 10:45 pm ...
So all I need to do is put a button on the form, and let the macro do its magic?
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/
OpenOffice 3.1 on Windows Vista
-
- Volunteer
- Posts: 1551
- Joined: Wed Jun 24, 2015 12:56 am
- Location: Colorado, USA
Re: Calculations in Form
As I mentioned in my previous post, solving your problem requires no Macros.Can you give a link to Macros. I am quite comfortable writing macros and scripts, after being in the IT industry for 50 years.
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
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.So all I need to do is put a button on the form, and let the macro do its magic?
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.
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11