Restart serial number daily

[Kevin Mullins]

I have a requirement from a client to create a consecutive ID number that starts from 0 each day, and increments on record creation.

 

Final solution would look like: VAL-YYMMDD-### (VAL-140506-003)

 

I have the first part worked out VAL-YYMMDD, but I am having a problem thinking through how I would have the number start from 000 each day.  I was wondering if MAX might get me something but I just can't come up with a way to construct it.

 

Any thoughts would be greatly appreciated.

[doughemi]

Try an auto-entered 

 

Let(
[last_sn = GetNthRecord ( YourTable::< YourSNfield> ; Get ( RecordNumber ) - 1 );
last_serial = Right(last_sn; 3);
last_date_text = Middle(last_sn; 5;6);
last_date = Date(Middle(last_date_text; 3;2); Right(last_date_text; 2); "20" & Left(last_date_text; 2));
current_date = Get(CurrentDate);
current_date_formatted = Right(Year(current_date); 2) &  Right("00" & Month(current_date);2)  &  Right("00" & Day(current_date);2);
the_sn= If(last_date  = Get(CurrentDate);SerialIncrement(last_serial; 1); "001")
];
"VAL-" & current_date_formatted & "-" & the_sn
)

PS: You're not going to use this as a key field for relationships, are you?

[comment]

I have a requirement from a client to create a consecutive ID number that starts from 0 each day, and increments on record creation.

 

Unless you implement a routine that resets the serial by script once every day at the start of the day, you run into danger of generating duplicates when two users create a new record each at roughly the same time.

[Kevin Mullins]

Try an auto-entered 

 

PS: You're not going to use this as a key field for relationships, are you?

 

 

Thanks for the suggestion, I will try it out in the morning.  And no this is a user field, I don't use any user fields for keys, just purely metadata.

[comment]

Tock, tock... is this on?

[Kevin Mullins]

Try an auto-entered 

 

Let(
[last_sn = GetNthRecord ( YourTable::< YourSNfield> ; Get ( RecordNumber ) - 1 );
last_serial = Right(last_sn; 3);
last_date_text = Middle(last_sn; 5;6);
last_date = Date(Middle(last_date_text; 3;2); Right(last_date_text; 2); "20" & Left(last_date_text; 2));
current_date = Get(CurrentDate);
current_date_formatted = Right(Year(current_date); 2) &  Right("00" & Month(current_date);2)  &  Right("00" & Day(current_date);2);
the_sn= If(last_date  = Get(CurrentDate);SerialIncrement(last_serial; 1); "001")
];
"VAL-" & current_date_formatted & "-" & the_sn
)

PS: You're not going to use this as a key field for relationships, are you?

 

Thanks Doug this worked perfectly.  I may make the users click a button to set this to try and avoid the issue that Comment was warning me about.

Unless you implement a routine that resets the serial by script once every day at the start of the day, you run into danger of generating duplicates when two users create a new record each at roughly the same time.

 

Thanks for the words of caution, there are only a few users so I don't think it will be a problem, but there is no reason I couldn't have a button to set the value to put a little delay into the process.

[comment]

there is no reason I couldn't have a button to set the value to put a little delay into the process.

 

I am afraid you are missing the point here. As long as the first user has not committed his/her new record (for example, they went to get a cup of coffee, or they got interrupted by a phone call, or they are contemplating what to enter), any other user that creates a new record during this interval will get a duplicate serial.

[David Jondreau]

If the OP is actually using a script to create the serial, and not auto-enter it, then that should work.

[comment]

If the OP is actually using a script to create the serial, and not auto-enter it, then that should work.

 

How so?

[David Jondreau]

By scripting the Commit[].

[comment]

I am afraid I don't see how that changes anything. Do you mean forcing the commit of a new record immediately upon creation? That's not quite the same thing as "using a script to create the serial". It would shorten the gap considerably, though. OTOH, it might have unwanted repercussions. OP didn't say anything about gaps in the sequence, in case records are deleted.

[David Jondreau]

" there is no reason I couldn't have a button to set the value to put a little delay into the process."

 

I read to mean he is going to have a button that says "Set Serial". User makes a new record, does some data entry, then clicks Set Serial. That sets the next number based on the calc above and commits the record.

 

There's a slim possibility users may click on the button at the exact same time, but I don't think that's a real concern.

[comment]

User makes a new record, does some data entry, then clicks Set Serial. That sets the next number based on the calc above and commits the record.

 

Ok, that's practically the same thing - the operative principle being commit immediately after evaluating the calc. That would lower the danger of duplicates - and require careful crafting of the user interface. All this to avoid the dead simple solution of resetting the next serial number as God and FMI intended...