PowerApps search and filters

(0) Share

Report

Posted on by 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)

Categories:

Building Power Apps

I have the same question (0)

All responses (15)

Answers (2)

Drrickryp Super User 2024 Season 1 on at

Like (2)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
mmmcoffeegood 58 on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
Drrickryp Super User 2024 Season 1 on at

Like (0)

Report
Copy link

Link copied!

Hi @mmmcoffeegood,

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

Was this reply helpful? Yes No
mmmcoffeegood 58 on at

Like (0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
mmmcoffeegood 58 on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
Drrickryp Super User 2024 Season 1 on at

Like (1)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
mmmcoffeegood 58 on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
Drrickryp Super User 2024 Season 1 on at

Like (1)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
mmmcoffeegood 58 on at

Like (0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
Drrickryp Super User 2024 Season 1 on at

Like (1)

Report
Copy link

Link copied!

To get rid of the delegation warnings create a collection from MRQ //Collections are not subject to delegation limits.
Make the OnVisible property of your Screen: ClearCollect(colMRQ,MRQ). //This creates the collection whenever the screen becomes visible.
Make the Items property of your gallery: (Filter(Filter(colMRQ,Status<>"Completed"), IsBlank(Assigned_To_1) || Assigned_To_1= User().FullName)) // This filters your new collection by Status first and then filters the result by Assigned_To_1 is blank or Assigned_To_1 is the current user. You may need to put User().FullName in quotes, I'm not sure about it so try it both ways to see which one works.

I tried to do this in the attached example using a Sharepoint list with >2000 rows. The Items property is shown in the box above the gallery. It filters a collection called docs that was ClearCollect(docs,Doctors) by whether the Middle initial (MI) is either blank or = "A." and then by Referring="True".

I tried to simplify the filter and this worked also: Filter(docs,IsBlank(MI) || MI="A.",Referring="True")

Was this reply helpful? Yes No