AnsweredAssumed Answered

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

Question asked by t_e_x on Jun 14, 2016
Latest reply on Jun 15, 2016 by PeterWindle

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 in Kentucky, and Contact B (Jon Smith) works for 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...