Skip to main content

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


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





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


    )


)

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


    )


)

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.

Reply


Terms & Conditions