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
DonLarson's avatar
DonLarson
Qrew Elite
6 years 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
------------------------------
  • 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
        ------------------------------