Dynamic form filter

  • 0
  • 1
  • Question
  • Updated 8 months ago
  • Answered
  • (Edited)
 I have the following relationships defined. 


One vendor has many purchase orders 
One vendor has many vendor contacts 


In a purchase order form I want to be able to add a vendor from a drop down list ( which i am able to do by defining a look up field) 
but i also want to be able to see which vendor specific contacts there are in the system and select a contact from a list..

So the functionality i am looking for is. 

In a form defined in the PO table. I pick a vendor from a drop down list.
Based on the vendor i have selected a vendor contacts dropdown ( or something similar if i drop down is not possible ) shows me all the contacts we have on file for the vendor. The user then selects a contact from the list.. 

Below is an entity relationship diagram for the defined tables.. 




Photo of Sumedha Weerasuriya

Sumedha Weerasuriya

  • 346 Points 250 badge 2x thumb

Posted 8 months ago

  • 0
  • 1
Photo of Noah McBride

Noah McBride

  • 356 Points 250 badge 2x thumb
So the first thing you need to do is establish the relationships between Vendor and Purchase Orders and Vendors and Vendor Contacts. You also need to establish a relationship between Purchase Orders and Vendor Contracts. This seems like it would be a one-to-one relationship, so the direct of the relationship should not matter.

Once these are established, create a form on the Purchase Order Table. Add the Related Fields "Related Vendor" and "Related Vendor Contacts" on this form. Them right click next to the "Related Vendor Contacts" field. Click on the option that says "Edit the Field Properties for this Field". Scroll down and check the Box that says "The Values in this field depend on a selection in another field":
Then select "A selection in [Purchase Orders : Related Vendor Contacts] shows choices where [Purchase Orders : Related Vendor] = [Vendor Contacts: Vendor]".

Return to the form and then the choices that you make in Vendor should populate the Vendor Contacts field with the related choices based on the relationship between Vendor and Vendor Contacts.