More info on linking records please!

[Bruce_Halliburt]

AirTable team: I’m not finding enough info in the documentation to know quite how to link records. I have a Contacts table and an Organisations table in my base. I want to find an organisation and link the a contact to it. I’m guessing the way to do this is to firstly find the record ID of the organisation (through iterating through the result of myOrganisationsTable.selectRecordsAsync() ) and use this to set the corresponding field in my contacts table? Do I need to do the same thing the other way round too - so add the contact ID to the Contacts field in my Organisations table? Some code examples along these lines would be good…

[openside]

Yes, you would need to find the record id of the linked record. Not having a way to query is going to be a real pain point for getting that id since looping through all records is only way.

But, you’ll only need to find the linked record for one side of the link. Once you add it, the reverse link coming back will happen automatically, so don’t need to update in both tables.

[Stephen_Suen]

Hi @Bruce_Halliburton, thanks for writing in.

Updating a linked record in one table will also link the record in the other table.

Here is a simple example:

let contactsTable = base.getTable('Contacts');
let contactsQuery = await contactsTable.selectRecordsAsync();

let organisationsTable = base.getTable('Organisations');
let organisationsQuery = await organisationsTable.selectRecordsAsync();

// Link the first record in Contacts to the first record in Organisations
let contact = contactsQuery.records[0];
let organisation = organisationsQuery.records[0];
await organisationsTable.updateRecordAsync(organisation, {
    'Contacts': [{id: contact.id}],
});

[JonathanBowen]

Here’s my variant: I’ve assumed there’s some data in the contact record that can be used to determine the Org.

So in this (somewhat contrived) example, the domain of the email, less the “.com” is the Org name (lower-cased).

let contacts = base.getTable("Contacts");
let orgs = base.getTable("Orgs");

let contactsQuery = await contacts.selectRecordsAsync();
let orgsQuery = await orgs.selectRecordsAsync();

for (let contact of contactsQuery.records) {
    for (let org of orgsQuery.records) {
        let email = contact.getCellValue("Email");
        email = email.split('@')[1];
        email = email.split('.')[0];
        let orgName = org.getCellValue("Name").toLowerCase();
        if (email == orgName) {
            await contacts.updateRecordAsync(contact, {
                "Org": [{id: org.id}]
            })            
        }
    }
}

It loops through every contact and for each contact, loops through the orgs until it finds a match.

JB

[Stephen_Suen]

Great idea! To avoid the nested loop, you could also create a mapping first based on an email domain field:

let contacts = base.getTable('Contacts');
let orgs = base.getTable('Orgs');

let contactsQuery = await contacts.selectRecordsAsync();
let orgsQuery = await orgs.selectRecordsAsync();

// You could also use an ES6 Map here:
// https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Map
let orgIdByEmailDomain = {};

// Map each email domain to an org record id
for (let org of orgsQuery.records) {
    let emailDomain = org.getCellValue('Email domain');
    if (emailDomain) {
        orgIdByEmailDomain[emailDomain] = org.id;
    }
}

for (let contact of contactsQuery.records) {
    // Look up this contact's email domain
    let email = contact.getCellValue('Email');
    if (!email) {
        continue;
    }
    let emailDomain = email.split('@')[1];
    let orgId = orgIdByEmailDomain[emailDomain];

    // If our mapping contains this email domain,
    // link this contact to the corresponding org
    if (orgId) {
        await contacts.updateRecordAsync(contact, {
            Org: [{id: orgId}]
        });
    }
}

[Bruce_Halliburt]

Thanks all for your help here - much appreciated! :slightly_smiling_face: