Linking fields to a Master table using non-primary fields

[Jamie_Rood]

Hi there,

I have a series of quality assurance data for my Online School which has been populated using different google forms and is therefore stored in different google sheets. I am bringing the data into Airtable to use an interface to visualise it better. The tables include:

Lessons 

Videos 

Graphics 

Proofing 

among others.

The unique identifiers for all of the records in these tables are the Subject, Grade and Module number. I therefore want to connect these tables based on these fields so I can populate all the data into a single table. 

I understand that linked records only work using primary fields (in this case, the timestamp), or a script. However, I have not been able to write a script that does not yield an error. 

Any assistance is greatly appreciated to identify an easier way to do this if possible.

Many thanks,

Jamie

[ScottWorld]

You don’t need to write a script for this. You can do this with automations — either Airtable’s built-in automations or by using an external automation tool like Make. 

If you use Airtable’s built-in automations, you can just use the “find records” action to find the record you’re looking for, and then link from there. 

Sorry that I can’t walk you through the entire process here because it would take me too long to type up all the steps, but it’s pretty easy. If you get stuck and you’d like to hire an expert Airtable consultant to help you, please feel free to contact me through my website: Airtable consulting — ScottWorld

[Stephen_Orr1]

You might use use lookup fields to display data from a linked table in another as read-only. Decide what your master table/entity will be and then create a series of linked record and lookup fields to create a single view in this table. Hope that helps!

[Jamie_Rood]

Thank you @ScottWorld @Stephen_Orr1 

I have created a linked field that links the Master Table to the Restructure - Lessons table and indicated the fields from the Restructure - Lessons table that it needs to look up.

I have then created an automation where:

-When record is created in Restructure - Lessons

-Find records where Subject = Subject, Grade = Grade and Module number = Module number 

-Update records (here is where I'm stuck)

I keep getting the error 'The record does not exist'

Any advise on how I can set up the update records step to make this work?

Thanks so much,

Jamie

[Stephen_Orr1]

Interestingly, in Airtable when you create an automation action step and attempt to save, it forces the automation to test itself on your existing records even if one does not exist.

Make sure there is at least one record that meets the conditions you set in the Find Records action and save/test again.

[Jamie_Rood]

Thanks @Stephen_Orr1 I have set up the automation and turned it on, and created some test records where I map specific fields that need to be updated when the conditions are met.

Unfortunately this is still not working. Please see this Loom video for my explanation. 

Any assistance would be greatly appreciated. Thanks

[Alexey_Gusev]

Hi, 
You can link it without automation.
Duplicate your timestamp field (in Master, for example). Change primary field to the formula exactly as in the Subject Grade Module field. Check it to be all unique, choose 'Unique' as field total and ensure that number of unique values match records count. (If not, add more field(s) to formula). Now switch to other table, create linked field (to the Master) and copy-paste whole Subject Grade Module column into it. It should link all records. If some unlinked left, that means Master has no such value. Now return to Master table, turn primary field back to Date and choose the format it was before (you have duplicated field nearby to compare). Field will become empty (but links will stay connected). Now copy duplicated field to the primary and you will see links still connected, according to Subject Grade Module matching.

then you can remove duplicated field to perform cleanup and repeat all procedure to link any other table. It might seem a bit complex, but after a few repeats, you will do such actions faster than time needed to read this reply ))

[Jamie_Rood]

Thanks @Alexey_Gusev I have tried most of it and it seems to be working.

One thing I can't understand is the data in my Master table is in a set order (copied and pasted from a sheet). Therefore the data from the lessons table needs to map directly to the same Subject Grade Module number in the Master. 

For example, I can't have a record that shows Maths Grade 10 Module 4 as the primary field in the master, but links to Science Grade 11 Module 5 data in the linked record. 

This is surely where some kind of find records automation needs to run?

Thanks,

Jamie

[Alexey_Gusev]

To be honest, I couldn't understand your question, and why did you mention records order.
The purpose of steps I described - temporarily swap your 'index' field with primary field, in order to set proper links. In usual normal state, you should be able to link tables without scripting and automations, that means at least one of two tables has unique primary field matching with values in some column of second table.
But sometimes life is far from our expectations, so we need to use tricks and workarounds.
Linking by paste column in Airtable works in a following way - for each pasted value, it search record with that name (name of record is a value of it's primary field) and then links to it. BTW, it ignores 'Limit selection to a view' and 'Allow multiple links' settings. If you have non unique primary field, it links only to the first found record with that name, and order is not guaranteed. If it can't found such record, it creates a new record.
So, if you try to link to a new empty table, and paste some random non-unique column X there, result will be a pivot table with unique set of column X values and multiple linking.
Note that new records can't be created in such way, when your primary field is formula (or other computed type)