Skip to main content
Solved

IF and FIND formulas in Integromat

  • November 13, 2019
  • 6 replies
  • 64 views
L

Hey,

I have a scenario in Integromat that integrates with my Airtable CRM and it triggers when someone buys my event ticket.

When they buy the ticket, they fill some info like CNPJ and the contact email in a form. Then the integromat scenario runs, it need to find in Airtable the CNPJ (rollup field), if it matches the CNPJ in the field, great! if not, it should find the contact email in the field Email Contato 1 (rollup field that join values from many contacts), if the email matches, great, this is the right record, but if not, it should find the contact email in the field Email Contato 2 (rollup field that join values from many contacts), if it is the right record, great, but if not, it should be empty.

I made a formula in integromat but it is not working as expected. It is below, but since integromat formulas change a bit because we can change the data before it goes to airtable, i’m also sending pictures.

pasting it looks like this

IF(
FIND(
“{{replace(replace(replace(21.answers[6].answer; “.”; emptystring); “/”; emptystring); “-”; emptystring)}}”,
{CNPJ Empresa}
),
“{CNPJ Empresa}”,
IF(
FIND(
“{{22.E-mail}}”,
{Email Contato 1}
),
“{{22.E-mail}}”,
IF(
FIND(
“{{22.E-mail}}”,
{Email Contato 2}
),
“{{22.E-mail}}”,
“”
)
)
)

What could be wrong? Sometimes it returns results without any match, so it should be empty.

Best answer by Justin_Barrett

Justin,

I got rid of those replace functions and omitted the quotes.

My formula is now like this

IF(FIND("{{22.CNPJ}}",{CNPJ Empresa}),{CNPJ Empresa},IF(FIND("{{22.E-mail}}",{Email Contato 1}),"{{22.E-mail}}",IF(FIND("{{22.E-mail}}",{Email Contato 2}),"{{22.E-mail}}")))

in image https://share.getcloudapp.com/geuYDgLX

yet my formula isn’t coming through, as you can see here https://share.getcloudapp.com/YEuv6ZbZ it should find the email in the {Email Contato 2} field.

but when I try a similar formula inside airtable it is working and returning the right email in the 2nd contact email field.

The formula that works in airtable is the following:

IF(FIND(“00000000”,{CNPJ Empresa}), “CNPJ Empresa”, IF(FIND(“lucaseatp@gmail.com”, {Email Contato 1}), “Email Contato 1”, IF(FIND(“lucaseatp@gmail.com”, {Email Contato 2}), “Email Contato 2”,"")))

It is the same example, having the right answer in a rollup field in the {Email Contato 2}, so all IF’s functions are working well in airtable and not finding in integromat… any thoughts?


Sorry for the delay. Our move is in two days, and I’ve been busy prepping.

After looking at this again, I think that I misunderstood your original goal. By searching to find either the CNPJ in the {CNPJ Empresa} field, or the email address in one of the two email fields, you’re actually trying to end up with a matching Airtable record, which will be processed in the rest of the scenario. Is that correct? If so, then the design of the formula is definitely wrong, at least in terms of how it should be designed specifically for use in Integromat.

The Formula field in the Airtable module in Integromat isn’t used in quite the same way as you’d use it in Airtable itself. In the latter case, you’re typically returning data to display directly in the formula field. However, what you’re trying to do in Integromat is find a matching record (or series of records, depending on your use case), using a formula to determine whether each record in the specified table is or isn’t a match. In other words, you don’t want the data from the field, so your formula shouldn’t return actual field data like it does now. You just need to know if the record matches, so the formula should return either TRUE or FALSE. If it’s TRUE, Integromat will pass the record to the rest of the scenario. If it’s FALSE, it won’t pass.

With that in mind, I believe your formula should be:

OR(FIND("{{22.CNPJ}}", {CNPJ Empresa}), FIND("{{22.E-mail}}", {Email Contato 1}), FIND("{{22.E-mail}}", {Email Contato 2}))

The OR() function will return TRUE if any of the conditions inside it are true, or return a TRUE-equivalent value like a positive number. So if the CNPJ is found in the {CNPJ Empresa} field, or the email address is found in either of the two email fields, the record will be considered a match, and will process accordingly.

6 replies

Justin_Barrett
Forum|alt.badge.img+21

Two things to consider…

  • You don’t need to include an empty string as the fallback result of an IF() function. I’m specifically talking about your final email check, where it falls back to “” if a match isn’t found in that second email field. In fact, it’s definitely preferred in some cases to leave out that last option, and let Airtable add the appropriate equivalent of nothingness based on the other data being returned. Not sure if that’s really contributing to your issue, but it’s worth a shot.
  • To prevent the scenario from proceeding with a false positive, add a filter between your trigger module and the next one in line, so that it only continues if certain criteria are met. This could be a comparison against how many results were found, the specific contents of those results, or whatever makes sense in your situation.
L
L
  • Lucas_Teles
  • Author
  • New Participant
  • November 13, 2019

Two things to consider…

  • You don’t need to include an empty string as the fallback result of an IF() function. I’m specifically talking about your final email check, where it falls back to “” if a match isn’t found in that second email field. In fact, it’s definitely preferred in some cases to leave out that last option, and let Airtable add the appropriate equivalent of nothingness based on the other data being returned. Not sure if that’s really contributing to your issue, but it’s worth a shot.
  • To prevent the scenario from proceeding with a false positive, add a filter between your trigger module and the next one in line, so that it only continues if certain criteria are met. This could be a comparison against how many results were found, the specific contents of those results, or whatever makes sense in your situation.

Justin,

I tried what you said in your first tip, but it didn’t work, it returned me an error when i deleted the “”

but thinking about your 2nd tip, I tried changing the CNPJ to be sure it doesn’t match any CNPJ and changed the email to be sure it would be find, but it didn’t! It is like the formula doesn’t get through the first IF and only FIND for CNPJ, and if it is false, it returns empty, instead of following the FIND email 1 and 2 formula.

Justin_Barrett
Forum|alt.badge.img+21

Justin,

I tried what you said in your first tip, but it didn’t work, it returned me an error when i deleted the “”

but thinking about your 2nd tip, I tried changing the CNPJ to be sure it doesn’t match any CNPJ and changed the email to be sure it would be find, but it didn’t! It is like the formula doesn’t get through the first IF and only FIND for CNPJ, and if it is false, it returns empty, instead of following the FIND email 1 and 2 formula.


To be more clear, you need to omit both the quotes and the comma before them, so the end of your formula would look like this:

...
IF(
    FIND(
        "{{22.E-mail}}",
        {Email Contato 2}
    ),
    "{{22.E-mail}}"
)
...

Not sure why you’re not getting through with the rest of the formula. You might try testing the output of those nested replace functions in Integromat. I haven’t used that function myself, but maybe something there is creating a false-positive for that first FIND().

L
L
  • Lucas_Teles
  • Author
  • New Participant
  • November 13, 2019

Justin,

I got rid of those replace functions and omitted the quotes.

My formula is now like this

IF(FIND("{{22.CNPJ}}",{CNPJ Empresa}),{CNPJ Empresa},IF(FIND("{{22.E-mail}}",{Email Contato 1}),"{{22.E-mail}}",IF(FIND("{{22.E-mail}}",{Email Contato 2}),"{{22.E-mail}}")))

in image https://share.getcloudapp.com/geuYDgLX

yet my formula isn’t coming through, as you can see here https://share.getcloudapp.com/YEuv6ZbZ it should find the email in the {Email Contato 2} field.

but when I try a similar formula inside airtable it is working and returning the right email in the 2nd contact email field.

The formula that works in airtable is the following:

IF(FIND(“00000000”,{CNPJ Empresa}), “CNPJ Empresa”, IF(FIND(“lucaseatp@gmail.com”, {Email Contato 1}), “Email Contato 1”, IF(FIND(“lucaseatp@gmail.com”, {Email Contato 2}), “Email Contato 2”,"")))

It is the same example, having the right answer in a rollup field in the {Email Contato 2}, so all IF’s functions are working well in airtable and not finding in integromat… any thoughts?

Justin_Barrett
Forum|alt.badge.img+21
  • Justin_Barrett
  • Inspiring
  • Answer
  • November 21, 2019

Justin,

I got rid of those replace functions and omitted the quotes.

My formula is now like this

IF(FIND("{{22.CNPJ}}",{CNPJ Empresa}),{CNPJ Empresa},IF(FIND("{{22.E-mail}}",{Email Contato 1}),"{{22.E-mail}}",IF(FIND("{{22.E-mail}}",{Email Contato 2}),"{{22.E-mail}}")))

in image https://share.getcloudapp.com/geuYDgLX

yet my formula isn’t coming through, as you can see here https://share.getcloudapp.com/YEuv6ZbZ it should find the email in the {Email Contato 2} field.

but when I try a similar formula inside airtable it is working and returning the right email in the 2nd contact email field.

The formula that works in airtable is the following:

IF(FIND(“00000000”,{CNPJ Empresa}), “CNPJ Empresa”, IF(FIND(“lucaseatp@gmail.com”, {Email Contato 1}), “Email Contato 1”, IF(FIND(“lucaseatp@gmail.com”, {Email Contato 2}), “Email Contato 2”,"")))

It is the same example, having the right answer in a rollup field in the {Email Contato 2}, so all IF’s functions are working well in airtable and not finding in integromat… any thoughts?


Sorry for the delay. Our move is in two days, and I’ve been busy prepping.

After looking at this again, I think that I misunderstood your original goal. By searching to find either the CNPJ in the {CNPJ Empresa} field, or the email address in one of the two email fields, you’re actually trying to end up with a matching Airtable record, which will be processed in the rest of the scenario. Is that correct? If so, then the design of the formula is definitely wrong, at least in terms of how it should be designed specifically for use in Integromat.

The Formula field in the Airtable module in Integromat isn’t used in quite the same way as you’d use it in Airtable itself. In the latter case, you’re typically returning data to display directly in the formula field. However, what you’re trying to do in Integromat is find a matching record (or series of records, depending on your use case), using a formula to determine whether each record in the specified table is or isn’t a match. In other words, you don’t want the data from the field, so your formula shouldn’t return actual field data like it does now. You just need to know if the record matches, so the formula should return either TRUE or FALSE. If it’s TRUE, Integromat will pass the record to the rest of the scenario. If it’s FALSE, it won’t pass.

With that in mind, I believe your formula should be:

OR(FIND("{{22.CNPJ}}", {CNPJ Empresa}), FIND("{{22.E-mail}}", {Email Contato 1}), FIND("{{22.E-mail}}", {Email Contato 2}))

The OR() function will return TRUE if any of the conditions inside it are true, or return a TRUE-equivalent value like a positive number. So if the CNPJ is found in the {CNPJ Empresa} field, or the email address is found in either of the two email fields, the record will be considered a match, and will process accordingly.

L
L
  • Lucas_Teles
  • Author
  • New Participant
  • November 21, 2019

Thanks Justin, that’s it!

It worked like a charm, thank you very much for all your explanations!

Terms & ConditionsAccessibility statement