Help

How to filter a linked record from a table by input with a script

Topic Labels: Scripting
Solved
Jump to Solution
232 2
cancel
Showing results for 
Search instead for 
Did you mean: 
Guics
5 - Automation Enthusiast
5 - Automation Enthusiast
I'm trying to filter my extract by the bank that the user select, but i'm always getting null.

What am i doing wrong?

const
 extract = await Extract.selectRecordsAsync({
    fields: ["Name", "Data", "Valor", "Bank"]
}).then(query => query.records)

let bank = await input.recordAsync("Escolha um banco: ", Banks)

const extractByBank = extract.filter(ext => ext.getCellValue("Bank") == [bank])
1 Solution

Accepted Solutions
brandon698sherr
4 - Data Explorer
4 - Data Explorer

Hello @Guics ,

The issue in your script arises from how you're comparing the linked record in the extract table with the selected bank record. In Airtable, linked records are objects, so you need to compare their IDs or the specific fields directly, not as arrays.

const extract = await Extract.selectRecordsAsync({
    fields: ["Name", "Data", "Valor", "Bank"]
}).then(query => query.records);

let bank = await input.recordAsync("Escolha um banco: ", Banks);

// Filter extract by the selected bank
const extractByBank = extract.filter(ext => {
    let bankCell = ext.getCellValue("Bank");
    // Bank cell might be null if there's no linked record
    if (bankCell && bankCell.length > 0) {
        return bankCell.some(linkedRecord => linkedRecord.id === bank.id);
    }
    return false;
});

// Log or use the filtered records as needed
console.log(extractByBank);

The Extract.selectRecordsAsync() method fetches records with the specified fields from the Extract table.

The input.recordAsync("Escolha um banco: ", Banks) method prompts the user to select a record from the Banks table.

The filter method iterates over each record in the extract array.
For each record, it checks the value in the "Bank" field using getCellValue("Bank").
Since the "Bank" field is a linked record field, getCellValue("Bank") returns an array of linked records.
The some method checks if any of the linked records' IDs match the selected bank's ID.


Ensure that the "Bank" field in some records might be empty. Check for null values before accessing the array.
Ensure you are comparing the IDs directly and not using arrays for comparison.

This script should now properly filter the extract records based on the user's selected bank, returning only the records linked to that bank.





Best Regards,
kynect

See Solution in Thread

2 Replies 2
brandon698sherr
4 - Data Explorer
4 - Data Explorer

Hello @Guics ,

The issue in your script arises from how you're comparing the linked record in the extract table with the selected bank record. In Airtable, linked records are objects, so you need to compare their IDs or the specific fields directly, not as arrays.

const extract = await Extract.selectRecordsAsync({
    fields: ["Name", "Data", "Valor", "Bank"]
}).then(query => query.records);

let bank = await input.recordAsync("Escolha um banco: ", Banks);

// Filter extract by the selected bank
const extractByBank = extract.filter(ext => {
    let bankCell = ext.getCellValue("Bank");
    // Bank cell might be null if there's no linked record
    if (bankCell && bankCell.length > 0) {
        return bankCell.some(linkedRecord => linkedRecord.id === bank.id);
    }
    return false;
});

// Log or use the filtered records as needed
console.log(extractByBank);

The Extract.selectRecordsAsync() method fetches records with the specified fields from the Extract table.

The input.recordAsync("Escolha um banco: ", Banks) method prompts the user to select a record from the Banks table.

The filter method iterates over each record in the extract array.
For each record, it checks the value in the "Bank" field using getCellValue("Bank").
Since the "Bank" field is a linked record field, getCellValue("Bank") returns an array of linked records.
The some method checks if any of the linked records' IDs match the selected bank's ID.


Ensure that the "Bank" field in some records might be empty. Check for null values before accessing the array.
Ensure you are comparing the IDs directly and not using arrays for comparison.

This script should now properly filter the extract records based on the user's selected bank, returning only the records linked to that bank.





Best Regards,
kynect

you can also use built-in filter of selectRecordsAsync, which can be set by recordIDs parameter.

I suppose that your Extract table linked to banks in Banks table. That means each bank has respective links to Extract table. The link is array of objects like  { name : 'City Bank' , id : 'recXyZ1234567890' }
at first, you ask user to select record in Bank table. then take cellValue of the field, linked to Extract (you should insert the real name of your field instead of 'Link to Extract')
then using map, you extract only id from each object. The result is array of IDs.
it can be done like  .map(obj=>obj.id)  . But I would prefer more descriptive way:

.map(({id,name})=>id))
then you use this array as parameter for selecting records:

 

let bank = await input.recordAsync("Escolha um banco: ", Banks).
then(rec=>rec?.getCellValue('Link to Extract').map(({id,name})=>id))

const extract = await Extract.selectRecordsAsync({
    fields: ["Name", "Data", "Valor", "Bank"],
    recordIds : bank
    }).then(query => query.records)