SuiteQL: Units Of Measure

Introduction

When we make any SuiteQL query to NetSuite that contains an item’s quantity, we have to make sure that it returns the quantity in the correct units of measure and if not, we have to convert it. Some fields such as costs are also stored in the base units of the items primary units type.

This post will discuss how units are stored in NetSuite and how we can convert some common queries into other units of measure. There are two tables UnitsType and UnitsTypeUom.

UnitsType

The Unitstype table is for the class of units of measure. For example, “Length” or “Weight”. On some implementations this is listed as “Pounds (LB)”.

This is a common mistake but won’t affect the way our query will run.

Let’s look at the main columns in the UnitsType table:

Column	Description	Type
id	Internal ID of the unit of measure class	Number
name		The name of the unit of measure class	String

That’s it. There are other columns but they don’t really matter for our purposes. It’s just a way for NetSuite to group units of measure together.

UnitsTypeUom

This table contains the actual units of measure. A column unitstype in this table references the UnitsType table.

Here are the main columns in the UnitsTypeUom table:

Column	Description		Type
internalid	Internal ID of the unit of measure		Integer
unitstype	Internal ID of the unit of measure class		Integer
unitname	The name of the unit of measure		String
conversionrate	The conversion rate to the base unit of measure		Decimal
baseunit	Indicates whether this is the base unit of measure for the unit of measure class	Boolean

So, for example, if we have a units type of “Weight” and two units of measure “Pounds” and “Kilograms” the UnitsType table might look like this:

id	name
1	Weight

and the UnitsTypeUom table might look like this:

internalid	unitstype	unitname	conversionrate	baseunit
1	1	Pounds	1	true
2	1	Kilograms	2.204	false

Transactions

See this post for more information on how to query transactions.

Suppose we have a purchase order with the following lines:

Item	Quantity	Units
Item 1	10	KG

The primary units type on Item 1 in Pounds and the purchase unit is Kilograms with a conversion rate of 2.204.

Suppose we write the following query:

SELECT
Transaction.tranid,
BUILTIN.DF(Transactionline.item) AS item,
TransactionLine.quantity,
BUILTIN.DF(TransactionLine.unit) AS unit
    FROM Transaction
    INNER JOIN TransactionLine ON TransactionLine.transaction = Transaction.id
WHERE transaction.tranid = 'PO1000'

The results that will be returned are:

tranid	item	quantity	unit
PO1000	Item 1	22.04	KG

This seems odd. SuiteQL is returning the quantity in Pounds but it recognizes that the unit of measure on the Transaction is Kilograms. This is because when it comes to the TransactionLine table NetSuite will always store the quantity in the base unit of measure for the units type of the item.

If we want to get the quantity in the units of measure on the transaction, in this case Kilograms, we have to convert it. To do that we must join the UnitsTypeUom table to the TransactionLine table and use the conversion rate to convert the quantity to the correct units of measure.

SELECT
Transaction.tranid,
BUILTIN.DF(TransactionLine.item) AS item,
TransactionLine.quantity / UnitsTypeUom.conversionrate AS quantity,
BUILTIN.DF(TransactionLine.unit) AS unit
    FROM Transaction
        INNER JOIN TransactionLine on TransactionLine.transaction = Transaction.id
        INNER JOIN Unitstypeuom on Unitstypeuom.internalid = TransactionLine.unit
WHERE transaction.tranid = 'PO1000'

transactionline.quantity is 22.04 and UnitsTypeUom.conversionrate is 2.204 so the quantity will be \(\frac{22.04}{2.204} = 10\)

Now the query will return:

tranid	item	quantity	unit
PO1000	Item 1	10	KG

As expected.

Getting the Quantity on a Transaction in the Purchase Order Units

If instead of getting the quantity in the units of measure on the transaction and instead we want the Purchase Units listed on the item record, we would write a similar query.

The Primary Purchase Units of Measure is stored on the Item record in the column purchaseunit.

SELECT Transaction.tranid,
BUILTIN.DF(TransactionLine.item) as item,
TransactionLine.quantity / UnitsTypeUom.conversionrate as quantity,
BUILTIN.DF(Transactionline.unit) as unit

    FROM Transaction
        INNER JOIN TransactionLine on TransactionLine.transaction = Transaction.id
        INNER JOIN Item on Item.id = TransactionLine.item
        INNER JOIN Unitstypeuom on Unitstypeuom.internalid = Item.purchaseunit

Where transaction.tranid = 'PO1000'

This will convert the quantity to the units of measure listed on the item record in the field “Primary Purchase Unit”.

Bills of Materials Units

Querying Bills of Materials components is discuessing in this post.

The quantity on the Bom table is stored differently than the quantity on the transactionline table. The quantity on the Bom table is stored in the unit of measure listed on the bom; no conversion is done when the bom is created.

Sometimes we want the quantity of the bom in the base unit of measure for the units type of the item.

Let’s look at an example.

Suppose my BOM looks like this:

Item	Quantity	Unit
Item 1	10	KG

In this case the Item has a primary units type of “Weight” and the unit of measure on the BOM is “Kilograms” with a quantity of 10. We want our query to return the quantity in Pounds.

To do this we have to join the unitstype table and then join whatever units of measure is the base unit for that item type.

SELECT
BUILTIN.DF(BomRevisionComponentMember.item) as item,
BomRevisionComponentMember.bomquantity * UnitsTypeUom.conversionrate as quantity,
UnitsTypeUom.unitname

FROM  BomRevisionComponentMember
    INNER JOIN Item on Item.id = BomRevisionComponentMember.item
    INNER JOIN UnitsType on UnitsType.id = item.unitstype
    INNER JOIN UnitsTypeUom on UnitsTypeUom.unitstype = unitstype.id and UnitsTypeUom.baseunit = 'T'
WHERE BomRevisionComponentMember.BomRevision = 1

This will return the quantity in the base unit of measure for the units type of the item.

Standard Cost

Even if you aren’t querying the quantity of an item, you may still need to convert it using the UnitsTypeUom table.

Standard costs, which can be found on the AggregateItemLocation table, are stored in the base unit of measure for the units type of the item. So if you want to get the standard cost in the purchase units of measure you will need to convert it:

Select
Item.itemid,
AggregateItemLocation.currentstandardcost / UnitsTypeUom.conversionrate as standardcost,
UnitsTypeUom.unitname
from item inner join AggregateItemLocation on AggregateItemLocation.item = item.id
inner join UnitsTypeUom on UnitsTypeUom.internalid = item.purchaseunit
where item.itemid = 'Item 1'

Conclusion

Whenever using multiple units of measure in an account care must be taken to make sure that whenever you query a quantity or a cost that you are getting the correct units of measure. Some records store the quantity in a unit entered on a record, some records store the quantity in the base unit.

Only trial and error will tell you which is which. Once you know how the quantity is stored on a record converting using suiteql is straightforward.