This site was built to compliment the existing NetSuite Professionals Slack Community by adding a means for members to easily find answers to common questions and to create a searchable database of those questions and answers.

NetSuite Professionals

I need to create a criteria on a saved search to return only the Revenue Arrangements that have a Geography that do not match the Customer Geography, we had a Celigo integration that was overwriting everything to North America : USA.  I just want to return the ones that need fixed.  I am thinking CASE WHEN but not getting it to work

<@UHWKT63HT>, If all spacing is the same (as indicated in your screenshot) you can use the below formula to return your desired results.  May want to first plug it in on the results, as a Formula Numeric field, so that you can visually see the logic.  Then move it to the criteria subtab to exclude the not needed results:

CASE WHEN SUBSTR({arrangement-geo-field-id}, INSTR( {arrangement-geo-field-id}, ':') -1) != SUBSTR({customer-geo-field-id}, INSTR( {customer-geo-field-id}, ':') -1) THEN 1 ELSE 0 END

Hope this helps!!

I'm guessing you have multiple joins in place and it's duplicating rows.

You might have to revisit how you built your search from the ground up

i would not suggest doing more than 1 table join at a time.