Auto generate unique ID

[meekychunky2004]

Hi all

Hope someone can help this extrememly novice with a massive project .... all help very much appreciated!!! Have had a look around and not found the answer.

Problem 1 ....

I want to generate a unique ID for a table named language, from the first 3 letters of an entered record, ie French, ID needs to be FRE.

Problem 2 ...

When I have managed to generate the language ID I need to create another unique ID for a book which consists of the language ID and 5 incremental digits, ie FRE00001 then FRE00002

Thanks in advance

 

[FoFa]

Does the sequence portion just a number that increments from 1 and keeps going, or unique to each prefix. What mean is:
FRE00001
FRE00002
ENG00003
GER00004
SPA00005

OR
FRE00001
FRE00002
ENG00001
GER00001

 

[meekychunky2004]

unique id

Thank you!! The second sample:

FRE00001
FRE00002
ENG00001
ENG00002
ENG00003

Does the sequence portion just a number that increments from 1 and keeps going, or unique to each prefix. What mean is:
FRE00001
FRE00002
ENG00003
GER00004
SPA00005

OR
FRE00001
FRE00002
ENG00001
GER00001

 

[FoFa]

Well, when doing this I usualy like to keep the prefix and the sequence field as seperate fields on the database, and just put them together when needed. Either way you most likely would have to do a get max to obtain the current highest sequence number for a prefix. THere is a slight draw back in that if you have a lot of users, in theory one could assign the number to a prefix if two folks adding a new row with the same prefix hit enter like at the same time. In reality I have never had this happen, but I always limit the code between the max check and the insert new row, by making them happen one right after the other. Basically right before you insert a new key, you need to do something like this.

2 fields, Prefix and suffix (suffix is the sequence number)
CurrSeq = dmax("Suffix","MyTable","Prefix = 'FRE')
CurrSeq = CurrSeq + 1
Insert new row using the updated CurrSeq for the suffix.

1 Field
TempString = dmax("MyKey","MyTable","MyKey like 'FRE*')
CurrSeq = 1 + val(mid(TempString,4))
TempString = Left(TempString,3) & trim(str(CurrSeq))
Insert new row using the updated TempString as the new key.

This is one way of doing it.

 

[meekychunky2004]

Thank you, I have tried this but am getting nowhere fast. Please could you explain exactly how to do this?

Well, when doing this I usualy like to keep the prefix and the sequence field as seperate fields on the database, and just put them together when needed. Either way you most likely would have to do a get max to obtain the current highest sequence number for a prefix. THere is a slight draw back in that if you have a lot of users, in theory one could assign the number to a prefix if two folks adding a new row with the same prefix hit enter like at the same time. In reality I have never had this happen, but I always limit the code between the max check and the insert new row, by making them happen one right after the other. Basically right before you insert a new key, you need to do something like this.

2 fields, Prefix and suffix (suffix is the sequence number)
CurrSeq = dmax("Suffix","MyTable","Prefix = 'FRE')
CurrSeq = CurrSeq + 1
Insert new row using the updated CurrSeq for the suffix.

1 Field
TempString = dmax("MyKey","MyTable","MyKey like 'FRE*')
CurrSeq = 1 + val(mid(TempString,4))
TempString = Left(TempString,3) & trim(str(CurrSeq))
Insert new row using the updated TempString as the new key.

This is one way of doing it.

 

[Mile-O]

This thread may be helpful.