[Elle Contributions: 0Level 1☆]

I need to create a saved search that will return the entity names of those that do not have a particular service. Here is some sample data:

Entity / Service

Company 1 / Service A

Company 1 / Service B

Company 1 / Service C

Company 2 / Service B

Company 2 / Service C

Company 3 / Service A

Company 4 / Service A

Company 4 / Service B

If I want to know which companies do not have service C, how do I get that? So, in the data above, I want it to return Company 3 and 4, but not 1 and 2. If I just exclude Service C, then I get all four because each of them has a service that isn't Service C. I tried to write a CASE statement, but I'm pretty new to NetSuite and can't figure out what fields to call and how to get it to return just the ones that don't have Service C. I've been staring at this for hours and I'm sure I'm just overlooking something. I'm also unsure of single or double quotes. Here are some of my attempts: (the difference in field is that I tried getting the data from an alternate location)

CASE WHEN {customlist27}1 LIKE '%'||'Firewall'||'%' THEN '1' ELSE '0' END

CASE { WHEN {custitem20} LIKE '%'||'Firewall'||'%' THEN '1' } ELSE "0" END

CASE (WHEN {custitem20} LIKE '%'||'Firewall'||'%' THEN '1') ELSE '0' END

Thanks!

Elle
This is a cached copy. Click here to see the original post.

[khultquist Contributions: 0Level 1☆]

Sometimes it's difficult/impossible to use Saved Searches to find records or joins that don't exist, but here are a few suggestions:If you are searching for Customers, and the services are Items, try using the search criteria of 'Items Ordered' and then choose 'none of' Service C
Use a Summary Saved Search, with Summary Criteria, to filter using max, min, count, etc

[Elle Contributions: 0Level 1☆]

So, if I add a filter like MAX, will that filter per Company?

[khultquist Contributions: 0Level 1☆]

It's not that straightforward. You will probably have to use COUNT of all service items >0, and MAX/MIN to of service items to be filter out the item you don't want.

Try the other way first, using 'Items Ordered'.

[Author]

Posts