Generating unique serial numbers

[Jeroen_Sarink]

Hi all,

Case
I've been working in Airtable for a few months now, preparing to launch a platform for our production planning (replacing our current Excel workflow). Each item that enters our production queue is scheduled for a specific week (2023-03 for example) and should get a unique serial number. The serial number consists of a prefix + an incrementing index number, as follows:

Prefix = Producer code + Year + Month letter. E.g. A23A for an item with Producer code 'A', planned for week 2023-03.

Incrementing index number = 3 digits

Final result e.g. = A23A501

Progress
I've currently set it up so that the calculated prefix for each record links to a table ('SN prefix') containing all prefixes used and the next available index number (rolling up the existing index numbers and adding 1).
When a new record is added, an automation fires a webhook, sending the record ID and prefix part to Make.com. Make then requests the next index number from the prefix table and fills out the serial number in the newly added record. The Make scenario is set to sequential processing, rendering unique serial numbers each run. I'm specifically using Make instead of regular Airtable automations to make use of sequential processing.

However - when I add a number (approx 10 to 100) new records at the same time, Airtable automations seem to run through these records in random order, resulting in unique but scrambled serial numbers. The unsorted (which is a requirement unfortunately) list of newly generated serial numbers would look something like this:

A23A505
A23A504
A23A509
A23A503
A23A508
A23A501
A23A506
A23A502
A23A507

This is of course not the incrementing list I was hoping for.

Could anyone point me in the right direction here? Any help would be greatly appreciated!

[Rupert_Hoffsch1]

Hi @Jeroen_Sarink !

Would you consider using a script for this?

Automation trigger could be that the item enters your production queue and runs a scripting action. This can then incorporate any logic you have.

Flow would be: Select all records, filter for records of current period, increment index by one and construct final string.

Best,
Rupert 

[Jeroen_Sarink]

Hi Rupert,

Many thanks for your reply!

I've tried to stay away from scripting until now, since I'm not familiar with Javascript / similar languages. I guess I can't keep avoiding it...

If you could help me with a rough outline of this script to get me on my way I'd greatly appreciate it! I'll get started learning the basics myself and I'll try to adapt the script to my exact use case. If not, many thanks for pointing me in the right direction!

[Rupert_Hoffsch1]

Hi @Jeroen_Sarink, I'm happy to write a first version if you share the base with me (or a sample / duplicate base following the same logic).

Best,
Rupert

[Jeroen_Sarink]

That'd be awesome! I've sent you a DM with a link to a copy of the table. Many thanks in advance!