cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ChrisC
Kudo Kingpin
Kudo Kingpin

Using Multiple Sort Orders in a Gallery

Hi all,

 

I have a gallery connected to a database table with the following columns:

ChildName
Center
Date
State

And I have implemented SortByColumns() in the gallery which sorts on search results (if any)

SortByColumns(Search('[dbo].[tblTardyStatus]_1', TextSearchBox1.Text, "ChildName", "Center","Date","State"),"State", ["New", "Absent", "Absent unexcused", "Late", "Late unexcused", "On-time"])

It sorts a filtered result based on the Search() parameters (text matching any of the 4 columns) and then sorts it by the "State" column with a custom order which is

["New", "Absent", "Absent unexcused", "Late", "Late unexcused", "On-time"]

The issue I'm having is then sorting the results in alphabetical order according to the ChildName column. I have by adding two new arguments to the SortByColumns like displayed on the sort function page for powerapps

SortByColumns(Search('[dbo].[tblTardyStatus]_1', TextSearchBox1.Text, "ChildName", "Center","Date","State"),"State", ["New", "Absent", "Absent unexcused", "Late", "Late unexcused", "On-time"], "ChildName", Ascending)

The issue is that it returns the error "SortByColumns has some invalid arguments"

 

Thanks for any help

6 REPLIES 6
v-micsh-msft
Community Support
Community Support

Hi @ChrisC,

 

The syntax for the SortByColumns is as below:

First usage:

SortByColumnsTableColumnName1 [, SortOrder1ColumnName2SortOrder2, ... ] )

Second usage:

SortByColumnsTableColumnNameSortOrderTable )

 

Per your formula, you are trying to combine those two usage into one, which is not supported.

To workaround this, take a try to add a nested SortByColumns funtion.

Formula should be:

SortByColumns(
    SortByColumns(
              Search('[dbo].[tblTardyStatus]_1', TextSearchBox1.Text, "ChildName", "Center","Date","State"),
              "State", 
              ["New", "Absent", "Absent unexcused", "Late", "Late unexcused", "On-time"]), 

"ChildName", Ascending)

Regards,

Michael

Community Support Team _ Michael Shao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-micsh-msft,

 

It doesn't seem to be sorting by childname still but I cannot see why your solution wouldn't work..

I also tried reversing the nest order to see if that would do it but unfortunately not. Thank you for the insight though, I will try and come up with a solution based on this

I'm seeing the same results as @ChrisC here- A Custom Sort + Sort by Alpha does not seem to be working. Does anyone know how to do a custom sort where a group of columns are presented first, then the remaining columns sorted alphabetical in ascending order? 

Example:

SortByColumns(
    SortByColumns(
        colData,
        "CHAR_NAME",
        [
            "THING1",
            "OTHERTHING1",
            "THING2",
            "OTHERTHING2",
            "ETC"
        ]
    ),
    "CHAR_NAME",
    Ascending
)

 Thanks

Hi @ericonline ,

Try this

Sort(
   SortByColumns(
      colData,
      "CHAR_NAME",
      [
         "THING1",
         "OTHERTHING1",
         "THING2",
         "OTHERTHING2",
         "ETC"
      ]
   ),
   CHAR_NAME,
   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.

Hi @WarrenBelz . Thanks for the idea. This sorting just eliminates the custom sort (in the square brackets) and puts the whole list in alpha. 

I'm looking for custom sort up top, then alpha after.

Hi @v-micsh-msft . Thanks for the idea. This results in losing the custom sort (everything in the square brackets). The list is sorted by alphanumeric only. 

I need the custom sort on top of the list and alphabetic after the custom sort.

Helpful resources

Announcements
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Difinity Conference 2022

Difinity Conference 2022

Register today for two amazing days of learning, featuring intensive learning sessions across multiple tracks, led by engaging and dynamic experts.

European SharePoint Conference

European SharePoint Conference

The European SharePoint Conference returns live and in-person November 28-December 1 with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Power Apps Ideas

Check out the New Ideas Site

We are excited to announce a new way to share your ideas for Power Apps!

Top Solution Authors
Top Kudoed Authors
Users online (5,934)