I hate it when that happens. In my early projects, when a user nneded to change a partnumber, the first question is the new partnumber already being used. Then I would work from the deepest nest back to the root. Sometimes the routines has to cross reference on another key to find a pointer to the field that needed to be updated. I tried to build systems that had this in mind and the file structure usually had an index.

And I tried not to replicate too much data. SOmetimes you have invoices with the customer code + part number for a lookup key. In this case the project would need to loop through the customer file to get the custmer key, and then look for in in the invoices file

do while !eof(customers')
lcKey=customers.custkey+oldpartkey
do while SEEK(lcKey,[INVOICES],[CUSTPART])
replace invoices.partkey with newOartKey in invoices
enddo
skip
enddo

*sometimes it as simple as
do while seek oldkey
replace mykey with newkey
enddo

You can also implement a link list service (a bogus key) whereby your lookup key is a SYS() generatedkey - and the partnumber is more or less a description. In a database like that, there would only be one place the partnumber would be. The invoice file would have instead the SYS() key (that will never change) and [instead] point to the part using that SYS() generated key. In this case - all the project would need to do is change the partnumber in the parts file.

if couse this means a point will be required for the real partnumber everytime that invoices is out put - but with VFP - it can be done in a single line function (what's cool is that this seek will always be true - so no if found or SEEK function:

SEEK .cPartID ORDER PARTID IN PARTSFILE
lcOutPutPartNo=PARTSFILE.PartNo

or

lcOutPutPartNo=iif(SEEK(.cPartID,[PARTSFILE],[PARTID]),PARTSFILE.PartNo,[** NOT FOUND])

If the system design is simple, you can get by with using real world numbers for index keys. It's more work to change them - but less work to reference them in the application


> Today, I answered a question regarding a change to a product code. Yesterday, I was called in to assist a VFP developer with a similar sort of issue. Yesterday it was a changing organizational unit ID. The problem in both cases was that the product code and organizational unit ID were both used as the primary key field and the value changed. In both of these cases, the problem could have been avoided had the developer simply applied "Ken's Hard and Fast Rule About Creating Primary Key Fields." There is only one rule for primary key fields: "The primary key field has only one purpose - to uniquely identify a record within a table." It therefore may not be used to identify a product or an organizational unit - it may identify a product record but not the product itself. It may be used to identify the orgainzational unit record, but not the organizational unit itself.
>
> I know, it sounds like Ken has truely lost his mind - he is talking in circles. Take a look at the following:
>
> The product code example was one that most can identify with so I will use it. In this case, the product code has a format of 11-22-33-4444. For some reason management decided to change the product code. The new product code would be 11-22-33-4444-A and another related product would probably become 11-22-33-4444-B. This is probably a common enough scenario, but if you use the product code as the primary key, what happens? If you just change the product code, you will be left with all that historical data sitting there with no product record! Your inventory for product 11-22-33-4444-A will be nil. Your inventory table will have a large number of records for product 11-22-33-4444 but there will not be a product record to match and therefore, you will not have a product description.
>
> So how do you solve this problem? You begin by creating a new product ID. Now you go into every table that uses the product ID as the foreign key and you look for any instance of product 11-22-33-4444 and you change it to 11-22-33-4444-A. Go to the next table and do it all over again. When you have finally gone through every table, you now delete the old product record. As you can see, a daunting task to be sure. Oh wait - didn't they tell you? You have a list of 473 similar products that need their product ID's changed!
>
> Lets apply "Ken's hard and fast rule about creating primary key fields." Management likes its product code. The designation 11-22-33-4444-A actually means something (or more likely, at one time in the far distant past, it did mean something.) Can you use the product code as the primary key? It is unique. It would therefore uniqely identify the record. NOPE - don't you do it! Instead, create another field and call it "ProductNo" or better still "RecordID" or even simply call it "ID." If you are working in VFP 8 or better, make this an autoinc field. and make this your primary key field. If you are working with VFP 7 or earlier, you will need to create a procedure that increments the record ID for you. The RecordID field has but one purpose - to uniquely identify the record. The productID can be used to identify the product, but the RecordID is yours and yours alone. Management does not get to change it - for that matter, management does not even have to know that it exists. You don't have to display it - nor do you have to print it.
>
> Now let's see what would have happend had those two developers applied "Ken's hard and fast rule about creating primary key fields." Management says "I need you to change these 473 product codes." You say "OK - not a problem. Have the data entry people go ahead and change them - just make sure that the new product codes are unique or it will give them an error and they won't be able to change the ID for that product."
>
> You still need to ensure that these new product codes are unique (make that index a candidate index,) but the user may change the product ID as needed. Because the product ID is not the primary key, it will never be used as a foreign key in another table (you use the RecordID instead.) If you only find the product ID in the product table, it only has to be changed in one place. If it only has to be changed in one place, you can let a data entry person do it. A far less daunting task - wouldn't you say?
>
> Hope this helps.
>
> Ken
> You shall know the truth - and the truth shall set you free. (John 8:33)