cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Excel_aint
Level: Powered On

Sorting Subtitles by number

My simple powerapp has 2 columns. First column (Title1) contains Names. Second column (Subtitle1) contains userID numbers. All data comes from an excel table on onedrive. I need to sort the userID column by number & not alphabetically. Currently single digit numbers are not sorting correctly,

e.g. 10,11,12,13,14,6,7,8,9  or  9,8,7,6,14,13,12,11,10

 

SortByColumns(Search(NamesTable, TextSearchBox1.Text, "Names","User_x0020_IDs"), "User_x0020_IDs", If(SortDescending1, Descending, Ascending))

 

How do I get it to sort by number correctly ?

Thank you

K

1 ACCEPTED SOLUTION

Accepted Solutions
PowerApps Staff CarlosFigueira
PowerApps Staff

Re: Sorting Subtitles by number

You can use the Sort function (instead of SortByColumns), where you can use an expression that would convert the text column to a numeric value:

Sort(
    Search(NamesTable, TextSearchBox1.Text, "Names", "User_x0020_IDs"),
    Value(User_x0020_IDs),
    If(SortDescending1, Descending, Ascending))

View solution in original post

8 REPLIES 8
Super User
Super User

Re: Sorting Subtitles by number

Hi @Excel_aint

You can add a column that contains the numeric representation of your userID values, and sort by that. The formula you would use would look like this:

 

SortByColumns(AddColumns(
Search(NamesTable, TextSearchBox1.Text, "Names","User_x0020_IDs"),
"UserIDInt",
Value("User_x0020_IDs")),
"UserIDInt",
If(SortDescending1, Descending, Ascending)
)

 

Excel_aint
Level: Powered On

Re: Sorting Subtitles by number

Thanks timl.
Why do I need to add a 3rd column ?
Is it not possible to format the subtitle to numbers or integers then sort ?
K
Super User
Super User

Re: Sorting Subtitles by number

Hi K,

I understand that it seems unnecessary to call AddColumns. So to avoid that, you can carry out the value conversion in the call to SortByColumns like so.
 

SortByColumns(Search(NamesTable, TextSearchBox1.Text, "Names","User_x0020_IDs"),
              Value("User_x0020_IDs"),
              If(SortDescending1, Descending, Ascending)
)

Just for clarity, AddColumns does NOT add a column to your underlying Excel data source. This function provides only a return value that includes the additional columns that you specify.
The benefit of AddColumns is that you can use it to build a base set of data that you can use to apply additional filtering. By calling AddColumns for example, it becomes possible to write formula to further Filter the result of your Search, based on <,>,>=,<= operators on your numeric UserIDs value.

PowerApps Staff CarlosFigueira
PowerApps Staff

Re: Sorting Subtitles by number

You can use the Sort function (instead of SortByColumns), where you can use an expression that would convert the text column to a numeric value:

Sort(
    Search(NamesTable, TextSearchBox1.Text, "Names", "User_x0020_IDs"),
    Value(User_x0020_IDs),
    If(SortDescending1, Descending, Ascending))

View solution in original post

Yobeekster
Level: Powered On

Re: Sorting Subtitles by number


@CarlosFigueira wrote:

You can use the Sort function (instead of SortByColumns), where you can use an expression that would convert the text column to a numeric value:

Sort(
    Search(NamesTable, TextSearchBox1.Text, "Names", "User_x0020_IDs"),
    Value(User_x0020_IDs),
    If(SortDescending1, Descending, Ascending))

Hi Carlos, 

 

I had the same issue, and I tried your solution, but the sort Sort.PNG

would no longer work. In other words, I am not able to sort the ID to Ascending or Descending. Its currently defaulted to Ascending and the button above doesn't do anything. 

 

By changing from SortByColumns to Sort, don't you lose functionality?

 

Regards,

Jason

 

Yobeekster
Level: Powered On

Re: Sorting Subtitles by number

Also, wouldn't it be better to convert or make the text a number right at the source of input.

 

In other words, the ID I have is autogenerated and not editable. Shouldn't I be able to make it a number initially, so that whatever I use it for downstream, it will be read as a number, so that I don't have to be concerned with changed formula's downstream?

 

 

PowerApps Staff CarlosFigueira
PowerApps Staff

Re: Sorting Subtitles by number

Sort has the same functionality as SortByColumns, except that it allows for arbitrary expressions to be used as the "sort value". In many cases the behavior is the same, but it's possible that some of those expressions cannot be delegated to the server.

PowerApps Staff CarlosFigueira
PowerApps Staff

Re: Sorting Subtitles by number

Yes, if the type of the source were a number from the start this would not be a problem; the issue is that the original poster used Excel as a data source, which doesn't have a concept of data types (it has a concept of data formatting), so all columns are returned as a string, which is why that workaround is needed.

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors
Users Online
Currently online: 352 members 6,465 guests
Recent signins:
Please welcome our newest community members: