Converting Record ID to Kurt's Method

July 27, 200124 yr

Kurt,

Your method (described in the FAQ) is so good I kick myself for not figuring it out years ago. You can bet that every new design from now on will use your method. Because for many years I have used the simple auto enter serial number I have many, MANY solutions that I now would like to convert to this scheme. It is worth the effort to me to convert my files to your method, but it appears pretty challenging. I hope you have experience in converting old systems to yours and can offer a good way to do it.

The gist of this post is to posit steps for converting legacy systems to this scheme and ask for your comments and improvements. It is a big job for multi-file systems, and before I tangle myself into the process I would like your critique and experience to do it as efficiently and safely as possible. Please bear with my logic and syntax.

The fundamental challenge is to retain all relationships.

These are issues I feel need to be addressed

a. Each file's current Record ID must be retained until the whole conversion is complete throughout the system.

b. Likewise, existing relationships must to be retained until everything is converted.

c. Match fields throughout the system need to be converted as well as each files Record ID

d. Correspondence between old key and match fields and new key and match fields must be established to ensure relationships maintain their integrity

e. Because forty databases have many layouts with related fields and portals, making this process manageable means updating existing key and match fields to the new data, rather than changing layouts to new fields. In other words, I want to end up with existing record ID and match fields to contain the new record ID numbers and auto enter properly.

What I have come up with is the following steps, but I feel like there must be a better way.

1. In each file, create a text field, NewRecordID, auto enter, your calculation.

2. In each file, create a text field, NewMatchField, for each match field already existing in the file. Make each new match field a lookup to collect the respective New RecordID using the existing respective relationships

3. Import the old data into clones with the option to auto enter turned on. This will populate the NewRecordID.

4. Re lookup on the old match fields to populate the NewMatchFields in each file. I should now have parallel fields containing new key and match fields.

5. In each new match field, turn off the lookup. Once the data is in the field I do not want it to change.

6. Change the OLD Record ID field from number to Calculation, Text, equal to NewRecordID. Exit Define Fields to let the calculation complete. Re-enter define fields and change the OLD Record ID field to text, auto enter your calculation.

7. Note each match fields auto enter properties. Change each old match fields to text calculations equal to their respective NewMatchFields, let them calculate as in step 6, the change their field definitions to text and restore any auto enter properties.

8. Check every updated database against the old one to ensure the data, reports, etc. coincide. Check them again.

9. Once everything is working properly, install on the client