Search code examples
formulanetsuite

NetSuite Results where one field is True and the other is False that it returns a result of ISSUE

I have a search that has a number of different criteria.

In the reuslt si need to have a formula that shows what actual criteria is the issue (there is too many to use highlighting as a transaction may have multiple of the issue) Its essentially comparing the value of 2 checkbox fields.

I need to be able to have Closed = FALSE Other field = TRUE then show "ISSUE" otherwise show "OK"

I need to use this to compare a few checkboxes and i am struggling with this one (all my other formulas are working and this is the only one i cant get working.

I tried varying versions of below and was using a formula text field, b ut i am clearly getting something wrong somewhere and well and truly stuck.

CASE WHEN {closed} IS 'F' AND {otherfield} IS 'T' THEN 'ISSUE' ELSE 'OK'
CASE WHEN {closed} = 'F' AND {otherfield} = 'T' THEN 'ISSUE' ELSE 'OK'
CASE WHEN {closed} = 'FALSE' AND {otherfield} = 'TRUE' THEN 'ISSUE' ELSE 'OK'
CASE WHEN {closed} = FALSE AND {otherfield} = TRUE THEN 'ISSUE' ELSE 'OK'
CASE WHEN {closed} IS 'FALSE' AND {otherfield} IS 'TRUE' THEN 'ISSUE' ELSE 'OK'
CASE WHEN {closed} IS FALSE AND {otherfield} IS TRUE THEN 'ISSUE' ELSE 'OK'
Solution

  • You have a syntax error - all CASE statements must end with END.

    CASE WHEN {closed} = 'F' AND {otherfield} = 'T' THEN 'ISSUE' ELSE 'OK' END

    Also, when struggling with issues like this, it often helps to include the field you're testing in the Results and see what values you get. You can then use those values in your formula. In this case, for boolean values as {closed} is, NetSuite returns Yes/No (which probably changes for accounts set to other than English). But this only applies when you add it via the dropdown. When you add it as a formula ( Field = Formula (Text) > Formula = {closed} ) then you see the result is 'F'.

    CASE WHEN {closed} = 'F' THEN 'ISSUE' ELSE 'OK' END

    This works, so it now just needs the {otherfield} integrated into the formula. You can test the results of {otherfield} in the same way, by including it by itself as a Result field, and by adding the same as above, just substituting the field, and if necessary the value:

    CASE WHEN {otherfield} = 'T' THEN 'ISSUE' ELSE 'OK' END

    If both give you the expected results, then you can combine them to arrive at your desired result, ending up where we started at the top:

    CASE WHEN {closed} = 'F' AND {otherfield} = 'T' THEN 'ISSUE' ELSE 'OK' END