AnsweredAssumed Answered

search based on difficult relationship

Question asked by brianpatterson on Aug 30, 2015
Latest reply on Sep 1, 2015 by Extensitech

My database has tables for clients and contacts. This is a 1 to many relationship.  My client wants to enter a contact name (first name, last name, and email) and see a layout where both the client and contact information are editable. The hard part is, the client also wants to see all of the other contacts for that client and be able to see a layout where that contact and the client information is editable. Data will be entered while the selected client is upon the phone.


In essence, the user enters a contact name, the system gets the ClientID (a foreign key in the contact table), and extends the found set with all other contacts with the same client ID. It then uses the client ID to gather client information


From the search form,  name and email values are linked to global fields in the globals table, which in turns links to the contacts table. (The globals table will be cleared when the phone session with the contact is ended.)  I don't know how to take the next step, getting all contacts associated with the client, to work. I think it could be done with a self-join based on client ID, or done be saving the client ID as a variable in a script and using it to run a second search.


So far, I can't get it to work. The failing is not getting additional contacts to show when needed.


I'd like suggestions on how to link the table involved in this process, how to extent the found set with additional contact for a client, and how do display the client editable fields and contact editable fields on one form, while maintaining the ability to easily jump to another contact within the same client.