Script to count records in tables and total in base

It will be near instant if you build the name/length list in a string and output the results outside the loop.

Most of the time is taken up with this line.
There isn’t a way to get a count without selecting the records and any table with thousands of records will take a little time to select the records so that you can return the length.

Ahhhh, yes - you’re right. The only way to speed this up is to use promise.all() and remove the awaits. This would allow multiple record selects to run simultaneously.

You can also speed up the process by limiting the fields that you are requesting. Tell selectRecordsAsync() that you only want the primary field.

How [exactly] do you do that?

@Bill.French

const table = await input.tableAsync('Pick a table');
const primaryFieldId = table.fields[0].id;
const queryResult =	await table.selectRecordsAsync({fields: [primaryFieldId]});
const recordCount = queryResult.records.length;
output.text(`Table [${table.name}] has ${recordCount} record(s).`);

I’m not setup to do speed tests on tables with multiple thousands of records, so I cannot give you specific statistics on how much faster this would be. The difference in speed would depend on the number of fields and the amount of data in those fields.

If either of you are setup for speed tests of this sort, would you mind reporting back the results?

const table = await input.tableAsync('Pick a table');
const primaryFieldId = table.fields[0].id;
const queryResult =	await table.selectRecordsAsync({fields: [primaryFieldId]});
const recordCount = queryResult.records.length;
output.text(`Table [${table.name}] has ${recordCount} record(s).`);

Of course, ergo - the most performant approach is to call selectRecordsAsync() without any fields list.

You’re right. No fields is better than even one field. Here is a better version that calls selectRecordsAsync() and returns no fields.

const table = await input.tableAsync('Pick a table');
const queryResult =	await table.selectRecordsAsync({fields: []});
const recordCount = queryResult.records.length;
output.text(`Table [${table.name}] has ${recordCount} record(s).`);

If you do not set the fields for selectRecordsAsync(), which was the case in the original example in this thread, the API returns all the fields. It is the same as with the Standard API.

Hmmm, that’s not my experience, what am I missing?

// Load all of the records in the table
let processTable      = base.getTable("Process Status & Duration");
let queryResults      = await processTable.selectRecordsAsync();
output.inspect(queryResults);

From a performance perspective, this behaviour is ideal; I needn’t tell the method to give me less; it seems to do that implicitly.

// Load all of the records in the table
let processTable      = base.getTable("Process Status & Duration");
let queryResults      = await processTable.selectRecordsAsync();
output.inspect(queryResults);

This seems to make sense, given the fact that to get any field value from a record you’ve queried, you still have to call .getCellValue() on the record. They are not simply properties on each record that can be gotten through dot notation.

// Load all of the records in the table
let processTable      = base.getTable("Process Status & Duration");
let queryResults      = await processTable.selectRecordsAsync();
output.inspect(queryResults);

You don’t see the fields when you use output.inspect(queryResults), because they are not public properties of the record object. But the data for the fields is still there, and available via the getCellValue() method.

Notice that getCellValue() is not an async method. You don’t have to wait for the result. The data is already there.

Okay - so if that’s true, then why would this not expose the Status element in the query results?

// Load all of the records in the table
let processTable      = base.getTable("Process Status & Duration");
let queryResults      = await processTable.selectRecordsAsync({fields: ["Status"]});
output.inspect(queryResults);

The outcome is the same as if not passing any value to selectRecordsAsync().

Once again, what am I missing?

// Load all of the records in the table
let processTable      = base.getTable("Process Status & Duration");
let queryResults      = await processTable.selectRecordsAsync({fields: ["Status"]});
output.inspect(queryResults);

That’s what I mean. You cannot see the list of fields and their values directly through dot notation in the queryResults object, even when they are there.

That is because the fields are not stored as public properties. They are only stored internally in the record object, and only available publicly via the getCellValue method. The output.inspect(queryResults) statement does not call getCellValue, so it has no idea what the field names or values are.

You got the data for the {Status} field. It’s in the query result. You can use getCellValue(“Status”) and see the answer.

Now, try my version that returns no fields {fields: []}, and try to get any cell value. It won’t be there. But output.inspect(queryResults) will display the same thing.

I see, so you are saying that unless you call selectRecordsAsync({fields: }), you are lessening performance because all fields are retrieved and accessible, right?

Exactly! Same as with the Standard API.

I don’t know the exact performance hit, and I’m not equipped to test it against a large dataset. But requesting less data should make things faster.

Ahhhh, file this under “you learn something every day”! Thanks for patiently holding my hand through this exercise. The big question now - circling back to the performance question - is {field:} more performant?

As you correctly assert, the outcome is NOT the same.

Sure, we’re all in this to help each other out, right?

I’m use to being able to google most of my programming questions, but that doesn’t work for the scripting block because the info isn’t widely spread on the internet yet.

As, I said, I’m not setup to do speed tests of this nature. For small data sets, it probably makes no difference. However, for massive data sets with lots of data in lots of fields, I’d bet it does. If either of you are setup to do speed tests with a massive data set, I’d be curious to see the results.

And you are right again! Approximately 18 times more performant.

Thanks for coming back and posting the speed test! I was really curious about the results.

I don’t have any tables with anything close to 20,000+ records.

wooof!

I had no idea all that data for each field had to be loaded in the background - thanks for pointing that out, @kuovonne!

I thought selectRecordsAsync() was just queueing up record identities for batch operations, and providing easy access to the fields/contents (since I assumed all the field data was already “loaded” into the browser by virtue of being in the table already).

Me too! This is a deep insight worth knowing about.

This just sped up my script dramatically!! Thank you for insight!

const table = await input.tableAsync('Pick a table');
const queryResult =	await table.selectRecordsAsync({fields: []});
const recordCount = queryResult.records.length;
output.text(`Table [${table.name}] has ${recordCount} record(s).`);

What does input represent here? Is that a variable that was set elsewhere?

The input object is provided by the scripting environment by the Scripting app, just like the base object and a handful of other objects. They are covered in the documentation.

Note that if you want to count the number of records in an automation script, you probably will not use the input object. Instead you will need to hardcode the name of the table.

Thanks so much for this, it's very useful!