Help

More efficient way to query records?

Topic Labels: Scripting extentions
9663 16
cancel
Showing results for 
Search instead for 
Did you mean: 
Bruce_Halliburt
6 - Interface Innovator
6 - Interface Innovator

Is there a way to retrieve specific records without iterating through all the results of a table using
table.selectRecordsAsync() or view.selectRecordsAsync()? Something like SELECT / WHERE queries in SQL? Or in AirTable terms, a Filter? This would be far more efficient than having to iterate through returned records checking for the records I’m interested in…

16 Replies 16

I did a quick write up of Javascript filter here:

This appears to only work on the first field in a table which is the index. This doesn’t work for other fields in the table?

Hi @Lisa_Hagan - yes the script is specifically pointing to the email field in my example:

let filteredRecords = query.records.filter(person => {
    return person.getCellValue('Email').includes(searchTerm)
})

But this could be changed to match against any field in the table

HI @JonathanBowen, any field could be searched on but the return value will always be for the first field.

H @Lisa_Hagan - what you get back is an array of the filtered records. By default this contains the id and the name (the Airtable record ID and the primary field value:

Screenshot 2020-09-07 at 20.20.26

But you can loop through this array and access any of the fields attached to each record. So if I have this:

Screenshot 2020-09-07 at 20.21.23

Then I can access the first name like this:

for (let person of filteredRecords) {
    console.log(person.getCellValue('First Name'))
}

So, the full script is:

let table = base.getTable('People');
let query = await table.selectRecordsAsync();

let searchTerm = await input.textAsync('Enter your search term');

let filteredRecords = query.records.filter(person => {
    return person.getCellValue('Email').includes(searchTerm)
})

console.log(filteredRecords);

for (let person of filteredRecords) {
    console.log(person.getCellValue('First Name'))
}

Or more generally:

for (let person of filteredRecords) {
    console.log(person.getCellValue('YOUR FIELD NAME'))
}
Lisa_Hagan
4 - Data Explorer
4 - Data Explorer

Thank you Jonathan. This makes sense.