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

Questions about pull records from sharepoint list 2000 records limitation workaround ?

good morning,guys

 

I have checked couple of threads about 2000 records limitation workaround with sharepoint list, I have below questions:

 

1. if I sort one column of SP list with sort.desending to create a collection onStart property , then I can pull top 500 records to my power app ?  as the list is likely to grow further .

         So my list stored records count exceed 2000 , I can avoid record limitation if I use this way ?

 

2. if I used SQL server as data source, if my power app pull records not to be limited ?

 

3. the records limitation only for mobile phone,tablet device or both ?

 

thanks

 

1 ACCEPTED SOLUTION

Accepted Solutions
v-yutliu-msft
Community Support
Community Support

Hi @wht_629 ,

Have you solved your problem?

If not, let me make a conclusion:

1)the limit of 2000 records is called delegation limit. This is powerapps limit, not device limit.

So no matter you run app in what device, you always will meet this limit.

2)the limit is for all data sources, not only sharepoint or sql server.

3)To avoid the affect of delegation limit, you could use delegate functions: sharepoint, sql server, cds.

But different data sources have different delegate functions.

Please refer:

delegate functions for sharepoint:

Item Number Text Boolean DateTime Complex [1]
Filter Yes Yes Yes No [4] Yes
Sort Yes Yes Yes Yes No
SortByColumns Yes Yes Yes Yes No
Lookup Yes Yes Yes No Yes
= Yes Yes Yes No [4] Yes
<, <=,<>, >, >= Yes [2] No No No Yes
StartsWith - Yes - - Yes
IsBlank - No [3] - - No

delegate functions for sql server:

Operation/Function Number Text Boolean DateTime Guid
Filter Yes Yes Yes No [1] Yes
Sort Yes Yes Yes Yes -
SortByColumns Yes Yes Yes Yes -
Search No Yes No No -
Lookup Yes Yes Yes Yes Yes
=, <> Yes Yes Yes No Yes
<, <=, >, >= Yes No No No -
in (substring) - Yes [2] - - -
+ Yes - - No -
- Yes - - No -
* Yes - - No -
/ Yes - - No -
StartsWith - Yes [3] - - -
EndsWith - Yes [4] - - -
IsBlank No [5] No [5] No [5] No [5] No [5]
Len (length) - Yes [6] - - -
Sum Yes - - - -
Average Yes - - - -
Min Yes - - No -
Max Yes - - No -

4)Yes, if you use filter and sort function in the app's OnStart to pull data to collection, you could avoid the effect of delegation.

Please do not forget to only use delegate functions when you pull data to collection.

 

Best regards,

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
iAm_ManCat
Super User
Super User

Hi @wht_629 

 

The 2000 record limitation actually refers to your App and formula interacting with the data source, so its about what is handled locally on the client-side vs on the data source side - your App can see more than 2000 records if the formulae you use are delegable.

 

If you used SQL as a data source, they are many delegable functions, but delegation can still be a concern if you use a formula operator that is not delegable - see here

 

The records limitation is for the platform itself, not the device type its used on - though if you use delegable formulae then this won't be an issue.

 

Cheers,

Sancho

 
@iAm_ManCat


Please 'Mark as Solution' if someone's post answered your question and always 'Thumbs Up' the posts you like or that helped you!


Thanks!
You and everyone else in the community make it the awesome and welcoming place it is, keep your questions coming and make sure to 'like' anything that makes you 'Appy
Sancho Harker, MVP


v-yutliu-msft
Community Support
Community Support

Hi @wht_629 ,

Have you solved your problem?

If not, let me make a conclusion:

1)the limit of 2000 records is called delegation limit. This is powerapps limit, not device limit.

So no matter you run app in what device, you always will meet this limit.

2)the limit is for all data sources, not only sharepoint or sql server.

3)To avoid the affect of delegation limit, you could use delegate functions: sharepoint, sql server, cds.

But different data sources have different delegate functions.

Please refer:

delegate functions for sharepoint:

Item Number Text Boolean DateTime Complex [1]
Filter Yes Yes Yes No [4] Yes
Sort Yes Yes Yes Yes No
SortByColumns Yes Yes Yes Yes No
Lookup Yes Yes Yes No Yes
= Yes Yes Yes No [4] Yes
<, <=,<>, >, >= Yes [2] No No No Yes
StartsWith - Yes - - Yes
IsBlank - No [3] - - No

delegate functions for sql server:

Operation/Function Number Text Boolean DateTime Guid
Filter Yes Yes Yes No [1] Yes
Sort Yes Yes Yes Yes -
SortByColumns Yes Yes Yes Yes -
Search No Yes No No -
Lookup Yes Yes Yes Yes Yes
=, <> Yes Yes Yes No Yes
<, <=, >, >= Yes No No No -
in (substring) - Yes [2] - - -
+ Yes - - No -
- Yes - - No -
* Yes - - No -
/ Yes - - No -
StartsWith - Yes [3] - - -
EndsWith - Yes [4] - - -
IsBlank No [5] No [5] No [5] No [5] No [5]
Len (length) - Yes [6] - - -
Sum Yes - - - -
Average Yes - - - -
Min Yes - - No -
Max Yes - - No -

4)Yes, if you use filter and sort function in the app's OnStart to pull data to collection, you could avoid the effect of delegation.

Please do not forget to only use delegate functions when you pull data to collection.

 

Best regards,

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

PA.JPG

New Release Planning Portal (Preview)

Check out our new release planning portal, an interactive way to plan and prepare for upcoming features in Power Platform.

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