Page 1 of 1

[Solved] Adding two fields on a form

Posted: Thu Oct 04, 2018 4:51 pm
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

Re: Adding two fields on a form

Posted: Thu Oct 04, 2018 7:38 pm
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"

Re: Adding two fields on a form

Posted: Fri Oct 05, 2018 11:05 am
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

Re: [SOLVED] Adding two fields on a form

Posted: Fri Oct 05, 2018 6:26 pm
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.