5 Replies Latest reply on Aug 28, 2014 10:44 AM by MattLeach

    Value list relationship setup


      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?

        • 1. Re: Value list relationship setup

          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.

          • 2. Re: Value list relationship setup

            Matt, just to be clear, when I say you should add a join table, I mean another occurrence of your existing join table.


            - Jim

            • 3. Re: Value list relationship setup

              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.

              • 4. Re: Value list relationship setup

                MattLeach wrote:


                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.

                1 of 1 people found this helpful
                • 5. Re: Value list relationship setup

                  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.


                  Thanks again!