[Ekosmerl]

Hi All,

How is it possible to set criteria in a saved search to search for any record based on a list of values?

For example, we have an external list of 10 customers and we need to pull an additional piece of information from their records in NetSuite.

I thought something along the lines of:

Formula (Text)

{Name} IN (‘Name 1′,’Name 2′,’Name 3’)

However, when I enter this in, it never saves my formula description (line two of the above example) making me think I can’t use “IN” for my function.

Any ideas?
This is a cached copy. Click here to see the original post.

[vdeymos]

You can use the IN function on a saved search. However, I don’t think your formula would work that way. You’ll probably need to use a CASE WHEN statement like this:

Case when {Name} IN (‘Name1’, ‘Name2’, ‘Name3) then VALUE else OTHER VALUE end

If you are setting this on the Criteria tab of the saved search, you can probably use a Formula (Numeric) field and then return 1 when it meets the CASE WHEN statement. After that, set the Criteria value to 1.

Let me know if that works for you.

[Ekosmerl]

Works great! Thanks so much!

[kdormuth]

Just wanted to say thanks – this helped me, too!

[ECHouse]

RE: Saved Search based on List of Values

Hi all, I’m a bit new to SQL enhanced searching. I have successfully added the following numeric formula to my Results: Case when {firstname} IN (‘Frank’, ‘Bob’, ‘Elizabeth’) then 1 else 0 end

I’m lost when it comes to setting “the Criteria value to 1″… what would that look like in this case?

In very much related news, can anyone recommend some resources for me to learn these SQL building blocks as they apply to Netsuite on my own?

Very hopefully yours,

Liz

---

rnedelkow replied on 03/06/2015, 01:38 PM: Hi Liz, you may have already seen these in SuiteAnswers, but here are a few good ones:

Answer Id: 8439

Answer Id: 10101

Answer Id: 10081

Answer Id: 8451

A popular formula thread on this forum is https://usergroup.netsuite.com/users…formula-fields

[khultquist]

Although it’s nice and compact, you don’t have to use SQL to do this, you can do it with Expressions in your saved search. In your saved search Criteria, check ‘Use Expressions’, then use Parentheses and ‘Or’ for each line

So your criteria would look like this:

…

( First Name is Frank Or

First Name is Bob Or

First Name is Elizabeth ) And

…

[ECHouse]

Thank you, khultquist. Unfortunately, my dream search isn’t that simple. I was hoping to be able to search against a list of hundreds of comma separated values.

[vdeymos]

Not sure if you’ve answered your question ECHouse, but if you place the formula in the Criteria tab of the search, it would look like this:

Filter: Formula (Numeric):

Pop-up window fields:

Formula: Case when {Name} IN (‘Name1’, ‘Name2’, ‘Name3’) then 1 else 0 end

Description: equal to

Value: 1

So when the formula runs, it would return a value of 1 when the name falls within the list of names you provided and it returns 0 if it doesn’t. The filter on the Criteria tab will only return results where the value is 1.

[Author]

Posts