Sinking Fast

[Roger1]

I have a DB with 5000 records and created a unique ID code based on First Name, Middle Name, Last Name and Date of Birth.

When I write a file note, they are stored in a second DB, utilising a button with a script that says, copy the current ID code and paste it into a new record in the file note DB. A portal from the first DB allows me to view all file notes for one person (because of the ID code match).

The data in the main base is refreshed from an external source weekly, but generally changes are not made to the data used for the unique ID code.

When I was away some space cadet allowed the refresh of data to change names (and therefore the ID code in base one) with the result that all my file notes in the portals dissappeared. They are still in Base 2, just can't see them in portal. I understand that the ID codes no longer match as the one stored in File notes is 'historical' and not interactive with the original. There is obviously a way to make ID Code in base 2 interactive with Base 1, so that a change to the data that reconfigures the code will update in base 2 and all file notes will continue to appear in portal in base 1. Can someone help? Thanks

[danjacoby]

A large item is how the data is "refreshed" in the main DB. Basically, anything you use as a key for relationships should not be allowed to be changed -- at least not wholesale.

Basically, you need to come up with a key that is unchangeable by this "refreshing" method (pun intended). I suggest that, instead of using a calc field, use a text field with an auto-enter calc. That way, this field won't be altered by this weekly update.

Another suggestion is to keep the file notes in a field in the same DB. I always put a "Notes" field into every DB I design, along with a scripted Time/Date button to stamp every note I make. It's not as organized as related notes, but it might help ensure the stability of the connection between notes and the person involved.

Doesn't exactly address your current problem, but this may help keep you from repeating it.

[Roger1]

Thanks Dan. I'll bear the id advice in mind for next time. All in one base would work but the refresh process means a replacement of the original base, which is OK but I needed to keep the file notes - hence the 2nd DB. Thanks for the quick response.