Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

Re: Count the amount of times a number appears in a column

5213 6
cancel
Showing results for 
Search instead for 
Did you mean: 
Adam_Kramer
6 - Interface Innovator
6 - Interface Innovator

Hi all,

I have three columns: Member Number, Referrer, and Referrals. I am trying to count how many records contain the Member Number in Referrer, for the Referrals field. In other words, if I have 100 member records, and 5 records have “24” in the Referrer field, I would want the Referrals field in Member Number 24’s record to say 5 (the amount of times they reffered members).

Does this make sense? Any ideas on how to do this?

Thanks so much!
Adam

15 Replies 15

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:

01%20PM

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

25%20PM

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

14%20PM

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.

57%20PM

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?

image.png

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.

image.png

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