Scenario
A user created an Item Fulfillment Saved Search and attempted to use formulas to calculate Sales Order rates and Item Fulfillment amounts using fields sourced from the related Sales Order transaction. Although the formulas were configured correctly, the results returned 0 in the search preview. The user needed to determine why the formulas were not working and how to correctly retrieve the related transaction values.
Solution
Based on testing, the Created From Quantity field cannot be sourced correctly using an Item Fulfillment Saved Search.
To retrieve the required data successfully, create a Sales Order Saved Search instead.
Criteria Tab
Add the following criteria:
- Type is Sales Order
- Main Line is False
- Applying Transaction : Type is Item Fulfillment
Results Tab
Use grouped results and formulas similar to the following setup:
- Date : Group
- Name : Group
- Document Number : Group
- Item : Count
Formula for Sales Order Rate
- Summary Type: Sum
- CASE WHEN {quantity} != 0 THEN {amount} / {quantity} ELSE 0 END
Formula for Item Fulfillment Rate
- Summary Type: Sum
- CASE WHEN {quantity} != 0 THEN ({amount} / {quantity}) * {applyingtransaction.quantity} ELSE 0 END
You may also add Item Fulfillment related fields from Applying Transaction Fields and remove unnecessary columns as needed.
Grouping the results is required because the quantity values are sourced at the line item level rather than the overall transaction level. If a Sales Order contains multiple items, each item appears on a separate row, which is why summary grouping is necessary.
-----
If you find this information useful, let us know by reacting or commenting on this post!
