"OR" and "AND" search formula together

[rrbbtalnorth]

Hi, I have a database of professionals for whom I want to create a formula to search for the appropriate therapist using MAKE
There are categories where I want to search all searchable categories, and there are categories where I want to search one or another category.

for example-

I am interested in looking for a therapist “for children” in the area or “Tel Aviv” or Jerusalem.

AND(
SEARCH(“children”,{categorys})
OR(
SEARCH(“Tel Aviv”,{categorys})
SEARCH(“Jerusalem”,{categorys})
)
)

But apparently the formula is incorrect.
Would appreciate help

[ScottWorld]

So all of the words you’re looking for are inside that one category formula field?

If so, this formula should work:

OR(

AND(
SEARCH("children",{categorys}),
SEARCH("Tel Aviv",{categorys})
),

AND(
SEARCH("children",{categorys}),
SEARCH("Jerusalem",{categorys})
)

)

[ScottWorld · Accepted solution]

One important thing to note is that Airtable is extremely finicky when it comes to capitalization. So Airtable will see “children” as different than “Children”.

[rrbbtalnorth]

Thank you very much, but unfortunately the formula does not work

And I checked in capital letters, everything is the same

[kuovonne]

It is hard to read your screen capture but here are some possibilities.

• Making sure you are using straight quotes " and not curly quotes. Your text example has some curly quotes “”.
• Make sure you are not mixing up curly braces and parentheses: { ,(, } , ).
• You are searching the field {Categorys} in all searches. Shouldn’t some of those searches be for the {Area} field or the {City} field?
• Several of your fields are lookup fields. You should turn them into text strings in the search.

Try something like this:

AND(
    SEARCH("children", LOWER({categorys}) ),
    OR(
        SEARCH("Tel Aviv", {Area} & ""),
        SEARCH("Jerusalem", {Area} & "")
    )
)

[rrbbtalnorth]

Thank you very much, I will try

[rrbbtalnorth]

It works great. Thank you

[ScottWorld]

Alternatively, note that it’s often easier to create & keep those large formulas in Airtable.

In Airtable, your formula field would result in a 1 (if it is true) or a 0 (if it is false).

Then, in Make.com, you would simply need to put the name of your formula field (within curly brackets) for your search criteria.