I want to know how to do BAQ part UOM Conversion.
Is there a way to do this? Because I looked in the table and there is no conversion number
You’re looking for the PartUOM table and the field is ConvFactor. You can see it in Part Entry/Tracker:
2 Likes
It looks almost correct, one thing you would need to consider is conversion symbol. Do a case statement if conversion symbol is’/’ then devide otherwise multiple.
2 Likes
I’m confused. Do you have an example?
Different UOMs may require different conversion methods, depending on how your system is set up. In our case, we sometimes need to divide to get the correct UOM conversion.
CASE
WHEN UOMConv.ConvOperator = '/'
THEN OrderRel.SellingReqQty / UOMConv.ConvFactor
ELSE OrderRel.SellingReqQty * UOMConv.ConvFactor
END
4 Likes
It is complicated, since the information is stored in different places ( IUM vs PUM vs SalesUM) also in supplier price list. Depending on what you are converting to what and if the UOM on each side of the conversation is part of the UOM class or part specific.
Here is an example:
Getting the IUM from UOMconv and PartOUM 
Getting sales UOM from the customer price list:
Calculated field for the conversion…
case
when Custprice2.PriceLstParts_UOMCode = Part.IUM then 1
else (
(
case
when Custprice2.UOMConv_UOMCode = Custprice2.PriceLstParts_UOMCode
and Custprice2.UOMConv_ConvOperator = '*'
and Custprice2.UOMConv_ConvFactor > 0
then Custprice2.UOMConv_ConvFactor
when Custprice2.UOMConv_UOMCode = Custprice2.PriceLstParts_UOMCode
and Custprice2.UOMConv_ConvOperator = '/'
and Custprice2.UOMConv_ConvFactor > 0
then 1 / Custprice2.UOMConv_ConvFactor
when Custprice2.PartUOM_UOMCode = Custprice2.PriceLstParts_UOMCode
and Custprice2.PartUOM_ConvOperator = '*'
and Custprice2.PartUOM_ConvFactor > 0
then Custprice2.PartUOM_ConvFactor
when Custprice2.PartUOM_UOMCode = Custprice2.PriceLstParts_UOMCode
and Custprice2.PartUOM_ConvOperator = '/'
and Custprice2.PartUOM_ConvFactor > 0
then 1 / Custprice2.PartUOM_ConvFactor
else 1
end
) / (
case
when UOMConv1.UOMCode = Part.IUM
and UOMConv1.ConvOperator = '*'
and UOMConv1.ConvFactor > 0
then UOMConv1.ConvFactor
when UOMConv1.UOMCode = Part.IUM
and UOMConv1.ConvOperator = '/'
and UOMConv1.ConvFactor > 0
then 1 / UOMConv1.ConvFactor
when PartUOM1.UOMCode = Part.IUM
and PartUOM1.ConvOperator = '*'
and PartUOM1.ConvFactor > 0
then PartUOM1.ConvFactor
when PartUOM1.UOMCode = Part.IUM
and PartUOM1.ConvOperator = '/'
and PartUOM1.ConvFactor > 0
then 1 / PartUOM1.ConvFactor
else 1
end
)
)
end
There is probably someone who has a better way, but this works for me. Hope it helps
Edit: for clarity, you need the conversion on both sides, since the conversion factors on each side are converting to the base UOM, which may or may not be one of the ones you care about.
4 Likes
What makes it more complicated is if you are not on the tracking UOM. Like you track cloth by the foot, but you have yard you’re trying to convert to meter. You have to go from Yard → Foot → Meter. (unless I’m dense and I missed something).
2 Likes
I saw it, it was very difficult for me to do. 
Can I have this BAQ?
Certainly, hopefully seeing how it fits together than help apply the idea to your use.
FFT_PriceAnalysisCostingWB2.baq (111.6 KB)
3 Likes
This does not solve all problems, but what I do for conversions is this, in a subquery:
So I make my own conversion table.
Then I join it like so. I joined on IUM, but you might join on PUM, etc.
And convert:
I think it works well.
5 Likes
How would this work if you have part specific conversions? For instance, converting yards to square feet on a rolled good where the roll width varies from part to part.
Agreed, that’s outside the scope of my trick. We basically never use part-specific UOM, so I can get away with that method.
But I feel like there has got to be a methodology that works in all scenarios.
For example, I would think you could join PartUOM to UOMConv in the way that I joined UOMConv to itself. Maybe do both versions in separate subqueries and join the part to both, then calculate (isnull() style) which to use.
Or - I hope there is a more elegant solution than that.
I think that would work most of the time.
We do have times when we setup a PUM as one unit but have multiple suppliers and purchase in different units between them. In that case, I need to use the UOM on the supplier price list. Epicor allows us to have specific conversion factors there too… LOL!
1 Like
Thank You Your BAQ is very complex and difficult. I have never seen a BAQ like this before.
Can I have your BAQ for example?
My purpose is to know if, for example, Volume 12, 1 box = 12 PCS, there are 38.17 BOX in stock on hand. I want to do a BAQ to determine the number of boxes in hand = 38 BOX and ? PCS.
Is there a way to do this?
I have many Uom Class.
The method described by @JasonMcD plus a couple more calculations should work to get your full boxes and remainder pcs. Like pseudo-sql:
SELECT
QtyOnHand,
FLOOR(QtyOnHand) AS FullBoxes,
ROUND(QtyOnHand * CalculatedConversionRate) AS TotalPieces,
ROUND((QtyOnHand * CalculatedConversionRate) % CalculatedConversionRate) AS ModPieces
FROM yourOnHandTable
Join jasonsConversionSubQuery;
