[Solved] Adding two fields on a form

Creating tables and queries

[Solved] Adding two fields on a form

Postby Maax555 » Thu Oct 04, 2018 4:51 pm

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/9sridxqgkq0uorg/Project%20Tracking%20Test%20V1.28.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
Maax555
 
Posts: 127
Joined: Tue Mar 28, 2017 11:56 am

Re: Adding two fields on a form

Postby UnklDonald418 » Thu Oct 04, 2018 7:38 pm

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   Expand viewCollapse 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.

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   Expand viewCollapse view
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.6 & LibreOffice 6.1.5.2 - Windows 10 Professional
UnklDonald418
Volunteer
 
Posts: 1224
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Adding two fields on a form

Postby Maax555 » Fri Oct 05, 2018 11:05 am

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
Maax555
 
Posts: 127
Joined: Tue Mar 28, 2017 11:56 am

Re: [SOLVED] Adding two fields on a form

Postby UnklDonald418 » Fri Oct 05, 2018 6:26 pm

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   Expand viewCollapse view
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.6 & LibreOffice 6.1.5.2 - Windows 10 Professional
UnklDonald418
Volunteer
 
Posts: 1224
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA


Return to Tables & Queries

Who is online

Users browsing this forum: No registered users and 3 guests