jpol
(Judy Poling)
May 1, 2020, 8:33pm
1
I am currently trying to create a BAQ that I want it to use the following logic but don’t know the correct way to write it using the GUI interface.
If the PartTran.PONum is blank use AverageCosting, if the PartTran.PO Num is not blank use PODetail.DocUnitCost.
Do I put this in a “Calculated Field” with a Data Type of Decimal?
Any insight would be helpful…it’s Friday I think my brain already checked out for the week!
Thank you!
The PartTran table already holds the Unit Cost of the record. Are you wanting a value different than that?
Otherwise a decimal calculated field:
case when PartTran.PONum = 0 then PartCost.AvgMaterialCost else PODetail.DocUnitCost end
You will need to do all the joins to these tables though.
jpol
(Judy Poling)
May 4, 2020, 4:17pm
3
You’re a life save - thank you so much, worked like a charm!
timshuwy
(Tim Shoemaker)
May 4, 2020, 4:38pm
4
I have started using the iif statement (two spell ‘if’ with two letter i)… i do this when the if statement is results in either A or B. it is simpler than a case statement… more like an Excel IF statement in format:
iif (PartTran.PONum = 0,PartCost.AvgMaterialCost , PODetail.DocUnitCost)
you can include more complex calculations in the iif statement as well.
timshuwy
(Tim Shoemaker)
May 4, 2020, 8:52pm
6
Yes @Jason_Woods , I have been doing it for quite a while.