Re: Automatically populating linked records
New Airtable user here, so this is a pretty basic question (and so hopefully a straightforward answer!), though I can’t find existing guidance in the forum.
Specifically, I’m having no luck creating a “Link to another record” column that automatically populates itself with those linked records.
The Project tracker template provides an example of exactly what I mean: the Task column is linked to the Tasks table and automatically fills with any new records that are added to the latter.
When I attempt to create a new “Link to another record” column with this functionality, however, it’s blank and stays blank (doesn’t pull in existing or newly created records from the Tasks table).
What am I missing? Do I also need to create an automation, app, or something else to do the work of pulling in the records? (I don’t see any such automation or app in the template though).
Any guidance extremely appreciated - thank you!
Accepted Solutions
I wrote a simple script to populate fields after creating a Link to Other Records Field, but because not everyone comes from a developer background, it may seem a little tricky. It does require some setup (preferably when creating a record), but in the end, it saves me a LOT of time with one click of a button.
*Note: my script is designed to link different tables, so I have not tested whether it links different bases.
The key to unlock the "link" is a UUID rather than an Airtable Record ID. I generate a common UUID to be stored in both records of the respective tables. For example:
If I have a table of Employees that I want to link to a table of Businesses, I would give each record in the Business table a filed called "UUID," and on the Emplloyee table, I would create a field called "businessUUID," which would correspond to the proper UUID from the Business table. See here:
Business Table
Employee Table
It takes some effort to generate the UUID's and get them configured correctly, but even this can be automated! It's best to add the UUID's when creating a record, so I realize this might be difficult for complex, existing bases.
And here's my script:
const businessTable = base.getTable('Business');
const employeeTable = base.getTable('Employee');
const businessFieldUUID = businessTable.getField('UUID');
const businessPrimaryField = businessTable.getField('Business Name');
const employeeFieldBusinessUUID = employeeTable.getField('businessUUID');
const employeeFieldLinkToBusiness = employeeTable.getField('LINK to Business');
const buinessesResults = await businessTable.selectRecordsAsync({fields: [businessFieldUUID, businessPrimaryField]})
const businesses = buinessesResults.records
const employeesResults = await employeeTable.selectRecordsAsync({fields: [employeeFieldBusinessUUID, employeeFieldLinkToBusiness]})
const employees = employeesResults.records
for (const employee of employees) {
const matchingBusiness = businesses.find((business) => business.getCellValue('UUID') === employee.getCellValue('businessUUID'))
if (!employee.getCellValue('LINK to Business')) {
employeeTable.updateRecordAsync(employee, {
'LINK to Business': [{'id': matchingBusiness?.id ?? ''}]
})
}
}After customizing the script to fit your needs, run it, and watch the fields populate 😁
I hope this might be helpful to someone!
