Help

The Community will be temporarily unavailable starting on Friday February 28. We’ll be back as soon as we can! To learn more, check out our Announcements blog post.

Find record by company name with contains function

661 10
cancel
Showing results for 
Search instead for 
Did you mean: 
vierless_julian
6 - Interface Innovator
6 - Interface Innovator

My challenge is to do a search that looks for records where my input is part of the company name. So essentially I am looking for a "contains" function.

Example searches:
1.
Input: "sephora"
Target: "company" field in record: "SEPHORA Inc."
2.
Input: "GoSports"
Target: "Go Sports Inc."

I guess the second example won't be possible but my plan is to just get all the matches based on company name, then name of the record and description and then let AI do a matching by probability and my other form inputs.

I am using the search module in Make.com.

My guess was to use either "SEARCH", "FIND", or "REGEX_MATCH" but no luck so far with my tests.

10 Replies 10
ScottWorld
18 - Pluto
18 - Pluto

When using Make’s Airtable integrations, you would just use normal Airtable formulas, and make sure that your formula results in the number 1 if you want Make to find that record.

You can test your formulas in an Airtable formula field to see if it works.

So, for example, if you’re looking for a word within a field, your formula would be:

FIND("string of text I’m looking for", {Field I’m Searching})

Back in Make, you would just substitute the text within the quotation marks with what you’re looking for, but everything else would remain the same.

Hope this helps! If you’d like to hire an expert Airtable consultant to help you with anything Airtable-related, please feel free to contact me through my website: Airtable consultant — ScottWorld

vierless_julian
6 - Interface Innovator
6 - Interface Innovator

Hi @ScottWorld !

Thank you for helping out.

I tried using this formula:
FIND("{{lower(1.Company)}}", {{lower("{Unternehmen}")}})

It outputs the company but only if it matches exactly my input. So if I put "SEPHORA Inc." just like in the record, it finds the record. If I put SEPHORA, it doesn't.

Should it not also output the record now since my input string is part of the record company name?

ScottWorld
18 - Pluto
18 - Pluto

It should! Can you post a screenshot?

vierless_julian
6 - Interface Innovator
6 - Interface Innovator

This one is not working but should:
wrong.png

This is when I do the exact name:
found.png

  

ScottWorld
18 - Pluto
18 - Pluto

Those look like proper Airtable formulas. If you type your formula into an Airtable formula field, does Airtable yield the results you're looking for there?

vierless_julian
6 - Interface Innovator
6 - Interface Innovator

I tried to add the formula field but it returns "0" for all records if I enter "Strabag".

When I enter the complete name (in this case "STRABAG BMTI GmbH & Co. KG") I get a "1".

This is the formula:

FIND("NAME OF COMPANY GOES HERE", {Unternehmen})

 

vierless_julian
6 - Interface Innovator
6 - Interface Innovator

Is there a way to create a matching regex for this? I tried generating a formula using the built in AI but it fails and outputs Errors.

ScottWorld
18 - Pluto
18 - Pluto

Hmmm... that's odd, because it works just fine for me. However, note that Airtable's FIND is case sensitive, so your idea to use the LOWER() function earlier was a good one. This formula should work in Airtable:

 
FIND(
Lower("NAME OF COMPANY GOES HERE"),Lower({Field Name})
)

- ScottWorld, Expert Airtable Consultant

vierless_julian
6 - Interface Innovator
6 - Interface Innovator

Ok, that is very interesting!

I just tried your function and it resulted in an error again. Then I changed the {Field Name} from {Unternehmen} to {Name} and suddenly it worked for my input. Maybe it has something to do with the field type of {Unternehmen}? It is a lookup field of another synched table in this base.

So for me your function works now but not for the right field yet.