Faster way to count records via API

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"];
      }
    });

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?