Unique ID composed of other values - Formula field or Trigger?

[biota]

I want to outomatically number projects upon creation, as follows: YYMM##, with ## being a sequential number that resets for each month. 

So the first project created this month would be 211201.

Having hit a wall with formula field, I have set up a trigger that partially does the job. The trigger is creating YYMM (2112) just fine, but I am struggling to get the sequential number after that.

I imagine a sequential number is not too big a deal, but am I making things too complex by wanting it to reset for each month?

Any tips re the below trigger woud be greatly appreciated. Or a formual field would be even better as it seems less complicated?

TIA

 

[ParkLoey]

Hi @biota! You're correct with the fact that resetting the sequence number makes it a little complicated, but you can do it! I attached a copy of my table where I tested this. You can import it to your account to have a look at t he trigger I created  In my sample, I have a date field where I can determine which records were added at what date (used for making the sequence number). I hope this helps!

- LOEY

DummyTbl__2021-Dec-27_2150.zip

[biota]

Thanks @ParkLoey! That is a very complex trigger that I'm not sure I will ever understand 

But I managed to import it to the table, so thanks a lot!