Using a dropdown to filter a gallery using Distinct

(1) Share

Report

Posted on by AdamH

580

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?

Categories:

Building Power Apps

I have the same question (0)

All responses (9)

Answers (1)

Sort by

WarrenBelz 156,404 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!
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

Was this reply helpful? Yes No
AdamH 580 on at

Like
a
(0)

Report
Copy link

Link copied!
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?

Was this reply helpful? Yes No
Verified answer

WarrenBelz 156,404 Most Valuable Professional on at

Like
a
(1)

Report
Copy link

Link copied!
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

Was this reply helpful? Yes No
AdamH 580 on at

Like
a
(0)

Report
Copy link

Link copied!
@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))

Was this reply helpful? Yes No
WarrenBelz 156,404 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

@AdamH ,

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

Was this reply helpful? Yes No
AdamH 580 on at

Like
a
(0)

Report
Copy link

Link copied!

@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.

Was this reply helpful? Yes No
WarrenBelz 156,404 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
AdamH 580 on at

Like
a
(0)

Report
Copy link

Link copied!

@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.

Was this reply helpful? Yes No
WarrenBelz 156,404 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

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)

Was this reply helpful? Yes No