Script to count records in tables and total in base

[Nigel_James]

Here is a script that will count records per table and output the result as a little CSV output.
It is a little quick and dirty and will take ‘some time’ to run if you have large record counts.

Hope it is useful to someone.

let tableCount = base.tables.length;
let totalRecords = 0;
for(let i = tableCount - 1; i >= 0; i--){
    let name = base.tables[i].name;
    let table = base.getTable(name);
    let result = await table.selectRecordsAsync();
    
    totalRecords += result.records.length;
    output.text(name + ' , ' + result.records.length);

}

output.text('Total Records in ' + base.name + ',  ' + totalRecords);

[Bill_French]

Nigel_James:

will take ‘some time’ to run if you have large record counts.

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

[Nigel_James]

Nigel_James:

let result = await table.selectRecordsAsync();

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.

[Bill_French]

Nigel_James:

isn’t a way to get a count without selecting the records

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.

[kuovonne]

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

[Bill_French]

kuovonne:

Tell selectRecordsAsync() that you only want the primary field.

How [exactly] do you do that?

[kuovonne]

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

[Bill_French]

kuovonne:

const queryResult = await table.selectRecordsAsync({fields: [primaryFieldId]});

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

[kuovonne]

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.

[Bill_French]

kuovonne:

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.

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.