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:
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:
with the settings:
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...