Pulling Name on a separate table from Email

[John_Meredith1]

Fairly new to AirTable. I currently am trying to build two surveys for my org’s membership. My goal is to automatically link the records in Survey2 (Email, Organization, Role…) to Survey1 (Name, Email, Age…), but I don’t want users to be able to see the names of everyone who has submitted a response to Survey1.

Is there a way to have them input their emails in Survey2, and then to automate AirTable to look up the record associated with that email in the table for Survey1, and update the name field to be linked to that record after they submit?

[Justin_Barrett]

Welcome to the community, @John_Meredith1! :grinning_face_with_big_eyes: This is definitely doable using a couple different options. If your base is in a Pro-plan workspace or higher, you can use Airtable’s newly-added automation features to do this (it’s still in beta, and there’s a chance it’ll be opened up to Free and Plus workspaces down the road, but for now it’s only on Pro or higher workspaces). Otherwise you’ll need to use an external integration service like Zapier or Integromat. Let us know which option you’d like to explore, and we can give you more specific direction.

[John_Meredith1]

Thanks @Justin_Barrett! I’ve got a pro-plan, so I’ve been exploring the automation feature—I just don’t know enough of scripting to build what I need. I’m also open to use Zapier if that’s easier, any help would be greatly appreciated!

[Justin_Barrett]

Either option would work, though my preference is to use Airtable’s automation features. Here’s a rough outline of the steps:

1. Create a new automation, using the “New record” trigger option. Adjust the trigger settings to find new records in the [Survey 2] table.
2. Add a “Run script” action to the automation. Add two input variables for the script, both coming from the trigger step:
   a. recordID - The record ID of the triggering record
   b. email - The email address in the triggering record
3. Use the following code for the script, replacing your table and field names as needed:

let config = input.config();
let survey1Table = base.getTable("Survey 1 Table Name");
let survey2Table = base.getTable("Survey 2 Table Name");

let query = await survey1Table.selectRecordsAsync();
let found = query.records.filter(record => record.getCellValue("Survey 1 Email Field") == config.email);

if (found) {
    await survey2Table.updateRecordAsync(config.recordID, {"Link Field to Survey 1 Table": [{id: found[0].id}]})
}

Save the script, activate the automation, and everything should be good. I just tested this script on my end, and it works well.