Discussions

Expand all | Collapse all

Filter Across Similar Fields only shows Field 1 values

  • 1.  Filter Across Similar Fields only shows Field 1 values

    Posted 29 days ago
    Need some formula help --
    4 Multi-Choice fields in same table: Company 1, Company 2, Company 3, Company 4.
    Each of these 4 fields has the same set of Choices: Red, Blue, Green, Yellow.
    Each record can have 0-4 Company fields filled in.
    I need a filter to identify if any of the 4 fields has "Yellow", then filter will show word "Yellow". Or if any of the 4 has "Red", show the word "Red".

    a. I created concatenated field Company1234 and then created a Formula Text field
    If (Contains([Company1234, "Yellow")=true, "Yellow", Contains([Company1234, "Red")=true, "Red", Contains([Company1234, "Blue")=true, "Blue", Contains([Company1234, "Green")=true, "Green","")
    Doesn't work the way I need it to - seems to only filter to show if Company 1 is the value.

    b. I tried another Formula Text field 
    If((Contains([Company 1], "Yellow")=true or Contains([Company 2, "Yellow")=true or Contains([Company 3], "Yellow")=true or Contains([Company 4], "Yellow")=true), "Yellow",
    (Contains([Company 1], "Red")=true or Contains([Company 2, "Red")=true or Contains([Company 3], "Red")=true or Contains([Company 4], "Red")=true), "Red",    etc
    This also filters if Company 1 has the value.

    This is part of an older app which was setup as a large flat file and I am trying to create some filters to check data while I transition it to Parent Child structure.

    ------------------------------
    Ruby Kapil
    ------------------------------


  • 2.  RE: Filter Across Similar Fields only shows Field 1 values

    Posted 29 days ago
    Maybe this is what you want
    List(", ",
    If(Contains([Company1234], "Yellow"), "Yellow")
    If(Contains([Company1234], "Red"), "Red")
    If(Contains([Company1234], "Blue"), "Blue")
    If(Contains([Company1234], "Green"), "Green"))


    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    markshnier2@gmail.com
    ------------------------------



  • 3.  RE: Filter Across Similar Fields only shows Field 1 values

    Posted 29 days ago
    Thanks Mark. The result I want is a Single value to show in the filter result, Green if any of the 4 companies have Green, Red if any of the 4 companies have Red.
    Blue,
    Green
    Red
    Yellow

    The List formula above is similar to my Company1234 field in that the filter results are: 
    Green
    Green, Red
    Red
    Red, Yellow


    ------------------------------
    Ruby Kapil
    ------------------------------



  • 4.  RE: Filter Across Similar Fields only shows Field 1 values

    Posted 29 days ago
    I guess I do not understand what you want to have happen if some of the companies have red and other have yellow and others have green.

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    markshnier2@gmail.com
    ------------------------------



  • 5.  RE: Filter Across Similar Fields only shows Field 1 values

    Posted 29 days ago
    Ruby,

    Since this is a temporary use you can do one long list:

    If (  [Company1], "Yellow")=true, "Yellow,
    [Company2], "Yellow")=true, "Yellow",
    [Company3], "Yellow")=true, "Yellow",
    [Company4], "Yellow")=true, "Yellow",
    [Company1], "Red")=true, "Red",
    [Company2], "Red")=true, "Red",
    [Company3], "Red")=true, "Red",
    [Company4], "Red")=true, "Red",  "Not Red or Yellow"  )


    ------------------------------
    Don Larson
    Paasporter
    Westlake OH
    ------------------------------



  • 6.  RE: Filter Across Similar Fields only shows Field 1 values

    Posted 29 days ago
    Thanks Don. It will filter to a single value, but the filter is only capturing those records where Company 1 is Yellow. If Company 2 is Yellow, it will not show up in the list of records for Filter = Yellow.

    ------------------------------
    Ruby Kapil
    ------------------------------



  • 7.  RE: Filter Across Similar Fields only shows Field 1 values

    Posted 28 days ago
    Ruby,

    What is the behavior you want on record if this is the data:

    Company 1 Yellow
    Company 2 Red
    Company 3 Green
    Company 4 Green

    For this single record you have one yellow, one red and two greens.   What do you want the formula to output?

    ------------------------------
    Don Larson
    Paasporter
    Westlake OH
    ------------------------------



  • 8.  RE: Filter Across Similar Fields only shows Field 1 values

    Posted 28 days ago
    Mark and Don, thanks for the help - here's some additional info --

    Record 1: Company 1=Red, Company 2 = Green, Company 3 is blank, Company 4 is blank
    Record 2: Company 1=Yellow, Company 2 = Red, Company 3 is Yellow, Company 4 is blank
    Record 3: Company 1=Yellow, Company 2 = Green, Company 3 is Blue, Company 4 is blank

    The formula field will be used as a Dynamic Filter in a Table Report. The filter choices will look like this:
    Blue
    Green
    Red
    Yellow

    In the Dynamic Filters on the left side of my Table report,
    If I select Blue, it will filter the report to show Record 3.
    If I select Green, it will filter the report to show Record 1 and Record 3
    If I select Red, it will filter the report to show Record 1 and Record 2
    If I select Yellow, it will filter the report to show Record 2 and Record 3.

    All the solutions I have tried have resulted in showing me only the value that is found in Company 1, so if I select Red, it will only show me Record 1, and not Record 2.

    ------------------------------
    Ruby Kapil
    ------------------------------



  • 9.  RE: Filter Across Similar Fields only shows Field 1 values

    Posted 28 days ago
    It would have been helpful to know in your original question that your purpose was to create Dynamic filters.  This would be a similar type question as to having 1 company field per record and it having a "Multi-Select" field type and wanting Dynamic filters.

    There is not a convenient solution for this.  You will need to use the filter box on top of the report.

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    markshnier2@gmail.com
    ------------------------------



  • 10.  RE: Filter Across Similar Fields only shows Field 1 values

    Posted 28 days ago
    Ruby,

    1) Leave the four individual columns in a table report and then enable the check box for the Quick Search Field




    2) Export the data us a BI/ BA tool like Tableau.  You can build a SQL Query there that will allow you to do that type of filter.

    ------------------------------
    Don Larson
    Paasporter
    Westlake OH
    ------------------------------



  • 11.  RE: Filter Across Similar Fields only shows Field 1 values

    Posted 28 days ago
    You  can also make a formula like this as a Rich Text field. 

    "<a name=" & [company1234] & "</a>"

    Include that on your report. It will look blank but will be searchable.  Do a column label override to name it short like just a . or else call it [.] to keep the field heading short.

    Very useful technique to allow searchability on a bunch of extra fields where you do not want the clutter on the report of the extra columns.

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    markshnier2@gmail.com
    ------------------------------



  • 12.  RE: Filter Across Similar Fields only shows Field 1 values

    Posted 28 days ago
    Mark and Don, thank you both for your helpful suggestions.

    ------------------------------
    Ruby Kapil
    ------------------------------