Using a dropdown to filter a gallery using Distinct

[AdamH]

Hi All, 

I am trying to add a drop down to an existing filter. The filter is already filtering a lot of fields and I'm having a problem placing the new drop down choice without breaking the filter.

My existing filter is:

With(
    {WMST: 'NC MST Tracker'}, Sort(
    Filter(
        WMST,
        (IsBlank(TextInput1.Text) ||
        (TextInput1.Text in 'Employee Name' || TextInput1.Text in Cohort || TextInput1.Text in 'Payroll Number' || TextInput1.Text in iPadAsset))
                         
            && Switch(
            Gallery1_1.Selected.Value,
            "Mentor",
            Mentor,
            "AET",
            'AET Instructor',
            "MST",
            'MST Assessor',
            "All",
            All,
            true
            
         && Grade.Value = Gallery1_1.Selected.Value) && Active.Value= "Yes"
    ),'Employee Name',If(SortDescending1, Descending,Ascending))

I have populated a drop down using the following:

Ungroup(
    Table(
        {MyTables: Table({Result: "All"})},
        {
            MyTables: SortByColumns(
                Distinct(
                    'NC MST Tracker',
                    MSTLocality.Value
                ),
                "Result"
            )
        }
    ),
    "MyTables"
)

Now I want to add dropdown2 to my existing filter, anyone have any advice?

[WarrenBelz]

Hi @AdamH ,

Probably something like this

With(
   {wMST: 'NC MST Tracker'}, 
   Sort(
      Filter(
         wMST,
         (
            Len(TextInput1.Text) = 0 ||
            (   
               TextInput1.Text in 'Employee Name' || 
               TextInput1.Text in Cohort || 
               TextInput1.Text in 'Payroll Number' || 
               TextInput1.Text in iPadAsset
            )
         ) && 
         Switch(
            Gallery1_1.Selected.Value,
            "Mentor",
            Mentor,
            "AET",
            'AET Instructor',
            "MST",
            'MST Assessor',
            "All",
            true
         ) && 
         Grade.Value = Gallery1_1.Selected.Value && 
         Active.Value= "Yes" &&
         MSTLocality.Value = Dropdown2Name.Selected.Result
      ),
      'Employee Name',
      If(
         SortDescending1, 
         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 

I came up with this code about an hour before you replied to my post. 

With(
    {WMST: 'NC MST Tracker'},
    Sort(
        If(Dropdown2.Selected.Result = "All", WMST, Filter(
            WMST,
            (IsBlank(TextInput1.Text) || (TextInput1.Text in 'Employee Name' || TextInput1.Text in Cohort || TextInput1.Text in 'Payroll Number' || TextInput1.Text in iPadAsset)),
            IsBlank(Dropdown2.Selected) || Dropdown2.Selected.Result in MSTGroup 
            && Switch(
                Gallery1_1.Selected.Value,
                "Mentor",
                Mentor,
                "AET",
                'AET Instructor',
                "MST",
                'MST Assessor',
                "All",
                All,
                true && Grade.Value = Gallery1_1.Selected.Value
            ) && Active.Value = "Yes"
        )),
        'Employee Name',
        If(
            SortDescending1,
            Descending,
            Ascending
        )
    )
)

It works, but can you think/see any potential problems with it? 

[WarrenBelz · Accepted solution]

Hi @AdamH ,

I think a bit more bracketing is needed to ensure it always works as intended

With(
    {WMST: 'NC MST Tracker'},
    Sort(
        If(
         Dropdown2.Selected.Result = "All", 
         WMST, 
         Filter(
            WMST,
            (   
               Len(TextInput1.Text) = 0 || 
               (
                  TextInput1.Text in 'Employee Name' || 
                  TextInput1.Text in Cohort || 
                  TextInput1.Text in 'Payroll Number' || 
                  TextInput1.Text in iPadAsset
               )
            ) &&
            (
               Len(Dropdown2.Selected.Result) = 0 || 
               Dropdown2.Selected.Result in MSTGroup 
            ) && 
            (
               Gallery1_1.Selected.Value = "All" ||
               Switch(
                  Gallery1_1.Selected.Value,
                  "Mentor",
                  Mentor,
                  "AET",
                  'AET Instructor',
                  "MST",
                  'MST Assessor'
               )
            ) && 
            Grade.Value = Gallery1_1.Selected.Value && 
            Active.Value = "Yes"
         )
      ),
      'Employee Name',
      If(
         SortDescending1,
         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 

I've actually found a problem with this now. Admittedly I looked at the code and thought it would work without testing it, having tested it today it's firing off errors. 

I'm not overly familiar with the Len function 

TextInput1 is self explanatory - Just a text input you would use for search

Dropdown2 was using:

//Ungroup(
//    Table(
//        {MyTables: Table({Result: "All"})},
//        {
//            MyTables: SortByColumns(
//                Distinct(
//                    'NC MST Tracker',
//                    MSTGroup
//                ),
//                "Result"
//            )
//        }
//    ),
//    "MyTables"
//)

 But Dropdown2 is now using a collection: [Item] collectrecords

OnVisible of screen: 

ClearCollect(collectrecords, {Result: "All"});
Collect(collectrecords, Distinct('NC MST Tracker', MSTGroup))

[WarrenBelz]

@AdamH ,

The second Dropdown2 should be Len(Dropdown2.Selected.Result) = 0 but other than that it should be valid.

[AdamH]

@WarrenBelz 

I'm just working on this now and changed the second dropdown2 to .result. 

But it seems to have an issue with Filter and Switch 

I don't use Len so i'm learning something new here. 

[WarrenBelz]

Hi @AdamH ,

I only use Len() as it covers both IsBlank() and IsEmpty(). There was an extra comma at the end of the With() statement (now fixed) - dangers of free-typing code.

[AdamH]

@WarrenBelz 

Yes that fixed it and thanks for the info around using Len() - Ill try and remember that in the future. 

The problem this now creates is that when an option is selected in dropdown2, no records show or even when I search for a record. Only when 'All' is selected, records show. 

[WarrenBelz]

Hi @AdamH ,

I cannot see your data, but either the selected item is All or blank (then all records will show) or the selected item is in MSTGroup (I do not know what that is)