Autopopulate table given condition

Hello!

Here is my situation. I have 2 tables.
Table A
Populates based on information submitted by the client. Fields include: name, email, subdivisions they live in, a description of a problem and an empty field with the name of the ambassador of that subdivision.

Table B
Has information about the ambassadors responsible for client of certain subdivisions.
Fields include: name email and an array of subdivisions they are responsible for.

I want to autopopulate the Ambassador field in Table A with the name of the ambassador responsible with that subdivision.

The program would need to look at the subdivision name in table A, then check which ambassador has that subdivision in table B and then fill the empty field in table A with the name of the appropriate ambassador.
I am hoping a IF (Find) would work but I can’t think of how to look into other tables

I tried by automating update record. I was trying to update a record if a record was created and add the name of the appropriate ambassadors but I can only choose 1 table at a time and does not seem to work

Ultimately I would like to airtable to send an email to the ambassador with the information of the client and the description of the problem the client is facing.

Thanks so much!

Hi @Giorgio_Bazzigaluppi and welcome to the community!

Maybe a “lookup” field could help?

  • put your your subdivisions in a table C and link them (“link to another record” field) to the ambassadors in table B (Allow “linking to multiple records”)

  • use another linked record field to table C in your table A and in the form. Clients can then select the subdivision

  • use a “lookup field” in table A that searches for the corresponding ambassador in table B via the linked table C

Since lookup fields are now supported for automations, you can add another lookup field to retract the corresponding email address from table B.

Unfortunately, you cannot use a lookup field in an Airtable form without using external parties.

It works @Databaser thanks so much!

I have a small issue though:
The form prior gave the user 1 option from a list to pick the sub development and move on.
Now it gives them the option to add as many developments as they chose.

Basically I turned that field from a multiple select to a “link” field which has no limit to selection

It is not a huge issue and I don’t think users will abuse it but do you think we could address it?

Thanks!

Change the Link field type configuration to only allow one selection by turning off “Allow linking to multiple records”.

1 Like

Glad I could help Giorgio!

I think I already have that and it still allows for multiple entries by the end user.
The field that allows subdivision selection in the form has “allow linking to multiple records” already switched off. One client can only live in one subdivision
The respective linked field in the other table has it on but due to necessity. Multiple clients can be in 1 subdivision.

thanks for your help @Kamille_Parks