Get the number of records in a table via API

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!

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.
2 Likes

How do you “perform a non-field query to get the record count”? 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);
1 Like

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

1 Like

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.

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

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.

1 Like

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.

1 Like

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.

LOL. Messy? Indeed!!! But that’s Airtable’s middle name (Air-Messy-Table).

The question you must ask -

Is it better to pull down 40,000 items to count them or call (a) which calls (b)?

Polling-based synchronization strategies are really inefficient. They don’t scale and in Airtable’s case, they can dramatically impact end user’s performance. Have you considered an action-based event handler that keeps your data in sync?

I agree with Bill that it is messy but also a better method of getting a record count than the alternative presented.

Having to read all of the records in a table to get the total count is fairly common in database systems.

Airtable webhook automations are still also technically in Alpha, so new features might still get added. Although it isn’t clear if it will ever get out of alpha or if it is under active development or not.

It also wasn’t clear in the original post that the target needing the total was another Airtable base.

I agree. I never disputed that. My comment about messiness was regarding the multi-tiered calling process (Item A calling Item B calling Item C) that was proposed. I still don’t see what’s wrong with reducing the number of calls from two to one by using some form of caching system.

While less risky, you’ll churn through your automation count quite quickly because the automation will be called for every new record. For example, if 100 new records are added at once, it will run 100 times, with each run linking one record. Your description makes it sound like it will only run once for each batch of new records, which is not the case.

1 Like

This topic was solved and automatically closed 3 days after the last reply. New replies are no longer allowed.