Scenario
A user is attempting to display the item rate field in a sales invoice saved search in NetSuite but is encountering issues due to the absence of a direct 'Rate' field and encounters error when attempting to derive this using a formula instead. The user encounters the error, "There is a divide by zero error in this search. It may be an error with a formula you have used. Please retry without the formula(s)…"
Solution
To display the item rate in a saved search, use a formula to calculate the rate by dividing the amount by the quantity. However, to avoid errors caused by division by zero, use the following approach:
- Edit the Search
- Replace the formula denominator with: NULLIF({quantity}, 0)
- Example Formula:
Formula (Numeric) = {amount} / NULLIF({quantity}, 0) - Click Save
Additional Notes
- The most common error occurs when the quantity is zero, leading to a division by zero error. The NULLIF function prevents this by substituting a null value if the quantity is zero.
- Ensure that all records being evaluated have valid quantity and amount values to prevent unexpected results.
Conclusion
By applying the above formula, users can effectively display the item rate in sales invoice saved searches without encountering division by zero errors, thereby enhancing search accuracy and reliability.
