Help

Re: Does this email exist in another table?

1207 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Steven_Kryger
5 - Automation Enthusiast
5 - Automation Enthusiast

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?

6 Replies 6

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.

Ephraim_Bismuth
5 - Automation Enthusiast
5 - Automation Enthusiast

it does work for me. Your explanation is very clear. You do such a great job on this community ! Thanks a lot.

Unfortunately it does’t work on new records created after this setup. Do you have any ideas how to make it work every time new record appears?
Thanks

Thank you very much for providing such complete solution for this issue. I was dealing preciselly with this situation and your answer was the perfect solution!