Help

Upcoming database upgrades. to improve our reliability at 03:30 UTC on Feb. 25 / 7:30pm PT on Feb. 24. Some users may briefly experience slow load times or error messages. Learn more here

"OR" and "AND" search formula together

Topic Labels: Formulas
Solved
Jump to Solution
1028 7
cancel
Showing results for 
Search instead for 
Did you mean: 

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
צילום מסך 2022-08-07 153654

1 Solution

Accepted Solutions

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”.

See Solution in Thread

7 Replies 7

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})
)

)

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”.

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

And I checked in capital letters, everything is the same

צילום מסך 2022-08-07 213109

צילום מסך 2022-08-07 213252

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} & "")
    )
)

Thank you very much, I will try

It works great. Thank you

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.