Is it possible to construct a search filter in suitescript that is equal to the SQL

where in

syntax? In SQL, this clause will only return exact matches for Paris and London

where city in ('paris', 'london')

I don't think you can do that with formula functions in NS, but I could be wrong there really is no comprehensive documentation on what you can do in SQL in saved searches, so someone please correct me. The way I'd handle this in a search would be to have a filter for each exact match and OR them after all my AND search filters. I believe these are called "filter expressions" if you're looking for documentation

[
    [filter 1],
    'AND',
    [filter 2],
    'AND',
    [
        [your london filter here],
        'OR',
        [ your paris filter here]
    ]
]

@Anthony OConnor Yeeeees, this is what I'm looking for. I'll just build the filters in a loop and run it like that. I believe the SQL engine essentially reads the

where xyz in ...

and does the same thing.

You would do something like

['city', search.Operator.ANYOF, [array_of_city_ids]]

yeah I was assuming its not select field... otherwise yeah, anyof works

I tried

ANYOF

on the Item.Name field and it only returns the first result from the array.

that doesn't sound right... we'll need more context to understand what's going on... share your code?

if its a text field its going to be search.Operator.EQUALTO in a bunch of ORs i believe

yeah, but if its just an item name... can you not get the item id instead, and then you can use ANYOF

true

can’t you do a Formula (Numeric)

CASE WHEN {city} = 'Paris' THEN 1 WHEN {city} = 'London' THEN 1 ELSE 0 END

and make the formula have to equal 1?

^^^ lol yeah this is exactly the kind of thing I know can be done, but I rarely use solutions like this.

oh boy I use this all the time

I guess although it is kind of neat, it seems like it will perform poorly in a query (I'm not sure it DOES, but it SEEMS like it would), so I just always want to use ids for what I'll need.

Case when {fieldid} in(‘test’, ‘test2’) then 1 else 0 end

works too