Help me generate unique codes with a running COUNTIF script

[Ollie1]

Hey guys,

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:

1. The customers first name.
2. The first letter of their surname.
3. 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.

Thank you
Ollie

[Alexey_Gusev]

Hi,

you can link your table1 to empty new-created table2, copy-paste abbreviated names to link field, switch to table2, add Count field to link field, return to table1, get this count field as lookup

in such way, you receive total number, but it doesn’t help to set unique number for each of them
to set numbers for each abbreviated name via script, you may use Map data type. but if you can’t write such script, you still may use formula.

• ensure your customer full names list containing unique values (you may add autonumber if needed)
• use similar (as count) approach to evaluate “number in set”
  example:
  you have
  Tim Smith
  Tim Short
  Tim Scott in table1
  all are linked to table2 as TimS, with “count”=3
  you should add fullname lookup in table2, returning to table 1 as
  “Tim Smith, Tim Short, Timm Scott” (i call it “allnames”)
  use something like FIND(“full name”,“allnames”)-FIND(“full name”,SUBSTITUTE(“allnames”, ‘,’ , ‘’)+1
  the logic is: you searching name in full list and then in full list without commas. if your name was first, position not changed, if second, position number will be (-1) and so on…

[Ollie1]

Thanks Alexey.

I have got your solution to work.

As my customer list continues to populate, will my existing customers continue to be in the same position in the formula you stated above?

I need it so that as more customers/abbreviated names are added to the base, the unique codes of my existing customers remain the same. Is this going to be the case in your solution? If so, what controls do I need on the base to ensure this remains the case?

I should add. I would ideally like it so that the first instance of any abbreviated name in my customer list is assigned the number 1 in their discount code e.g. ‘BobM_1’. It looks like the numbers are assigned in reverse order in your solution, with my oldest customers getting the highest numbers.

I really appreciate your help Alexey. I would also be interested in a scripting solution as I believe that is probably more stable and scalable, but this is super helpful and may be the way forward for at least the time being.

[Ollie1]

Okay. I managed to write a script that works. I am sharing my script below in case anyone stumbles upon this thread.

When a record is created, the script looks at a field (in my case the customer’s abbreviated name), and spits out a running count of the number of occurrences it has appeared in across all records in my table, sorting by the date the record was first created.

Any feedback would be welcomed.

The script runs when a new customer is created in the table. The automation that triggers the script passes in the customer’s abbreviated name (named ‘CustomersABNAME’ in the script below).

// input relevant field from automation
let project = input.config();
//Direct script to table, sorting by desired field
var table = base.getTable("Customers");

var query = await table.selectRecordsAsync({
    sorts: [
        {field: "Created At", direction:"asc"},
    ]
});

//define all records which have the same abbreviated name as the new customer as the target for running count
let targets = query.records.filter((record)=> {
    
        return record.getCellValue("Abbreviated name") === project.CustomersABNAME
    });

//perform a running count on target records

let runningTotal = 0;
for (let record of targets) {
    runningTotal += 1;
    await table.updateRecordAsync(record, {
        'Running total': runningTotal,
    });
}```

[Alexey_Gusev]

Hi,

Well I think scripting solution is better in your case, if you want that existing codes remain the same. Also, you don’t need to rely on linked fields and additional tables. My formula solution was just improvisation from head, i never tested it in real task.
it nice that you know how to use “script step” and define input variables in a correct way.

but I see some possible issue
your script updates all customers with matching CustomersABNAME each time it runs, even if they already have ‘Running total’ filled

• if you remove any customer from the “middle”, and script runs again for that ABNAME, it will “shift” numbers created later than deleted customer.
• considering all existing customers for now have their ‘Running total’ filled, each time the script runs, you are performing several redundant update operations, while you need to only update one record

By the way, just for info, you may meet a problem when updating 20-30 or more records in a loop, using updateRecordAsync (single update method). But that’s not accurate, sometimes you can update more without any problem. I experienced that issue in scripting App, but not sure about Automation scripting.

You may ignore that if you never remove customers and your numbers are not so big.
But you may think about some improvements:

…(your script)…

let targets = query.records.filter((record)=> {  
        return record.getCellValue("Abbreviated name") === project.CustomersABNAME
    });
let total=targets.length;
output.set('runningTotal',total);

(…script end…)

then you can use output variable ‘runningTotal’ in next step, where you update record, which triggered the script.

this metod will cause trouble if you remove somebody. you may not rely on count then, instead you have to find maximum existing vaue and increase it by one. note that javascript sort should be used with attention to data type.

looking to your script again, i thought that better solution is to
use ‘desc’ sorting instead of ‘asc’
use
let target = query.records.find(...
then
target.getCellValue('Running total') will be your maximum.
the rest - just a matter of “don’t mess with data types and get 5+1=51”
anyway, i just brought some ideas, and you are to decide whether to use it.

btw, that’s maybe classic and well-known, but i feel it should be here