cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
debarbanson
Helper II
Helper II

Possible limitations gallery filter?

Hi all,

 

I will soon start with building a new app for which I need to present the following:

 

1: a gallery where based on a text input search field, user can search for a file number and the gallery will return the equivalent record. However the total unique file numbers is around 3.000, stored in a Sharepoint Online list.

Following code in the items property of the gallery:

Search(Distinct('Agreements','File number'),TextInput1.Text,"Result")

I use distinct given it could be in the list the file number will be present multiple times, due to step 2:

 

2: based on the file number I will select in the gallery, based on the filtered result of step 1, I will assign a variable to store the selected file number once you click on the > icon in the gallery for details.

this will take you to another screen with a new gallery with records related to the file number from step 1 (as stored in the variable). On average there will be around 10 records related to a file number. The total number of records, again in a Sharepoint Online list, will be around 30.000.

Following code in the items property of the gallery:

 

Filter('Agreements','File number' = varSelectedFilenumber)

 

Question I have is the following: Is Powerapps able to filter the file numbers, although there are around 3.000 unique ones in the Sharepoint list. And then if I select a specific number, will it show the related records, where Powerapps need to pull them up from a list of around 30.000 records.

 

So far, both galleries work and I don't get any delegation warnings. However currently I only have like 10 records in the Sharepoint list for testing purposes. The end state solution will be one list with around 30.000 records, containing a file number id which can be present more than once in the file number column, and 30.000 unique id's in the file number details column.

 

I was wondering, is there anything I need to take into account when it comes to the gallery being able to search through and present all the data and not just e.g. base the search result on only the first 2.000 records. Looking forward to any insights one may have. Many thanks in advance.

1 ACCEPTED SOLUTION

Accepted Solutions
WarrenBelz
Super User
Super User

Hi @debarbanson ,

Firstly Distinct() is not Delegable (and I have noticed lately it does not give any warnings), so it will only action a dataset of up to 2,000 records. Also Search() is not Delegable, so the same applies. With 30,000 records, there are not a lot of workarounds I could send you exploring. As far as insights (and this will work up to 2,000) I keep self-populating reference lists of unique values in large lists.

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

View solution in original post

8 REPLIES 8
WarrenBelz
Super User
Super User

Hi @debarbanson ,

Firstly Distinct() is not Delegable (and I have noticed lately it does not give any warnings), so it will only action a dataset of up to 2,000 records. Also Search() is not Delegable, so the same applies. With 30,000 records, there are not a lot of workarounds I could send you exploring. As far as insights (and this will work up to 2,000) I keep self-populating reference lists of unique values in large lists.

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

View solution in original post

debarbanson
Helper II
Helper II

Hi @WarrenBelz many thanks for sharing your insights. Have to figure out a way then to cluster the data in such a way that it will generate < 2000 records, a challenge in itself and perhaps in the end the conclusion will be that it cannot be done with Power Apps. Would be a shame.

debarbanson
Helper II
Helper II

@WarrenBelz could a potential solution be to load static excel data to powerapps? The unique file numbers (around 3.000) are static as well as the 30.000 unique technical file numbers, they will not change. And then after selecting a file number and technical file number, pull the corresponding data from the Sharepoint Online list?

Hi @debarbanson ,

I have never tried to load that much Excel data (in fact I simply use SharePoint reference lists as mentioned and I can tell you they work well).

debarbanson
Helper II
Helper II

Hi @WarrenBelz , thanks for quick reply. Can you explain a bit more what you mean with Sharepoint reference lists?
Do you have an example how that works?

Hi @debarbanson ,

On my larger lists I prepare "reference" lists of any distinct values I want to use in drop-downs (I already have a lot of these anyway in lists I want to look other fields up on). If the user wants to add a new value, I have a button for this which makes a text box instead of the drop-down visible - the card Update is set conditionally to this on a Variable. On the OnSuccess of the Form, if the Variable has been invoked, I check the reference list for the value and if it is not present, add it to the list.

This is one of the small Delegation workarounds you may need if you want to use SharePoint on large lists and find Distinct values.

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

WarrenBelz
Super User
Super User

Hi @debarbanson ,

Just checking if you got the result you were looking for on this thread. Happy to help further if not.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

debarbanson
Helper II
Helper II

Hi @WarrenBelz  I'm testing my solution at the moment, seems like I am able to filter the full list based on delegable filters. Thanks for putting me on the right track when it comes to delegation.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (2,593)