I'm trying to make a saved search of Purchase Order line items. One of the columns is supposed to be the total quantity received for that line item. I can get it either by using "Fulfilling/Receiving Transaction: Quantity in Transaction Units" or {fulfillingtransaction.quantityuom}. The problem with this approach is when an item was received multiple times by partial deliveries, it creates multiple lines in the saved search since the "join" is 1:many. I'd like to maintain a single line for each PO line item so I used the "Quantity Fulfilled/Received" (which is the sum of all item receipts) but it's always in base units. Is there a way to get this in transaction units?

I think you can use:

{quantityshiprecv} / ({quantity} / {quantityuom})

nice suggestion. will try this out first.

Ugh, that's a tricky one, as even though in the UI on inventory items there is

Quantity On Hand

(in stock units) and

Quantity On Hand (Base Unit)

(which you would be able to derive the stock unit's "conversion rate" from), saved search doesn't have access to

Quantity On Hand (Base Unit)

If you're fortunate to have a units naming pattern where the conversion rate is in the name of the unit (example below), then you can use this formula to get the stock units conversion rate (which you would divide(?) the quantity in base units by)

NVL(REGEXP_REPLACE({stockunit}, '[^0-9]'),1)

┌───────────┬─────────────────┐
│ UNIT NAME │ CONVERSION RATE │
├───────────┼─────────────────┤
│ Ea        │               1 │
│ Pack      │               1 │
│ Case(2)   │               2 │
│ Case(3)   │               3 │
│ Case(4)   │               4 │
│ Case(5)   │               5 │
│ Case(6)   │               6 │
│ Case(8)   │               8 │
│ Case(9)   │               9 │
│ Case(10)  │              10 │
└───────────┴─────────────────┘

Unfortunately, our naming pattern doesn't include the conversion rate. Is there really no other way?

Just checked and #C2A1ZEMF0 datasets do have access to conversion rate! However I do believe Saved Searches are unable to pull conversion rate 😩