Does this email exist in another table?


#1

I have 2 tables, both with email addresses.

In Table #1, I would like to be able to show (e.g. a checkbox) if an email address appears in Table #2.

Is this possible?


#2

Hi Steven

This isn’t possible with Airtable on it’s own but would be quite straightforward to achieve with Zapier or Integromat (you would need a paid account to do this).


#3

Thanks Julian. How would I do this with Zapier?


#4

Actually, this is quite possible — and easy — to do entirely within Airtable. In fact, I just put together a base demonstrating this in under 5 minutes. (The demo base uses bogus UPS shipping codes instead of email addresses simply because I had access to a large number of fake UPS codes, but the principal is the same.)

The secret is to create a third table to which each record in both [Table 1] and [Table 2] is linked. This third table (which I named [Join]) contains only a single record, and that record contains only a single field (in this case, called {Name}). In {Name}, I entered a single period character ('.').

To both [Table 1] and [Table 2] I added a Formula field called {Link to Join} with the following configured formula:

'.'

— that is to say, a period character enclosed in quotes.

In both [Table 1] and [Table 2] I right-clicked on {Link to Join} and changed the field type from ‘Formula’ to ‘Link to another record’. I selected [Join] as the table to which to link. This caused each record in [Table 1] and [Table 2] to be linked to the record named '.' in [Join].

Finally, I created a Rollup field called {Duplicate Address} in [Table 2]. It used the links provided in {Link to Join} to roll up the field {Link to Table 1} using the aggregation function

IF(
    FIND(Email, ARRAYJOIN(values)),
    '✅',
    BLANK()
    )

(In other words, if the value of {Email} can be found among the email addresses rolled up from [Table 1], display :white_check_mark:, the White Heavy Check Mark emoji.)

I tested this by copying an email address from [Table 1] and pasting it into a random record in [Table 2]. Immediately, a check mark emoji appeared in {Duplicate Address}.

You can find my demonstration base here. Initially, I created it with 1,200 dummy records in both [Table 1] and [Table 2], but I’ve since trimmed each table to 400 records to stay within free plan limits.

Please let me know if my explanation of this base isn’t clear…


Edit: Replaced UPS codes with bogus email addresses; both courtesy Fake Name Generator.