A Hard Lesson

[LaRetta]

I wanted to share a very bad experience, hopefully to prevent someone else from ever doing something so stupid. I had two complete sets of dBs. Set #1 was being modified at the design level (calcs added, scripts, etc.). I was using Set #2 to continue data entry. My plan was to delete all records from Set #1 and import all updated data from Set #2 when ready to apply the new changes.

I completed this switch of dBs and was quite proud of myself. Everything appeared fine. Over the course of this week, I have entered 50+ new Clients, Contracts, etc. This morning, I realised I have DUPLICATE PRIMARY ID's throughout all my dBs

It seems that my one-time delete & import didn't take into account that several fields are based upon auto-entry allocation numbers, and I didn't think to go to Field Definition, and RE-SET the serial numbers to the last number +1 of my new (larger) dBs. And each new Contract, etc. passes on this duplicate ID throughout my structure. I will have a mess identifying and correcting this without losing my relationships.

During the import, I considered the data in the FIELDS, but not the AUTO-ENTER functions. I just didn't know ... a very hard lesson ...

LaRetta

[BobWeaver]

Well, don't feel too bad. I've done it too. That's one of the advantages to using Captkurt's unique ID system. You don't have to remember to reset serial numbers. Unfortunately, I have inherited the job of maintaining a particularly badly set up database system that is still running on FM version 3. Since that version doesn't let you set the next serial number under script control, you have to remember to do it manually. Every so often imports have to be done from other files. Once or twice I've found that serial number hasn't been reset properly and so much new data has been added since the import, that it's not practical to go back to a backup.

Something to remember is that any records that are imported or manually added will be at the end of the database when the records are unsorted. So, you can search for duplicate serial numbers, unsort them, then find the first record where the numbers jump back, then manually re-serialize them from that record onwards with a replace command. Before you re-serialize them, you probably will want to have a temporary serial number field where you can store the incorrect serial numbers, in case you have to refer back to them. Then, you will have to go into the related files and correct any erroneous values there too. Again, the same rules apply. Unsort the list and look for the first records added after the import was done. All records before that should be okay. The ones that come after this you will likely have to manually check to see which of the two possible parent records they relate to. But, you may be able to use some information peculiar to you system to help figure that out. You can create two reverse relationships back to the correct serial number field, and the incorrect serial number field. Any records that only have a valid relationship to a single record should be okay, and you can omit them. Hopefully, all that you will be left with, will be a small set of records that you can then manually check.

Another thing that is very valuable, is to have a creation date field and modification date field in every file. This also helps to isolate the good records from the bad ones.

[LaRetta]

Hi Bob,

Thanks for the suggestions - that will certainly help. I began designing and utilizing FM *on the fly* and have been overloaded ever since. Another hard lesson ... I had on my list that each dB should have a CreationDate/ModificationDate, but just hadn't 'gotten around to it.'

Luckily, it appears that I'm dealing with only approximately 40 new records but since they all are n:n, it will still be difficult to determine which Contract goes with which Client, etc. I worked all night to meet a critical deadline (Therapist Paychecks), only to discover at 5:30 this morning that I'm dealing with a bogus dataset. I feel a bit shell-shocked! Did I really say I liked database design? I must have been drunk

LaRetta

[Vaughan]

Been there, done that too. It's now in my "list of traps to look for."

[Lee Smith]

What is Captkurt's unique system?

[LaRetta]

Hi Lee,

His unique primary key post can be found at the following url:

Unique Primary Key

And thanks Bob and Vaughan, for making me feel a bit better about my error.

LaRetta

[LiveOak]

I'll add my name to the list. I had one to fix just recently and I have NO excuse, as I work with Kurt.

-bd

[Lee Smith]

Thank You LaRetta

Lee

[jasonwood]

While we're on the topic of "things not to do"...

I once decided it would be a good idea to add a field to a line items database with tens of thousands of records... from home. I don't remember the particulars, but it took a LONG time over my cable modem, and gave up part way through the night... when I got to work the line items database was corrupt. Lost about a months worth of invoices! ...but only a day of unprinted ones, which we managed to figure out. The rest could be re-entered from paper with a few days of labour.

Which brings up another important lesson... after you implement a backup solution... periodically make sure it's WORKING.

And if your backup overwrites the previous backup... swap media/drives every so often, so you don't backup a bad copy over a good copy!