>I've taken over an app that uses sys(2015) to generate values for a candidate key. Users are getting "Unique index violation" errors occacionally and have to re-enter data. I cannot change the data structure to hold more characters.
>
>I'm replacing 2nd and 3rd chars with info from userid, in hopes of greater chance of uniqueness.
>
>Any better ideas for a temporary quick fix?
>
>Much Thanks
>Jeff
Jeff,
If you could go for a new generator it'd be better. Till then for the structure you've on hand :
first char. of sys(2015) is always an underscore. Then rest of chars represent the time in base 36. It's safe to change first char. You could do this for example :
-Keep a table with MAC addresses of users
-On entry to app find station's MAC address in table
-Store chr(recno('Stations')) && oApp.cStation = GetStation()
-Stuff(sys(2015),1,1,oApp.cStation)
Field default value might look like :
Stuff(sys(2015),1,1,iif(type('oApp.cStation')='C',oApp.cStation,GetStation())
This would allow up to 255 stations to produce unique IDs (provided none of the computer clocks falls back - very unlikely to produce an existing key though)
PS: Still doing some kind of generating a new id. However from performance point this would be fast IMHO.
sys(2015) generated value could be casted to base62, base222-255 to provide more space for stationID and station count limit increases to 0xFFFFFFFF.
Wearing my flamesuit another side note (bad I don't have enough knowledge to defend or demonstrate this, just personal experience) :
Even though I use integer surrogate keys a lot I don't put integer field as the first field. If I can't find any other char field I place a dummy char field as the first field. Using integer field as first one caused me enough headaches in the past (as per corruption).
Cetin