NetSuite has many limitations. Saved searches are powerful, but out of the box do not have a way to compare two fields to see if they match. But here is a handy way to use a CASE WHEN statement in a saved search to do just that.

The Scenario

Recently, we were faced with a bit of fraudulent orders on the eCommerce site I manage. As a way to catch these orders before processing, we wanted to find orders where the shipping and billing state did not match. 100% of the bogus orders met this criteria, so it was a quick and easy check. Another common thread of these orders: every email had a number in it! Read this article on how to deal with this.

How to Verify That Two Fields Do Not Match

Since my saved search is using summary fields, I chose “summary” under criteria to perform my check. Here’s what I did.

Summary Type: Sum
Field: Formula (Numeric)
Formula: CASE WHEN {shipstate} = {billstate} THEN 1 ELSE 0 END
Value is Not Equal to 0

What this does is assigns 1 for a match and 0 for a non match. Then, your results only show orders where the shipping state and billing state do not match.

This method of using CASE WHEN can be used in a lot of interesting ways. But since you can’t directly compare one field to another, this is the how I solve this problem.