In NetSuite, tracking back-ordered quantities directly from sales orders within a saved search can be challenging, as the back-ordered quantity isn’t directly available in the search results. However, a formula can be implemented to effectively derive the back-ordered quantity for items in sales orders. Here’s a step-by-step approach to using this formula in your saved search.
Understanding the Formula
To obtain the back-ordered quantity, we need to calculate it based on three fields in the sales order:
- {quantity}: Total quantity ordered for an item
- {quantityshiprecv}: Quantity fulfilled or shipped
- {quantitycommitted}: Quantity committed for shipping or receiving
To obtain the back-ordered quantity, select the formula field in the Results of the Saved search, Then use the formula:
CASE WHEN {quantity} > NVL({quantityshiprecv},0) + NVL({quantitycommitted},0)
THEN NVL({quantity},0) – NVL({quantitycommitted},0) – NVL({quantityshiprecv},0)
ELSE 0 END