[Solved] Adding two fields on a form

Creating tables and queries
Post Reply
Maax555
Posts: 127
Joined: Tue Mar 28, 2017 11:56 am

[Solved] Adding two fields on a form

Post by Maax555 »

So I have made some progress but again stuck with calculations on a form.
So I have the form EditPartNumberCosting, this has two fields BOMTotal and LabourCost.
I simply wish to add these two together to create a CostofSale field which will then be used to create a suggested price once a GM field is updated with a value. I have tried many ways but sadly failing. I have tried to add a CostofSale field to a new subform based on a new query.
I copied the qCalcNewLabourCost query and tried to change out the relevant parts but having trouble.
I dont fully understand the function of a field having tablename.* as used in the qCalcNewLabourCost query. Despite looking for something online that makes sense of this I am not able to fully understand it. I see that it includes all of the fields from the table in the results but why?
https://www.dropbox.com/s/9sridxqgkq0uo ... 8.odb?dl=0
Thanks
Last edited by Maax555 on Fri Oct 05, 2018 12:24 pm, edited 1 time in total.
Regards Maax
LibreOffice 6.1.4.2 on Windows 7
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Adding two fields on a form

Post by UnklDonald418 »

You have qCalcNewBOMValue that returns "BOMTotal" and qCalcNewLabourCost that returns "LabourCost"
Since Base allows you to use queries as data sources for another query, the following simple query should calculate the value you need and also bring along a key to link to the Master form.

Code: Select all

SELECT "qCalcNewBOMValue"."PartNumberID",
 "BOMTotal" + "LabourCost" AS "CostofSale" 
FROM "qCalcNewLabourCost", 
            "qCalcNewBOMValue" 
WHERE "qCalcNewLabourCost"."PartNumberID" = "qCalcNewBOMValue"."PartNumberID"
On the form document EditPartNumberCosting you have 2 forms on the SubSubForm level. Add a 3rd one using the above query as the data source and use "PartNumberID" to link the Master and Slave fields.
I dont fully understand the function of a field having tablename.* as used in the qCalcNewLabourCost query.
Perhaps, when designing the form someone wasn’t sure what fields they might need
or maybe if the query is used in another place where those extra fields might be needed
and/or it is easy to type.
If qCalcNewLabourCost is only used on LabourCostSub the following should also work

Code: Select all

SELECT "PartNumber2"."PartNumberID", 
"PartNumber2"."LabourTime" * "Rates"."RateValue" AS "LabourCost"
 FROM "PartNumber2", 
             "Rates" 
WHERE "PartNumber2"."RateID" = "Rates"."RateID"
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
Maax555
Posts: 127
Joined: Tue Mar 28, 2017 11:56 am

Re: Adding two fields on a form

Post by Maax555 »

CODE: SELECT ALL EXPAND VIEW
SELECT "qCalcNewBOMValue"."PartNumberID",
"BOMTotal" + "LabourCost" AS "CostofSale"
FROM "qCalcNewLabourCost",
"qCalcNewBOMValue"
WHERE "qCalcNewLabourCost"."PartNumberID" = "qCalcNewBOMValue"."PartNumberID"
On the form document EditPartNumberCosting you have 2 forms on the SubSubForm level. Add a 3rd one using the above query as the data source and use "PartNumberID" to link the Master and Slave fields.
This is exactly what I tried to do but obviously failed. Quite annoying as I feel i should have been able to achieve this without help at this stage.
I will copy my form and try to do it again without referring to your above working answer. Hopefully I can then see where i went wrong.

Many thanks
Regards Maax
LibreOffice 6.1.4.2 on Windows 7
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: [SOLVED] Adding two fields on a form

Post by UnklDonald418 »

Perhaps you were experiencing the same issue that I've been seeing.
I tried another query that is based on tables, not other queries.

Code: Select all

SELECT "CV"."PartNumberID",
       "CV"."BOMTotal" + "CV"."LabourCost" AS "CostofSale" 
FROM ( SELECT "PartNumber2"."PartNumberID",
              "PartNumber2"."LabourTime" * "Rates"."RateValue" AS "LabourCost",
              "PartNumber2"."BOM" * "Percentage"."PercVal" AS "BOMTotal" 
       FROM "PartNumber2" 

       JOIN "Rates" 
         ON "PartNumber2"."RateID" = "Rates"."RateID" 
       JOIN "Percentage" 
         ON "PartNumber2"."BOMAddRateID" = "Percentage"."PRateID"
      ) "CV"
When I run this query it works fine, so I used it as a data source on a form.
Oddly, when I open the form I get a Column not found exception.
If I close the database and reopen it with OpenOffice Base, the form works perfectly.
It appears there may be a bug in LibreOffice Base that might explain your failure.
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
Post Reply