Search and Sort issues

[Anonymous]

Greetings, 

I am new to PowerApps. I have major issues with the sort and search buttons that are given to me when creating a PowerApp from a data source.

I am connected to a SharePoint list. I'd like for the sort button (figure 1) to sort the data by ID (integer). The default appears to be:

                                                                                              Figure 1

Function: 

OnSelect = UpdateContext({SortDescending1: !SortDescending1})

Expected Result of function: Sort Alphabetically

Actual Result: Nothing Happens.

Tried Solutions: I have pressed that up and down arrow button and clicked the refresh button next to it. Nothing happened. I have been through to documentation and tried to use examples from there. They have also not worked. 

Error listed: none

------------------------------------------------------------------------------------------------------------------------------------------

Problem: I attempted to rewrite the sorting function to sort numerically. 

Function:

OnSelect = UpdateContext({SortDescending1: !SortDescending1, SetPriority: "ID"})

Expected Result: Sort Numerically

Actual Result: Nothing Happens 

Tried Solutions: Again I have been through to documentation trying to get such functionality to work. 

Error listed: none

--------------------------------------------------------------------------------------------------------------------------------------

Problem: The search button does not work. There was no starting functionality added by default on startup. 

                                                                                                  Figure 2 

Desired Result: Ideally a user could search for several popular columns in the SharePoint list (Title, ID, Category, Department, Priority). The search button would filter for that text.

Tried Solutions: I have referenced the documentation and do not understand what I could be doing wrong. I have tried to write several functions for the result I'd like. I tried to keep it simple until I understand more. For example, I want to only search for a particular item ID. 

Function: 

OnSelect = Search( ECTCR_Tracking, SearchInput.Text, "ID" )

Note: The text input the search button "should" be referencing is named SearchInput. 

Error listed: "Suggestion: Part of this Search formula cannot be evaluated remotely due to service limitations. The local evaluation may produce suboptimal or partial results. If possible, please simplify the formula. For more information, please see the search function documentation. "

Please help. I don't understand what is going on. 

[Anonymous]

Thanks for the response. 

I am still having a bit of trouble with this. I appreciate your patience with me.

The formula I am now using is:

Items =  SortByColumns(Filter(ECTCR_Tracking, StartsWith(Title, SearchInput.Text) || StartsWith(Text (ID, "[$-en-US]##"), SearchInput.Text)), "ID", If(SortDescending1, Descending, Ascending))

None of my data shows up in the browser view using this formula. I don't get an error, but the suggestion from earlier shows up about "Part of this StartsWith formula cannot be evaluated remotely due to service limitations . . .". 

??

[Michael]

So, I asked around and it seems like Sharepoint has a known limitation that prevents it from searching a numerical column. Since this function can't be delegated to the server, PowerApps does the best it can and attempts to download the first 500 rows from the data source and perform your filter operation locally. I suspect that you have a large data source and the rows you are interested in are not in the first 500, therefore the search comes up empty handed. To work around this, it's possible to create a new column in Sharepoint that's a text version of the ID and perform the filter on that column instead. I hope that is sufficient for you, even if it is an ugly solution.

[Anonymous]

The SharePoint list only has 4 entries. I'm guessing I'm just a giant noob. But hearing about the query limitation is interesting . . .

For whatever reason my forumla:

SortByColumns(Filter(ECTCR_Tracking, StartsWith(Title, SearchInput.Text) || StartsWith(Text (ID, "[$-en-US]##"), SearchInput.Text)), "ID", If(SortDescending1, Descending, Ascending))

Is now working. Thanks for all the help.

[Anonymous]

I need to chain several more "if" statements on that items property. How can I achieve that? 

I have a UpdateContext variable set that triggers when I push a button. How can I have several if statements? 

Example:

Items = SortByColumns(Filter(ECTCR_Tracking, StartsWith(Title, SearchInput.Text) || StartsWith(Text (ID, "[$-en-US]##"), SearchInput.Text)), "ID", If(SortDescending1, Descending, Ascending), If(ReviewCompleted, Filter(ECTCR_Tracking, CP_x0020_Status.Value = "Approved")))

The above example doesn't actually work, however. 

[Michael]

So, what's happening originally with the If() isn't a chaining. The last argument to SortByColumns() is a boolean parameter indicating the direction of the sort. You can add additional parameter pairs indicating subsequent columns to sort by and whether they should be ascending or descending sorts. However, it seems like you want to modify the filter.

In order to filter by a more complex formula, you're going to want to go back to the first argument. If you simply want to toggle based on the ReviewCompleted value, you can use:

If(ReviewCompleted,
    Filter(ECTCR_Tracking, CP_x0020_Status.Value = "Approved"),
    Filter(ECTCR_Tracking, StartsWith(Title, SearchInput.Text) || StartsWith(Text(ID, "[$-en-US]##"))
)

If, instead, you would like to further filter the table based on the ReviewCompleted value, you can nest the two statements like so:

Filter(
    If(ReviewCompleted,
        Filter(ECTCR_Tracking, CP_x0020_Status.Value = "Approved"),
        ECTCR_Tracking
    ),
    StartsWith(Title, SearchInput.Text) || StartsWith(Text(ID, "[$-en-US]##")
)

Let me know if you have any questions with this format.

[Anonymous]

Well, neither of those formulas actually work. I'm trying to create a custom one that will not return errors now. 

I'd like to keep the sorting for ascending and descending. I'd like to sort by CP_x0020_Status.Value = "Approved" as well. Both would be their own separate button. 

If I use this formula:

If(ReviewCompleted,
    Filter(ECTCR_Tracking, CP_x0020_Status.Value = "Approved"),
    Filter(ECTCR_Tracking, StartsWith(Title, SearchInput.Text) || StartsWith(Text(ID, "[$-en-US]##"))
)

There are several issues with it. I have changed it to:

If(ReviewCompleted,

Filter(ECTCR_Tracking, CP_x0020_Status.Value = "Approved"),

Filter(ECTCR_Tracking, StartsWith(Title, SearchInput.Text) || StartsWith(Text(ID, "[$-en-US]##"), SearchInput.Text)

))

However, the search function doesn't work anymore. When typing in the search input it doesn't fire any action. 

The second formula provided has missing arguments and errors as well. I have changed this to:

Filter(
If(ReviewCompleted,
Filter(ECTCR_Tracking, CP_x0020_Status.Value = "Approved"),
ECTCR_Tracking
),
StartsWith(Title, SearchInput.Text) || StartsWith(Text(ID, "[$-en-US]##"), SearchInput.Text
))

However, again the search functions do not work. Here is a visual to help you understand what I'm trying to accomplish.

I believe what the solution is something like having multiple If() conditions as the documentation says. However, I cannot get this to work correctly. Like this:

SortByColumns(

Filter(ECTCR_Tracking, StartsWith(Title, SearchInput.Text) ||

StartsWith(Text (ID, "[$-en-US]##"), SearchInput.Text)), "ID",

If(SortDescending1, Descending, Ascending,

ReviewCompleted, Filter(ECTCR_Tracking, CP_x0020_Status.Value = "Approved")))

[Anonymous · Accepted solution]

I had several issues with those formats. There are mistakes with the functions provided. After correcting them, and making a lengthy post that was mysteriously deleted after an hour, I have decided that PowerApps is not capable of giving me the functionality that I'd need.

I want to be able to sort the list based on several buttons, just like the ascending/descending default sort button. I have read the documentation and settled that perhaps this:

SortByColumns(

Filter(ECTCR_Tracking, StartsWith(Title, SearchInput.Text) ||

StartsWith(Text (ID, "[$-en-US]##"), SearchInput.Text)), "ID",

If(SortDescending1, Descending, Ascending, ReviewCompleted, Filter(ECTCR_Tracking, CP_x0020_Status.Value = "Approved")))

Might be the correct approach. Review completed is just another button quite like SortDescending1. However, because this does not work and produces errors, I need to find another approach. I made another browse screen that gets navigated to when the button ReviewCompleted is clicked. The browser formula for items is this: 

Filter(
If(true,
Filter(ECTCR_Tracking, CP_x0020_Status.Value = "Approved"),
ECTCR_Tracking
),
StartsWith(Title, SearchInput_1.Text) || StartsWith(Text(ID, "[$-en-US]##"), SearchInput_1.Text
))

I wanted the sort button to work with this as well but was unable to do so. Without a competent debugger, PowerApps is a nightmare to do anything with. I will continue this trend of making separate screens. 

[Michael]

Can you explain to me what you're trying to do with the last If() statement in your first example?

If(SortDescending1, Descending, Ascending, ReviewCompleted, Filter(ECTCR_Tracking, CP_x0020_Status.Value = "Approved")))

[Anonymous]

Sure.

The variable "ReviewCompleted" was set on an OnSelect property of an icon. It has the same format as SortDescending1. 

I have a button at the top of the app next to the sort descending. This button, when clicked, needs to filter a SharePoint list based on a value of "Approved". 

Query the SharePoint list and return all records with CP_Status = "Approved". Display those records to the user. 

I also have another button next to the button just described that does the opposite. Query the list and return all records with a CP_Status of "Pending". 

Did that make sense? 

(I now have two icons because I wasn't able to make it work correctly. Now clicking an icon takes the user to a different browser with the correct query results, rather than returning the query results on the original browsergallery screen)

[Michael · Accepted solution]

Hi Jonathon,

I'm sorry, my intent with my most recent formulas was not to give you the full formula for a copy/paste as it is getting quite large, but rather to create the table that is then passed in to SortByColumns. My apologies for not making that clear.

The reason you are currently running into trouble is because that If() is solely for passing either "Descending" or "Ascending" to the final argument of the SortByColumns() function. If you are adding more conditions, they must also either return "Descending" or "Ascending" otherwise it is meaningless to SortByColumns(). Instead, you need that If() logic to be part of the original table that is being sorted. For a copy/paste solution, you can try the following:

SortByColumns(
    Filter(
        If(ReviewCompleted,
            Filter(
                ECTCR_Tracking, 
                CP_x0020_Status.Value = "Approved"
            ),
            ECTCR_Tracking
        ),
        StartsWith(Title, SearchInput.Text) || 
        StartsWith(Text(ID, "[$-en-US]##"), SearchInput.Text)
    ), 
    "ID", 
    If(
        SortDescending1, 
        Descending, 
        Ascending
    )
)

Hopefully the variable names are all correct here. This is for the latter solution I gave where ReviewCompleted is an initial filter that is applied before filtering based on the search input text. If this doesn't work, or you have further questions, I can break down what each piece here is doing.