How to use custom sorting in canvas apps

What is custom sorting?

Records can be easily sorted in a canvas app in two ways - ascending and descending. However, often there is a need to sort in a more complex/custom manner.

Use case for custom sorting

A perfect example is sorting of activities by a specific order of their statuses (Open, Scheduled, Completed, Closed). This one can be achieved pretty easily by using the SortByColumns function.

SortByColumns(
    Filter(
        Activities,
        Regarding = gblSelectedOpportunity,
        'Activities (Views)'.'Appt and Task View'
    ),
    "statecode",
    [
        'Activity Status (Activities)'.Open,
        'Activity Status (Activities)'.Scheduled,
        'Activity Status (Activities)'.Completed,
        'Activity Status (Activities)'.Canceled
    ]
)

To learn more about Sort and SortByColumns functions, click here.

Implementing custom sorting

I had a use case in one of my projects, that was a bit more complicated than the one above.

List of requirements:

1. Show overdue appointments and tasks first (sorted by due date - most overdue ones at the top) that are open/scheduled
2. Show other appointments and tasks, sorted by due date (sooner due dates at the top) with completed/cancelled ones at the bottom (after open/scheduled)

As you an see, this one was complicated because I had to split the list of open and scheduled tasks/opportunities into two separate lists but still show as part of the same gallery.

To achieve this, here is what I did:

1. I added a flexible height gallery
2. Then I set its Items property to

   Sequence(2)

3. After that, I nested a gallery inside the parent flexible height gallery
4. Then, I set the Items property of this gallery to:

   If(
       ThisItem.Value = 1,
       Sort(
           Filter(
               Activities,
               Regarding = gblSelectedOpportunity,
               'Activities (Views)'.'Test View',
               'Activity Status' = 'Activity Status (Activities)'.Open || 'Activity Status' = 'Activity Status (Activities)'.Scheduled,
               'Due Date' < Today() 
           ), 
           'Due Date', 
           SortOrder.Ascending 
       ), 
       SortByColumns( 
           Sort( 
                Filter( 
                       Activities, 
                       Regarding = gblSelectedOpportunity, 
                       'Activities (Views)'.'Test View', 
                       'Due Date' >= Today() || ('Activity Status' = 'Activity Status (Activities)'.Completed || 'Activity Status' = 'Activity Status (Activities)'.Canceled)
               ),
               'Due Date',
               SortOrder.Ascending
           ),
           "statecode",
           [
               'Activity Status (Activities)'.Open,
               'Activity Status (Activities)'.Scheduled,
               'Activity Status (Activities)'.Completed,
               'Activity Status (Activities)'.Canceled
           ]
       )
   )

After updating the Items property of the gallery to the above code, here is how the list of activities looked:

Recent articles

1. How to use Dataverse views to filter related records
   
2. Have you played Connect 4? In Power Apps?
3. Have you played Battleship? In Power Apps?