Autoincrement - idnumbers ....

[drgarytraub]

What would be the best way to automatically generate idnumbers that are based on the first 6 letters of the last name, plus a sequential number like 0000 - if there is already an idnumber based on this last name, then the suffix would be 0001.

So, for example, if John Smith is entered, then his ID is SMITH0000. Then, if another John Smith is entered or even a Jane Smith, then their ID would be SMITH0001.

Any help would be appreciated.

Gary Traub

[Chris.Tanti]

RE: Autoincrement - idnumbers ....

Not sure how you would be able to keep count of the specific surname frequency, but if you are able to use a unique sequential number instead then you could do it by creating two fields.
1.ID_Number, Character, Length 4 (or larger)
Set the field rule to autoincrement.

2.ID, Character Length 30 (or larger)
Set the field rule to calculated and make the expresion something like this
trim(surname)+trim(id_number)

If you realy require the number to be based on the number of surname occurances, then maybe the count function could do it, but it may be tricky getting it to work quickly, and would probably work best in a default expresion as apposed to a calc.

Note sure if that helped!

[drgarytraub]

RE: Autoincrement - idnumbers ....

Hi Chris,

Thanks for your input. Actually, I had created a script that does the job and works quickly. I was just thinking that there might be a simpler way via field rules, but maybe not. Anyway, for anyone who is interested, here is the script that I use:

newid.value=upper(alltrim(left(lastname.value,6)))
newid.value=newid.value+"0000"

dim tstr as c
dim tstrnum as n
tstr="0000"
dim tid as c
tid=newid.value

dim tbl as p
tbl=table.open("patients")
query.filter="left(idnumber,6)=left(tid,6)"
query.order="idnumber"
query.options=""
tbl.query_create()
dim tn as n
tn=0
while .not. tbl.fetch_eof()
tn=tn+1
tbl.fetch_next()
end while
tbl.close()
tstrnum=val(tstr)+1
tstr=padl(alltrim(str(tn)),4,"0")
newid.value=upper(alltrim(left(lastname.value,6)))+tstr

idnumber.value=newid.value

If anyone has a better way, please let me know. Thanks.

Gary Traub

[Al Buchholz]

RE: Autoincrement - idnumbers ....

Will this be in a multi user environment?

Have you tested it to be sure that it won't duplicate numbers if two users overlap work on the same client?

[CALocklin]

RE: Autoincrement - idnumbers ....

Not really much better but you could do this right after the query_create():

tbl.fetch_last()
tstrnum = val(right( idnumber, 4 )) + 1

The tbl.fetch_last() will only fetch to the last record in the query and, since it's sorted by ID number, the last one should have the highest value.

[JohnZaleski]

RE: Autoincrement - idnumbers ....

If you had an index for just the last name and a field called sequence number, this would generate the sequence number and could be used as a default value, saved at end of data entry with a skip: Your id number could be a calc field based on last name and sequence. You have to be careful to consider multi-user possibilities. What if two people are entering a "smith" at the same time.

RIGHT("0000" + LTRIM(STR(VAL(LOOKUPC("L",Last_name, "Sequence", "Customers", "Tag_lastname"))+1)),4)

[drgarytraub]

RE: Autoincrement - idnumbers ....

Regarding the possibility of foul-ups in a multi-user environment, what is the best way to avoid problems?Currently, what I do is to create the idnumber when the record is saved, thinking that it would be unlikely for two users to be saving a new record with the same name at exactly the same time. Is this the way to go?

Gary

[Peter.Wayne]

RE: Autoincrement - idnumbers ....

gary,
i think your method will fail for persons with last names shorter than 6 characters.
e.g.
"Ng,Tran"
you check for 6 identical characters in the last_name + number field, so
NG0001
will show up as a different last name than
NG0002
when you add the 2nd person with the last name of "Ng".

[drgarytraub]

RE: Autoincrement - idnumbers ....

Hi Peter,

I just tested it and it does seem to work, at least on one machine. Here is what I did to test it out. I put in a person with first and last names of Aa i.e., the name I entered was "Aa, Aa". The script generated an idnumber of AA0000. I then entered another person with exactly the same name. The script generated an idnumber of AA0001. Am I missing the point?

Thaks for your help.

Gary

[Al Buchholz]

RE: Autoincrement - idnumbers ....

Is this an id to link to child records?

What do you do when a person changes names? Married, divorced, etc.

What advantage is there in having the id field that includes part of the name?

When some significance is put into an id field, it usually causes trouble.

[Peter.Wayne]

RE: Autoincrement - idnumbers ....

gary,
i think (at least from looking at your script) that the next time you enter some AA,AA, you will get another AA0001. i don't think you'll ever get to AA0002. i could be misunderstanding your code.
- peter

[drgarytraub]

RE: Autoincrement - idnumbers ....

Al:

points are well taken, but is there a better way?

Gary

[drgarytraub]

RE: Autoincrement - idnumbers ....

Peter:

You are 100% correct. I cannot see the error in the script though. Any suggestions? Thanks

Gary

[drgarytraub]

RE: Autoincrement - idnumbers ....

An help on this would be GREATLY appreciated. I am stumped.

Gary Traub

[Al Buchholz]

RE: Autoincrement - idnumbers ....

KISS - Keep It Simple Stupid - It says that right on a sign in my office.

I refer back to that when I have trouble with things like this.

Why not have a simple unintelligent id field that serves as a linkage?

I still don't see the advantage that is gained by this numbering scheme that you are looking at. Enlighten us and we can help provide a solution.