This seems like it should be simple, but for some reason I can't get this to work... I am trying to build a transaction saved search that shows the user (person viewing the search) all transfer orders that have their location in either the {location} field or {transferlocation} field. Here is the formula (Numeric) I am adding to the criteria and setting is equal to 1:
CASE WHEN {location} = {user.location} THEN 1 ELSE 0 END
Seems pretty simple, but this formula errors out. I have also tried this and it doesn't error, but I get 0 results:
CASE WHEN {location} LIKE {user.location} THEN 1 ELSE 0 END
Once I get this to succeed I should be able to either add to this formula or add a new formula criteria for the {transferlocation}. Either way, any suggestions would be great. Below I will add all criteria/results/filters just for transparency:
Criteria:
Main Line is True
Type is Transfer Order
Status is any of Transfer Order:Partially Fulfilled, Transfer Order:Pending Approval, Transfer Order:Pending Fulfillment, Transfer Order:Pending Receipt, Transfer Order:Pending Receipt/Partially Fulfilled
(not working) Formula (Numeric) is 1 CASE WHEN {user.location} = {location} OR {user.location} = {transferlocation} THEN 1 ELSE 0 END
Results:
Date Created
Document Number
Location
To Location
Filters:
Date Created
Most likely due to NetSuite perceiving the possibility of data type differences. Try wrapping your location fields in NVL and/or TO_CHAR or using ...location.id}