1 of 1 people found this helpful
Is the reference number the link in the relationship?
Yes it is.
Then you should probably use a dedicated field for the foreign key that the user never sees. Then, changing the reference number doesn't break the relationship. It's just another field on the related record that will should automatically update.
Unless I'm just not following you. If not, can you provide a sample file or some screenshots
Plus 1 on reply by SteveMartino proper implementation of primary keys means that this type of thing is never allowed to happen in the first place.
But if you need a short term fix for the current design while transitioning to a better one where this is not needed:
Find the related records with this value FIRST. Go TO Related Records is an easy way to do this. Then use replace field contents or a looping script to update those records. Then update the parent record's match field to the same value in order to maintain a correct "link" between the two tables for these records.