Get the number of records in a table via API

[Evgeny_Bylym]

Hi!

How to determine the number of records / entries in a table via API using curl / php / pyton?

Imagine a table with 12304 entries. Our current stumbling block is the API limit: we can only get 100 entries at a time via the API using PHP. In this regard, we have to make a lot of requests page by page. Then count the number of pages and multiply them by 100. Is there a more elegant way?

Thank you!

[Bill_French]

Evgeny_Bylym:

Is there a more elegant way?

Yes.

1. Create a webhook automation in the base that listens for HTTP requests that identify the table name.
2. When a request arrives, open the table, perform a non-field query to get the record count (this is very fast).
3. Return the count.

[ScottWorld]

How do you “perform a non-field query to get the record count”? I’m assuming this involves a custom JavaScript?

[Bill_French]

ScottWorld:

I’m assuming this involves a custom JavaScript?

Yes, a webhook with a script action. Even with a very full table, this will run in about 5s, whereas, an API process might require 15 seconds or more and will slow even more at scale. I tested the following on 37,000 records with 58 fields. I believe that by selecting fields: you are essentially ensuring that no data besides the record IDs will be returned.

Maybe there’s a faster way to get the record count or even a non-code approach - all ears if there is.

console.log('Get Table Count');
let ordersRecords = await ordersTable.selectRecordsAsync({
    fields: []
});
console.log(ordersRecords);
console.log('Record count:' + ordersRecords.records.length);

[kuovonne]

Bill.French:

I tested the following on 37,000 records with 58 fields.

Did you do a new test? In your test in this thread, your test data set had 22593 records and an unspecified number of fields, and the result was 18 times faster requesting only one field (versus all fields).

[Justin_Barrett]

Bill.French:

Return the count.

This is the problem that I see. Webhook automations can only process incoming data. They can’t send back results. Airtable returns a generic success/failure response to the request as part of the webhook trigger step, but there’s no way to override that.

[Bill_French]

But they can call another end point to pass the value.

[Bill_French]

Yes, this was a separate test because I wanted to make sure nothing had changed in the SDK since 2020.

I’m not convinced this is the most elegant approach, but it’s the one I use and has been reliable.

[Justin_Barrett · Accepted solution]

Bill.French:

But they can call another end point to pass the value.

True, but that just feels messy, doesn’t it? Script A calls Webhook Automation B, which tallies the records and then calls Endpoint C with that value to do whatever is next. It feels like this awkward division of tasks between Script A and Endpoint C would make upkeep more of a pain. Also, if Endpoint C was another Airtable webhook-triggered automation, that eats up the webhook quota twice as fast.

All that aside, I’m curious to hear more from @Evgeny_Bylym re: what the record count is being used for. Is it just displayed in an interface of some kind, or does it have a greater purpose? Either way, perhaps that value could be cached in the target base and retrieved directly when needed. Along that line, the only option that comes to mind would be to have a timed automation in that base save the record count from the main table into a single record in another table. The script in question then retrieves that single record to get the count. It’s kinda messy in its own way—keeping a table with no other purpose than to store a single value, and the retrieved count may be off if it’s grabbed between automation runs—but it’s also simpler because the main script can both retrieve that value and do whatever needs to be done with it.

[Evgeny_Bylym]

Thanks to everybody for the feedback! :slightly_smiling_face:

We use total number of records to track sync progress.
We get 100 records via API. Then 100 more and so on till the end.
Actual number of records helps us to track the % of sync that is done.

Thank you for the idea.
We’ve decided to act in a less risky way:

1. Created a separate table named ‘Total’.
2. Added Link field to initial table. It links records with ‘Total’ table.
3. Added automation that runs when a record is created the initial table. It links all new records in the initial table with a single record in ‘Total’ table if triggered.
4. Created a Count field in a ‘Total’ table. It counts all linked entries.