Using a Gallery as a filter for Switch & Choice Fields

[AdamH]

Hi All, 

I have a gallery that I am using as a 'Filter Buttons' for my main gallery which was setup to filter records depending if a switch was on or off within a persons record.

I need to change this to incorporate some choice fields that are within the records. As an example:

Choice Fields from a dropdown field:

- Group1

- Group2

- Group3

Code I was using for far is:

With(
    {WMST: 'NC MST Tracker'}, Sort(
    Filter(
        WMST,
        Or(IsBlank(TextInput1.Text),
        TextInput1.Text in 'Employee Name',
        StartsWith(
            'Employee Name',
            TextInput1.Text
        )) && Switch(
            Gallery1_1.Selected.Value,
            "Mentor",
            Mentor,
            "AET",
            'AET Instructor',
            "MST",
            'MST Assessor',
            true
        )
    ),'Employee Name',If(Sort, Descending,Ascending))
)

Anyone able to advise me with this?

Thanks

[WarrenBelz]

Hi @AdamH ,

A couple of things - what are you trying to do with the Switch statement

Switch(
   Gallery1_1.Selected.Value,
   "Mentor",
   Mentor,
   "AET",
   'AET Instructor',
   "MST",
   'MST Assessor',
   true
)

You have not specified which field you are filtering, so that bit does nothing presently.

This part

Or(
   IsBlank(TextInput1.Text),
   TextInput1.Text in 'Employee Name',
   StartsWith(
      'Employee Name',
      TextInput1.Text
   )
) 

if you use the second one, you do not need the third, however if you are happy with StartsWith() and not in, the filter becomes Delegable and you do not need the With() statement (which only takes away the warning - it still only works on the first 2,000 items)

Can you please explain a bit more what you are doing with the Choice fields

[AdamH]

@WarrenBelz 

Regarding the switch statement: 

With(
    {WMST: 'NC MST Tracker'}, Sort(
    Filter(
        WMST,
        Or(IsBlank(TextInput1.Text), // SEARCH BAR
        TextInput1.Text in 'Employee Name',
        StartsWith(
            'Employee Name',
            TextInput1.Text
        )) && Switch(
            Gallery1.Selected.Value,
            "Mentor",
            Mentor,
            "AET",
            'AET Instructor',
            "MST",
            'MST Assessor',
            true
        )
    ),'Employee Name',If(Sort, Descending,Ascending))
)

This is the [items] property of my main gallery [browsegallery1] on my [BrowseScreen1] that shows all the staff records. At the top of the screen, there is another gallery [Gallery1] which I've formatted to look like and act as buttons. 

(i think you helped me with this before...)

Within each staff record, I have toggle true/false switches - Example, My record will have MST switched to true, therefor on [BrowseScreen1] which is my main screen, if press 'MST' it will filter all the records with 'MST' toggle switch set to true. So the [Items] property of [Gallery1] is: ["All","Mentor","AET","MST"]

What I want to do is expand [Gallery1] and have this filter a LIST field and Combobox on my app as well as the existing toggle fields. 

[WarrenBelz]

Thanks @AdamH ,

I remember the code now, but you had it in amongst the filter rather than the sort (which you had further down). Please look at the below for suggested structure

With(
   {WMST: 'NC MST Tracker'}, 
   Sort(
      Filter(
         WMST,
         (
            Len(TextInput1.Text) = 0 || 
            TextInput1.Text in 'Employee Name'
         ) && 
         StartsWith(
            'Employee Name',
             TextInput1.Text
         ) &&
         (
            Len(YourGradeDD.Selected.Value) = 0  ||
            Grade = YourGradeDD.Selected.Value
         )
      ),
      Switch(
         Gallery1.Selected.Value,
         "Mentor",
         Mentor,
         "AET",
         'AET Instructor',
         "MST",
         'MST Assessor',
         'Employee Name'
      ),
      If(
         Sort, 
         Descending,
         Ascending
      )
   ) 
)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps

[AdamH]

Hi @WarrenBelz 

Yes I thought it was yourself that may have helped with this previously. 

Looking at your code, so if I wanted apply those principles in my [Gallery1], where would I insert the additional items? Basically, I make new buttons on my [Gallery1] which filter the grade

Example...

Would it be something like?

Len(YourGradeDD.Selected.Value) = 0  ||
            Grade = Grade1.Selected.Value ||
            Grade = Grade2.Selected.Value ||
            Grade = Grade3.Selected.Value ||

[WarrenBelz]

@AdamH ,

Is this a new gallery ? Gallery 1 is sorting, not filtering.

If you have a new gallery (Gallery2) for those controls with the Items

[
   "Grade 1",
   "Grade 2",
   "Grade 3",
   "Grade 4"
]

it would be something like this

With(
   {WMST: 'NC MST Tracker'}, 
   Sort(
      Filter(
         WMST,
         (
            Len(TextInput1.Text) = 0 || 
            TextInput1.Text in 'Employee Name'
         ) && 
         StartsWith(
            'Employee Name',
             TextInput1.Text
         ) &&
         Grade = Gallery2.Selected.Value
      ),
      Switch(
         Gallery1.Selected.Value,
         "Mentor",
         Mentor,
         "AET",
         'AET Instructor',
         "MST",
         'MST Assessor',
         'Employee Name'
      ),
      If(
         Sort, 
         Descending,
         Ascending
      )
   ) 
)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps

[AdamH]

@WarrenBelz 

Sorry for the late reply on this, I was unwell with a chest infection and have only come back to the world of the living.

This is not with a new gallery, I have just added to the [ITEMS] of the existing Gallery1 - sorry for the confusion between sorting and filtering which has made you think I've added a new gallery - I'll be careful with my choice of words next time.

Gallery1 is sorting and I wanted to add the ability to sort with the switch statement (toggle switches for Mentor, AET & MST

and

also the grade field (LIST) Grade 1, Grade 2 and Grade 3. 

[AdamH]

Hi @WarrenBelz 

I've had some time to review your code and I do manage to get the grade [LIST] sort to work, but the switch sort has stopped working. I noticed that the true is missing from the end of the switch section:

 'MST Assessor',
            true
        )
    ),'Employee Name',If(Sort, Descending,Ascending))

I tried to re-insert the 'Employee Name' true), with your version and I cannot get it to work. 

[WarrenBelz]

@AdamH ,

How are you determining the Grade sort and how does that mix with the gallery determined sort at the bottom?

[WarrenBelz]

Hi @AdamH ,

I cannot test it presently, but the Switch code should work on the basis that 'Employee Name' should apply if none of the others are chosen. You could go back to

If(
   Gallery1.Selected.Value = "Mentor",
   Mentor,
   Gallery1.Selected.Value = "AET",
   'AET Instructor',
   Gallery1.Selected.Value = "MST",
   'MST Assessor',
   'Employee Name'
)

but it is the same thing