cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Alfie_Smith
Helper I
Helper I

Delegation query - Would this work?

Hi there,

 

I am rather new to PowerApps, and have been tasked with making an application for a sharepoint list that is around 2.3k in size. 

I have been looking into how to make the application, and stumbled upon delegation, which in truth has been confusing me a small tad. 

 

My question is as follows - lets say I had a gallery using datasource 'Table1' (2.3k in size). In this gallery, I used a filter. The filter makes it so that the gallery only shows data where a column equals x.

e.g. Filter( Table1, Example = "x" )

 

The gallery will now only show 500-700 rows out of the 2.3k.  Now, I know that Filter is delegable, and the size of the datasource isn't an issue (I think!). By this point, I have set the data row limit to 1000 in the advanced settings.

 

The question is this. If I were to use non-delegable functions to filter this gallery at its filtered size, would I face issues where all of the data isn't showing? 

 

Please correct me and inform me of where I'm wrong - I'm not sure whether this would be allowed and the question might not make sense due to my lack of understanding of delegation in the first place. 

 

Thank you!

1 ACCEPTED SOLUTION

Accepted Solutions
WarrenBelz
Super User III
Super User III

Hi @Alfie_Smith ,

You are on the right track with your enquiries to manage delegation issues. Also, you can increase the limit to 2000 if required.

If you have not read this document, it is a very good place to start.

But, back to your question. If you can get a filtered list to under 2000 items, then you can Collect it and then all queries, delegable or not, will work. Your example involves a text field, where both the field type and the operator (equals) are delegable.

To collect it, use 

ClearCollect(
   colWhatever,
   Filter(
      Table1, 
      Example = "x" 
   )
)

This will give you a data set - basically a table in the "memory" of the app that you work with including for instance basing galleries on and filter further in any way you need to.

There are many other things you can do including "collecting" only the fields you need from the table.

I am happy to help with any further queries you may have on this.

 

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

4 REPLIES 4
WarrenBelz
Super User III
Super User III

Hi @Alfie_Smith ,

You are on the right track with your enquiries to manage delegation issues. Also, you can increase the limit to 2000 if required.

If you have not read this document, it is a very good place to start.

But, back to your question. If you can get a filtered list to under 2000 items, then you can Collect it and then all queries, delegable or not, will work. Your example involves a text field, where both the field type and the operator (equals) are delegable.

To collect it, use 

ClearCollect(
   colWhatever,
   Filter(
      Table1, 
      Example = "x" 
   )
)

This will give you a data set - basically a table in the "memory" of the app that you work with including for instance basing galleries on and filter further in any way you need to.

There are many other things you can do including "collecting" only the fields you need from the table.

I am happy to help with any further queries you may have on this.

 

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

Thank you for the prompt response!

 

I was reading the document you sent and read that 'ClearCollect' isn't delegable, but you mentioned it as part of the solution.

As you said then, as long as I am able to filter the list down to below 2000 (Is this limit set as it is the max data rows non-delegable?), it doesn't matter?

 

Also, the fact that I can store a memory of the data is a great feature, but what if later on I needed to update the data-sources information? As I would be working with the 'memory' of that data, would I still be able to update the data-source using a collection? 

 

With this memory of the gallery, would I be able to do something along the lines of 'getting' all of the data of a particular column? e.g.

 

Collect(colExample, Distinct(colWhatever, 'Column')

 

I would use this data to fill in a dropdown box or a combobox for further filtering by the user. 

 

And the last of my questions! Would I use the clearCollect code in the gallery screen?

e.g. 

(Screen1)

OnVisible = ClearCollect(
   colWhatever,
   Filter(
      Table1, 
      Example = "x" 
   )
)

 

Once again, thank you!

 

Hi @Alfie_Smith ,

To answer your questions

I was reading the document you sent and read that 'ClearCollect' isn't delegable, but you mentioned it as part of the solution.

As you said then, as long as I am able to filter the list down to below 2000 (Is this limit set as it is the max data rows non-delegable?), it doesn't matter?

Yes - you have that right - ClearCollect (or Collect if you are adding to a Collection) will gather a maximum of 2000 records at a time from a delegable query that returns this number or less. So my example was based on the assumption (as you had noted that)

Filter(
   Table1, 
   Example = "x" 
)

would return less than 2000 records. Once you have done this, you can use any valid query to filter it further.

Also, the fact that I can store a memory of the data is a great feature, but what if later on I needed to update the data-sources information? As I would be working with the 'memory' of that data, would I still be able to update the data-source using a collection? 

I probably did not elaborate enough oh this. I use collections for almost all of my galleries to display to record set the user needs to see. Once you select a record, the Edit and/or view form will then be based on the data source, with the Items property something like

Lookup(
   [Data set],
   ID = [Gallery name].Selected.ID
)

This can then be saved directly to the data, by SubmitForm or Patch.

Once you get used to them, they can be edited, added to and deleted from and can be basically your "sand pit", which works significantly faster than accessing the base data, particularly on mobile devices. You can also have cascading "sub collections" filtered on the previous one. I also use collections for most of my DropDown/ComboBox items.

With this memory of the gallery, would I be able to do something along the lines of 'getting' all of the data of a particular column? e.g.

Collect(colExample, Distinct(colWhatever, 'Column')

Absolutely, in fact you have hit on one of the most important non-delegable filters Distinct

I would use this data to fill in a dropdown box or a combobox for further filtering by the user. 

Answered above - that is exactly what I use them for

And the last of my questions! Would I use the clearCollect code in the gallery screen? e.g. (Screen1)

OnVisible = ClearCollect(
   colWhatever,
   Filter(
      Table1, 
      Example = "x" 
   )
)

If you mean the OnVisible property of the screen is

ClearCollect(
   colWhatever,
   Filter(
      Table1, 
      Example = "x" 
   )
)

then yes. I tend to do all my static data collection at App OnStart and then recollect if necessary to reflect any user changes. As mentioned in my earlier post, the AddColumns and DropColumns features allow collection of only the data set you need.

I am happy to explain further as you absorb all of this. Here is a very good tutorial from Shane Young (@Shanescows ) on the subject.

 

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.

 

 

Amazing stuff.

 

I'm starting to get the idea that collections is the key to working around the issue of delegation if used properly. 

Lookup(
   [Data set],
   ID = [Gallery name].Selected.ID 
)

From looking at this code, I am assuming this requires the data source to have each row have a unique ID?

 

I'll make sure to watch Shawns video - I've been watching quite a few the last couple days.  It's a shame that a lot of important functions are non-delegate. 

 

If any more questions come to mind (which I'm sure they will), I'll mention them here. 

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are Your Ready?

Test your skills now with the Cloud Skill Challenge.

Top Solution Authors
Top Kudoed Authors
Users online (57,113)