Answered

Sort Gallery by Month, Day, and Year NOT WORKING CORRECTLY!

(0) Share

Report

Posted on by cjwilson80

I have an app with a home page gallery that lists all the items, and it can be sorted and filtered by multiple categories. However, when the user goes in to click the sort arrow icon for the 'Go Live' date, the dates seem to be sorting by month, and not by the full month/day/year format.

My ITEMS property of the Gallery is the following:

Search(
 Sort(
 Filter(
 AddColumns(
 'BARC Submissions',
 "SubmissionOwnerName",
 SubmissionOwnerSearch.DisplayName
 ),
 IsBlank(ComboBox1.Selected.Value) || SubmissionStatusChoice.Value = ComboBox1.Selected.Value
 ),
 Switch(
 varSortColumn,
 "SubmissionName",
 'Submission Name',
 "SubmissionOwner",
 SubmissionOwnerSearch.DisplayName,
 "GoLive",
 DateTimeValue(GoLive),
 "SubmissionStatus",
 SubmissionStatusChoice.Value
 ),
 varSortOrder
 ),
 TextInput1.Text,
 "SubmissionName",
 "SubmissionOwnerName"
)

However, when the user clicks the sort icon next to Go Live, it sorts the dates by the month, day, and then year, so all the dates are mixed up... see my image here:

How do I fix this?? HELP PLEASE!

Categories:

Building Power Apps

I have the same question (0)

All responses (4)

Answers (1)

Sort by

russrimm Microsoft Employee on at

Like
a
(1)

Report
Copy link

Link copied!
Hi @cjwilson80. It is due to a limitation with the Sort() function and other functions that refer to columns by name. You must, without exception, provide column names as primitive strings. That is, hard-coded text values. You can't change the sort column using Switch to dynamically set the column as you are attempting to do here.

To get around this limitation, you will have to write separate Sort() formulas, one for each column you wish to sort by, and then pick one using a Switch() or If() function.

Here's a simplified example without the Filter, Search, and AddColumns (which can be added back into the 'With' function.) I'm also assuming your varSortOrder is a boolean value that's being flipped between true/false by your sort icon.

With( {myData: 'BARC Submissions'}, If( varSortColumn = "GoLive", Sort( myData, DateTimeValue(GoLive), If(varSortOrder, SortOrder.Ascending, SortOrder.Descending) ), varSortColumn = "SubmissionName", Sort( myData, "Submission Name", If(varSortOrder, SortOrder.Ascending, SortOrder.Descending) ) ) )

Was this reply helpful? Yes No
cjwilson80 95 on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @russrimm, THANK YOU THANK YOU THANK YOU!

This solution works for the "Go Live" column, but now I'm struggling to add back in the other sorts. When I click on the sort icon for a different column, the gallery goes blank. For instance, the "Status" column sort icon will make the gallery go blank if I click on it, because it's not in the new code. I know above you said I could add back in the switch, but I'm not sure how to go about this. Could you please provide a sample or solution for adding back the other columns?

**I have the following code now in my "Items" property of the gallery. Everything seems to be working EXCEPT when I try to sort the "Status".... When I click the icon to sort "Status", the gallery goes blank. What am I missing?**

With( { myData: Search( Sort( Filter( AddColumns( 'BARC Submissions', "SubmissionOwnerName", SubmissionOwnerSearch.DisplayName ), IsBlank(ComboBox1.Selected.Value) || SubmissionStatusChoice.Value = ComboBox1.Selected.Value ), Switch( varSortColumn, "SubmissionName", 'Submission Name', "SubmissionOwner", SubmissionOwnerSearch.DisplayName, "SubmissionStatus", SubmissionStatusChoice.Value ), varSortOrder ), TextInput1.Text, "SubmissionName", "SubmissionOwnerName" ) }, With( {myData: 'BARC Submissions'}, If( varSortColumn = "GoLive", Sort( myData, DateTimeValue(GoLive), If( varSortOrder = "Descending", SortOrder.Ascending, SortOrder.Descending ) ), varSortColumn = "SubmissionName", Sort( myData, 'Submission Name', If( varSortOrder = "Descending", SortOrder.Ascending, SortOrder.Descending ) ), If( varSortColumn = "SubmissionOwner", Sort( myData, SubmissionOwnerSearch.DisplayName, If( varSortOrder = "Descending", SortOrder.Ascending, SortOrder.Descending ) ) ) ) ) )
Sta

Was this reply helpful? Yes No
Verified answer

russrimm Microsoft Employee on at

Like
a
(1)

Report
Copy link

Link copied!
Hi @cjwilson80 , so "Status" is populated from SubmissionStatus, correct? If so, what column type is SubmissionStatus? Choice?

Try this out?

With( { myData: Search( Sort( Filter( AddColumns( 'BARC Submissions', "SubmissionOwnerName", SubmissionOwnerSearch.DisplayName ), IsBlank(ComboBox1.Selected.Value) || SubmissionStatusChoice.Value = ComboBox1.Selected.Value ), Switch( varSortColumn, "SubmissionName", 'Submission Name', "SubmissionOwner", SubmissionOwnerSearch.DisplayName, "SubmissionStatus", SubmissionStatusChoice.Value ), varSortOrder ), TextInput1.Text, "SubmissionName", "SubmissionOwnerName" ) }, With( {myData: 'BARC Submissions'}, If( varSortColumn = "GoLive", Sort( myData, DateTimeValue(GoLive), If( varSortOrder = "Descending", SortOrder.Ascending, SortOrder.Descending ) ), varSortColumn = "SubmissionName", Sort( myData, 'Submission Name', If( varSortOrder = "Descending", SortOrder.Ascending, SortOrder.Descending ) ), varSortColumn = "SubmissionOwner", Sort( myData, SubmissionOwnerSearch.DisplayName, If( varSortOrder = "Descending", SortOrder.Ascending, SortOrder.Descending ) ), varSortColumn = "SubmissionStatus", // Add this condition Sort( myData, SubmissionStatus, // Assuming SubmissionStatus is the column name If( varSortOrder = "Descending", SortOrder.Ascending, SortOrder.Descending ) ) ) ) )

Was this reply helpful? Yes No
russrimm Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

@cjwilson80 Did you get it resolved? If you ever get really stuck and your org has a Microsoft Unified support agreement, ping your CSAM - we have many cloud solution architects, like myself, focused on all things Power Platform who can help out with challenges like this!

Was this reply helpful? Yes No