Re: 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.
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);
- Iterate all records to determine who has become a member based on a referral and what their current referral count is.
- Loop across this subset to determine the current referral count and to flag records where previous counts are different from current counts.
- 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).
