Conditional List, Many-To-Many Records

[nesor]

This should be easy but it has me stumped:

Tables:

Companies

Contacts

Comp_Cont_Many (table joining many contacts with many companies)

Orders

Many-to-many relationship between companies and contacts.

Each order may have only 1 Contact and 1 Company.

On the Order input form, there's a drop-down menu for Contact, and another for Company (to assign a single Contact and a single Company to the order).

The Contact value list on the order input form is not conditional. User may select any Contact from the Contact table to put into an order.

The Company value list should be conditional, based on the Contact for the order.

Example:

Jon Doe (contact) belongs to Companies ABC and XYZ via the Comp_Cont_Many table.

User creates an order and Jon Doe is the Contact for said order.

When user goes to the Company field of this order (Order layout), the drop-down menu should show companies ABC and XYZ as options for this orders, but should NOT show any Companies for which Jon Doe does not belong.

Stumped on how the relationships should be structured for this conditional value list. Any help appreciated.

[comment]

Assuming you are ordering from a company, your core relationships should be:

Contacts -< CompanyContacts >- Companies -< Orders >- Contact

For the conditional value list, you need also:

Orders::ContactID = CompanyContacts 2::ContactID

CompanyContacts 2::CompanyID = Companies 2::CompanyID

Select from field Companies 2::CompanyID, showing only related values starting from Orders.





Alternatively, you could define a calculation field in CompanyContacts =

Companies::Name

and dispense with the second relationship, selecting from the field CompanyContacts 2::CompanyID.

[nesor]

Thank you. I'll give this a try.

Assuming you are ordering from a company, your core relationships should be:

Contacts -< CompanyContacts >- Companies -< Orders >- Contact

For the conditional value list, you need also:

Orders::ContactID = CompanyContacts 2::ContactID

CompanyContacts 2::CompanyID = Companies 2::CompanyID

Select from field Companies 2::CompanyID, showing only related values starting from Orders.





Alternatively, you could define a calculation field in CompanyContacts =

Companies::Name

and dispense with the second relationship, selecting from the field CompanyContacts 2::CompanyID.