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

[wht_629]

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

[iAm_ManCat]

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

[v-yutliu-msft · Accepted solution]

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,