Best answer

Help crafting an Airtable search formula in my Zap

  • 9 October 2019
  • 10 replies
  • 3307 views

Userlevel 7
Badge +9

Hey team!

I am working on a Zap and trying to use a search formula to be able to find a phone number when there are multiple phone numbers in a cell. These are text strings and not linked records as I know linked records are identified by ID. Forewarning, I'm terrible with crafting formulas!

Here is where I'm at with the formula:

Image 2019-10-08 at 7.31.59 PM.pngHere is the formula in textual format if you want to play with it:

=IF(ISNUMMBER(SEARCH({{68474804__fromPhoneNumber}},{Client phone for Zap})),{{68474804__fromPhoneNumber}},"False")

Basically, what I want to happen is for the search formula to look at a comma-separated list of phone numbers, see if the phone number from Ring Central is in that list and if so, return the number.

Here are some modifications/things I know that need to change:

  • Airtable doesn't seem to support ISNUMBER so that will have to be removed
  • I actually don't care if the phone number itself is returned. Returning "True" or "False" will work just fine.

Maybe @Openside, you know how to help? Open to any and all suggestions and thanks in advance!


icon

Best answer by Openside 9 October 2019, 15:22

View original

10 replies

Userlevel 5
Badge +3

Typically I use the FIND function for this. I'm on my phone so can't type well the full formula but something like: If(find(<trigger value>,{field}), {field})


I would also suggest trying to get the formula working within an Airtable formula field first, then convert to zapier


Userlevel 7
Badge +9

Hey @Openside,

Thanks for the quick reply! Unfortunately, this still isn't working for me. 😞 Here is my new search formula:

IF(FIND({{68474804__fromPhoneNumber}},{Client phone for Zap}),"true","false")

I did test it out in the formula field and that seemed to work:

Image 2019-10-08 at 8.27.11 PM.png

Any other ideas with how I can get this to work within my Zap? I'm getting an invalid formula error:

Image 2019-10-08 at 8.28.07 PM.png


Userlevel 5
Badge +3

Do you have to still add the quotes around the trigger value?


Userlevel 7
Badge +9

@Openside no, I tried that already and got the same error. 😞 Airtable's documentation seems to not require them, either: https://support.airtable.com/hc/en-us/articles/218151657#formula


Userlevel 5
Badge +3

Actually it looks like their documentation does require quotes:


Contact Name}="<TriggerValue>(Step 1: From Name)<TriggerValue>"


Userlevel 5

@jesse could you show a sample of the comma-separated values you're searching? (I know you might want to redact them, but it might be useful to see the data even if it's partially obscured.) I've done some truly ludicrous formulas in Airtable and would be happy to take a crack at this.


Userlevel 7
Badge +9

@Openside ahh, you're right! Missed those and @TheDavidJohnson yes, happy to! Adding the quotes did remove the error. For testing purposes, though, I descided to keep the formula simple just so I can try to get the custom search to find the right record. My search formula is now as follows:

IF(FIND("12023870318",{Client phone for Zap}),"true","false")

It finds a record but it actually finds one without a phone number...strange! Any other ideas?

(removed a few images for client privacy reasons)


Userlevel 5
Badge +3

I think it's cause you're formula is always returning something considered true. The string "false" is actually true in programming 🙂.


So if you remove the IF and just use the find function it should work


Userlevel 7
Badge +9

That was it, @Openside! Thank you and @TheDavidJohnson for all of your help with this!


Userlevel 5

Awesome! Glad you got it solved!



Reply