NetSuite get transactions that do not contain items with specified attributes

Question

I am attempting to create a list of open pending approval sales orders that do not contain items with specific values defined in a custom field. I am able to do this when the sales order contains only items that meet this criteria. However, when their are two items and one meets while the other does not my search is no longer valid.

I have two sales orders. Sales order 123 has a shipping method of Ground, while Sales order 321 has an item with Shipping method of Ground and shipping method of Freight. I expect to get only Sales order 123 returned.

I made this formula in criteria section:

CASE WHEN {item.custitem_shippingmethod} = 'Freight' Or {item.custitem_shippingmethod} = 'Free Freight' THEN 1 ELSE 0 END

but got both orders returned. I tried using the same formula in the summary criteria but that also did not work. Any suggestions?

Picture of Criteria in NetSuite

Thank you!

You mention that order 321 has two lines, of which one line does match the criteria. Your search should return order 321 for each line that is found with the matching criteria, since "Main Line" is set to false. In your case, is order 321 returned once in your results? — Charl
Your search essentially returns each order that has at least one line which does match your criteria. — Charl
How do you intend to use these results, do you only want this saved search to be available in the UI or will you be using the results in any scripts? — Charl
We are using these results to auto-approve Marketplaces sales orders that do not have freight items in them. Freight requires additional steps and confirmations with customers while ground items do not. This search is being used within a work-flow. — David

CraigyD CraigyD · Accepted Answer · 2018-04-24T15:10:16

You could potentially use summary criteria. It's practical but it's not the cleanest looking search. You need to have a corresponding formula column in your results for it to work:

Group by Document Number.
Create a formula (Numeric) result column with summary type of Sum using your above formula.
Create a summary criteria of type formula (Numeric) with summary of type Sum and use the same formula and set the value to be less than 0.

This will return only records that do not include those shipping methods.

Alternatively, have you considered running the logic (workflow/suitescript) when the record is saved and storing a checkbox value such as "Does not include freight"? It would make searches based on that criteria easier.

For example if you store the ship method on the line, something like:

// Set your freight method indexes

var freightMethods = ['1','2']


var itemLinesCount = nlapiGetLineItemCount('item');


// If a line is found with one of the freight methods you're looking for then mark the record.

for(var i = 1; i < itemLinesCount; i++)
{
  var shipMethod = nlapiGetLineItemValue('item', 'custcol_shipmethod', i);

  if(freightMethods.indexOf(shipMethod) !== -1)
  {
    nlapiSetFieldValue('custbody_includes_freight', 'T');
    break;
  } 
}

If you store the ship method only on the item record it can be a bit trickier to manipulate (due to the way Netsuite handles item record types).

NetSuite get transactions that do not contain items with specified attributes

2 Answers