More efficient way to query records?

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…

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)

1 Like

@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.

1 Like

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. :wink:

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.

1 Like

@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:

2 Likes