Tag a field with a Unique Number

[Jman311311]

I have a database called master.dbf that has 100,000 records. 1 of the fields is called Unique. The Unique field is blank. I need a program that can start tagging the unique field with the number 1, then the next record will be tagged 2, etc...

Basically trying to tag each unique field with a unique number. How do I do this?

Thanks again for your help.

 

[JRB-Bldr]

A unique value for a field is one thing and a number representing some 'known' parameter such as record number is another thing.

You can get a unique character string by using the SYS(2015) utility.

You can generate your own new incremented value from previous values and use it.

You can use the RECNO() of the individual record, but if you DELETE and PACK records, then over time this number might not be unique.

You can run a record counter routine and increment the value as the next record is encountered, but, again, if you DELETE and PACK records, then over time this number might not be unique.

Good Luck,
JRB-Bldr

 

[Jman311311]

I already packed the records. This is just a one time thing. How do I append the record number into the unique field?

 

[JRB-Bldr]

You will most likely have a problem with a field named UNIQUE

That is a system 'key' word which will likely cause difficulties in your use of it.

I just now tried to create a new table for testing using the field name UNIQUE and I got an error.

Lets assume that you are using another field named KEY
If so then...
REPLACE ALL KEY WITH RECNO()
will replace all values of the KEY field with the record number.

However, just because you PACK'd the table today and eliminated all DELETED records, does not mean that you will not need to do it again in the future.
If you should do so you would be introducing a possible problem for future record entries if you were to reference by record number.

Good Luck,
JRB-Bldr

 

[Nigel Gomm]

2 suggestions..

1. have a separate control table that contains the last unique id used.

e.g.

(assuming set reprocess to automatic)

if rlock('control')
select master
append blank
replace unique with control.lastid+1
replace control.lastid with master.unique
unlock in control
else
*!* bomb
endif

Worth having a startup routine that does something like...

select max(unique) from master into array lazy
if lazy(1) > control.lastid
replace control.lastid with lazy(1)
endif

2. Upgrade to VFP9 and use an autoinc integer field

3. Don't use RECNO(). It's just asking for trouble.

 

[TamarGranor]

You can definitely use RECNO() to set up these unique values initially. Then, if you need to be able to add new records and make sure they also get unique values in this field, use something like the code Nigel posted.

Tamar

 

[alvechurchdata]

Something like

replace all unique with recno()

will put a unique number in each record. Then use the master table technique to assign a unique number to new records as they're added. If you rely on the record number in production use then it's just a matter of time before two users try to add records more or less simultaneously and both get the same "unique" value.

Geoff Franklin

http://www.alvechurchdata.co.uk