Generate Case Number

Hello Guys,

I would like to change my rule for generating of case number.

At this moment I'm using something like this 

"APP" &
text(now(),"yy") &
text(now(),"mm") &
text(now(),"dd")
&
"-" &
ri!request.id,

where id is id of my case in DB. This number for example is "APP-191004-10".  

I would like to change this rule for having "id" reseting every day. What i mean is for example users create 100 case this day so we start APP-191004-1 and the last number for today would be APP-191004-100. On the next day this number starts from 1 APP-191005-1. Can you help me with remaking this rule

Thanks 

Dawid :)

  Discussion posts and replies are publicly visible

Parents
  • Hi Dawid

    I've done this before in the following way:

    • create a database table that holds the current value if 'id'. Initially set the value to 1
    • alongside this value have another column that holds a date value
    • use Appian (or a Stored Proc) to retrieve the current value of 'id'. If the date in the DB is the same as today's date then add 1 to the value of 'id' and write that back to the DB along with the current date
    • if the value of the date in the column is different to today's date simply re-set 'id' to 1 and write it back along with the current date

    (Use of a Stored Proc is more robust as it allows you to lock the current row and thus ensure concurrent requests to it do not result in the same value being retrieved/written)

Reply
  • Hi Dawid

    I've done this before in the following way:

    • create a database table that holds the current value if 'id'. Initially set the value to 1
    • alongside this value have another column that holds a date value
    • use Appian (or a Stored Proc) to retrieve the current value of 'id'. If the date in the DB is the same as today's date then add 1 to the value of 'id' and write that back to the DB along with the current date
    • if the value of the date in the column is different to today's date simply re-set 'id' to 1 and write it back along with the current date

    (Use of a Stored Proc is more robust as it allows you to lock the current row and thus ensure concurrent requests to it do not result in the same value being retrieved/written)

Children
No Data