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

[Julian_Kirkness]

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?

[Jeremy_Oglesby]

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

[Bill_French]

Julian_Kirkness:

what if you want a subset - for example all those records

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.

[Kasra]

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.

[Bill_French]

Kasra:

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?

[Kasra]

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!

[Bill_French]

Bill.French:

… 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?

Kasra:

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

[Bill_French]

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

[Kasra]

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?

[Bill_French]

Kasra:

can you share more about your use case for typecast

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.