Jump to content

How to update unique ID field in two related files


This topic is 6617 days old. Please don't post here. Open a new topic instead.

Recommended Posts


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...)



Link to comment
Share on other sites

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. wink.gif

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. crazy.gif


Link to comment
Share on other sites

This topic is 6617 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.