Auto generating transaction number and resetting it every month

Hi All,

I got a scenario where I need to generate a transaction number in the below-mentioned format.

TRN-MMyy-000001

Where TRN is fixed, MMyy is the creation month and year and the last six characters are the auto-generated numbers. 

Rule : 

1. The last 6 digits should be auto-generated in a serial starting from 000001 and incrementing then.

2. The last 6 digits should be unique for a given month-year (MMyy)

3. The last 6 digits should get reset every month. For an example, if TRN-1119-001234 is the last generated number for November-2019 then in December the number to start with TRN-1219-000001.


Any suggestions would be helpful.

Thanks :)

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?....)

Solution

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 

Solution

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 : )

Eduardo Jauch wrote:

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 

Thanks, Eduardo Jauch, for your suggestion. 

As per your advice, I am storing the counter in a separate table. I have created a function that generates the number. There, I am locking the counter record then updating the record after the generation of the number (using the function GetEntityForUpdate()). 

Here I have a question, suppose 2 requests are raised at the same time and the record is locked by one of them, then in that case, will it throw an error or will the second record wait until it is released. 

As per my knowledge in dotnet, the request should wait.


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.

Eduardo Jauch wrote:

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.

Yes, for now, it's working for me, but I need to test it though by creating the scenario. I will update on it soon.