changing a one to many relationship...

[Virtus85]

Okay, so I have a relatively smooth running database here, but the design requirements just changed and I'm wondering how to go about this change.

The way it stands now:

Client--

Each client can have many programs, and the programs can have many inputs.

Now, I need to modify the Client to Program relation. Each client can have multiple programs, but any certain one program can be used by one or more of the clients. The way I have it set up now is that the Client ID matches to the client ID in another table, which is assigned to a single program ID, and then all the inputs have their corresponding program ID's.

Realtional databases kinda confuse me, and I dont think filemaker will like it if I just match more client ID's to the progams by entering multiple client ID's seperated by commas.

So what would be the best way to do this without tearing everything down and rebuilding? Or am I making this too complicated and I actually CAN just match client ID C01,C02,C03, and C05 to program ID P0074 by adding them in the text box?

[John Mark Osborne]

What you need is another table called a Join. You have described a many-to-many relationship between Clients and Programs. The proper way to design this in FileMaker is through a Join table. The Join table will have two foreign keys to store the primary keys from Clients and Programs. It is possible these will be the only two fields in the Join table but I can't tell for sure without knowing more about your solution.

Next, create the relationships from Client to Join and Join to Program. Make sure to allow the creation of records in the Join table occurrence from your Client table occurrence. This will allow you to create records through a portal which is described in the next paragraph. It's also a good idea in this scenario to check the delete records option so any time you delete a client, the join table records are removed also.

In your Client layout, create a portal showing records from the Join table. Place the foreign program key from the Join table into the portal and any other fields you want from the program table. Add a value list showing the primary key and another field from programs to the foreign program key.

Now all you have to do is choose programs from the popup menu in the portal and records are automatically created in the Join. This allows you to do reporting from the Join table that you wouldn't get without a join table since you now have a record every time you attach a program to a client. This is amazingly flexible. It also allows you to add fields specific to each program attached to a client (maybe a quantity field).

BTW, you can create a multi-key by entering multiple Client IDs separated by returns (not commas) but this should only be used for techniques and not for the structural relationships in your solution.