cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Kevin00
Helper I
Helper I

Multiple filters working together, but sorts need to work independently. Also, best way to to implement multiple filters.

I have a gallery that gets data from a SharePoint list. The gallery will have multiple filters (Event {Lookup}, Status {Choice}, Date, Location) along with sorting for each filter. I have already implemented Event & Status filters that work together and give the expected results (see code below).

The problem is the sort. Right now if I sort Status it sorts within each Event. What I really want is for all Status to be together regardless of Event, and visa-versa. So even though the filters work together, the Sort's should not. Any help will be appreciated.

I realize that the more filters & sorts I add the more complex (conditionals) I will have to add. I have been piecing this together via Shane Young & April Dunnam videos, so there may be a more non-basic/efficient way to add multiple filters and sorts. Please feel free to share any links or words of wisdom.

 

If(
	//Event = All & Status = Open (everything but Complete... Not in the SP Choice list)    
    "All" in ComboBox1.SelectedItems.Title And ComboBox1_1.Selected.Value = "Open",
        Sort(
			Filter(
				AddColumns(
					MyDataSource,
					"Event",
					Event.Value,
					"OpenStatus",
					Status.Value
				),
				!IsBlank(Event) And OpenStatus <> "Complete"
			),
			Event,
        //Sort parameter for SortByColumns Event field. If initially comes in sets to Asc else uses a toggling var
        If(
            IsBlank(EventSortDesc),
            Ascending,
            If(
                EventSortDesc,
                Descending,
                Ascending
            )
        ),
        "OpenStatus",
        //Sort parameter for SortByColumns Status field. If initially comes in sets to Asc else uses a toggling var
        If(
            IsBlank(StatusSortDesc),
            Ascending,
            If(
                StatusSortDesc,
                Descending,
                Ascending
            )
        )
    ),
	
	//NEXT IF: Event = selected Event & Status = Open 
	Not("All" in ComboBox1.SelectedItems.Title) And ComboBox1_1.Selected.Value = "Open",
	SortByColumns(
		Filter(
			AddColumns(
				MyDataSource,
				"Event",
				Event.Value,
				"OpenStatus",
				Status.Value
			),
			Event in EventsSelected And OpenStatus <> "Complete"
		),
		"Event",
		//Sort parameter for SortByColumns Event field. If initially comes in sets to Asc else uses a toggling var
		If(
			IsBlank(EventSortDesc),
			Ascending,
			If(
				EventSortDesc,
				Descending,
				Ascending
			)
		),
		"OpenStatus",
		//Sort parameter for SortByColumns Status field. If initially comes in sets to Asc else uses a toggling var
		If(
			IsBlank(StatusSortDesc),
			Ascending,
			If(
				StatusSortDesc,
				Descending,
				Ascending
			)
		)
	),
//... Next 2 conditionals (Event = All & Status = selected Status) & (Event = Selected Event & Status = Selected Status) ...
)

 

1 ACCEPTED SOLUTION

Accepted Solutions
Kevin00
Helper I
Helper I

Got it! I think Bof's help in re-engineering the code allowed me to see the solution. I had to take my "If" statement outside of my SortByColumns function and then just add "UpdateContext({EventSortDesc: Blank()});" to the Status sort button and vis-versa to the Status sort button.

With(
    {
        MyDataSource: If(
            "All" in ComboBox1.SelectedItems.Title And ComboBox1_1.Selected.Value = "Open", /*CASE 1: Event = All & Status = Open (everything but Complete... Not in the SP Choice list) */
            Filter(AddColumns(MyDataSource,"Event",Event.Value,"OpenStatus",Status.Value),!IsBlank(Event) And OpenStatus <> "Complete"),
            Not("All" in ComboBox1.SelectedItems.Title) And ComboBox1_1.Selected.Value = "Open", /*CASE 2: Event = selected Event & Status = Open */
            Filter(AddColumns(MyDataSource,"Event",Event.Value,"OpenStatus",Status.Value),Event in EventsSelected And OpenStatus <> "Complete"),
            "All" in ComboBox1.SelectedItems.Title And Not(ComboBox1_1.Selected.Value = "Open"), /*CASE 3: Event = All & Status = selected Status*/
            Filter(AddColumns(MyDataSource,"Event",Event.Value,"OpenStatus",Status.Value), !IsBlank(Event) And OpenStatus = ComboBox1_1.Selected.Value),
			Not("All" in ComboBox1.SelectedItems.Title) And ComboBox1_1.Selected.Value <> "Open",/*CASE 4: Event = Selected Event & Status = Selected Status*/
            Filter(AddColumns(MyDataSource,"Event",Event.Value,"OpenStatus",Status.Value), Event in EventsSelected And OpenStatus = ComboBox1_1.Selected.Value)
            )
    },
	
	If(
		IsBlank(EventSortDesc) And Not(IsBlank(StatusSortDesc)), /*CASE 1: Sort by Status. EventSortDesc = Blank & StatusSortDesc <> Blank */
			SortByColumns(
				MyDataSource,        
				"OpenStatus",
				If(
					StatusSortDesc, Descending, Ascending            
				)
			),
			
		Not(IsBlank(EventSortDesc)) And IsBlank(StatusSortDesc), /*CASE 2: Sort by Event EventSortDesc <> Blank & StatusSortDesc = Blank */
			SortByColumns(
				MyDataSource,
				"Event",
				If(
					EventSortDesc, Descending, Ascending            
				)
			),
			
		IsBlank(EventSortDesc) And IsBlank(StatusSortDesc), /*CASE 3: Sort by Event EventSortDesc = Blank & StatusSortDesc = Blank */
        SortByColumns(
				MyDataSource,
				"Event",
				Ascending            
			)	
	)			
		
)

View solution in original post

3 REPLIES 3
v-bofeng-msft
Community Support
Community Support

Hi @Kevin00 :

If the data structure of the sorted object and the sorting conditions remain unchanged, I suggest you use the with function. for example:

With(
    {
        TheTable: If(
            "All" in ComboBox1.SelectedItems.Title And ComboBox1_1.Selected.Value = "Open",/*Case1*/
            Filter(AddColumns(MyDataSource,"Event",Event.Value,"OpenStatus",Status.Value),!IsBlank(Event) And OpenStatus <> "Complete"),
            Not("All" in ComboBox1.SelectedItems.Title) And ComboBox1_1.Selected.Value = "Open",/*Case2*/
            Filter(AddColumns(MyDataSource,"Event",Event.Value,"OpenStatus",Status.Value),Event in EventsSelected And OpenStatus <> "Complete"),
            "All" in ComboBox1.SelectedItems.Title And Not(ComboBox1_1.Selected.Value = "Open"),/*Case3*/
            Filter(AddColumns(MyDataSource,"Event",Event.Value,"OpenStatus",Status.Value),YourFilterRule),/*please set YourFilterRule*/
            Filter(AddColumns(MyDataSource,"Event",Event.Value,"OpenStatus",Status.Value),YourFilterRule),/*please set YourFilterRule*/
            )
    },
    SortByColumns(
        TheTable,
        "Event",
        If(
            IsBlank(EventSortDesc),
            Ascending,
            EventSortDesc,
            Descending,
            Ascending            
        ),
        "OpenStatus",
        If(
            IsBlank(StatusSortDesc),
            Ascending,
            StatusSortDesc,
            Descending,
            Ascending            
        )
    )
)

Best Regards,

Bof

 

Kevin00
Helper I
Helper I

Thank you Bof. This did make code more concise and readable; However, I still have the original problem of the Sort not working independently of the other Sorts. I have posted the more concise updated code below.

With(
    {
        MyDataSource: If(
            "All" in ComboBox1.SelectedItems.Title And ComboBox1_1.Selected.Value = "Open", /*CASE 1: Event = All & Status = Open (everything but Complete... Not in the SP Choice list) */
            Filter(AddColumns(MyDataSource,"Event",Event.Value,"OpenStatus",Status.Value),!IsBlank(Event) And OpenStatus <> "Complete"),
            Not("All" in ComboBox1.SelectedItems.Title) And ComboBox1_1.Selected.Value = "Open", /*CASE 2: Event = selected Event & Status = Open */
            Filter(AddColumns(MyDataSource,"Event",Event.Value,"OpenStatus",Status.Value),Event in EventsSelected And OpenStatus <> "Complete"),
            "All" in ComboBox1.SelectedItems.Title And Not(ComboBox1_1.Selected.Value = "Open"), /*CASE 3: Event = All & Status = selected Status*/
            Filter(AddColumns(MyDataSource,"Event",Event.Value,"OpenStatus",Status.Value), !IsBlank(Event) And OpenStatus = ComboBox1_1.Selected.Value),
			Not("All" in ComboBox1.SelectedItems.Title) And ComboBox1_1.Selected.Value <> "Open",/*CASE 4: Event = Selected Event & Status = Selected Status*/
            Filter(AddColumns(MyDataSource,"Event",Event.Value,"OpenStatus",Status.Value), Event in EventsSelected And OpenStatus = ComboBox1_1.Selected.Value)
            )
    },
    SortByColumns(
        MyDataSource,
        "Event",
        If(
            IsBlank(EventSortDesc), Ascending,
            EventSortDesc, Descending, Ascending            
        ),
        "OpenStatus",
        If(
            IsBlank(StatusSortDesc), Ascending,
            StatusSortDesc, Descending, Ascending            
        )
    )
)
Kevin00
Helper I
Helper I

Got it! I think Bof's help in re-engineering the code allowed me to see the solution. I had to take my "If" statement outside of my SortByColumns function and then just add "UpdateContext({EventSortDesc: Blank()});" to the Status sort button and vis-versa to the Status sort button.

With(
    {
        MyDataSource: If(
            "All" in ComboBox1.SelectedItems.Title And ComboBox1_1.Selected.Value = "Open", /*CASE 1: Event = All & Status = Open (everything but Complete... Not in the SP Choice list) */
            Filter(AddColumns(MyDataSource,"Event",Event.Value,"OpenStatus",Status.Value),!IsBlank(Event) And OpenStatus <> "Complete"),
            Not("All" in ComboBox1.SelectedItems.Title) And ComboBox1_1.Selected.Value = "Open", /*CASE 2: Event = selected Event & Status = Open */
            Filter(AddColumns(MyDataSource,"Event",Event.Value,"OpenStatus",Status.Value),Event in EventsSelected And OpenStatus <> "Complete"),
            "All" in ComboBox1.SelectedItems.Title And Not(ComboBox1_1.Selected.Value = "Open"), /*CASE 3: Event = All & Status = selected Status*/
            Filter(AddColumns(MyDataSource,"Event",Event.Value,"OpenStatus",Status.Value), !IsBlank(Event) And OpenStatus = ComboBox1_1.Selected.Value),
			Not("All" in ComboBox1.SelectedItems.Title) And ComboBox1_1.Selected.Value <> "Open",/*CASE 4: Event = Selected Event & Status = Selected Status*/
            Filter(AddColumns(MyDataSource,"Event",Event.Value,"OpenStatus",Status.Value), Event in EventsSelected And OpenStatus = ComboBox1_1.Selected.Value)
            )
    },
	
	If(
		IsBlank(EventSortDesc) And Not(IsBlank(StatusSortDesc)), /*CASE 1: Sort by Status. EventSortDesc = Blank & StatusSortDesc <> Blank */
			SortByColumns(
				MyDataSource,        
				"OpenStatus",
				If(
					StatusSortDesc, Descending, Ascending            
				)
			),
			
		Not(IsBlank(EventSortDesc)) And IsBlank(StatusSortDesc), /*CASE 2: Sort by Event EventSortDesc <> Blank & StatusSortDesc = Blank */
			SortByColumns(
				MyDataSource,
				"Event",
				If(
					EventSortDesc, Descending, Ascending            
				)
			),
			
		IsBlank(EventSortDesc) And IsBlank(StatusSortDesc), /*CASE 3: Sort by Event EventSortDesc = Blank & StatusSortDesc = Blank */
        SortByColumns(
				MyDataSource,
				"Event",
				Ascending            
			)	
	)			
		
)

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (3,015)