More efficient way to query records?

Hi @Bruce_Halliburton - no, at the moment it doesn’t appear that you can. The best option is to use a view on the table (using a filter) and selectRecords from this, but of course, this is a “fixed” filter, not something dynamic you can apply in the script.

JB

Thanks for confirming this, JB - Not a show-stopper but certainly a nice to have. I’ve not used AirTable’s API but I’m guessing the same is true there…?

Well, in the API, when you list records, you are querying a view from the start by default, but you can also add a filterByFormula param to this to further filter the view records, so this might be better for you.

JB

Scripting looks like it’s got loads of potential. It’s a shame it isn’t possible to query records, especially as the regular API can do this.

Is there a possibility of actually calling the Airtable API from the script editor? Seems a bit crazy, but that could be a way to use filterByFormula.

This is the main use case for me — being able to query data dynamically (rather than the fixed views that are the only way possible in Airtable)

@Tom_Kerswill - the great thing about the scripting block is that you can filter your records with all the power of JavaScript after you’ve pulled them from your table.

Bear in mind that Script Blocks are working in the context of the existing browser app which has already performed a significant series of tasks to bring the data for the base into scope.

Sure, you can do that and this is especially useful if you want to process your data more slowly. As such, your hunch that it is a bit crazy is fully validated. :winking_face:

Sure, this is not an unreasonable assertion or approach in a traditional client-server relationship, but Script Blocks is not a traditional client-server environment. Think of Script Blocks as serverless containers running inside a client that is itself a container which has already asked for and received all of the data associated with a base.

I believe (architecturally) a call to <table>.selectRecordsAsync() is simply a formality; a way to associate existing data pointers with your block. This would explain why Script Blocks are blistering fast.

From my experience – and not without great trepidation – I worried about this exact issue and pondered whether I could be creating performance issues. However, I soon realized that the core task of any Script Block is to simply filter the data (which is already likely in memory) as it pertains to a given process objective. And Javascript is certainly the best way to achieve that unless they also supported Python (I predict that someday there will be a Python script block).

I’m pretty sure this use of javascript for filtering and such is what the dev team had in mind - a climate that frees us to focus on the data, not necessarily getting the data.

Not So Optimal, Perhaps…

Indeed, API queries add a degree of standardization making it possible to assert processes involving the data without crafting a wholly new approach using the raw nature of JavaScript. However, you are free to utilize a more abstract approach similar to (or actually employing) search.js, jsql, or graphql. Doing so might provide a query underpinning that can homogenize data access models across your Script Block apps.

I hope the day will come when it appears to us!

oLπ

Almost certainly it will happen because Airtable is about data and data represents the most effective way to create competitive differentiation and productivity for businesses of all sizes. As such, the nature of business and organizational success in this and coming decades require that we capture, manage, and understand data thoroughly.

Python is the undisputed language of data science and any javascript programmer who has taken the time to learn Python fully understands why it is such an important leap forward when it comes to understanding data.

@Jeremy_Oglesby, would you be able to provide some examples of how to do this? This is actually something that I’ve been looking for instructions on how to accomplish and haven’t really been able to find anything that clearly explains the process.

Thanks!

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

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

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:

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

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'))
}

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

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'))
}

Thank you Jonathan. This makes sense.