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' [Vendor1], [Vendor2], [Vendor3], [Vendor4], etc...
- I would like to create a new drop-down field [Vendors In Order] that lists only the 'vendor names' that have been assigned to the [Vendor#] fields

For Example:
[Order Number]  = 100 (has following vendor fields populated)
  • [Vendor1] = Some Store
  • [Vendor2] = Carrier Company
  • [Vendor3] = Whatever Warehouse
  • [Vendor4] = <blank>
  • [Vendor5] = <blank>
  • [Vendor6] = <blank>

while in [Order Number]  = 100
- when I go to the  [Vendors In Order] field it would lists these options in drop-down list
  • Some Store
  • Carrier Company
  • Whatever Warehouse
Also here is image to help visualize:
  • MattStephens's avatar
    MattStephens
    Qrew Cadet
    8 years ago
    Drop-down lists are either a manual list, or from a related table. I'm not aware of any way to set the list based on other fields in the same table. However you could use Table to Table imports to set up a related table like this:
    1. Create a table called, say, Order Vendors, with just two fields: [Orders] & [Vendor Name]
    2. Set up 4 Table to Table imports that copy the Order Number and Vendor from your ORDERS table to the Order Vendors table. Import 1 copies  [Order Number] & [Vendor 1 - Name], Import 2 copies [Order Number] & [Vendor 2 - Name] etc.
    3. Then create a relationship where Order Vendors is the parent of ORDERS. Keep the Reference Field set to [Related Order Vendor]. Set the first (proxy) Lookup field to be [Vendor Name], and the second Lookup field to be [Order]
    4. Once the relationship is created, change the ORDERS field [Order Vendors - Vendor Name] to your preferred name ([Vendors in Order]).
    5. Make the [Related Order Vendor] field conditional on the [Order Number] = [Orders] (see Conditional values in the field properties.
    The clunky thing with this solution is that it requires an admin to run the Table to Table imports - this is easy to do once set up, but not ideal. I'll have a think about a better solution for you....
  • MattStephens's avatar
    MattStephens
    Qrew Cadet
    8 years ago
    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 ;-)