At work, I was recently tasked with pulling the total weight of a Sales Order from NetSuite. We were going to use this value to send to carriers, in order to get an accurate rate, and for the label when shipping.

Sales Order Lines

Let’s start by querying the table that has all the order information to get a list of items in the Sales Order.

SELECT
	salesOrderLine.uniqueKey
FROM Transaction salesOrder
LEFT JOIN transactionLine salesOrderLine
	ON (salesOrder.id = salesOrderLine.transaction)
WHERE 
(salesOrder.type = 'SalesOrd') AND
(salesOrderLine.ItemType IS NOT NULL) AND
(salesOrderLine.ItemType IN ('InvtPart','Assembly')) AND
(salesOrderLine.TaxLine = 'F') AND
(salesOrderLine.assemblyComponent = 'F') AND
(salesOrder.tranid = 'SO45554')

Let’s break down the query to help you better understand further.

LEFT JOIN transactionLine salesOrderLine
	ON (salesOrder.id = salesOrderLine.transaction)

This portion of the query joins together the items on the Sales Order. All Transaction Lines are stored on the transactionLine table.

(salesOrderLine.ItemType IS NOT NULL) AND
(salesOrderLine.ItemType IN ('InvtPart','Assembly')) AND
(salesOrderLine.TaxLine = 'F') AND
(salesOrderLine.assemblyComponent = 'F') AND

In the WHERE clause, we have added a few lines here. These lines are used to filter out the assembly components, taxes, discounts, etc. Anything that is not an item in our Sales Order.

Sales Order Item Weights

Now, we are going to join together a few more items in order to get the weight of all the items in our Sales Order.

SELECT
	salesOrderLine.uniqueKey,
	item.weightunits,
	item.weight,
	itemunit.name as unit,
	ABS(salesOrderLine.quantity) as quantity
FROM Transaction salesOrder
LEFT JOIN transactionLine salesOrderLine
	ON (salesOrder.id = salesOrderLine.transaction)
LEFT JOIN item
	ON (salesORderLine.item = item.id)
LEFT JOIN itemunit
	ON (item.weightunit = itemUnit.key)
WHERE 
(salesOrder.type = 'SalesOrd') AND
(salesOrderLine.ItemType IS NOT NULL) AND
(salesOrderLine.ItemType IN ('InvtPart','Assembly')) AND
(salesOrderLine.TaxLine = 'F') AND
(salesOrderLine.assemblyComponent = 'F') AND
(salesOrder.tranid = 'SO45554')

We JOIN on the Item table to get the weight of the item from our transaction line, and JOIN on the ItemUnit table in order to get the name value of our unit. Without the ItemUnit table, the unit column will just be an integer value of our unit.

Running this query, the results may look something like this:

This query should allow you to then process your weights accordingly. I’m sure it’s possible to modify the entire query to get the gram value of the weight of your order, but I did not need to go that far.

Example

To give you an idea on how you can use this to get the total weight of the order, I’ve provided an example I wrote in C#.

public class NetSuiteWeight
{
    public string UniqueKey;
    public string WeightUnits;
    public decimal Weight;
    public string Unit;
    public int Quantity;
}

private function GetTotalOrderWeight(List<NetSuiteWeightObject> weights) {
	decimal totalWeight = 0;
	
	foreach (NetSuiteWeightObject weight in weights)
	{
	    switch (weight.Unit)
	    {
	        case "g":
	            totalWeight += weight.Weight * weight.Quantity;
	            continue;
	        case "lb":
	            // Convert lb to grams
	            totalWeight += (decimal) (weight.Weight / (decimal)0.0022) * weight.Quantity;
	            continue;
	    }
	}
	return totalWeight;
}

List<NetSuiteWeightObject> weights would come from the results of your query. I use JsonConvert.DeserializeObject to convert the JSON returned by NetSuite.