|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Auto Generate Transaction no with (DDMM0001)this format [message #378879 is a reply to message #378785] |
Fri, 02 January 2009 04:13   |
 |
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
It is because you are selecting COUNT(*) from a table which is - from a form point of view - the same as it was during the time you inserted the first record into a data block.
How come? Because these changes were NOT committed!
One would tell: so commit after inserting every record! OK, that might be a solution, but - do you really want to do that? What happens in a multi-user environment? Imagine two users filling in the same form (different data, of course). Every one of them selects COUNT(*) which is the same. DDMM is also the same. Finally, when they commit, you'll get DUP-VAL-ON-INDEX error because both of them will have the same key value!
Therefore, using something like "COUNT(*) + 1" or "MAX(value) + 1" is a bad idea in multi-user environment.
What you might do is to use a sequence. It will always return unique values, nonetheless the number of users - every "sequence.nextval" will return a unique number. However: this will most probably result in gaps (DDMMssss, ssss = sequence):02010001
02010002
02010023
... If that's OK, it will simplify the job.
Or, yet another idea: as the first part of the key value is not discussable (it is always DDMM), perhaps you could use one of Forms system variables: :SYSTEM.TRIGGER_RECORD. It returns number of a current trigger record so you might LPAD it in order to create unique keys for records you are entering. Though, it would also jeopardize uniqueness (as every other user would use exactly the same :system.trigger_record values).
In my opinion, you should stick to a sequence.
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
