You've almost got it in the diagram you've attached.
You have the Session table occurrence and you have the Session_SP table occurrence. What you still need is the Join table to connect them. So add that Join table, and now connect it to the Session table using the Client Selector in the Sessions table and your existing fk_ClientID in the Join table. And then make your usual connection between the Join table and the Session_SP table (sp id to sp id).
That gives you the relationship on which you can base a value list. When the user selects a Client (and sets the Client_Selector), you'll limit your SPs to those who are connected by the Join table.
Matt, just to be clear, when I say you should add a join table, I mean another occurrence of your existing join table.
But i need it to work the opposite. When an SP is selected, i want to limit the list of clients to those whom the SP is assigned to. The SP will always be selected prior to the client.
1 of 1 people found this helpful
But i need it to work the opposite
Then you simply need to use the other keys as matchfields in the suggested TO chain.
Create a new join table TO, relate it to Sessions via SP_selector, create another TO of Clients (or use the existing one) and relate it to the new join TO, then define your value list as (new)ClientTO::yourField(s), “Include only related values starting from” Sessions.
Thank you both very much.
I originally tried your suggesiton and it was not functioning but after going backwards and working through each relationship i found that i had an incorrect relationship between the join table and the SPs so now all is working as expected.