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
[Solved] Adding two fields on a form
[Solved] Adding two fields on a form
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
LibreOffice 6.1.4.2 on Windows 7
-
- Volunteer
- Posts: 1549
- Joined: Wed Jun 24, 2015 12:56 am
- Location: Colorado, USA
Re: Adding two fields on a form
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.
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.
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
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"
Perhaps, when designing the form someone wasn’t sure what fields they might needI dont fully understand the function of a field having tablename.* as used in the qCalcNewLabourCost query.
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
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
Re: Adding two fields on a form
CODE: SELECT ALL EXPAND VIEW
SELECT "qCalcNewBOMValue"."PartNumberID",
"BOMTotal" + "LabourCost" AS "CostofSale"
FROM "qCalcNewLabourCost",
"qCalcNewBOMValue"
WHERE "qCalcNewLabourCost"."PartNumberID" = "qCalcNewBOMValue"."PartNumberID"
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.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 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
LibreOffice 6.1.4.2 on Windows 7
-
- Volunteer
- Posts: 1549
- Joined: Wed Jun 24, 2015 12:56 am
- Location: Colorado, USA
Re: [SOLVED] Adding two fields on a form
Perhaps you were experiencing the same issue that I've been seeing.
I tried another query that is based on tables, not other queries.
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.
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"
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
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11