NetSuite contains a Unit of Measure (UOM) feature that allows one to specify various unit types. Each unit type consists of a base unit and as many other units of the target type as required. For example, given a unit type “Liquids”, we could choose Gallons as our base unit and include other liquid units like Barrels each having a conversion rate to the base unit. Unit conversions in SuiteScript have traditionally been a challenge as relevant information is not exposed in the N/search API. Gladly, SuiteQL provides a means to realize unit conversions in code. This article provides some sample code to help you the job done.
While NetSuite can automatically apply unit specifications to perform conversions between units of the same type on transactions, replicating this behavior in the script without hardcoding the conversion factor has been traditionally challenging. The reason is that, although NetSuite exposes a Unit Type saved search, there is no way to search for a particular unit by its internal ID because that field is not exposed. This appears to be an omission in the API as the units have internal IDs; they are simply not accessible.
Thus, when automated conversion between units is required, developers either take the potentially dangerous route of driving conversions based on unit names (which might not be unique or may change over time, leading to interesting bugs) or they simply bypass the units of measure table altogether and hardcode the desired conversion rates which produce duplication, limits scalability, and somewhat defies the purpose of having a UOM system in the first place.
Best Method
The SuiteQL data model (accessible in script via the N/query module) can be used to solve this problem as the Unit Types table contains the missing information.
Here’s a sample query to convert between two units using SuiteQL. Essentially, we leverage the base unit as a common denominator to enable conversion from the source to the target unit.
function getUnitConversionRate(uom) {
// We use SuiteQL as the UnitType saved search does not allow searching
// for conversion rates by UOM id (only supports parent unit type id)
var queryResults = query.runSuiteQL({
query:
'SELECT uom.unitname, uom.conversionrate ' +
'FROM unitsType ' +
'INNER JOIN unitsTypeUom as uom ' +
'ON (unitsType.id = uom.unitstype) ' +
'WHERE uom.internalid = ' + uom // Remove if you want the full UOM conversions table
}).asMappedResults();
var uomToBaseConversionRate;
if (queryResults.length === 1) {
uomToBaseConversionRate = queryResults[0].conversionrate;
}
return uomToBaseConversionRate;
}