Forum Discussion

RubyKapil's avatar
RubyKapil
Qrew Trainee
6 years ago

Filter Across Similar Fields only shows Field 1 values

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 ...
Formulas and functions
RubyKapil's avatar
RubyKapil
Qrew Trainee
6 years 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
------------------------------
DonLarson's avatar
DonLarson
Qrew Elite
6 years 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
------------------------------
  • RubyKapil's avatar
    RubyKapil
    Qrew Trainee
    6 years 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
    ------------------------------
    • MarkShnier__You's avatar
      MarkShnier__You
      Icon for Qrew Legend rankQrew Legend
      6 years 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
      ------------------------------
    • DonLarson's avatar
      DonLarson
      Qrew Elite
      6 years 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
      ------------------------------
      • MarkShnier__You's avatar
        MarkShnier__You
        Icon for Qrew Legend rankQrew Legend
        6 years 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
        ------------------------------