Skip to main content

Faster way to count records via API

T

Hi All,

I’m wondering what is the best way to calculate some basic counts on a table via the API. We need to calculate simple counts such as total records with a Status of “Fulfilled” and total records where Status is “Fulfilled” with where the Fulfilled Date is today (see excerpt below). I’ve written a Node Twilio function to retrieve the records and then loop through them and calculate which ones meet the criteria. We then use Zapier to retrieve this output from Twilio and put it into our Slack channel daily. However, as the table grows over 30,000 records, I’m running over the 10 second execution limit in Twilio. Is there a better way, or should I look for a different place to host my function not subject to the 10 second limit?

let metrics = {
  matchedToday: 0,
  fulfilledToday: 0,
  fulfilledTotal: 0,
};

base("Master Intake")
  .select({
    fields: ["Status", "# to feed", "Created Date", "Fulfilled Date"],
    cellFormat: "string",
    timeZone: "America/Los_Angeles",
    filterByFormula: '{Status} = "Fulfilled"',
    userLocale: "en-us",
  })

  .all((err, records) => {
    if (err) {
      console.error(err);
      return;
    }

    records.forEach((x) => {
      let record = x.fields;
      let createdDate = record["Created Date"] ? record["Created Date"].substr(0, 10) : null;
      let fulfilledDate = record["Fulfilled Date"] ? record["Fulfilled Date"].substr(0, 10) : null;

      // If the '# to feed' record is empty, we don't have anything to count, so skip it
      if (!record["# to feed"]) {
        return;
      }

      // Matched Today
      if (
        createdDate &&
        /(Matched|Fulfilled|Not picked up)/.test(record.Status) &&
        isSameDay(createdDate, reportDate)
      ) {
        metrics.matchedToday += +record["# to feed"];
      }

      // Fulfilled today
      if (
        record.Status === "Fulfilled" &&
        fulfilledDate &&
        isSameDay(fulfilledDate, reportDate)
      ) {
        metrics.fulfilledToday += +record["# to feed"];
      }

      // Fulfilled Total
      if (record.Status === "Fulfilled" && fulfilledDate) {
        metrics.fulfilledTotal += +record["# to feed"];
      }
    });

kuovonne
Forum|alt.badge.img+17
  • kuovonne
  • Brainy
  • June 16, 2020

The Airtable Standard REST API has a page limit of 100 records per page, and a rate limit of 5 requests per second. That’s a maximum of 500 records per second, if you time everything perfectly. If 30,000 records match your filter, that will take 600 seconds simply to get all the records.

You cannot get more than 5,000 records in 10 seconds, and even that is a theoretical limit. In practice, the limit will be smaller. If you will need significantly less than 5,000 records, you might be able to get speed up the data a bit if you just need a count of the records, and not the actual data.
You can explicitly tell the API that you do not want any fields, so that it returns only record ids. This will reduce the amount of data that needs to flow over the internet. Note that you can use a field in filterByFormula even if that field is not returned.

If this answers your question, please mark this post as the solution. Otherwise, could you please give a bit more details and a screen capture?

L
  • Lei_Zhu
  • New Participant
  • March 26, 2022
kuovonne wrote:

The Airtable Standard REST API has a page limit of 100 records per page, and a rate limit of 5 requests per second. That’s a maximum of 500 records per second, if you time everything perfectly. If 30,000 records match your filter, that will take 600 seconds simply to get all the records.

You cannot get more than 5,000 records in 10 seconds, and even that is a theoretical limit. In practice, the limit will be smaller. If you will need significantly less than 5,000 records, you might be able to get speed up the data a bit if you just need a count of the records, and not the actual data.
You can explicitly tell the API that you do not want any fields, so that it returns only record ids. This will reduce the amount of data that needs to flow over the internet. Note that you can use a field in filterByFormula even if that field is not returned.

If this answers your question, please mark this post as the solution. Otherwise, could you please give a bit more details and a screen capture?


Hello Kuovonne.
I hope you are doing well.

Recently, I am building script using PHP(Laravel) and want to integrate Airtable API. For now, I have over 4000 records in my table. But when I try to get all records using Airtable API, it returns only 100 records.

This is a really serious problem for me.
I want to get all records at the same time.
Can you let me know how to get it at once?
I am waiting for your reply.

Thanks. Regards

kuovonne
Forum|alt.badge.img+17
  • kuovonne
  • Brainy
  • March 26, 2022
Lei_Zhu wrote:

Hello Kuovonne.
I hope you are doing well.

Recently, I am building script using PHP(Laravel) and want to integrate Airtable API. For now, I have over 4000 records in my table. But when I try to get all records using Airtable API, it returns only 100 records.

This is a really serious problem for me.
I want to get all records at the same time.
Can you let me know how to get it at once?
I am waiting for your reply.

Thanks. Regards


Welcome to the Airtable community!

You can only get 100 records at a time with the REST API. If you want to get more requests, you need to make multiple requests. See the REST API documentation on pagination. You will need to get the offset returned from the first request and submit that offset in your next request for the next page of values until you no longer receive an offset. If you want all records, be sure to leave maxRecords blank.

E
Lei_Zhu wrote:

Hello Kuovonne.
I hope you are doing well.

Recently, I am building script using PHP(Laravel) and want to integrate Airtable API. For now, I have over 4000 records in my table. But when I try to get all records using Airtable API, it returns only 100 records.

This is a really serious problem for me.
I want to get all records at the same time.
Can you let me know how to get it at once?
I am waiting for your reply.

Thanks. Regards


I’ve also had this problem, also seen other people struggle with this as well.

I’m building https://nocodebase.xyz/ which creates an API with your airtable data, it by-passes the record limit, you can call up to 50,000 records with only one request.

M
1 person likes this
  • M
    Magdiel_Juma
M

I think the main point here is that there should be a simple ‘Count’ fuction in the generated Airtable API, which can accept a filter passed in just as the other ‘List’ functions do.

There is no intent here to return the actual values of the data, just the count – which is a single number result, rather than rows and rows of data that needs to be paginated.

M
K
K
8 people like this
  • M
    Matt_Langston
  • K
    Kevin_Cassidy
  • K
    Kevin_Bonham
  • R
    Remi_de_Juvigny
  • D
    d6nomad
  • V
    vicenteguedes
  • E
    EstherDadivas
  • J
    jpdephillips
K
Marcel_Bradea wrote:

I think the main point here is that there should be a simple ‘Count’ fuction in the generated Airtable API, which can accept a filter passed in just as the other ‘List’ functions do.

There is no intent here to return the actual values of the data, just the count – which is a single number result, rather than rows and rows of data that needs to be paginated.


@Marcel_Bradea wrote:

I think the main point here is that there should be a simple ‘Count’ fuction in the generated Airtable API, which can accept a filter passed in just as the other ‘List’ functions do.

There is no intent here to return the actual values of the data, just the count – which is a single number result, rather than rows and rows of data that needs to be paginated.

Yes - this is what I came for. I'm trying to put a progress meter into an API, so that someone can have a sense of how long their query will take. To do this, it would be necessary to get a count of records (just a single integer value that match a query would be quite helpful) - is this possible?

J
1 person likes this
  • J
    jpdephillips

Reply


Most helpful members this week

Terms & Conditions