>>>>>>While not a large table about 2000-3000 records it has losts of fields. He asked how hard
>>>>>>would it be do re-sequence the fields so they are sequnecial (i.e. nolonger as missing
>>>>>>values). Is there an efficent way of doing this? If so what is it?
>>>>>
>>>>>Would it work for you to make copies of those tables, associate them with a DBC, set cascading
>>>>>referential integrity on updates, make your changes, and then copy the data back to the original
>>>>>tables?
>>>>
>>>>My question is once I do that what is the best way to resequence the key field which is defined
>>>>as a 9 character field, but is really a nine digit value with leading zeros, i.e. the simplest method
>>>>the comes mind is in a SCAN loop start at the first record and for each record replace the
>>>>current value of the key field with an integer value converted into a 9 character value with leading
>>>>zeros.
>>>>
>>>>Is this the best approach or is there a better one?
>>>>
>>>>The key field is not linked to any other table in any way (other fields are, but they are used
>>>>purely as decodes in reports).
>>>
>>>Oh, I assumed you meant that the key field appeared in other tables: that's why I suggested using
>>>referential integrity to make the updates. Yeah, your method above should work.
>>
>>Thanks for reply. I wrote a quick program and it seems to work.
>
>Great. :-) Did you use the PADL() function to get the leading zeros, or did you add them another way?

I used PADL(), FWIW here is the code (original table is master.dbf).

LOCAL linvnum

*** delete table mastern if it exists
IF USED('mastern')
	USE IN mastern
ENDIF
IF FILE('mastern.dbf')
	DELETE FILE mastern.db
ENDIF

*** select table master  
IF NOT USED('master')
	USE master EXCLUSIVE IN 0
ENDIF
SELECT master

*** make a copy of the table
COPY TO mastern
IF NOT USED('mastern')
	USE mastern EXCLUSIVE IN 0
ENDIF
SELECT mastern

*** add an index to the the table
INDEX ON invnum TAG INVNUM 

*** replace existing inventory number with a sequencial number
i = 1
GOTO TOP 
SCAN
	linvnum = PADL( i , 9 , '0' )
	REPLACE invnum WITH linvnum
	i = i + 1
ENDSCAN	
? 'last new invnum = ' , linvnum