PowerApps search and filters

[mmmcoffeegood]

I have a few questions:

First: is it possible to point powerapps data to a particular view on a sharepoint list?

Second: I am trying to write an expression for my search box/gallery to only show items that are assigned to [Me] (or the person/user logged into the app. is it possible to use sort or filter to accomplish this?

Lastly: what is the most resource-efficient way to write a search expression for a larger list. (over 500 items)

[Drrickryp]

Hi @mmmcoffeegood,

In answer to  your questions,

1) Powerapps is a way of displaying your data from the list.  There are two controls that do this: galleries and the datatable control.  You should be able to customize the gallery to show any data in your list. 

2) The User() function can return the full name, email or image of the current user. To check it, you can insert a label control and set its text property to User().FullName and it should show your name. If you have a field in your list that has the author's email, you can filter the list to show items created by that person: ie. Filter(mylist,Author=User().Email).

3) Currently, the Search() function is not delegatable in Sharepoint lists.  That means Search() is limited in Sharepoint lists to the first 500 items by default, this can be increased to 2000 by going to File->App Settings->Advanced Settings and increasing the data row limit for non delegatable queries from 500 to 2000. However, it will still throw an error message regarding limitations but will still work for lists less than 2000.  In Sharepoint you can work around these non delegation limits by using delegatable functions instead. Filter() and StartsWith() are delegatable in Sharepoint and the combination of these functions can search very large lists, In my experience, over 20,000 rows. In the example attached, there is  a TextInput control and a DataTable control.  The Items property of the DataTable is Filter(ICD,StartsWith(ShortDes,TextInput1.Text)).  The data source is a list of over 40,000 diagnosis codes.

Tip: To achieve the best results when designing your Powerapps, you need to understand delegation.  Powerapp functions either work on the local device (nondelegatable) or work at the level of the network (delegatable).  An overview is at https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/delegation-overview

Different data sources, ie. Sharepoint, Sql Server, Excel tables, etc. have different rules.  To see which functions are delegatable in Sharepoint, check out this article.  It is always updated. 

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/delegation-list.  

[mmmcoffeegood]

Thank you for your reply.

Here is my situation. I am attempting to get my gallery to show the following:

When status is not "Completed", and assigned_to_1 = the user signed into the app, or assigned_to_1 = null

here is my failed attempt to write that..

Filter(MRQ, Status.Value <> "Completed", And(Assigned_To_1=User()),Or(Assigned_To_1 = IsEmpty(MRQ)))

MRQ is my sharepoint test list 

Assigned_To_1 is the person assigned to the request

[Drrickryp]

Hi @mmmcoffeegood,

Are you getting an error message?  If so, could you post it please. 

[mmmcoffeegood]

I'm not getting any data pulled into the app. It should only be one item in the Sharepoint list. Just trying to prepare for a larger list.

[mmmcoffeegood]

I do see where it lost the connection to the sharepoint data. My syntax may have caused that.

[Drrickryp]

Hi @mmmcoffeegood,

To isolate the problem, try simplifying the Filter statement in the Items property of the gallery to just one argument and see if it works. ie. Filter(MRQ, Status.Value = "Completed"), then try Filter(MRQ, Status.Value <> "Completed") and see if there is an error.  Try the other conditions individually and let me know what errors you get. 

[mmmcoffeegood]

with Filter(MRQ, Status.Value <> "Completed")

I get no errors and it returns items that aren't marked status of "Completed"

I do get a couple delegation warnings just the yellow triangle!

with Filter(MRQ, Status.Value = "Completed") it is the same as above, no errors just a couple delegation warnings. 

Filter(MRQ, "Assigned_To_1" = User()) With this, I lose data connex and get an invalid argument error. same without quotes around Assigned_Tofield. Also with this, I get a delegation warning.

Filter(MRQ, "Assigned_To_1" = IsEmpty()) Same as the previous expression. I get an invalid number of arguments error and an Invalid argument type.

The issue seems to be with thelater two expressions

[Drrickryp]

User() needs more specificity.  It can be either User().FullName, User().Email or User().Image. Add a label control and set the text property to User().FullName and see if it is an exact match to one in your list. To Filter the list for only items created by the current user, you would use Filter(MRQ,Author.DisplayName=User().FullName)

Proper syntax for finding MRQ without assigned user would be Filter(MRQ, IsBlank("Assigned_To_1" )).  Once we make sure all of the conditions are valid and in the proper syntax, we can start combining them.

[mmmcoffeegood]

I appreciate you helping me mentally [sort] this out.

I feel like progress is being made.

Here's what have now.

What I want to occur:

Filter(MRQ, IsBlank("Assigned_To_1" )) >>>---> finds request within MRQ without an assigned user.
Filter(MRQ, Status.Value<>"Completed") >>>---> finds request within MRQ whose status is not "COMPLETED"

Filter(MRQ, Assigned_To_1.DisplayName=User().FullName) >>>---> finds request within MRQ where Assigned to is equal to the user signed into the app.S

Current Syntax:

Filter(MRQ, Or(Status<>"Completed", IsBlank("Assigned_To_1"),Assigned_To_1.DisplayName=User().FullName))

still getting a delegation warning and an invalid argument type. 

It's like when I string multiple functions together it loses the connection to Sharepoint. (Screen shot attached)

Again, thank you for your brain cells.