I have a base with a few thousand customers in it. Each customer has their own record, which includes the time and date that the customer first made a purchase from my store.
I’m looking to generate a list of unique (but memorable) referral codes for each customer in my base.
To achieve this, I would ideally like to generate unique codes for each customer by conflating:
- The customers first name.
- The first letter of their surname.
- The running total occurrences of any given abbreviated name.
(With 1) and 2) put together being the customer’s abbreviated name.)
So, if your name was Bob Matthews, and you were the first Bob M that had ever made a purchase from my store, your unique code would be BobM_1. If you were the second Bob M to ever make a purchase, your code would be BobM_2, and so on.
Example list of customers (sorted by when the customers first made a purchase.)
Tim Smith > TimS_1
Kate Field > KateF_1
Tim Short > TimS_2
Ben Thomas > BenT_1
Richard Burton > RichardB_1
Ben Thwaites > BenT_2
I can extract the customer’s abbreviated name into a field via a formula. However, I am struggling to find or write a script that is able to give me a running count of the number of instances of any given abbreviated name.
I could do this in Excel with a COUNTIF formula, but I’d love to be able to do it natively and automatically within Airtable.
I have seen this Airtable Scripting, but after trying all day I don’t appear able to augment it to fit my use case.
I would be incredibly grateful if any of you could give me a hand.