SortByColumns Search a collection using a variable to display in a gallery

[Fleming]

Hello,

I need to sort a field within a gallery. I created a gallery to display fields stored in my collection. I need to place my "NHPNum" field in ascending order. I'm using the below and returning an error. 

SortByColumns(Search(HPCollection,varBuilderCase.OpportunityID,"OpportunityID"),NHPNum,Ascending)

"varBuilderCase" is my variable to return the information link to a particular opportunity ID. 

My original syntax that is working is :NHPNum

Which of course is just displaying the values for this field. The issue is my list of records in the gallery is not in order. Any suggestions are appreciated. Thanks in advance.

[PowerAddict]

Are you saying that the above expression is displaying all the expected results but not in the correct sort order? Are you getting any errors?

---
If you like this reply, please give kudos. And if this solves your problem, please accept this reply as the solution. Thanks!

Hardit Bhatia
https://thepoweraddict.com

[Fleming]

I am getting an error. Thanks

[PowerAddict]

Can you please share the error?

[Fleming]

The errors are: "The function 'Search' has some invalid arguments." & "The function 'SortByColumns' has some invalid arguments.

But perhaps I'm using the wrong syntax altogether. In the Text property for this field: HNPNum and the gallery is connected to HPCollection. I return the following results:

I need to sort NHPNum in ascending order. When I did a simple sort formula: Sort(HPCollection,NHPNum,Ascending) my error was "Expected Text Value". So any assistance to get it in ascending order would be appropriated. Thanks 

[v-yutliu-msft]

Hi @Fleming ,

Could you tell me how do you set variable varBuilderCase?

I need to know the data type of varBuilderCase.

Please make sure that "varBuilderCase.OpportunityID" is text type.

The right syntax of Search function is: 

Search( Table, SearchString, Column1 [, Column2, ... ] )

The second parameter should be string.

What's more, you should change SortByColumns function too.

Please use formula like this:

SortByColumns(.......,"NHPNum")

You need to use  ""  on both sides of the fieldname.

SortOrder.Ascending is the default. You do not need to list it.

Here are docs about these two functions for your reference:

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-sort

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-filter-lookup

Best regards,

[Fleming]

@v-yutliu-msft  The variable is set multiple steps back when the user enters the ID. This pulls information saved for the given opportunityID from my datasource (SharePoint) and save it to the collection (HPCollection). OpportunityID is a text field.
I did updated the search and sortbycolumn syntax to your suggestion and I received the attached errors. When I hoover over NHPNum it states the field does not exist. I also included an image of the field from my collection. At this point, I'm ok with removing the search functionality if I can get assistance on a simple ascending sort for NHPNum which is a number field in my collection. Thanks in advance.