Insert Sum of Two fields In The Same Record Into Third Field

Creating tables and queries
Post Reply
k.vallely
Posts: 1
Joined: Sat Apr 04, 2009 4:10 pm

Insert Sum of Two fields In The Same Record Into Third Field

Post by k.vallely »

I have a table of customer orders with fields something like this:

customer : order-number : item1 : item1cost : item2 : item2cost : total-cost

I want the total-cost field to be the sum of item1cost and item2cost.
Do I use a query or can I do it in a formula?

Can I do it in my order entry form?
Can I create a dynamic total-cost field that is the sum of the item1cost and item2cost fields and updates when those fields are changed?

Please try not to smirk.
I'm a beginner.
I downloaded this week.
OOo 3.0.X on Ms Windows XP
QuazzieEvil
Volunteer
Posts: 283
Joined: Tue Dec 04, 2007 6:38 pm
Location: Houston, TX

Re: Insert Sum of Two fields In The Same Record Into Third Field

Post by QuazzieEvil »

you can either do it in a view/query or use OO Basic.

Using a view or query is the simplest method. Simply create a field that is the calculation (rather than selecting a field). The downside is that you may have difficulties creating a form base on a query and be able to update the underlying table.

With basic you can do something like this:

Code: Select all

Sub calcField(Event As Object)
REM Bound to 'Text Modified' event of Base & Height controls.
On Error Goto HandleError
	Dim v1 As Double
	Dim v2 As Double
	Dim Form As Object
	
	Form=Event.Source.Model.Parent
	REM calc value of controls Base & Height, and store in Area--bound to table fields with same name
	v1=Form.getByName("Base").BoundField.getDouble()
	v2=Form.getByname("Height").BoundField.getDouble()
	Form.getByName("Area").BoundField.updateDouble(v1*v2)
HandleError:
	If err<>0 Then Exit Sub
End Sub
User avatar
normansimonr
Posts: 117
Joined: Wed Feb 16, 2011 8:22 pm
Location: Colombia

Re: Insert Sum of Two fields In The Same Record Into Third F

Post by normansimonr »

Hi everyone. I have this same question: I used this SQL query:

SELECT <field1>, <field2>, <field3>, (<field1> + <field2>) AS "SUM" FROM <Table_name>

And it worked.

But SELECT <field1>, <field2>, <field3>, (<field1> + <field2>+ <field3>) AS "SUM" FROM <Table_name>

Didn't work. What's wrong? I am using REAL variable types and I have tried a lot of different parenthesis combination. Thinking about k.vallely's question and the mine, can you help us?

Thankyou very much.
OpenOffice 3.1 on Ubuntu-Linux 9.04
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Insert Sum of Two fields In The Same Record Into Third F

Post by FJCC »

This works for me

Code: Select all

SELECT "Track0", "Track1", "Track2" ,"Track0" + "Track1" + "Track2" AS "SUM" FROM .....
It also works with parenthesis around the whole sum. Can you post the exact query you are using. And are you using the embedded HSQLDB or some other DB?
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
User avatar
normansimonr
Posts: 117
Joined: Wed Feb 16, 2011 8:22 pm
Location: Colombia

Re: Insert Sum of Two fields In The Same Record Into Third F

Post by normansimonr »

Ok. It resulted just taking the parenthesis away; I won't get confused including () in the future. Thanks.
OpenOffice 3.1 on Ubuntu-Linux 9.04
rudolfo
Volunteer
Posts: 1488
Joined: Wed Mar 19, 2008 11:34 am
Location: Germany

Re: Insert Sum of Two fields In The Same Record Into Third F

Post by rudolfo »

normansimonr, please don't run away with this! FJCC stated that the query worked either way.
And that's what I can confirm ... for the embedded database:

Code: Select all

SELECT "Track0", "Track1", "Track2" , ( "Track0" + "Track1" + "Track2" ) AS "SUM" FROM .....
works for me, as well.
My experience with SQL statements is that an additonal pair of paranthesis never harms ... except for readability. Even the following really sick way to write the query statement gives the same results:

Code: Select all

SELECT ("Track0"), ("Track1"), "Track2" , ( "Track0" + "Track1" ) + "Track2" AS "SUM" FROM .....
So please help others, and don't leave them with the missconception that paranthesis around a field with operators need to be removed. Investigate your situation again and give us some good conclusion.
OpenOffice 3.1.1 (2.4.3 until October 2009) and LibreOffice 3.3.2 on Windows 2000, AOO 3.4.1 on Windows 7
There are several macro languages in OOo, but none of them is called Visual Basic or VB(A)! Please call it OOo Basic, Star Basic or simply Basic.
kraftwerk
Posts: 9
Joined: Tue Jan 05, 2016 6:49 am

Re: Insert Sum of Two fields In The Same Record Into Third F

Post by kraftwerk »

Hi,

First of all, sorry to be 7 years late into this thread, but I have been searching last three days for a solution to my problem :).

I have tried the calcfield procedure shown in this thread. It's great and exactly what I am looking for. Thanks for that.

Before actually testing it on my real database elements, I decided to do a test on a test form and test table inside my database.

To do so, I created a very simple table called TblTest which contain 4 fields: Index (integer auto), FieldA (double), FieldB (double) et FieldC (double)

I created a form that contain these 4 fields with the "wizard".

I created a Module1 in which I pasted the calcfield sub. That Module is linked to the database.

In Calcfield, of course, I changed "Height" and "Base" for "FieldA" and "FieldB". I also changed "Area" for "FieldC".

I affected the calcfield macro to fields FieldA and FieldB on event "Text modification".

I created an entry in the form, I saved it, then I attempt to change either FieldA or FieldB.

With the "basic" debug mode, I can see that the program accesses the macro, but it exits the sub on Handlerror. It does not reach "v1=..." or "v2=...".

It seems that the bug occurs at this line: "Form=..."

Is there something I am doing wrong?

Thanks a lot and sorry again for reigniting a cold thread, but I really need your help guys.
Windows 8
Apache OpenOffice 4.1.1
F3K Total
Volunteer
Posts: 1038
Joined: Fri Dec 16, 2011 8:20 pm

Re: Insert Sum of Two fields In The Same Record Into Third F

Post by F3K Total »

Hi,
please supply your file, then you will get a solution.
Otherwise it's like poking around in the dark.
R
  • MMove 1.0.6
  • Extension for easy, exact positioning of shapes, pictures, controls, frames ...
  • my current system
  • Windows 10 AOO, LOLinux Mint AOO, LO
User avatar
MTP
Volunteer
Posts: 1620
Joined: Mon Sep 10, 2012 7:31 pm
Location: Midwest USA

Re: Insert Sum of Two fields In The Same Record Into Third F

Post by MTP »

The macro provided by QuazzieEvil takes an Event as an input parameter. If you assign the macro to a button on a form and push the button to activate the macro, the "button-pushed" event will be passed to the macro. The Form= line will start with the "button-pushed" event to find the button and then the button's parent, the form object.

(Side note: a form object as in a collection of form controls and potentially other forms, called subforms if they are located inside another form - the "form document" is at a higher level where FormDocument has a DrawPage which has a FormsContainer which has a form or forms that are collections of controls).

If you are just trying to run the macro from the editor, of course there will be no event passed and that line will fail.

Also, the macro manipulates form controls and does not access the underlying table. In addition to having a button on your form document, you would need to have textbox controls named Base, Height, and Area for the macro to be able to complete.
OpenOffice 4.1.1 on Windows 10, HSQLDB 1.8 split database
kraftwerk
Posts: 9
Joined: Tue Jan 05, 2016 6:49 am

Re: Insert Sum of Two fields In The Same Record Into Third F

Post by kraftwerk »

Thanks F3K Total and MTP,

In my case, I am trying to pass "Text modification on the form's txtbox object" as the event. The event, as such, seems to trigger the macro ok, because the program enters briefly the sub.

I have included a database containing only my test table, test form and macro below.

Are there other files required besides the .odb? If so, I'll add them.

Thanks for your time, I appreciate it. :o
Attachments
My Database.odb
Test database for calcfield macro testing
(15.36 KiB) Downloaded 265 times
Windows 8
Apache OpenOffice 4.1.1
F3K Total
Volunteer
Posts: 1038
Joined: Fri Dec 16, 2011 8:20 pm

Re: Insert Sum of Two fields In The Same Record Into Third F

Post by F3K Total »

Hi,
there where two problems:
  • The naming of the controls in the code was wrong, e.g. there is no field called FieldA but fmtFieldA exists, use the Form Navigator to explore.
  • If you modify a formatted field, then you can see the changed content, but it wasn't already send. So e.g.

    Code: Select all

    Form.getByName("fmtFieldA").BoundField.getDouble()
    cannot read the modified value. By inserting two rows, it does the "Enter" for you.
Here's a code, working for me

Code: Select all

Sub calcField(Event As Object)
REM Bound to 'Text Modified' event of Base & Height controls.
On Error Goto HandleError
   Dim v1 As Double
   Dim v2 As Double
   Dim Form As Object
   FormattedField = Event.Source.Model
   FormattedField.commit 'This does the "Enter" for you.
   Form = FormattedField.Parent
   REM calc value of controls fmtFieldA and fmtFieldB, and store total in fmtFieldC--bound to table fields with same name
   v1=Form.getByName("fmtFieldA").BoundField.getDouble()
   v2=Form.getByname("fmtFieldB").BoundField.getDouble()
   Form.getByName("fmtFieldC").BoundField.updateDouble(v1+v2)
   HandleError:
   If err<>0 Then Exit Sub
End Sub
I prefer a version without code, see 2nd form in your modified file attached.
R
Attachments
My Database.odb
(23.81 KiB) Downloaded 327 times
  • MMove 1.0.6
  • Extension for easy, exact positioning of shapes, pictures, controls, frames ...
  • my current system
  • Windows 10 AOO, LOLinux Mint AOO, LO
kraftwerk
Posts: 9
Joined: Tue Jan 05, 2016 6:49 am

Re: Insert Sum of Two fields In The Same Record Into Third F

Post by kraftwerk »

Both methods work great on this end too.

I am not sure to understand the difference between the formattedfield and the boundfield.

I have an extensive VBA background, but that version of Basic will require some getting used to for me. I guess I'll have to go through the Basic bible a bit.

You have been of tremendous help :super: . Thanks a million :bravo: !

I hope this will help other users having the same problem also.
Windows 8
Apache OpenOffice 4.1.1
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Insert Sum of Two fields In The Same Record Into Third F

Post by Villeroy »

The VB(A) code to do the same thing with OpenOffice would be exactly the same. The difference is not the language. The only difference is the thing you talk to in either language.
Yes, it is possible to control OpenOffice through VB(A).
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
kraftwerk
Posts: 9
Joined: Tue Jan 05, 2016 6:49 am

Re: Insert Sum of Two fields In The Same Record Into Third F

Post by kraftwerk »

Yes, the way to refer to object and properties is a major difference. It's good to know that's it's not that different for other aspects. I suppose it's like knowing how to ride a bicycle, it comes back to you even years later.

I would gladly change the topic title to 'solved' but I don't have the power to do so since I did not create the thread.

Thanks again you all.
Windows 8
Apache OpenOffice 4.1.1
kraftwerk
Posts: 9
Joined: Tue Jan 05, 2016 6:49 am

Re: Insert Sum of Two fields In The Same Record Into Third F

Post by kraftwerk »

I have a final subquestion on this issue.

I have used method 2 with much success in my database. I noticed the FieldC result is not actually recorded in the table. Is it possible to record FieldC together with FieldA and FieldB when the button is pressed. I know that, for most cases, you would not need to save the result because you have all you need to calculate it in the table, but I will use this result often elsewhere and it will be very useful if it's recorded in the table.

Is there something I can add to the SQL code to record the data of FieldC in the table when the button is pressed?

Thanks.
Windows 8
Apache OpenOffice 4.1.1
kraftwerk
Posts: 9
Joined: Tue Jan 05, 2016 6:49 am

Re: Insert Sum of Two fields In The Same Record Into Third F

Post by kraftwerk »

I'm doing a follow-up on the question presented in my last post, is it at all possible to do record the calculated FieldC value in the table TblTest when button is pressed?

Thanks.
Windows 8
Apache OpenOffice 4.1.1
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Insert Sum of Two fields In The Same Record Into Third F

Post by Villeroy »

Of course it is possible (if you find out how).
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
kraftwerk
Posts: 9
Joined: Tue Jan 05, 2016 6:49 am

Re: Insert Sum of Two fields In The Same Record Into Third F

Post by kraftwerk »

Can you help? ;)
Windows 8
Apache OpenOffice 4.1.1
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Insert Sum of Two fields In The Same Record Into Third F

Post by Villeroy »

kraftwerk wrote:Can you help? ;)
No, because for various reasons this is bullshit.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
kraftwerk
Posts: 9
Joined: Tue Jan 05, 2016 6:49 am

Re: Insert Sum of Two fields In The Same Record Into Third F

Post by kraftwerk »

I am confident that your attitude does not represent most users of this forum.
Windows 8
Apache OpenOffice 4.1.1
User avatar
MTP
Volunteer
Posts: 1620
Joined: Mon Sep 10, 2012 7:31 pm
Location: Midwest USA

Re: Insert Sum of Two fields In The Same Record Into Third F

Post by MTP »

Well, first, in a database, calculated fields should not be saved in tables. The exception might be if your database was gigabytes in size and saving a calculated field made some very frequently run query take 5 minutes instead of 20 minutes.

I'm fairly confident your database is not gigabytes in size and your queries all run in fractions of a second. So you should not be saving calculated data in tables.

Second, you haven't commented on what section of the macro code or what part of the form you think needs to be changed. This silence is easily interpreted as "I haven't even tried to understand the help that was provided earlier, I just want other people to do all the work for me". Sharing the efforts you have made so far is a big motivator to volunteers that we're helping you not just with this problem, but to grow your understanding of the software so you can solve future problems on your own.
OpenOffice 4.1.1 on Windows 10, HSQLDB 1.8 split database
kraftwerk
Posts: 9
Joined: Tue Jan 05, 2016 6:49 am

Re: Insert Sum of Two fields In The Same Record Into Third F

Post by kraftwerk »

Hi MTP.

Thanks for your courteous explanation and feedback, it's very much appreciated. I am sorry if my behaviour was perceived that way because I am thankful for all the help.

(In the following text, when I say method #1 and method #2, I am refering to both methods presented by F3K Total in Tue Jan 05, 2016 1:15 pm post.)

I tried both method #1 (macro) and method #2 (no macro) and I had decided at the time to go forward with #2 as it was recommended as better by F3K Total. Method #2 is indeed very elegant.

However, I noticed the following, only yesterday, after performing further tests with Method #1.

Method #1 does write the calculated value in the table. And method #2 does not write the calculated value in the table. So #1 gives me something closer to what I was looking for. I used that code and I ended up associating it with a button click instead of a txtchange event. So the visible effect for the user resembles very much method #2.

It was hard for me to add more details than I provided in Wed Jan 06, 2016 1:58 pm post. My question was, for method #2, is there any additional SQL code I can add to save the value in the table? (and that was before I noticed that method #1 did that already). I googled around first to see if SQL can do this in Open-Base, without success. I had not much progress to report on that.

I am sorry if this was interpreted as insufficient information or borderline silence, it was not my intention.

Take care and thanks again.
Windows 8
Apache OpenOffice 4.1.1
Post Reply