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


#1

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!


#2

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.


#3

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