An alternative approach would be to use a join table as you now have a many to many relationship.
Clients::kp_ClientID = Client_File::kf_ClientID
Files::kp_FileID = Client_File::kf_FileID
Now the duplication takes place in Client_File instead of Files so you still have one record for each file in that table. A portal to Client_File can be used to list files for a given client when placed on a client layout and can be used to list clients when placed on a Files layout.
If you are unfamiliar with join tables, you may want to examine this demo file: http://www.4shared.com/file/dZ0bjclw/ManyToManywDemoWExtras.html
Where I get stumped is that each file has additional File Data that is stored in a seperate file and at present related to Files table via File ID.
Clients -------- Files -------- File Data
Now I would have Clients ------ Client Files ------- Files so I need to add a Client ID to the File Date table but I don't know how to relate it back to Clients table.
Not what I am suggesting. What I am suggesting would be:
You would not add a client ID field to the files table but instead add a new table that stores Client ID from Clients and File ID from Files to link a given client to a given file.
If two clients, ClientID = 5 and ClientID = 8 both need to be linked to the same file (FileID = 6), you create two records in the Client_File table:
One with ClientID = 5 and FileID = 6. One with ClientID = 8 and FileID = 6.
You can find examples of how to set up layouts for managing this kind of many to many relationship in the demo file.
And if there are some specifics that are different in a certain file that is shared by multiple clients, other fields can be added to the Client_File table.
Thank you for being so patient Phil. I got it now. I had to add a Client ID field to the third file (File Data) that stores file and client specific data and than relate it to to Client_File table via Client ID and File ID field instead of realting Files ----<FileData Tables as it was before.
Hmmm, sounds like you could use File Data as your join table then.
Yes, I see what you mean. But I think this way will be "cleaner". Now I run into another problem. How do I display the 2nd Client on the same layout (based on CLient_Files). I did a script that will switch between them. But can't figure a way how to show the other one on the same layout. I would like the user to see at a glance if the 2nd client for this client is added yet or not yet. If there isn't I got a button triggered script that will add it. If there is they should see and they can use a button to switch between them. I belive I need to use a portal but can't figure which TO to use and how to relate it.
Why would this be a layout based on Client_Files? Usually, you would base a layout on either Client or Files.
Client_Files is your join table or are we using different names?
From the Files layout, you can add a portal to the join table to list all clients linked to that file. You can add fields from the Client table to the portal row to include additional info such as the client name.