Possible limitations gallery filter?

(0) Share

Report

Posted on by debarbanson

187

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.

Categories:

Building Power Apps

All responses (8)

Answers (1)

Sort by

Verified answer

WarrenBelz 156,732 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

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.
debarbanson 187 on at

Like
a
(0)

Report
Copy link

Link copied!

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 187 on at

Like
a
(0)

Report
Copy link

Link copied!

@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?
WarrenBelz 156,732 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

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 187 on at

Like
a
(0)

Report
Copy link

Link copied!

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?
WarrenBelz 156,732 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

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 156,732 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

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 187 on at

Like
a
(0)

Report
Copy link

Link copied!

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.