How to update unique ID field in two related files

[kfok]

I have two files (A and which are related by a unique ID field (ID). When I change the value of the ID of A, the ID of B does not change automatically. Does anyone know how to solve this problem?

[stanley]

Kfok:

In your example, ID B should not actually change when you change ID A. If you want that to happen, why are the records in different files? If, on the other hand, you're trying to figure out why you're losing your relationship when you change the key field (i.e. ID A), the answer is that you shouldn't even be able to change the value of ID A. It's a key field. It should be an auto-enter non-modifiable serial number field. Let FileMaker handle it. If you have a field you're going to need to change, by all means make it something other than the field you're going to base your relationship on. This somehow seems apt on Valentine's Day (or rather, Valentine's Night, when most of my friends were in the pub loathing the fact that they actually had no valentine...)

HTH

-Stanley

[LaRetta]

Hi kfok,

To add to Stanley's points ... if you are changing your unique ID fields it's because you've attached a meaning to the IDs. This is not recommended. You will get yourself in trouble if you do so because you'll be having to change them as their meaning changes; hence running into problems similar to yours and the minute you change one, you break the relationship bond and create orphans. Match your IDs; use your IDs consistently; and forget they mean anything because they simply shouldn't. They represent only unique identifiers between your relationships (for FM to use behind the scenes).

Now ... if you have fields in two different file/tables (non-IDs) that you wish to change in relation to each other that is fine also. Your unique IDs establish the relationship, then just make the (non-ID) field in file/table B use an Auto-Enter Lookup (or calc) and point through your relationship to file/table A's same (non-ID) field.

Now, having said that ... you CAN rename your IDs occasionally (emergency). You would establish a new set of unique IDs using two new fields, ie, in your main file, run a new ID through NewID field, then go to your related table and (using your original relationship) write that new ID into your NewID field in your related, as: Set Field [NewID; OriginalRelationship::newID ]

Once the new IDs have been written, you can use Replace Contents in both files to set the old ID with the NewID field data. I would never do this while hosted. If you really NEED to do this ONCE, be sure you're clear how (test it first) and back up your files before you start. I lost this relationship when new (my related file had 180,000 orphans as a result). And it had even been explained to me beforehand but, although I thought I understood the dynamic, I really didn't. Luckily I had a backup available.

LaRetta