Hello,
When users insert a new row into a table, I want to auto-assign an ID to the new record.
The ID number has to follow the format YYMMDDXX where XX is an integer value starting at 0, so any records entered today would be 09070700, 09070701, 09070702, 09070703 etc. Tomorrow's would be 09070800, 09070801, 09070802 etc.
Can this be done, and if so, how?
Presumably it would be done when tablename.RowMod = 'A'?
Is this correct?
Thanks for any help in advance.
Scott.
You have to add a trigger procedure to your table (DataDictionary)
like this example on the table State:
TRIGGER PROCEDURE FOR Create OF State.
DEFINE BUFFER OldBuf FOR State.
DEFINE VARIABLE li AS INTEGER NO-UNDO.
SESSION:DATE-FORMAT = "ymd".
FOR EACH OldBuf FIELDS() NO-LOCK:
li = li + 1.
END.
ASSIGN ID =
INTEGER(STRING(TODAY,"999999") + STRING(li,"99")) NO-ERROR.
IF ERROR-STATUS:ERROR THEN RETURN ERROR.
Hey.
Thanks very much for the advice, much appreciated. Thank you for the code too, thats great.
Scott
Oooops !
I've forget to filter the counter only for TODAY.
The right way is like this:
FOR EACH OldBuf FIELDS()
WHERE ID GE INTEGER(STRING(TODAY,"999999") + "00") NO-LOCK:
li = li + 1.
END.
While the proposed solution should work, please reconsider your design. Among other things, it will break if you ever get over 100 entries per day.
It is a general rule of data base design that one should never use "meaningful" keys and especially one should not use a concatenated key. Much better to use a sequence as an arbitrary record identifier and leave the date as a date or date-time field. If useful, you could even include sequence within day as a separate field.
Also, what happens when you create record 09070701 and then figure out something was set incorrectly and it should really be on the 8th, but you have already created 09070801?
With separate fields and an arbitrary key, you can make such corrections and not destroy the relational integrity of any links. And, if you sort by date then ID, if you change the date as above it will still sort in the right place.
Hi Thomas,
I hear what you're saying. I wanted to do it just by incrementing an int value and having a separate datetime column but it was decided to go this way so now I need to think about the best way to implement the chosen solution.
I appreciate the advice though, thank you.
You should go back and fight again. You're right. Doing it this way is bad practice. Bring up the examples I gave. And, when they say, "never happen" point out that "nver happen" always happens.
OK, I got permission to do it my way so just want to use an int value to be unique identifier for each new entry in the table.
I want to read out the highest existing value into a variable then increment it by one then assign it in the new row. In SQL I would have had this query written without thinking about it but am floundering around writing it in Progress.
Anyone know of a good online language guide to Progress, or failing that, an SQL to Progress translator?
Re the guide, you are aware that the full manual set is on-line here at PSDN. That's really all there is.
You probably aren't going to like this, but I wouldn't do it this way either. If you read the last record and add 1 you are setting yourself up for conflicts. Either you read it exclusive lock, in which case some other user trying to get it at the same time won't be able to lock it and will have to loop waiting, or you read it no-lock and you have two users adding 1 to the same number and having to handle the resulting conflict. Why not just use a sequence. Designed exactly for the purpose. If you wait until you have validated everything and then, in a very small transaction, grab the next value and commit the record, there will be very rare rollbacks and thus very few gaps, if any.