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

[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
)

[WarrenBelz]

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]

I am using Employee.DisplayName as you stated.

Custom date column = any date column I create in my SharePoint list.

[WarrenBelz]

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

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]

@9erFan925 ,

Why have you split the Sort into two portions ?