RENUMBERING FIELDS IN JOINED TABLES

[Simon Hall]

Good morning. I have two tables joined by a field called Visit, which is simply a chronological number. For one entry in the first table, there can be many entries in the second table. I would like to renumber the Visit field in the first table. How can I force FM to automatically renumber the Visit field in the second table?

Thanks

[comment]

t cannot be done "automatically". You must script this (or do it manually, step-by-step) - and do it very carefulIy. 

This is a type of action that should never be necessary. Your parent table (in fact, every table) should have an ID field containing a value that is both unique and permanent - usually an auto-entered serial number or a UUID. This field should be the match field in the relationship to the child table. I strongly recommend you switch your solution to this arrangement first. Then you will be able to renumber the parent's Visit field without affecting the child table at all.

 

Edited August 24, 2021 by comment

[Simon Hall]

Thanks. Understood. Easy enough to generate a serial number for the parent table, but how would I apply that to the child table so that they still match correctly?

[comment]

Once you have populated the ID field in the parent table, define a new ParentID field in the child table. Place it on the layout of the child table. Show all records. Click into the ParentID field and select Records > Replace Field Contents… > Replace with calculated result:

YourParentTable::ID

Then you can switch the relationship to use these two fields instead of the Visit field (which is no longer needed in the child table).

Make sure to have a backup before you try this, as there is no undo if you make a mistake.

 

[Simon Hall]

That's great. Thanks very much for your help!