Canvas app w/ SharePoint list - Sort w/ Switch statement treats date column values as text?

(0) Share

Report

Posted on by 9erFan925

Hi friends,

I'm writing a canvas app with a SharePoint list back-end.

I've created a dynamic sort within my canvas app that sorts a gallery based on which text label the user clicks.

The function works perfectly - except, when sorting by any of the date columns I've created in my list? The sort function treats each date column as text and therefore does not sort correctly.

The exception to this problem is the "Created" column. The "Created" column sorts as you'd expect however, my "StartDate" date/time column (example format: 1/12/2024) sorts as text?

I can only infer that the Switch statement is transposing the data format cause other non-date column types are also being evaluated in my switch statement.

Set(_varColumn,"Start Date");
//SORT DATA SOURCE BY DYNAMIC VARIABLE VALUE
Sort(
 Filter(
 'MyDataSource',
 Active = 1
 ),
 Switch(
 _varColumn,
 "Employee",
 //THE Employee COLUMN IS A PERSON COLUMN IN THE SHAREPOINT LIST
 Employee,
 "Start Date",
 //THE StartDate COLUMN IS A CUSTOM DATE TIME COLUMN IN THE SHAREPOINT LIST
 StartDate,
 "Created",
 //THE Created COLUMN IS THE DEFAULT COLUMN IN THE SHAREPOINT LIST
 Created
 ),
 SortOrder.Ascending
)

Categories:

Building Power Apps

All responses (5)

Answers (0)

Sort by

WarrenBelz 156,748 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @9erFan925 ,

The Switch() statement would not do that, except the one above should be Employee.DisplayName - also what do you mean by "Custom Date Column"
9erFan925 24 on at

Like
a
(0)

Report
Copy link

Link copied!

I am using Employee.DisplayName as you stated.

Custom date column = any date column I create in my SharePoint list.
WarrenBelz 156,748 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!
@9erFan925 ,

I just noticed you have a Set() command at the top of your filter - I assume this is a different piece of code - it does not belong in a Filter. Your issue seems to be that conditional Sort is not Delegable with SharePoint - you can do this (worked fine testing here), but the top filter needs to return record numbers under your Delegation limit.

With( { _Data: Filter( 'MyDataSource', Active = 1 ) }, Sort( _Data, Switch( _varColumn, "Employee", Employee, "Start Date", StartDate, "Created", Created ), SortOrder.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.

MVP (Business Applications) Visit my blog Practical Power Apps
9erFan925 24 on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @WarrenBelz,

I wasn't able to solve this issue w/ out the use of an IF statement. The if statement now checks to see if the variable value being passed represents a date column. If it does, then do Sort A, else do Sort B.

I provided my updated syntax below as an example of what I ultimately had to do. If the switch statement wasn't the culprit here, I've not solved the problem, just wrote the code in a different way to accomplish the same result. With more time, maybe I could dig in deeper to find the true issue - possible the sort itself?

With( { _Data: Filter( 'MyDataSource', Active = 1 ) }, If(_varColumn in “Created, Start Date”, Sort( _Data, Switch( _varColumn, "Start Date", StartDate, "Created", Created ), SortOrder.Ascending ), Sort( _Data, Switch( _varColumn, "Employee", Employee ), SortOrder.Ascending )) )
WarrenBelz 156,748 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

@9erFan925 ,

Why have you split the Sort into two portions ?