Search code examples
casenetsuiteresultsetcase-whensaved-searches

Nested CASE WHEN Formula(text) in Saved Search Results in NetSuite

I have two fields _courier and _trackingnumber. I would like to create in results nested CASE WHEN formula (text) under the following conditions:

  1. the courier is = 'NAME' and
  2. if the _trackingnumber of this courier does not start with 0 then add 00 else add _trackingnumber &

I have tried this CASE WHEN option and I realize that AND function does not exist in Functions list in Netsuite

CASE WHEN {_courier} = 'NAME' AND ({_trackingnumber},'0'THEN ('00' || {_trackingnumber}) ELSE ('&' || {_trackingnumber}) END

And I got this error message:

I got this error: ERROR: Invalid Expression

Can you please advise how to fix it?

Solution

  • Try this:

    CASE WHEN {_courier} = 'NAME' AND SUBSTR({_trackingnumber},1,1) <> '0' THEN ('00' || {_trackingnumber}) ELSE ('&' || {_trackingnumber}) END