How to automatically find another record in another table?

[User_Research]

Hi there, I have a form in one table which users can fill out. One of the input fields is asking for their email address. Now I would like to automatically find this user with the provided email address in another table which contains additional information of that user. Is that possible automatically without manual linking to a record?

In Google Sheets I have this kind of formula to solve the problem but I cannot find something similar in Airtable: =ArrayFormula(IF(ISEMPTY(B2:B);;IF(ISERROR(COMPARE(B2:B;Database!B:B;0));“Participant not in database”;HYPERLINK(CONCAT(“docs.google.com/spreadsheets/d/1234567890/edit#gid=1234567&range=B";COMPARE(B2:B;Database!B:B;0));"D...;%22Details) of participant”))))

[bdelanghe]

I have two three answers:

1. You can’t do this without linking. There is no way to tell Airtable to ‘look’ without being explicit about the relationships of the tables. So you’d have to create a table of emails that would be linked to the table that your form view is on. This will then be linked each time a user fills out the form. (downside: Every time a user enters their email they can see everyone else’s.)

2. You can do this with Zapier. This can be set up to run code every time there is a new form entry. And that code can be set up to look for the user information based on their email. And then add that in information to your table. (downside: is depending on how many entries there are you might need a paid plan. And there is a time delay of up to 15min)

3. Create a linked email field, but don’t have it on the form. Instead, the form will have an email field. Once the form is filled out set up Zapier to trigger. So that it copies the email from the user input to the linked field. Then you can have the user information as lookup fields. (upside: if you update user information it’ll be updated in the lookup as well.)

@User_Research If you decide to go with any of these. Let me know I can help if you hit any roadblocks. Best of luck!!