Help

Scripting - filter by "Link to another record" field

Solved
Jump to Solution
336 2
cancel
Showing results for 
Search instead for 
Did you mean: 
Conor_McKeever
5 - Automation Enthusiast
5 - Automation Enthusiast

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
 
 
 
1 Solution

Accepted Solutions
TheTimeSavingCo
18 - Pluto
18 - Pluto

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

 

See Solution in Thread

2 Replies 2
TheTimeSavingCo
18 - Pluto
18 - Pluto

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
11 - Venus
11 - Venus

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.

Ben_Young1_0-1738260042055.png

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:Ben_Young1_1-1738260266262.png

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.

Ben_Young1_2-1738260630218.png

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:

Ben_Young1_3-1738261368049.png