More info on linking records please!

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.

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}],
});

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}],
});

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

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}]
            })            
        }
    }
}

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}]
        });
    }
}

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}]
        });
    }
}

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