Unique ID issue

[jbk]

I have created a simple database program for our company. We have several sales associates using this program on there laptops, and to keep the records unique, I have made the "sale call id" start the sales associates initials. For example, one of the salesman's initials is "TRE", and I have started his database records at "TRE101". I setup the field as auto increment, character and a field size of 8. This would allow this particular salesman to have several thousand records. As records were entered, the first record would start at TRE101 then TRE102 etc...., when it reached TRE999 the next record I thougt would be TRE1000, TRE1001 etc.... What I have found has happened since entering record# TRE999, his next record is TRF001, TRF002 etc... Does anyone know a simple fix or do I have to start the first record at say "TRE10001" and worry about when he has entered tens of thousands of records?

[Mike Wilson]

Re: Unique ID issue

Jon,
Are you using this field as a linking field for child records? If so you will have to also change your child table field. Is there a problem making the field C 12 and TRE000001000 and not worry about it ?

[jbk]

Re: Unique ID issue

Thanks Mike for you reply. This table is not linked to any other table. When you say to make this a C 12 and TRE000001000 are you talking about the first record. If the salesman already has records, can I change the settings without messing up things?

[Mike Wilson]

Re: Unique ID issue

To get all things to new format do this in this sequence. For simplicity, I am going to use the field name YID for whatever your unique id field name is. You will have to change it to be what yours is:

1. Back up the table!! Add a new field to the table to create a new unique id field. Name the field QID and make it character, 12,0. Make the field rule for the QID field calculated with the equation:

substr([COLOR="red"]YID[/COLOR],1,3)+"000000"+substr([COLOR="Red"]YID[/COLOR],4,3)

2. Save the table changes, right click on table, Menu item Utilities>Recalc Calculated fields (field rule). Look at the table and see the QID field should look like TRE000000001.

3. In design mode make the YID field character 12,0 and the field rule calculated with equation QID.

4. Repeat step 2. Look at the table and see your unique field is now in the format TRE000000001.

5. Return the YID field to autoincrement with Data Entry default style Simple and default value "TRE000000001".

6. Delete the QID field.

Hope that helps.

[Tom Cone Jr]

Re: Unique ID issue

Jon, I'm curious how you're getting the auto-increment field rule to use the correct initials for the different sales associates?

[Mike Wilson]

Re: Unique ID issue

Jon, I'm curious how you're getting the auto-increment field rule to use the correct initials for the different sales associates?

Tom,
He wrote the following:

For example, one of the salesman's initials is "TRE", and I have started his database records at "TRE101".

So I'm guessing he began by entering everyone's default and sequence and the autoincrement is taking it from there.

[Tom Cone Jr]

Re: Unique ID issue

Thanks, Mike. Unless he's using different tables for each sales associate how does the auto-increment rule know which "prefix" to use ? Doesn't the rule grab the highest value in the table ? I must be missing something.

[Al Buchholz]

Re: Unique ID issue

Sample Time......

[Mike Wilson]

Re: Unique ID issue

Thanks, Mike. Unless he's using different tables for each sales associate how does the auto-increment rule know which "prefix" to use ? Doesn't the rule grab the highest value in the table ? I must be missing something.

I am assuming they are all independant units. He wrote:

We have several sales associates using this program on there laptops....... I have started his database records at "TRE101".

I was assuming that on TRE's he started the table with TRE101 and made the simple autoincrement default to "TRE101", and on John Q Smith's laptop he started the first record in the table as JQS101 and made the simple default to "JQS001". Then on XYZ's laptop he started the table with XYZ101 and made the simple default "XYZ001".