Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Mar 05, 2020 04:49 PM
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);
Mar 06, 2020 07:19 AM
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.
Mar 06, 2020 07:24 AM
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.
Mar 06, 2020 07:28 AM
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?
Mar 06, 2020 07:35 AM
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.
Mar 06, 2020 07:38 AM
I see, so you are saying that unless you call selectRecordsAsync({fields: }), you are lessening performance because all fields are retrieved and accessible, right?
Mar 06, 2020 07:44 AM
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.
Mar 06, 2020 07:47 AM
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.
Mar 06, 2020 07:56 AM
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.
Mar 06, 2020 09:44 AM
And you are right again! Approximately 18 times more performant.
Mar 06, 2020 09:51 AM
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.