Forum Discussion

trobergetroberg's avatar
trobergetroberg
Qrew Cadet
8 years ago

Need filtered Drop down list to show options based on date entered in multiple fields

Trying to see if this scenario is possible: - Parent ORDERS table that creates an [Order Number] - The [Order Number]  has multiple fields that may (or may not) contain multiple 'vendor names' [Ven...
MattStephens's avatar
MattStephens
Qrew Cadet
Here's a much cleaner way of setting up your database:
  1. You should have a separate Vendors table to list all your vendors.
  2. Your Orders table won't need multiple vendor fields (this is good database practice too)
  3. Add a new OrderVendors table and create the following three relationships to it:
  4. Orders is the parent
  5. Vendors is the parent
  6. Orders is also a child.
  7. In the Vendors (parent) to OrderVendors (child) relationship, add a lookup to the Vendor name (if not already included in the OrderVendors table)
  8. In the OrderVendors (parent) to Orders (child) relationship, create a lookup field to Vendor - name. This field then becomes your drop-down field that you wanted.
Hope that makes sense. Here's a dummy app to show you how it works:
https://beca.quickbase.com/db/bmnx9r95z
trobergetroberg's avatar
trobergetroberg
Qrew Cadet
8 years ago
Awesome thanks...I will play with this.  My challenge is that our database is old and already has many records set up the way I show in my example.  
> I will set up the tables as you recommended and see if there will be some way of changing how things are done now, with this new method of handling Vendors.
> truly appreciate your prompt feedback - now I got some work to do ;-)