I am writing a BAQ to summarize all materials used in a quote. All the materials are found in the QuoteMtl table, but sometimes the same material is used with different UOMs. I my case a sheet of steel is either specified in SHEETS or FT^2. I need to convert the EstUnitCost to base units and then to the quoted units using formula EstUnitCost / BaseConversionFactor * QuotedUnitsConversionFactor. I can get this to work in SQL, but not using the BAQ editor. Has anyone done this in a BAQ, do you have an example or steps of how it was done?
@mchernecki A calculated field is basically sql commands. What is the calc you have tried and what error did you get? Sometime it is as simple as wrapping the calculation in parentheses.
1 Like
Your example makes sense, but I am getting all the conversion factors for every UOMClass and divide by zero errors. Can you give an example of using the conversion on the unitcost? maybe I am doing the final setup wrong.
I have looked at this sample code a bit more and the UOMConv only shows the standard conversions from the base UOM, anything non standard is zero and is in the PartUOM table. As an example, one part needs to be converted from the base FT^2 to SHEETS (40 FT^2). The problem is UOMConv doesn’t have the needed conversion and the Part UOM doesn’t have the base. I can get the list of all conversions by doing a union between UOMConv and PartUOM, the problem I found is BAQ designer will only allow unions with the top level query and not between 2 subqueries. Any other ideas would be appreciated.
Hi Mike,
Have a look at the below, it converts the on-hand qty in IUM to the SalesUM from OrderRel. Similar to what you’re looking for - first it converts IUM to BaseUM and then BaseUM to SalesUM.
One subquery for the conversion (you can probably get rid of the part table there):
with the following joins:
Part.Company = UOMClass.Company
Part.UOMClassID = UOMClass.UOMClassID
UOMClass.Company = UOMConv.Company
UOMClass.UOMClassID = UOMConv.UOMClassID
Part.Company = PartUOM.Company
Part.PartNum = PartUOM.PartNum
UOMConv.Company = PartUOM.Company
UOMConv.UOMCode = PartUOM.UOMCode
You will have 3 calculated fields there:
ConvFactor = (case when PartUOM.ConvFactor is null then UOMConv.ConvFactor else PartUOM.ConvFactor end)
ConvOperator = (case when PartUOM.ConvOperator is null then UOMConv.ConvOperator else PartUOM.ConvOperator end)
NewFactor = (case when ConvOperator=’/’ then (1/ConvFactor) else ConvFactor end)
Then, in your main query you will use the subquery above 2 times, just like mentioned above (ignore everything except tables 1, 13 & 14, it’s an old query back from E9):
IUM to BaseUM:
BaseUM to SalesUM:
Finally, to get the qty’s in the new UOM, use this as a formula in your main query:
NewQty = (PartWhse.OnHandQty) * UOMConv1.Calculated_NewFactor * (1/UOMConv2.Calculated_NewFactor)
PartWhse.OnHandQty is in IUM while the NewQty is in SalesUM.
I hope that makes sense, it’s been a while since I worked on this but I know it works.
Dragos
Ah, that’s interesting, we don’t really have Part-specific UOM conversions (occasionally for a PO for “pairs” of safety gloves that are really an “each” of a set, but I digress).
Sorry, I was on PTO the last several days. Let us know how it goes.
@Dragos I see you snuck into the forum yesterday! Welcome to the group. You were always a great help on that other site…
Hi Dragos,
Thanks for the information. I had some issues with the UOMConv, joining Part to PartUOM and then UOMConv to PartUOM. the first join is fine, but the second join doesn’t work out in BAQ designer, the join is missing the ON keyword and replaced with an AND
@JasonMcD thank you, it’s good to be here!
@mchernecki that’s weird, looking at your version and at the image I think something went wrong with your query. Maybe try to start from scratch ? Or if you have a backup of it somewhere.
I have them as simple joins, nothing special about them:
LE: mine looks the same and I don’t have any errors:
Dragos
Hi Dragos,
All good, I redid the BAQ from scratch and now it works. Thanks for your help.
1 Like