Help with creating IDs in a many to many

[Proprius]

Here is my setup. I've got a many to many relationship between "Cases" and "Clients" through what I guess is a join table "Matters". Each Case has multiple Clients, and each Client has multiple Cases, but each Matter involves only one Client and one Case. Each matter stores the ID numbers of the Clients and Cases.

Right now, to create another Matter and link a Case with a Client, on the Case layout I have a drop-down list of all the Client names taken from a value list, and a button that executes a script to make a new matter. My problem is getting the Client's ID number to complete the relationship. I'm sure there is something obvious that I'm missing, any help would be greatly appreciated! Thanks!

Edited August 13, 2007 by Guest

[David Jondreau]

You can create a value list based on the Client ID that will show the Client's name. So it looks like you're selecting the Name, but really you're selecting the ID and that Client ID should be complete the relationship between Matters and Clients.

[bcooney]

You could create a new matter from a layout based on a Matter table occurence (TO). Then, have two popup menus for the user to select a client and case. Set the popups to value lists that consist of ClientID and Name, just showing name and CaseID and Name, just showing Case Name.

The problem with popup menus is that they are miserable if the lists get long. Then, you might try a "Select" button next to the Client Name which brings up a new window of all Clients in a list view. See this thread for example file.