Solved: Search and Sort issues

Anonymous · ‎10-17-2017

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 · ‎10-23-2017

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.

View solution in original post

Michael · ‎10-25-2017

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.

View solution in original post

Michael · ‎10-25-2017

Also, I realized you may run into issues since you have two separate buttons for Approved/Pending. If you are modeling this interaction based on the sort order, the primary difference is that sorting has two states, ascending and descending. So, the button simply toggles between these two. However, your approved/pending filtering actually has three states that are related to each other. no filtering, approved only, pending only.

In order to properly handle this, it won't be sufficient to use the same interaction as the Sort button. Instead, you may want to have a dropdown that chooses between one of the three states. You can then change the Filter based on this dropdown's value with the Switch() function.

This could look like

Filter(
    ECTCR_Tracking,
    Switch(
	ApprovalStatus.Selected.Value,
	"Approved Only", CP_x0020_Status.Value = "Approved",
	"Pending Only", CP_x0020_Status.Value = "Pending",
	"Any", true
    )
)

When this is nested into the entire function, it would look like the following:

SortByColumns(
    Filter(
        Filter(
            ECTCR_Tracking,
            Switch(
                ApprovalStatus.Selected.Value,
                "Approved Only", CP_x0020_Status.Value = "Approved",
                "Pending Only", CP_x0020_Status.Value = "Pending",
                "Any", true
            )
        ),
        StartsWith(Title, SearchInput.Text) || 
        StartsWith(Text(ID, "[$-en-US]##"), SearchInput.Text)
    ), 
    "ID", 
    If(
        SortDescending1, 
        Descending, 
        Ascending
    )
)

View solution in original post

Michael · ‎10-17-2017

Hi @Anonymous,

Sorry to hear that you're having difficulty with the search and sort functionality. The best way to think about these is that these buttons are setting state which is later used elsewhere in the app. Instead of changing the search and sort buttons directly, you will want to look at what is happening in the Items property of your BrowseGallery1. This function makes use of the TextSearchbox1.Text and SortDescending1 state variables to display your items as appropriate. If you are still having difficulty, please let me know what the formula is for this Items property. You may also want to reset the Search and Sort buttons to their original functions.

Anonymous · ‎10-17-2017

The BrowseGallery1 only has this:

Items = ECTCR_Tracking

Which is the name of my data source.

I changed it to

Items = SortByColumns(Filter(ECTCR_Tracking, StartsWith(ComplianceAssetId, TextSearchBox1.Text)), "ComplianceAssetId", If(SortDescending1, Descending, Ascending))

However, the search button still doesn't work. Nor does the filter button.

Michael · ‎10-17-2017

Is this app one that you built from scratch or via app from data on Sharepoint? If it is the latter, do you see the same behavior from a new app from data?

Anonymous · ‎10-17-2017

Built from data using SharePoint.

I created another app from the same list to see any differences. The SearchIcon1 OnSelect = false. The search button doesn't work on the newly created app either.

I want to be able to search for values in the columns Title and ID from the SharePoint list.

I'd also like sort to sort the list numerically based on the ID of the item rather and alphabetically. Do you think you can help with that or point me somewhere that can?

I was able to get the search and sort functionality correctly working as per the default -- but I don't want the default settings.

Michael · ‎10-17-2017

Great, if you have the default working, it shouldn't be too much more work to modify it to look at the columns you are interested in. What is the formula for the BrowseGallery1 Items property now that it is working and what are the names of the columns you wish to search and sort by?

Anonymous · ‎10-17-2017

Items = SortByColumns(Filter(ECTCR_Tracking, StartsWith(Title, SearchInput.Text)), "Title", If(SortDescending1, Descending, Ascending))

I want to be able to search for columns "Title" and "ID", not just Title.

I also want to sort numerically based on ID (which is an integer value) rather than default sort alphabetically.

Thanks for the help.

Michael · ‎10-18-2017

The following modifications should take care of what you are looking for:

Items = SortByColumns(Filter(ECTCR_Tracking, StartsWith(Title, SearchInput.Text) || StartsWith(ID, SearchInput.Text)), "ID", If(SortDescending1, Descending, Ascending))

To break it down, "||" is the "OR" operator. You can also get the same effect by wrapping these two with the Or() function. Documentation for this is here. I am adding a second StartsWith() call to allow for search to look for a value in either the Title column or the ID column. Secondly, the "Title" was a parameter to the outer SortByColumns() function which has documentation here. This second parameter specifies the column to sort by. The first parameter is actually a table generated by the earlier Filter() function. Let me know if you still have any questions or if this does not work for you.

Thanks,

Michael

Anonymous · ‎10-18-2017

I'm actually an experienced programmer. I've been looking at the documentation and trying to understand them.

The issue I have with the formula you gave me is that:

StartsWith is expecting a text value and ID is a number.

The ID column is the auto-generated field in the SharePoint list.

So that solution doesn't work. I wish it would.

Michael · ‎10-18-2017

You can use the Text() function to transform a value to text and Value() to convert text to a number. Depending on the behavior you want from the search box, you can either use StartsWith() and convert the ID to a Text value and allow a broader set of matches or you can convert the search text to a number and look for exact ID matches.