Dynamically sorting a gallery list - numerical / alphabetical sorts in the same condition statement

[Kato249]

Hi all,
I struck a really annoying problem with sorting. I have a dropdown box that allows the user to sort using one of a number of fields (see below)

And the code that sits behind the gallery:

Sort(
    AddColumns(
        Filter(
            Jobs,
            // First, eliminate the most amount of records to avoid delegation issues.
            (IsBlank(txtSearchCriteria.Value) || 
                Lower(txtSearchCriteria.Value) in Lower('Job Number') ||
                Lower(txtSearchCriteria.Value) in Lower('Purchase Order') ||
                Lower(txtSearchCriteria.Value) in Lower(Project) ||
                Lower(txtSearchCriteria.Value) in Lower(Customer.'Company Name')),
            
            If(
                "Complete" in Concat(cmboJobStatus.SelectedItems, Value) || 
                "Cancelled" in Concat(cmboJobStatus.SelectedItems, Value),
                // If either "Completed" or "Cancelled" is selected
                'Job Status' in cmboJobStatus.SelectedItems.Value,
                // If neither "Completed" nor "Cancelled", include all other statuses
                Not('Job Status' in ["Complete", "Cancelled"])
            ),
            
            // Check if user or other job criteria matches conditions
            (IsBlank(displayUserJobs) || cmboAssignee.Selected.UserGUID in 'Assigned To'.UserGUID),
            (IsBlank(varMilestoneLookup) || Milestone in cmboMilestone.SelectedItems.Value),
            (IsBlank(varJobStatusLookup) || 'Job Status' in cmboJobStatus.SelectedItems.Value)
        ),
        JobNumberNumerical,
        Value('Job Number')
    ),
    If(
        drpdwnSortBy.Selected.Value = "Job Number", JobNumberNumerical, //The numerical version of 'Job Number'. Primary Name field (Text) - not a number because of dataverse limitation.
        drpdwnSortBy.Selected.Value = "Project Name", Project, //Text Field
        drpdwnSortBy.Selected.Value = "Customer", Customer.'Company Name', //Lookup field
        drpdwnSortBy.Selected.Value = "Date", 'Milestone Date', //date field
//        drpdwnSortBy.Selected.Value = "Newly Assigned", 'Newly Assigned?', //yes/no field.
        JobNumberNumerical        
    ),
   If(
        varSortDirection = "Ascending", SortOrder.Ascending, SortOrder.Descending
    )
)

 The first issue I encountered was 'Job Number' was being alphabetically sorted, so a number like 4567 appeared after 20679. I worked around this by adding the column 'JobNumberNumerical'. Now the issue I face is the sort function is trying to sort text fields numerically. It appears the sort function determines the sort type by the type of the first variable in the IF list. So if I put a text field first, my numerical fields go back to being sorted alphabetically. It doesn't seem to apply to dates though.

I've spent countless hours trying to work around this by Googling, Co-Piloting, ChatGPT4o and the proposed solutions become very unwieldy and cumbersome. I'm sure someone else has struck this issue before and I'm hoping they might have a nice little workaround.

Thanks!

[WarrenBelz]

Hi @Kato249 ,

This is just a thought as your code is technically correct from what I can see. Try getting rid of the first sort as that is the "fall back" default anyway

Sort(
   AddColumns(
      Filter(
         Jobs,
         IsBlank(txtSearchCriteria.Value) || 
         Lower(txtSearchCriteria.Value) in Lower('Job Number') ||
         Lower(txtSearchCriteria.Value) in Lower('Purchase Order') ||
         Lower(txtSearchCriteria.Value) in Lower(Project) ||
         Lower(txtSearchCriteria.Value) in Lower(Customer.'Company Name'),
         If(
            "Complete" in Concat(cmboJobStatus.SelectedItems, Value) || 
            "Cancelled" in Concat(cmboJobStatus.SelectedItems, Value),
            'Job Status' in cmboJobStatus.SelectedItems.Value,
            Not('Job Status' in ["Complete", "Cancelled"])
         ),
         (
            IsBlank(displayUserJobs) || 
            cmboAssignee.Selected.UserGUID in 'Assigned To'.UserGUID
         ),
         (
            IsBlank(varMilestoneLookup) || 
            Milestone in cmboMilestone.SelectedItems.Value
         ),
         (
            IsBlank(varJobStatusLookup) || 
            'Job Status' in cmboJobStatus.SelectedItems.Value
         )
      ),
      JobNumberNumerical,
      Value('Job Number')
   ),
   Switch(
      drpdwnSortBy.Selected.Value,
      "Project Name", 
      Project,
      "Customer", 
      Customer.'Company Name',
      "Date", 
      'Milestone Date',
      "Newly Assigned", 
      'Newly Assigned?',
      JobNumberNumerical        
   ),
   If(
      varSortDirection = "Ascending", 
      SortOrder.Ascending, 
      SortOrder.Descending
   )
)

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

[Kato249]

Hi @WarrenBelz ,

Thanks for your reply!

I tried your suggestion by commenting out the first sort and it reverted to sorting the numerical column alphabetically... It's such a frustrating little problem! Also, for my data, and I don't know why, but sort accepted my If function whereas switch (which is my preferred method too!) kept throwing weird 'expected xyz' errors.

One alternative that I was looking at was to add a bunch of helper columns that would give the numerical value equivalent to the text fields and sort that way, but geee, I don't know! It seems like a great way to choke up resources, especially if a user needs to search the cancelled or completed job records as we add 10,000 records per year.

[WarrenBelz]

Hi @Kato249 ,

Yes it is frustrating - also have you tried SortByColumns ?

[Kato249]

Yep @WarrenBelz , it appears to exhibit the same behaviour as 'Sort'.

It's as if the sort/sortbycolumns functions don't treat each case or condition on its own, rather it applies whatever the type of the first condition is... having said that, date sorting still works so maybe I can turn the Job Number into a date column (assuming it's like Excel and stores dates as numbers)? 🤔

[Kato249]

EDIT: Thought I had a successful workaround but unfortunately not. Sort is exhibiting weird behaviour on the collected date field. 🥴

Bingo!

There's a workaround! Add a column as a date. Then put one of the text fields as the first condition in the If function (my code below):

Sort(
    AddColumns(
        Filter(
            Jobs,
            // First, handle the text search which is most likely to narrow down the dataset significantly
            (IsBlank(txtSearchCriteria.Value) || 
                Lower(txtSearchCriteria.Value) in Lower('Job Number') ||
                Lower(txtSearchCriteria.Value) in Lower('Purchase Order') ||
                Lower(txtSearchCriteria.Value) in Lower(Project) ||
                Lower(txtSearchCriteria.Value) in Lower(Customer.'Company Name')),
            
            If(
                "Complete" in Concat(cmboJobStatus.SelectedItems, Value) || 
                "Cancelled" in Concat(cmboJobStatus.SelectedItems, Value),
                // If either "Completed" or "Cancelled" is selected
                'Job Status' in cmboJobStatus.SelectedItems.Value,
                // If neither "Completed" nor "Cancelled" is selected, include all other statuses
                Not('Job Status' in ["Complete", "Cancelled"])
            ),
            
            // Check if user or other job criteria matches conditions
            (IsBlank(displayUserJobs) || cmboAssignee.Selected.UserGUID in 'Assigned To'.UserGUID),
            (IsBlank(varMilestoneLookup) || Milestone in cmboMilestone.SelectedItems.Value),
            (IsBlank(varJobStatusLookup) || 'Job Status' in cmboJobStatus.SelectedItems.Value)
        ),
        JobNumberasaDate,
        Date(1900,1,1) + Value('Job Number')
    ),
    If(
        drpdwnSortBy.Selected.Value = "Project Name", Project, //Text Field
        drpdwnSortBy.Selected.Value = "Customer", Customer.'Company Name', //Lookup field
        drpdwnSortBy.Selected.Value = "Date", 'Milestone Date', //date field
        drpdwnSortBy.Selected.Value = "Newly Assigned", 'Newly Assigned?', //yes/no field.
        JobNumberasaDate        
    ),
   If(
        varSortDirection = "Ascending", SortOrder.Ascending, SortOrder.Descending
    )
)

For anyone sorting that is encountering an error like: the value '<string>' cannot be converted to a number (or a date) - the above fix is a way to get around it.

TL;DR

- Sort and SortbyColumns cannot sort both numerically and alphabetically at the same time (even with IF statements).

- Import / add a numerical column as a date.

- Put the conditions that sort text at the top. (as per @WarrenBelz's first advice).

- Then conditions that include date sorting.

EDIT: I jumped the gun but will leave this reply here in case it inspires another solution.

[WarrenBelz]

@Kato249 ,

Sounds like a plan 

[Kato249 · Accepted solution]

Turns out the solution to my problem was pretty simple. Simply append a bunch of zeros to the front of the number and use the right function to truncate the string. Now I can sort any number up to '99,999,999'. Wish I could make it dynamic, and probably could, but don't have the time to work that out.

Sort(
    AddColumns(
        Filter(
            Jobs,
            // First, handle the text search which is most likely to narrow down the dataset significantly
            (IsBlank(txtSearchCriteria.Value) || 
                Lower(txtSearchCriteria.Value) in Lower('Job Number') ||
                Lower(txtSearchCriteria.Value) in Lower('Purchase Order') ||
                Lower(txtSearchCriteria.Value) in Lower(Project) ||
                Lower(txtSearchCriteria.Value) in Lower(Customer.'Company Name')),
            
            If(
                "Complete" in Concat(cmboJobStatus.SelectedItems, Value) || 
                "Cancelled" in Concat(cmboJobStatus.SelectedItems, Value),
                // If either "Completed" or "Cancelled" is selected
                'Job Status' in cmboJobStatus.SelectedItems.Value,
                // If neither "Completed" nor "Cancelled" is selected, include all other statuses
                Not('Job Status' in ["Complete", "Cancelled"])
            ),
            
            // Check if user or other job criteria matches conditions
            (IsBlank(displayUserJobs) || cmboAssignee.Selected.UserGUID in 'Assigned To'.UserGUID),
            (IsBlank(varMilestoneLookup) || Milestone in cmboMilestone.SelectedItems.Value),
            (IsBlank(varJobStatusLookup) || 'Job Status' in cmboJobStatus.SelectedItems.Value)
        ),
        JobNumberPadded,
        Right("00000000" & Text('Job Number'), 8)
    ),
    If(
        drpdwnSortBy.Selected.Value = "Project Name", Project, //Text Field
        drpdwnSortBy.Selected.Value = "Customer", Customer.'Company Name', //Lookup field
        drpdwnSortBy.Selected.Value = "Date", 'Milestone Date', //date field
        drpdwnSortBy.Selected.Value = "Newly Assigned", 'Newly Assigned?', //yes/no field.
        JobNumberPadded      
    ),
   If(
        varSortDirection = "Ascending", SortOrder.Ascending, SortOrder.Descending
    )
)