TL;DR
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.
Challenge
While NetSuite can automatically apply unit specifications to perform conversions between units of the same type on transactions, replicating this behavior in 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.
Tip: The Units of Measure feature needs to be turned on via
Setup >> Company >> Enable Features >> Multiple Units of Measureafter which the list will be accessible viaLists >> Accounting >> Units of Measure.
Here’s an illustration of the issue.
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 produces duplication, limits scalability, and somewhat defies the purpose of having a UOM system in the first place.
A Better Way
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 the Units Type definition from the NetSuite Records Catalog (Customization >> Records Catalog). This is the data model that the N/query module uses. As you can see, we have access to the internal ID of the units. Thus, we can write SuiteQL queries to perform conversions!
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.
Note: This is a naive implementation for illustration purposes e.g. we perform a conversion even if both units are the same, there’s no error handling, etc.
/**
*@NApiVersion 2.0
*@NScriptType UserEventScript
*/
define(['N/query'],
function(query) {
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;
}
function beforeSubmit(context) {
var rec = context.newRecord;
var uom1rate = getUnitConversionRate(rec.getValue('my_first_uom_field_id'));
var uom2rate = getUnitConversionRate(rec.getValue('my_second_uom_field_id'));
var targetRate = parseFloat(uom1rate) / parseFloat(uom2rate);
}
return {
beforeSubmit: beforeSubmit
}
});
Notice that the above approach of running a separate query to get each unit conversion rate is wasteful. A more elegant approach is illustrated below. Basically, we’re passing all the UOMs of interest and getting the conversions between them which we store in a map for use as needed.
/**
*@NApiVersion 2.0
*@NScriptType UserEventScript
*/
define(['N/query'],
function(query) {
function getUnitConversionRates(uoms) {
var conversionRatesByUom = {};
var queryResults = query.runSuiteQL({
query:
'SELECT uom.internalid, uom.unitstype, uom.baseunit, uom.conversionrate ' +
'FROM unitsType ' +
'INNER JOIN unitsTypeUom AS uom ON (unitsType.id = uom.unitstype) ' +
'WHERE unitsType.id IN (' +
// Inner query gets only the unit types represented by the input UOMs to limit the dataset
'SELECT DISTINCT ut2.id ' +
'FROM unitsType AS ut2 ' +
'INNER JOIN unitsTypeUom As uom2 ON (ut2.id = uom2.unitstype) ' +
'WHERE uom2.internalid in (' + uoms.join(',') + ')' +
')'
}).asMappedResults();
var unitsTypes = [];
queryResults.forEach(function (result) {
conversionRatesByUom[result.internalid] = result.conversionrate;
if (unitsTypes.indexOf(result.unitstype) < 0) {
unitsTypes.push(result.unitstype);
}
});
// Prevent meaningless conversions.
if (unitsTypes.length > 1) {
throw 'Invalid UOM conversion request between different unit types: ' + JSON.stringify(unitsTypes)
+ '. Conversions must be between units of the same type; otherwise, the results will be meaningless.'
}
return conversionRatesByUom;
}
function beforeSubmit(context) {
var rec = context.newRecord;
var uom1 = rec.getValue('my_first_uom_field_id');
var uom2 = rec.getValue('my_second_uom_field_id');
var conversationRatesByUom = getUnitConversionRates([uom1, uom2]);
var uom1rate = conversationRatesByUom[uom1];
var uom2rate = conversationRatesByUom[uom2];
var targetRate = parseFloat(uom1rate) / parseFloat(uom2rate);
}
return {
beforeSubmit: beforeSubmit
}
});
Supporting Conversions Between Different Unit Types
NetSuite’s Units of Measure feature does not support conversions across unit types. For example, there is no way to specify a conversion from a weight unit (e.g. pounds) to a volume unit (e.g. barrels). It is important to note that, while the system will not prevent you from performing such conversions using functions similar to the ones illustrated above, the outputs will be wrong! Thus, in the second code sample above, we’ve added a sanity check in lines 36-39 to prevent such meaningless conversions.
Nevertheless, there are situations where conversions across unit types are required. Recently, while working with a client in the waste management industry, I needed to convert pounds to barrels or gallons. The client provided a custom formula and I built a conversion using SuiteScript. This was liberating for the client as their previous ERP limited them in this area. The details of the solution are beyond the scope of this article. Reach out if you have such advanced use cases and we’ll be glad to assist you.
SuiteQL is the Future
While I don’t believe that saved searches are going anywhere anytime soon (although they are now being considered end-of-life), NetSuite developers and business analysts will do themselves well by learning the basics of SQL and starting to use SuiteQL. As illustrated in this and other articles, SuiteQL may be the only way to address a previously impossible challenge. As of the time of writing, NetSuite has not released any tools for executing queries. However, the community has stepped in to fill that gap. Check out Tim Dietrich’s SuiteQL Query Tool (free) and Prolecto’s NetSuite SQL Query Tool (free for customers).
Related Posts
NetSuite Insights is proud to partner with Prolecto Resources Inc. – the unrivaled #1 NetSuite Systems Integrator and thought leader in the space! Learn more about how Prolecto can supercharge your NetSuite experience and deliver the best return on your NetSuite investment.
NetSuite Insights is on a mission to raise the standards around NetSuite practices, one insight at a time. If that resonates with you, learn how you can become an author/collaborator here.
Don’t miss a beat – subscribe to our no-nonsense email list to have these game-changing insights hit your inbox as soon as they’re published. Ignorance? Nah, you’ve got a smarter option. Choose wisdom, choose insights!
Awesome