Im having an issue understanding how to create a relationship for my certain scenario.
Below is currently how my relationship graph is setup:
Here is a brief overview:
Each client can have multiple service providers (SP) assigned to their account. I've setup a join table to accomodate this as you can see above.
The sessions table will be where all notes will be entered in by a SP for a client's record. Each SP will be accessing the database from an iPad.
They will be brought to a screen where there is a field (Sessions::SP_Selector)for them to click a dropdown and select their ID #. There will also be a field (Sessions::Client_Selector) for them to click a drop down and select the Client that the session is for which is where i am coming into an issue.
I'm trying to setup a value list that will show only those clients who are assigned to the SP selected on the session screen.
What my original thought was to join Sessions::SP_Selector = cleint_SP_join::_fk_SPID and then create a vlue list using client table including only related values from cleint_SP_join but that resulted in an empty list.
What would be the proper way to setup the relationship for the above scenario to work correctly?