ANS-0001 · SAVED SEARCHES & FORMULAS
How to Correct Inflated Sums in NetSuite Saved Searches
Correct inflated sums in NetSuite saved searches by applying a DISTINCT formula to manage duplicated values.
Short answer
To correct inflated sums in NetSuite saved searches caused by duplicated transaction line data, apply a specific DISTINCT formula. This formula uniquely identifies and sums each location's quantity in transit, preventing repetition and ensuring accurate aggregation of values.
Scenario
When a NetSuite saved search requires combining a sum of quantity in transit for all locations with transaction line data, a common issue arises. Including transaction lines can cause the location quantity in transit to be repeated in detail lines, leading to significantly inflated sums for the quantity in transit.
Solution
When summing the location quantity in transit, use a DISTINCT formula for the location quantities in transit plus the inventory location internal IDs divided by a very large number and round the result fewer decimals. By dividing the inventory location by a very large number, the amount added to the location quantity in transit is a very small decimal. However, the values still become unique per quantity in transit and location internal ID, and the DISTINCT function will only include each location's quantity in transit once. By rounding to several fewer decimal places, the impact of the location internal ID is removed. The formula used is: ROUND(SUM(DISTINCT {locationquantityintransit} + {inventorylocation.internalid}/100000000000000),6)
Expert NetSuite Support
Need help with this NetSuite issue?
Saved Searches & Formulas consulting and configuration support