Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

Re: Can table.selectRecordsAsync() query a table or view?

6649 0
cancel
Showing results for 
Search instead for 
Did you mean: 

I see that you can get all records from a table or view - and even sort them. But what if you want a subset - for example all those records connected to another record? It’s not always possible or desirable to create views for this type of thing?

10 Replies 10

Presumably you can do that filtering with JavaScript on the set of all records. I don’t know exactly how it might go, but perhaps something like:

records = table.selectRecordsAsync();
filteredRecords = [];

records.map(record => ({
   if (record.getCellValue("Cell to Check") === "Desired Value")
      filteredRecords.push(record);
}));

:man_shrugging:t2: I’m not great with JavaScript, but I think that should work

Um, the API does support filter queries that use every-day formula constructs. This eliminates the need for special views to pare down the result sets.

Yep, echoing @Jeremy_Oglesby – you can select the records from a table or view, then filter them in your code. Here’s a similar snippet, but showing how to do it from a specific view and filter down to records where the “Name” field is longer than 5 characters:

let query = await view.selectRecordsAsync();
let filteredRecords = query.records.filter(record => {
    let name = record.getCellValue('Name');
    return name !== null && name.length > 5
});

Since the scripting block runs inside your base, it uses a slightly different mechanism than the public API. So right now it doesn’t support filterByFormula queries, but it can quickly load all the records in a table or view instead.

@Kasra, thanks for this guide-point.

To be clear, are there any memory or other client-side constraints at scale when loading all records and forcing the client to deal with the burden of filtering?

We’re going to keep an eye on real-world constraints and resource usage—if you hit memory limits in extremely large tables let us know!

I’ll take that as a “Yes”.

Another question - is there a way to apply typecast=true in the Scripting Block when writing a record or group of records.

Typecast isn’t supported in the scripting block right now. Out of curiosity, can you share more about your use case for typecast – e.g. is it mainly the convenience of automatically creating linked records?

Imagine a block that auto-tags content; this post explains it in detail, the Cliff-notes follow.

  • The words and phrases that trigger the creation of tags is unknown at block run-time.
  • The block scans designated fields to create a list of tags appropriate for a given record.
  • The tags are formulated based on an NLP-like function that eliminates stop-words and determines the tag values.
  • Tags should ideally be stored as multi-select values in a single “Tags” field.
  • Creating these multi-select options is not possible without {typecase : true}.

Ergo, the only way to do this is to push the tags into a text field, and then change the field type to a multi-select after running. Aside from the absurdity of this post-tagging manual process, it prohibits future tagging on new records where new tag values may naturally and predictably occur.

As you can see, this is a show-stopper to using NLP to magically tag content in an integrated fashion and forces me right back to an external API process.