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

Re: Search and Sort issues

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 . . .". 

 

??

 

Microsoft Michael
Microsoft

Re: Search and Sort issues

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.

Johnathon_S
Level: Powered On

Re: Search and Sort issues

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.

 

 

 

Johnathon_S
Level: Powered On

Re: Search and Sort issues

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. 

 

 

Microsoft Michael
Microsoft

Re: Search and Sort issues

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.

Johnathon_S
Level: Powered On

Re: Search and Sort issues

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.
 
not_complete.PNG
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")))
Johnathon_S
Level: Powered On

Re: Search and Sort issues

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

Microsoft Michael
Microsoft

Re: Search and Sort issues

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")))
Johnathon_S
Level: Powered On

Re: Search and Sort issues

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". 

header.PNG

 

 

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)

Microsoft Michael
Microsoft

Re: Search and Sort issues

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

Helpful resources

Announcements
thirdimage

Power Apps 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

SecondImage

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)
Users online (5,051)