On 2010 Nov 15, at 9:24, John Raid wrote:

> Hi all,
> 
> I inherited a FM file with a table with owners and a linked by IDkey vehicle
> table.
> 
> Linked to the vehicle table are several other tables like maintenance,
> accessories etc.
> All made in FM4, now converted to FM11 (and still working).
> 
> I have to add a functionality so that a 'vehicle' can change from 'owner'.
> 
> What is the best (easy) way to do this, without losing the history of the
> linked tables on maintenance etc? 
> 
> Now I have zkp_customerID and zkf_vehicleID as key fields.
> 
> Is it safe to just add a second vehicleID value to the zkf_vehicleID and
> make a sort of multi-key value of it, return separated?
> Or will I work myself in a corner.
> 
> I was thinking to just add a second, or several, vehicle key values to the
> zkf_vehicleID by script....
> 
> Solutions appreciated.
> Tx


John, it sounds as if you need an intermediate table, also called a join, link, or merge table. It would sit between a table of vehicles and a table of owners and have a code pointing to each of them. Thus, for example,
   Vehicle ID 1234 = 2009 Ford Focus with VIN 123145678901234 (from Vehicle table)
   Owner ID 5555 = John Smith, 345 Elm St., Wood Violet, WI, 54321 (from Owner table)

Then, if John sold the Focus to Mary Johnson, you'd create a NEW record in this merge table,
   Vehicle ID 1234 = 2009 Ford Focus with VIN 123145678901234
   Owner ID 6666 = Mary Johnson, 456 Oak St., Wood Violet, WI, 54321

and maybe you'd also create a new record for John's replacement vehicle,
   Vehicle ID 4567 = 2010 Chevy Malibu with VIN 231456789012345
   Owner ID 5555 = John Smith, 345 Elm St., Wood Violet, WI, 54321

This leaves you with 1 record for each person and 1 record for each vehicle. Within the record for each, you can create a portal showing ALL the related records from the join file. Thus John's record would show both the Focus and the Malibu, and the Ford's record would show both John and Mary as owners.

The join table should include at least 2 other fields, a starting date and an ending date, so you could readily see that John no longer owned the Focus, because there would be an ending date in that record (the same date, as it happens, as Mary's start date for the same vehicle).