How to automate adding values from one table to another based on shared field records (shared ID#s)

[PSUFA_Robot]

Hello!

I have two large tables with different data for members of my union. Individual members in both tables share the same ID # in a field in both tables.

I’d like to link the tables in such a way that data from field from Table A will automatically copy to a linked field from Table B, based on the common ID# field that the two entries/rows share.

How would I go about doing this?

Most beginner field-linking demos show me how to link fields, but then I have to select which record to link to manually by expanding the cell and selecting the record. But Table A and B both have hundreds of records and I would like to automate this process so that anytime there is a value in the linked field from Table A, it gets automatically added to the linked field in Table B, organized around their shared ID # field.

I can do some scripting but am not an expert.

Thank you!
Jacob

[TheTimeSavingCo]

Hey Jacob, I think if I were you I’d reorganize the tables so both their primary fields were the common ID# field

Once that’s done, we can then just paste the commond ID# value into the linked field, and the links would happen automatically

(We can also automate this step so that any new rows that get created have this done automatically too)

[PSUFA_Robot]

Thanks!
I’ve been trying this out and have a few questions

1. When I set this up, it makes automatically makes 2 fields in my Table B.
   One called “Table A” that just copies the Primary Field (ID #) of the values being copied from Table A. And another field that has the info I want to copy.
   Is it possible to get rid the Table A Primary Field copy over to Table B?

2. What is the way you suggest to automate this process when adding new rows?

Thanks!
Jacob

[TheTimeSavingCo]

PSUFA_Robot:

When I set this up, it makes automatically makes 2 fields in my Table B.
One called “Table A” that just copies the Primary Field (ID #) of the values being copied from Table A. And another field that has the info I want to copy.
Is it possible to get rid the Table A Primary Field copy over to Table B?

Hmm, sounds like the data you’re copying over has a comma in it! You’re going to have to replace the comma with something else I’m afraid. When we paste comma separated values into a linked field, each value gets treated as one record

PSUFA_Robot:

What is the way you suggest to automate this process when adding new rows?

This would depend on how your rows are getting added to Table A. If these are made via a form or an API call, using the trigger “On record created” would do fine.

If you’re creating these manually, we need to make sure the automation only triggers after the common ID# has been fully keyed in, and the simplest way to handle it would be to just use a checkbox field to trigger the automation. (Honestly, the field’s created manually it might be simpler to just paste it in)

[PSUFA_Robot]

Thanks again.
I think I got a working system going based on your first example.

For the 2 fields question above I think I can state the issue better.

Can I link two tables just with a look up field, and not needing to first assign a field “link to another record” first? It makes an extra placeholder field that I don’t use.

Thanks!
j

[Vivid-Squid]

PSUFA_Robot:

Can I link two tables just with a look up field, and not needing to first assign a field “link to another record” first?

Hi @PSUFA_Robot,
Look Up fields only work with Linked Records. If you do not want to see the created Linked Record, you can Hide the field from View.

[Alexey_Gusev]

You can also consider gathering all data into one table and use different views for different sets of data.

Airtable Support

Combining multiple tables into one table using views

  In general, you can think of a table as a list containing all the people, ideas, or items of a specific type. For example, a table could be a list of blog posts or recording artists on your recor...