Read email from table 2, find same email on table 1 and get the name to fill table 2

[Aline_Branco_Wi]

Hi, there!!
I need an automation to do exactly what the subject of the topic says. We’ll fill the e-mail field in Table 2 from a csv import. Then we need the automation to lookup that email on Table 1, get the Name value corresponding and then fill the field Aluno in table 2.
I found a script in another post that I adapted to what I need but I’m still learning to code so I need help to fix my script. Can anyone pleeeease give me a help?

About the script: I added 2 variables - tho I don’t actually need record ID - but I followed the instructions on the other post.
Variable 1 is recordID and the value is Airtable record ID
Variable 2 is email and the value is email
Both variables are being pulled from Table 2

let config = input.config();
let table1 = base.getTable("Base de Alunos");
let table2 = base.getTable("Entregas Plataforma");

let query = await table1.selectRecordsAsync({fields: table1.fields});
let found = query.records.filter(record => record.getCellValueAsString("Nome") == config.email);


if (found) {
    await table2.updateRecordAsync(config.recordID, {"Aluno": [{id: found[0].id}]})
}

[Russell_Findlay]

You should be able to do this without automations and without scripting

If the email is the primary field for any table then you can change the input field from an email field to a linked record field - when it is entered it will link to that record and then you can use a look up field to bring the name across (and any other data) … this approach would keep data consistent too.

[ScottWorld · Accepted solution]

Welcome to the community, @Aline_Branco_Wilhelm!

I don’t know scripting, so we will have to wait until a scripting expert chimes in here to provide a direct answer to your exact question.

However, there are at least 3 ways of doing this that don’t require scripting at all:

1. As @Russell_Findlay outlined above, simply change your email address field to a linked record field that links to Table 1. Then import the email addresses into that field. This approach has the limitation of your email address needing to be the primary key of Table 1.

2. The 2nd approach is to use Airtable’s native automations. You would use the “Find Records” action to find the matching email address in Table 1, and then have the automation link the records. This removes the limitation of your email address needing to be the primary field, but it adds a new limitation that it will fail if it finds more than one matching email address in table 1.

3. The 3rd approach is to use an external automation tool like Make.com, which removes all of the limitations altogether. The challenge here is that there is a learning curve to using an external automation tool, but the upsides are that there are no more limitations & you don’t need to know any code.

[Aline_Branco_Wi]

Hey @Russell_Findlay!
If I understood correctly your suggestion, I tried that way, but if the email field is a linked record and the Aluno field is a lookup, I can’t fill the email field manually or from a csv. It just let me select the record from table 1.
Also Table 1 is synced from another base so I cannot change any field and the primary field is the name.

[ScottWorld]

You can actually change the primary field on a synced destination table, so that might be the solution for you.

You might only be able to do that during the initial setup of the sync though. I’m not 100% sure and I’m writing this from my phone right now so I can’t check right now. If that’s the case, you can re-setup the sync.

[Aline_Branco_Wi]

Hey @ScottWorld!!
The linked record option is not the best for what we need, but your second solution was great! I’m trying to design the steps and I’ll tell later if it worked or if I need any additional help.
Thanks a lot for your insights!

[atusr]

Hi Aline & Scott,

Russell did not mention that the “email address needs to be the primary key of Table 1”, he mentioned “If the email is the primary field for any table”, so you may just need to change the FieldType of EmailAddress in Table1 to the LinkedTable EmailAddresses.

With that i do not see any Cons.