1 Reply Latest reply on Jun 15, 2016 1:20 AM by PeterWindle

    Conditional Value List with a one-to-many-to-one relationship

    t_e_x

      Here is the scenario of what I am trying to accomplish:

       

      I have a customer table with a contacts table with an address table (billing, shipping, mailing, etc.) configured as follows:

       

      Screen Shot 2016-06-14 at 5.02.26 PM.png

      Customer_Address_Billing (Mailing and Shipping) are all instances of a single Addresses table.  I have designed this way so that I can have one company with multiple locations and contacts retain the same CustomerID;  ie, Contact A (John Doe) works for Amazon.com in Kentucky, and Contact B (Jon Smith) works for Amazon.com in Colorado, hence different mailing and shipping addresses, but same billing address.

       

      I would like to create a conditional value list so that when a new contact to created, the user can select addresses already associated with a given CustomerID (Customer Table).  But my link between the two tables is the Customer_Contact table, hence the one to many to one.  I have created a value list based on:

      Screen Shot 2016-06-14 at 4.56.55 PM.png

      with the settings:

      Screen Shot 2016-06-14 at 5.11.23 PM.png

      but I receive <no values defined> upon view the list.  Within the Specify Fields for Value List dialog, if I select "Include all values" I do have a list of every address in the table, but not the ones associated with the designated CustomerID.

       

      My question, my rusty table relationships knowledge to clouding my progress, what would be the correct relationship to achieve my goal?

       

      Thank you for your thoughts...

       

      Michael

        • 1. Re: Conditional Value List with a one-to-many-to-one relationship
          PeterWindle

          Not exactly the way I would build by relationships...

           

          In Contacts, I would have calc fields to represent each address type, then have relationships from the Contacts to the Address TOC's using two criteria, the Contact ID and the Type. The "type" can be in the Address table via a portal/relationship in Contacts based on just Contact ID, just use a drop down list in the portal to set the address type field.

          The value list you want should then be very easy to setup, just use the relationship from Contact to address (using Contact ID) and viola, your list should show.

           

          Each other TOC (based on Contact ID and "type") could be used from Contacts to obtain the type of address you want, at any time.

          I hope this helps.