You need to link records by a VesselID value that never ever changes no matter what happens to that vessel. The simplest FileMaker method is to define a field, I'd call it __pkVesselID as an auto-entered serial number and use it to link Vessel records to other tables. When a boat changes hands and data about that vessel changes such as the "Rego" (whatever that is), the serial number in this field remains unchanged and thus still links to the same related records.
In my naming convention here, "pk" stands for "primary key" and I precede it with two underscores so that anytime FileMaker lists fields in alphabetical order, it automatically sorts to the top of the list, which often saves me time finding that field for a relationship, value list set up, etc...
The way correctly defined primary keys complicate good user interface design and how to work around those complications to produce a user friendly interface is one of the topics covered in my forthcoming "Adventures In FileMaking #2" freeware database file so I've been reviewing and working on this very concept over the last few days.
Thanks for the reply, would you mind guiding me on how to exactly do this step by step please. I don't really understand all this programming stuff I do try but I just can't seam to get a handle on it.
The problem is that while I know what to do, I don't know your file, in particular, I don't know what tables and relationships will be affected by this change. These changes could be extensive and you will need to move forward cautiously with numerous saved back up copies to protect your data if a change made by you goes terribly wrong.
I'll provide an example here with just two fields. I'm using terms from your first post, but don't assume that they actually match your current design.
Let's say that you have two tables, Vessels and Radio Log linked by the field "VesselName" instead of a proper serial number based primary key:
Vessels::VesselName = RadioLog::VesselName
If someone risks bad luck by renaming their vessel, it will break the links for that Vessel to any existing records in RadioLog (sound familiar?) But to add in the serial number field, you need to be able to replace vessel name fields with the newly added serial number fields without also breaking the connection for your existing records.
Step 1, Go to Manage|Database|Relationships and add a new number field to Vessels named __pkVesselID. Define it to be an auto-entered serial number field.
Step 2; Save a back up copy of your file. Go to a Vessels layout and temporarily add the new __pkVesselID field to the layout. Switch to Browse mode, select Show All Records and then use Replace Field Content's Serial Number option to assign a serial number to every existing Vessels record in your table. Be sure to select the option that also updates your auto enter options. That way, if yo assign a value to 200 vessels in your table, creating a new record will produce a record with a value of 201 in this __pk field and you avoid getting a duplicate value.
Step 3; Return to Manage | Database | Fields and add a simple number field, _fkVesselID to the RadioLog table. This field is NOT an auto-entered serial number field.
Step 4; Save a back up copy of your file. Go to a Radio Log layout, add the _fkVesselID field temporarily to this layout, Show All Records and use Replace Field Contents to update this field. But instead of selecting the serial numbers option, use the calculation option and specify Vessels::__pkVesselID as the value to be entered into each field. (This layout must be based on an occurrence of Radio Log that is linked to Vessels in the relationship I posted at the beginning. )
Step 5; Save a copy of your file. Go to Manage | Database | Relationships and modify your existing relationship between Vessels and RadioLog to use __pkVesselId and _fkVesselID as match fields in place of VesselName.
Step 6: Review your database design in order to remove all instances of RadioLog::VesselName from your layouts, tables, scripts and calculations. Replace with Vessels::Vessel Name and modify any other design elements as needed to use the new relationship details in place of the old. Eventually, you'll be able to delete the VesslName field from the RadioLog table as this data should only be stored in the Vessels table.
I will play with this over the next few day I think I understand what you are saying, I do backup databases a lot during any modifications as I have destroyed quite a few in my time.
I will let you know how I go and thanks very much for your time and understanding.
Have a good weekend
Thank for your input, I have it working sort of, what I have found now is that the Membership Renewal Form still shows the old boats details, I have put a status field with a value list of A for active and N for not active, is there a script or script trigger that can have the new vessel details appear in the Membership Renewal Form. Have attached an image for you to ponder. The top picture is vessel detail section of the Membership Renewal Form, the 2nd pic vessel details (if boat is still the same) the information in this layout is what is showing even if we have entered a second vessel in vessel details 2 , the 3rd pic is Vessel details 2 which we will use to input the new vessel details if there were to sell there boat. what we need is something like if vessel details status = N (not active) then use vessel details 2 if status = A (active) to enter the relevant info into the first pic(membership renewal form). I hope this make sense, I think I just confused myself.
When combining data from two different tables on the same layout, a key question to ask yourself is "Should I Lookup (Copy) this data from the related table or just display the actual data?"
My guess from what you report is that you are copying over data instead of just displaying it. I would guess that you have fields that use auto-enter field options (either looked up value or a calculaiton) to copy over data from the related table. Thus, when you change data in the related table directly, the data here, which is a copy of the original does not automatically update.
There are ways to force a relookup of this data, but the simpler approach is to remove the fields that copy data over and replace them on this layout with the fields from which they are copying data.
You might find the details fo this thread helpful: Auto Fill
Thanks heaps PhilModJunk, works like a charm.