CODEWITHSUNDEEP
netsuite
David
David

Reputation: 17

NetSuite get transactions that do not contain items with specified attributes

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!

Upvotes: 0

Views: 1689

Answers (2)

CraigyD
CraigyD

Reputation: 36

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).

Upvotes: 1

bknights
bknights

Reputation: 15402

Does the line being returned have a freight value or are you getting another line from the same order?

Upvotes: 0

Related Questions