Scripting - filter by "Link to another record" field

Question

I have this code that works well when filtering a field that is SIngle Line Text

empName="Dummy Employee"

let records = query.records.filter(record => record.getCellValue("Name") === empName);

But

If i want to filter a field that is a "Link to another record" field, it will not find it.

let records = query.records.filter(record => record.getCellValue("Linked Name") === empName);

I have a work around where I have a formula field that converts the value in the field "Linked Name" to a string, but i would like to tidy it up.

Has anybody encountered such an issue?

Thanks in advance

TheTimeSavingCo · Accepted Answer

Yeah that's because the value of a linked field is an array, not text.  Try this:let records = query.records.filter(record => record.getCellValue("Contacts")?.[0]?.name === empName);

Ben_Young1 · Answer

Hey @Conor_McKeever!

As Adam called out, when retrieving the value of Airtable's Linked Record field type from the Scripting API, the field returns an array of objects with the following shape:

{ id: string, name: string }

The ID property is the record ID of the linked record, while the name property is the value of the linked record's primary field.

Here's an in-depth demo of how I would approach this problem in a scaled production environment:

I spun up a simple base with two tables: Contacts and Companies.
For now, we'll presume that a Contact has a 1-to-1 relationship model with a Company.

Three of the contacts are related to "Company A", while the other two are related to "Company B." Our initial goal is to use a script to query our contact records and return all contact records that are related to "Company A."

Here's the script I spun up to accomplish this:

const ENV = {
    companyQueryString: "Company A"
};

const getTables = (...ids) => ids.map(id => base.getTable(id));
const [contactTable] = getTables("tbl15jzb3Hxq5VoYj");
const [contactFields] = [
    {
        name: "fldehm1E2EP8wROqu",
        linkCompanies: "fldOTI0GzpY15rX98"
    }
];

class Contacts {
    static #filterRecord(contactRecord, companyName) {
        try {
            const [{ name }] = contactRecord.getCellValue(contactFields.linkCompanies);
            return name === companyName;
        } catch (error) {
            return false;
        }
    }

    static #filterRecords(contactRecords, companyName) {
        return contactRecords.filter((r) => 
            this.#filterRecord(r, companyName)
        )
    }

    static async queryRecords(companyName = "") {
        const { records: [...records] } = await contactTable.selectRecordsAsync({
            fields: Object.values(contactFields)
        });

        if (!companyName){
            return records;
        }

        return this.#filterRecords(records, companyName);
    }
}

await Contacts.queryRecords(ENV.companyQueryString)
    .then((contactRecords) => 
        console.info({
            contactRecords,
            contactNames: contactRecords.map((r) => 
                r.getCellValueAsString(contactFields.name)    
            )
        })
    )

Executing this returns this:

Okay, sweet. That covers a scenario where we want to filter against a single string. Now, for the sake of public knowledge and thought experiment, let's complicate the requirements.

Let's now presume that a single contact record can be related to one or more companies.
Furthermore, let's expand the scope of the search so that we can search for contact records that match any of one or more companies.
I've gone ahead and added a new company ("Company C") to the mix and added the new company record to two of the contacts. My goal will be to find all contact records that are related to either Company A or Company C.

Here's the refactored script that allows me to do this:

const ENV = {
    companySearchValues: [
        "Company A",
        "Company C"
    ]
};

const getTables = (...ids) => ids.map(id => base.getTable(id));
const [contactTable] = getTables("tbl15jzb3Hxq5VoYj");
const [contactFields] = [
    {
        name: "fldehm1E2EP8wROqu",
        linkCompanies: "fldOTI0GzpY15rX98"
    }
];

class Contacts {
    static #filterRecord(contactRecord, companyNames) {
        try {
            const fieldValue = contactRecord.getCellValue(contactFields.linkCompanies);
            const uniqueNames = new Set(
                fieldValue.map(({ name }) => name)
            );

            return companyNames.some((name) => uniqueNames.has(name));
        } catch (error) {
            return false;
        }
    }

    static #filterRecords(contactRecords, companyNames) {
        return contactRecords.filter((r) => 
            this.#filterRecord(r, companyNames)
        )
    }

    static async queryRecords(companyNames = []) {
        const { records: [...records] } = await contactTable.selectRecordsAsync({
            fields: Object.values(contactFields)
        });

        if (!companyNames.length){
            return records;
        }

        return this.#filterRecords(records, companyNames);
    }
}


await Contacts.queryRecords(ENV.companySearchValues)
    .then((contactRecords) => 
        console.info({
            contactRecords,
            contactNames: contactRecords.map((r) => 
                r.getCellValueAsString(contactFields.name)    
            )
        })
    )

The Contacts.queryRecords static method now accepts an array of strings. Those strings are the company names we want to filter against. Additionally, the private filterRecord method has been refactored to check all the linked company records against the array of strings.

As expected, when running this version of the script, we are returned the following:

Scripting - filter by "Link to another record" field

2 replies

Reply

Most helpful members this week

Reply

Related topics

GooglePlay new Subscription APIicon

Google Play Subscription Changes: when will RevenueCat support the new subscription structure?icon

Second app: Could not validate subscriptions API permissionsicon

Warning in google play console even after update revenuecat versionicon

Notice about InAppProduct API usageicon

Most helpful members this week

Sign up

Login with SSO

Login to the community

Login with SSO

Scanning file for viruses.

This file cannot be downloaded