Discussions

Expand all | Collapse all

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

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

    Posted 04-01-2017 20:30
    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:
    
                                                    


  • 2.  RE: Need filtered Drop down list to show options based on date entered in multiple fields

     
    Posted 04-02-2017 00:06
    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....


  • 3.  RE: Need filtered Drop down list to show options based on date entered in multiple fields

     
    Posted 04-02-2017 00:27
    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


  • 4.  RE: Need filtered Drop down list to show options based on date entered in multiple fields

    Posted 04-02-2017 14:25
    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 ;-)