Help

Re: Automatically populating linked records

Solved
Jump to Solution
6526 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Elizabeth_Murna
4 - Data Explorer
4 - Data Explorer

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).
ss

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!

1 Solution

Accepted Solutions
Kevin_DiBenedet
6 - Interface Innovator
6 - Interface Innovator

I have been trying to build an automation that attempts to link records that are now being synced into my base from another base. My new table primary field is a student’s name. It is attempting to match a primary field from my main table which is also the student’s name. This has proven to be successful when the student’s name matches. It fails when the student has a nickname or uses a middle name. Makes sense since the primary fields don’t match.

What I wanted to try and build in the automation was to match and link based on a unique email address that exists in both tables. I can’t tell if this just isn’t possible, or whether I’m not setting up or understanding the limitations of the Automation tab.

Any ideas would be appreciated!!

See Solution in Thread

9 Replies 9
ScottWorld
18 - Pluto
18 - Pluto

Welcome to the community, @Elizabeth_Murnane!

In Airtable, you have to manually link records -- but you can do this through an automation, if you'd like.

Here are some options for linking:

1. You can use Airtable's Automations to automatically link records in Airtable.

2. If you already have a large set of records that you need to link, you can do a one-time-only linking of your records by converting a text field (in table #2) into a linked record field that links to records (in table #1).

3. You can turn to Make's Airtable automations & integrations to do automatic linking for you. Make is a no-code automation platform that gives you tons of customizability & tons of power that Airtable doesn't natively give you.

p.s. If you have a budget for your project and you’d like to hire an expert Airtable consultant to help you with any of this, please feel free to contact me through my website: Airtable consulting — ScottWorld 

Got it — thanks for confirming and for that tip!

Kevin_DiBenedet
6 - Interface Innovator
6 - Interface Innovator

I have been trying to build an automation that attempts to link records that are now being synced into my base from another base. My new table primary field is a student’s name. It is attempting to match a primary field from my main table which is also the student’s name. This has proven to be successful when the student’s name matches. It fails when the student has a nickname or uses a middle name. Makes sense since the primary fields don’t match.

What I wanted to try and build in the automation was to match and link based on a unique email address that exists in both tables. I can’t tell if this just isn’t possible, or whether I’m not setting up or understanding the limitations of the Automation tab.

Any ideas would be appreciated!!

@Kevin_DiBenedetto Unlike most other database systems, Airtable doesn’t let you link records based on the values of fields.

Because of that, you can only match to an entire record, which is designated by its primary field.

Since the email address is not the primary field, you would need to figure out what the primary field value is for the record that has the email address you’re looking for. Once you have the primary field value, then you can link the records to each other.

If you need to search for the records before linking, you can do this with Airtable's automations, but there are some limits you might hit against when searching with Airtable's automations, so I would typically recommend doing all of this with Make's Airtable automations.

p.s. If you have a budget for your project and you’d like to hire an expert Airtable consultant to help you with any of this, please feel free to contact me through my website: Airtable consulting — ScottWorld 

Thanks ScottWorld, that worked for me where endless Airtable videos failed!
But given the amazing functionality of Airtable, I’m surprised that you can’t look up a record entry via a common linking entry (other than a one-off via the primary field). I have linked my tables, but every new record in the first table needs to be manually linked to the second to look up the linked data :frowning: . Is this really true?

Thanks @ScottWorld - this solved the problem I had as a new user of airtable.

For others who may need a pointer, the support article "Converting existing fields to Airtable linked records" was where I found details on how to do this (in particular the section "Linking existing tables of related records in Airtable").

PedroCerrano
4 - Data Explorer
4 - Data Explorer

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

Business Table.png

Employee Table

Employee Table.png

 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!

Here's a link to a UUID generator.

Remember you need a separate UUID for everything you want to identify uniquely, but be sure to share the same UUID with the items you want to link. Again, based on my example above:

A business has its own UUID
An employee has its own UUID
The employee has a 2nd UUID that is the same as the business UUID

Like this...
Business > UUID : 1c1062d8-23ef-48ab-814c-6f33a39b3574
Employee > UUID : b29f9135-900e-41cb-a0cc-71cfca50aa55
Employee > businessUUID : 1c1062d8-23ef-48ab-814c-6f33a39b3574

To simplify the process, here's a script to generate a UUID:

const createUUID = () => {
    return 'xxxxxxxx-xxxx-4xxx-yxxx-xxxxxxxxxxxx'
        .replace(/[xy]/g, function (c) {
            const r = Math.random() * 16 | 0, 
            v = c === 'x' ? r : (r & 0x3 | 0x8);
        return v.toString(16);
    });
}

console.log(createUUID())