- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Jan 30, 2025 01:16 AM
I have this code that works well when filtering a field that is SIngle Line Text
Solved! Go to Solution.
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Jan 30, 2025 05:41 AM
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);
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Jan 30, 2025 05:41 AM
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);
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Jan 30, 2025 10:24 AM
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: