Help me generate unique codes with a running COUNTIF script

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…

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.

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,

    });

}```

// 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,

    });

}```

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