cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Advocate I
Advocate I

PowerApps search and filters

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)

 

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Super User
Super User

Re: PowerApps search and filters

double pipe "||" is a logical or.

View solution in original post

Highlighted
Super User
Super User

Re: PowerApps search and filters

Hi @mmmcoffeegood,

 

I am pleased that you got it to finally work.  If you are satisfied with the result, please mark the thread as solved.

View solution in original post

15 REPLIES 15
Highlighted
Super User
Super User

Re: PowerApps search and filters

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.  

Highlighted
Advocate I
Advocate I

Re: PowerApps search and filters

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

 

Highlighted
Super User
Super User

Re: PowerApps search and filters

Hi @mmmcoffeegood,

 

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

Highlighted
Advocate I
Advocate I

Re: PowerApps search and filters

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.

 

Highlighted
Advocate I
Advocate I

Re: PowerApps search and filters

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

Highlighted
Super User
Super User

Re: PowerApps search and filters

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. 

Highlighted
Advocate I
Advocate I

Re: PowerApps search and filters

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

 

 

 

 

Highlighted
Super User
Super User

Re: PowerApps search and filters

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.

 

Highlighted
Advocate I
Advocate I

Re: PowerApps search and filters

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.

 

 

Helpful resources

Announcements
secondImage

Demo-Extravaganza 2020

Check out these cool Power Apps & vote on your favorite!

secondImage

Robotic Process Automation

Let's talk about the solution provided by Microsoft for Robotic Process Automation (RPA)

secondImage

Community Highlights

Check out whats happening in Power Apps

secondImage

Community User Group Member Badges

FIll out a quick form to claim your community user group member badge today!

Top Solution Authors
Top Kudoed Authors
Users online (8,119)