NetSuite formula(text) problem.
I have a specific search I need to power up but I could also really use this for general use across a few types of reporting.
We use a lot of custom body fields which do not seem to have access to the Keyword Match option in the dropdown system or I would be using commas left and right.
I need to search a single custom field for multiple possible answers.
I was thinking maybe a CASE WHEN formula but I don't have an else.
Lets call the field states. I want this report to only return rows of records where there are the following 5 states in the custom body field "Shipping State". it isn't allowing keyword match. I only want to see transactions with Michigan, New Jersey and Floria (this is hypothetical) so I want just to put a filter in up front to pair down the results.
CASE WHEN {custbody.shippingstate} IS 'Michigan' ...
Can I use an OR construct here? and do I need an else?
I tried using coalesce but I don't think I had the syntax around the coalese right.
Any help for a formula newbie appreciated.
Try the following as a formula text result column in a NetSuite saved search.
CASE WHEN LOWER({custbody.shippingstate}) LIKE '%michigan%'
OR LOWER({custbody.shippingstate}) LIKE '%jersey%'
OR LOWER({custbody.shippingstate}) LIKE '%florida%'
THEN 'Y' END
LIKE conditions are case sensitive, so that's why I incorporated the LOWER() method. Reminder in NetSuite "%" matches any string of any length (including zero length, and "_" matches a single character. If this works as a results column you can add the formula text as a criteria to filter results.