Count the amount of times a number appears in a column

This may be possible in a single table, but it would require doing some complex linking to a helper table. Airtable records have no knowledge of each other, and the only way to sorta-kinda make that happen is via tricky table weaving.

What I suggest is re-thinking your base design just a touch. The [Members] table you have now would mostly contain basic member info, but not the info about the referrals themselves:

The referrals would be built a new table named [Referrals], with two link fields pointing back to the first table:

With those links in place, you’ll end up with two matching link fields in [Members]:

The second one is what you want, as that tells you how many times that person referred someone else. Add a count field to tally up those links, and you have the number of times that person referred another.

Hey Justin,

Thank you for the details instructions on how to set this up. Your solution definitely works as a manual process, but I’m not feeling like I’m saving much time if any at all by doing things this way. I also seem to be able to accomplish the exact same thing in just one table, so I’m not understanding the value of the second table at all.

I should have mentioned this before, but I have forms that submit data to the Members table, to import the referral information. Without the ability to search outside of its own record, is this going to be a fully manual process either way?

Thank you,
Adam

Sorry for the delayed reply. I thought about linking between member records in the same table, but with same-table linking, you only get one link field: the one tracking the “outgoing” links. For a situation like this, if Member 9 referred Member 18, you’d add a link to Member 18 on the record for Member 9, and then a count field could tally all of those outgoing links to other records.

Based on your description above, though, I got the impression that you wanted to set up the links the other way, so that when Member 18 comes on board, it’s indicated that Member 9 referred them. That would still be doable by linking from 18 to 9, but you wouldn’t be able to see the total number of members referred by Member 9 directly on their record because there’s no way of seeing those “incoming” links within the same table. Both sides of a linked record setup are only visible when linking across tables, which is why I did the two-table setup.

@Adam_Kramer,

Is this roughly what you’re looking for?

Yes! Exactly! How did you do it?

Well, it’s a bit of a deep story, but I’ll summarize for now and then follow up with more details.

One of the issues with using Zapier (and other tools like it) is that it doesn’t really give integration engineers access to the underlying behaviors. In your example, this is a non-starter for tools like Zapier because your business requirements are complex and beyond the scope of single-pass integrations.

The term “single-pass” refers to a loop across all records during the integration process. Your example is a good one because it requires a multi-pass process (specifically three passes);

1. Iterate all records to determine who has become a member based on a referral and what their current referral count is.
2. Loop across this subset to determine the current referral count and to flag records where previous counts are different from current counts.
3. Loop across this subset to update the actual records with the latest referral counts where - AND ONLY WHERE - those counts have changed.

#3 is extremely important if you want this process to scale to thousands of members and be well-optimized to not abuse Airtable’s API.

Each pass is optimized to be the smallest possible review of the data. As such, while pass one looks at all members with referral activity, pass two is far smaller, and pass three is almost trivial.

For the example data shown, it takes about 130 milliseconds using a Google Apps serverless function. There are less than 10 members, but I believe this will scale to 100,000 users without breaking a sweat and largely depends on member referral activity and the interval between referral count refreshes. If this process is performed every five minutes, there will never be a period that requires significant updates to the table.

I built the solution in Google Apps Script using a new platform I’ve been working on called Airborne. This is not a released toolkit; it’s in development and testing and I’ll be happy to share it with you including all source code as soon as I have time to configure it for your own installation.

The code is pretty straightforward - just 62 lines with liberal commenting.

Ideally, this solution will be deployed via a virtualized block (i.e., an embedded web app where you specify the base ID, your API key, and the table name). Describing the business logic is a lot like a formula. Once configured, the virtualized block will handle all the server stuff, register the chron process at the interval desired, and it will run indefinitely to support the count of referrals for each member.

Without an integrated scripting model in Airtable that also supports record iterations and process automation, we have few alternatives to achieve seemingly simple business requirements such as your own. Airtable also lacks the native ability to call webhooks when events occur in a table. I anticipate this will change soon allowing this process to kick off when - and only when - the member table is modified.

If you are really pressed to have this operational in production and cannot wait for me to tidy this up a bit, I could set up the service in your Gmail or G-Suite email account. All I would need is a spreadsheet you create and share to me (bill.french@gmail.com). Using that I will drop in my code and configure it for your production table (a copy initially for testing).

Hi Bill,

Thank you for creating such a cool and customized solution to this. I apologize for the delay in responding to this. This definitely does the trick, but I’m curious if it’s reliant on Airborne and Google Apps Script? What is the cost to run Airborne? Ideally, I’d prefer to run anything like this locally on my server if I’m going to be running things externally, but open to the possibility of running things elsewhere as needed.

Thanks again!
Adam

Hi Adam,

A lot has happened since I wrote this. In fact, so much has happened I can’t even remember what I wrote here. :winking_face:

Big change - the name Airborne has been adopted by an actual product, a search engine that’s available through professional services at my firm. So, we need to call this something else like Touchstone (a synonym for referral).

Yes. It is code written in Google Apps Script (a superset of javascript) and therefore must run in the serverless Google Apps Script environment. This environment can be created in three ways:

1. In a free Gmail account
2. In a G-Suite account (preferred)
3. In a Google Cloud Platform

That’s fine, here are the steps to doing that:

1. Take the script that I published
2. Translate it into NodeJS
3. Import the Airtable SDK
4. Convert the Airtable get/put calls to the SDK equivalents
5. Create a chron configuration to run the process on an interval
6. Deploy

Hi Bill, I’m trying to deploy your script to perform pretty much the exact same requirement.

I want it to trigger and run from Zapier - as part of the same Zap I already use to write each new User record to my Users table. So after each new user record is created, this script could run and update referral counts.

Could you please provide any comentary on how this might be set up?

Thank you

Sure. To do what I did here, you’ll need the script running in a Google Account (either a free Gmail account or a G-Suite account), This will show you how to create a Google Apps Script project which provides a place for the shared script to run.

In my view, this scripted process needs to run every five minutes to tally the totals and update them into Airtable. There doesn’t need to be any dependencies on Zapier - to runs when it does and this script is simply looking at the latest data and performing the computations.

It’s all free and you’ll be amazed once you get something simple runnin.

Thanks Bill - I appreciate your help.

For my application I have a bunch of things that happen when a new record is written to the Users table, and it all revolves around zapier automations, so I really want this part to be triggered in the same way, rather than on a time basis. I can probably figure out how to make that happen once I actually get the script working by using zapier to call the URL of this script deployed as a web app.

My problem right now is I have not been able to make the script work. Your screenshot starts at line 11 and does not run as is - I’m a complete noob in this stuff but seems clear to me that the script will need to start wih something like:

var Airtable = require('airtable');
Airtable.configure({
    endpointUrl: 'https://api.airtable.com',
    apiKey: 'YOUR_API_KEY'
});
var base = Airtable.base('BASE_KEY');

Thanks again for any further help!

Cheers

var Airtable = require('airtable');
Airtable.configure({
    endpointUrl: 'https://api.airtable.com',
    apiKey: 'YOUR_API_KEY'
});
var base = Airtable.base('BASE_KEY');

Okay - I see where you’re hitting a wall.

Calling the URL of any script deployed as a web app requires a web service, not a timed script that executes as I have mentioned before. A web service like this must be deployed to listen for calls from Zapier and it must be publicly accessible and preferably uses an authorization token of some type to avoid POSTs from bad actors and hacker bots.

You can create just such a service using the doPost() method for the ContentService SDK in Google Apps Script. This is probably the best tutorial for this activity.

function doPost(e) {
  // do some stuff with Airtable data...
  return ContentService.createTextOutput("200");
}

In the spot where I indicated, you would use Google Apps Script and the Airtable API to perform some operations to recalculate fields across the rows.

And since Google Apps Script is a blocking architecture, you needn’t actually return the HTTP response code of 200 because you don’t really care about letting Zapier know when the process completes. Rather, you just want Zapier to kick this process off and let it run until complete. As such, the return() statement isn’t even needed.

Essentialy, you are chaining webhooks where this Google Apps Script webhook server will be called by the first webhook server (Zapier). The endpoint of your Google Apps Script webhook server will become apparent as soon as you run through the tutorial and deploy the server. That URL (trails with “…/exec”) will be what you have Zapier call and pas to it a payload of instructions (or not). It you just want it to run, you don’t have to pass anything to it.

var Airtable = require('airtable');
Airtable.configure({
    endpointUrl: 'https://api.airtable.com',
    apiKey: 'YOUR_API_KEY'
});
var base = Airtable.base('BASE_KEY');

This part is entirely separate from the above response discussing a webhook server that listens for calls from Zapier. The code I published doesn’t disclose all of the underlying API processes necessary to retrieve and update Airtable records. Below are those functions and you are also free to use the javascript SDK instead.

//
// get airtable (universal)
//
function atGetTable_(baseKey, tableName, logit)
{
  logit = (logit == undefined) ? false : logit;
  const options = {
    method: 'GET',
    headers: {
      'Authorization' : 'Bearer ' + airtableAPIKey,
      'Content-type': 'application/json'
    },
    muteHttpExceptions : true
  };
  var response = UrlFetchApp.fetch(airtableAPIEndpoint + baseKey + "/" + encodeURIComponent(tableName), options).getContentText();
  if (logit)
    Logger.log(response);
  return(response);
}

//
// update airtable (universal)
//
function atUpdateTable_(baseKey, tableName, payload, apiKey)
{
  apiKey = (apiKey == undefined) ? airtableAPIKey : apiKey;
  var options = 
      {
        method: 'PATCH',
        headers: {
          'Authorization' : 'Bearer ' + airtableAPIKey,
          'Content-Type'  : 'application/json'
        },
        payload : JSON.stringify(payload),
        muteHttpExceptions : true,
        followRedirects: true
      };
  // Logger.log(airtableAPIEndpoint + baseKey + "/" + encodeURIComponent(tableName));
  var response = UrlFetchApp.fetch(airtableAPIEndpoint + baseKey + "/" + encodeURIComponent(tableName), options).getContentText();
  return(response);
}

Thanks Bill, I really appreciate your help.

I’ll let you know how I go with putting these pieces together!

This seems like such a simple task but in practice is ridiculously complex. is this still the case three years on? As I am looking for the same solution. I am currently looking to move some excel data to airtable but it is so far not near impossible. Simple things like ‘CountIf’ require hugely complex external scripts.