This can be rather simple and I don't think you need some code to "reset" it.

You could create an entity like...

[NumberGen]
{
Id: Long Integer/Identifier
Prefix: Char(3)/Text
Year: Date(YYYY)/Text/Integer
Month: Date(MM)/Text
Number: Integer/Text
IsUsed: Boolean
}

Then set a unique index on [Prefix,Year,Month,Number].
*You could also combine the Year and Month field, but you need some logic when inserting to always ensure the same format is being used....

The IsUsed boolean can be used to periodically generate a number of rows for the current year+month+prefix, because if you have multiple processes running they might try to create the same record at the same time so if you have a pool of available numbers multiple processes can use them at the same time!

Also this will also work when trying to create data for the past, you can still use an older date to get numbers for that date, if you would use 1 single point of number generation this would not be possible.

Lastly, you can also choose to ommit the prefix, I thought it would be nice to allow for multiple prefixes to be used, perhaps for different applications?

Also make sure that when using integers, when displaying the prefix you would need to padd the value with 0's until you reach the target length of 6. Though you could store it as a text, integers are relatively faster. (And why would you store human readable data in a database anyway?....)

I would use a table to store the counter for each TRN-MMyy. A new line could be created in many ways, even trough timer, if the TRN is known. 

To get/increase the counter, I would use an SQL with an input and a pair UPDATE and SELECT

UPDATE {Pool} SET @counter = {Pool}.[counter], {Pool}.[counter] = {Pool}.[counter] + 1;

SELECT @counter;

In general this works as the row will be locked for reading and writing during an update, but you must check how this works on the database you're working with. 

Cheers 

Yeah, If you have created date time field then, this logic will also be useful

Your assign can be like this

Syntax Editor Code Snippet

"TRN-"+FormatDateTime(CurrDateTime(),"MMyy")+"-"+AutoGeneratedNumber

To get the auto generated number

you need to check for transaction records

(This is a sample entity)

Set max records (1)

if the record is empty in this table, you need to hard code the value as 10001(intial - default) (add this value as configurable site property)

autogennum = 10001

so in final Transaction value you need assign as

"TRN-"+FormatDateTime(CurrDateTime(),"MMyy")+"-"+AutoGeneratedNumber

Ex: TRN-1219-10001

But if the table record is not empty

Use String Split

input parameter Text as Transaction.value

delimiter as "-"

Autogeneratednumber = textoInteger(String-Split.List[2].text)+1

Ex : Transaction.value ="TRN-1219-10001"

String-Split.list[2].text will be "10001"

autogenvumber will get the value as 10002 ( as per above code)

so finaly ,

the new value will be TRN-1219-10002

This will be the final logic view

Happy Low coding : )

Hello Pranav,

No error will happen. The first GetForUpdate() will lock until the transaction is committed, and only after the second will be able to execute (it will wait).

What I am not sure is if the data the second will get is the NEW number or the OLD one. You will need to test it to see if it works.