Conditional Drop Down Set Up

  • 0
  • 1
  • Question
  • Updated 2 years ago
  • Answered

I'm triyng to create a drop down that will do one of two things.  The Field is named Payee.  The two options in this drop down field are "Client" and "Vendor".  If the Payee is Client, the process stops there.  if the Payee is Vendor, I want another drop down to populate from my Vendors Table.  I'm not quite sure how to set that up.

Photo of Jay (DDS)

Jay (DDS)

  • 430 Points 250 badge 2x thumb

Posted 4 years ago

  • 0
  • 1
Photo of Xavier Fan

Xavier Fan, Champion

  • 930 Points 500 badge 2x thumb
One way to do this:

Say the [Payee] field is located in the "Payments" table.

You'll need a relationship from your Vendors table to the Payments table - "Vendors" would be the parent table.

Relationship:  1 Vendors has many Payments

From that Vendors table - you'll have the [Related Vendor] field, and a reference proxy field such as [Vendor Name].

So on the Payments form - you add a dynamic form rule

If [Payee] is equal to the value "Vendor"

Show [Vendor Name].

This displays the [Vendor Name] dropdown when [Payee] = "Vendor", otherwise it hides it.

So if [Payee] = "Client" - you're done.  [Vendor Name] remains blank.

If [Payee] = "Vendor", then you can select from the [Vendor Name] dropdown.
Photo of Arshad Khwaja

Arshad Khwaja

  • 3,152 Points 3k badge 2x thumb
Can we have a conditional drop down from within the same table?
You would have to describe your situation with a little more detail.
Photo of Arshad Khwaja

Arshad Khwaja

  • 3,152 Points 3k badge 2x thumb
I have field Development Type, which has three values to choose from.  These are long descriptions.  Then I have a Development Code field in the same table.  These are two letter codes corresponding to Dev Type.  For example, a refurbishment is always known as RR and extension  is RE.  What I want the user is to select the dev type and the dev code shows the right code for that. In MS Access, I could achieve it without having  to save a table.
You would either have to do that with a form rule, which obviously only works in forms, and not grid edit, or else just make the second field to be a pure formula field based off the data entry in the first field.
Photo of Arshad Khwaja

Arshad Khwaja

  • 3,152 Points 3k badge 2x thumb
Thanks. Could you kindly give me an example of form rules?
if there is a one for one relationship between Development type and Development Type,  make a table for the choices even though it would be a small table with only three records in it. And then I would have a relationship  and pull in both the development type in the development code from that table.

 And alternative would be a hard coded formula to calculate the value of the development code but I don't think it's good practice to do that become because it becomes very obscure how one would add a new drop down choice.

 I would not recommend using for rules because they don't working grid edit.

 But having said all that and example of a formal role would be this
When development type equals Choice 1
Change the value of development code to Code1
Photo of Arshad Khwaja

Arshad Khwaja

  • 3,152 Points 3k badge 2x thumb
Many thanks for your help.
Photo of Arshad Khwaja

Arshad Khwaja

  • 3,152 Points 3k badge 2x thumb
Mark

Fairly a long time since I asked the question. As you are familiar with my set up,  I would like to restrict the choices in a field based on another field.  So do I need to establish separate tables.  In this case you might wish to read my following earlier note: 

"I have field Development Type, which has three values to choose from.  These are long descriptions.  Then I have a Development Code field in the same table.  These are two letter codes corresponding to Dev Type.  For example, a refurbishment is always known as RR and extension  is RE.  What I want the user is to select the dev type and the dev code shows the right code for that. In MS Access, I could achieve it without having  to save a table."
You will need to have a table for the choices for the Development Code. However, you will not need to have a field for the Development Tyoe. That can be a simple multiple choice field.
Photo of Arshad Khwaja

Arshad Khwaja

  • 3,152 Points 3k badge 2x thumb
Mark sorry to bother you on this.  If I have three fields and  first determines the drop down on the second and the second field determines the drop down on the third,  do iIneed one table table to hold these drop downs or would there be three separate tables and these will be then  be looked up from let us say projects table  when someone is setting up a project.  As an example, if category is PNP and the Dev Type is 'New', Refurbishment', extension, Dev Codes are corresponding value for New = N, Refurbishment = RR  etc. 

In the attached screenshotI have shown the relationship. As I have existing data in projects table, in the proposed set up would I need to re allocate new values? 
I think that we would would need to deal one on one with this detail level of question.
Photo of Arshad Khwaja

Arshad Khwaja

  • 3,152 Points 3k badge 2x thumb
OK no pobs. 

This conversation is no longer open for comments or replies.