Does this email exist in another table?

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).

Thanks Julian. How would I do this with Zapier?

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.

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.