Lookup based on a text (i.e. non-linked) field

[Minji_Ro]

in one table called Appointments, i’m importing appointment data from Acuity using Zapier. one of the fields i’m dumping into this table is the client’s e-mail address.

i’d like to use this e-mail address to lookup the client’s name in a different table called Contacts. i can’t figure out how to do this since the e-mail imported from Acuity/Zapier is a single text field and not a linked field to Contacts.

this seems really straightforward so perhaps i’m missing something obvious. any/all help is greatly appreciated, thank you!

[W_Vann_Hall]

Minji_Ro:

this seems really straightforward so perhaps i’m missing something obvious.

You’d be surprised how not straightforward it is.

There are a couple of ways to do this. Your best bet is probably to create a table (or modify your existing one) to list contacts keyed by email address and have the Acuity Zap enter the retrieved email into a linked field.

Alternatively, you can link every [Contacts] record to a single record in a new table I’ll call [Match]; create a new field in [Contacts]containing a key:value pair consisting of {email}:{name}; roll up all of the key:value pairs into a single concatenated string in [Match] using '|'&ARRAYJOIN(values,'|') as the aggregation formula; and, finally, search the concatenated string for the retrieved email address and, if found, extract the contact name, using an aggregation formula similar to this:

IF(
    FIND(
        '|'&{AcuityEmail}&':',
        values
        ),
    MID(
        values,
        FIND(
            '|'&{AcuityEmail}&':',
            values
            )+LEN(
                AcuityEmail
                )+2,
        FIND(
            '|',
            values,
            FIND(
                '|'&{AcuityEmail}&':',
                values
                )+1
            )-FIND(
                '|'&{AcuityEmail}&':',
                values
                )+LEN(
                    AcuityEmail
                    )+2
        )
    )

(You may have to tweak some of those offsets a bit, as I’m freestylin’ here with no way to check for accuracy.)

I can’t point to any reference off the top of my head of someone doing the same thing as you, but you can find very similar routines in a number of my other community posts and replies.

There could certainly be other ways to approach this problem — in fact, I fervently hope there are, and that some clever soul posts a more attractive alternative to these two.

[Minji_Ro]

thank you very much. changing everything to key off of e-mail / a separate table makes a lot of sense! appreciate your help.

[Ally_Warson]

Can you match one gmail email to an airtable list and then update the date? A calendar invite will have 5 people on the invite for example and I want Zapier to be able to find the match between one of the emails and one of the emails on the airtable people list and then update the date within airtable based on the email. Is this doable?

[W_Vann_Hall]

That’s actually a very different use case than the first poster was facing, but, yes, it should be possible to do it with help from a SaaS integration service such as Zapier. It potentially gets a little hairier depending on the exact process flow. For instance, if you’re sending out invitations, and you want Zapier to trigger on receipt of a response from one of the individuals on the email list, that’s pretty straightforward.

If the Airtable base contains one person per record, it’s extremely straightforward: Once Zapier triggers, you search for the Airtable record with an email address matching that of the response sender and update it. If the record contains multiple email addresses, the search becomes a little more complicated; you’ll probably want to use a search formula to find the record.

It’s triggering on the email’s being sent that has potential for introducing hair — again, largely depending on table structure. For instance, if your invite contains, say, five email addresses, and your base is organized by individual, you’ll need a mechanism in Zapier that allows you to loop through the recipient addresses, isolate each email address, search for it in Airtable, and update records applicably. Likewise, if both the invitation and your base contain blocks of multiple email addresses, identifying the correct record can become tricky — especially if an individual email address can be a member of more than one block.

However, the answer to your question remains ‘yes’: It can be done with Airtable and Zapier. Some scenarios more easily than others, perhaps, but all are well within the abilities of the integration.

[Ally_Warson]

“It’s triggering on the email’s being sent that has potential for introducing hair — again, largely depending on table structure. For instance, if your invite contains, say, five email addresses, and your base is organized by individual, you’ll need a mechanism in Zapier that allows you to loop through the recipient addresses, isolate each email address, search for it in Airtable, and update records applicably. Likewise, if both the invitation and your base contain blocks of multiple email addresses, identifying the correct record can become tricky — especially if an individual email address can be a member of more than one block.”

How can I isolate each email address and then have it be searched for within airtable? This is where I am struggling. I tried to use Zapier formatting,etc. and I can’t get it to read within airtable.

[W_Vann_Hall]

I’ve responded via PM…