Collection not collecting all rows

[ruth]

I'm creating a very basic collection from a contacts sharepoint list. the list contains approximately 10k records. For some reason, not all the rows are being "collect". The records that are not being included are random. I know there are issues when working with a table greater than 2000 rows, but my understanding is that collections do not have the same limations.  

My code is in fact basic - ClearCollect(ccContact, tblContact)

Any ideas?

ruth

[ruth]

@CNT I'm using Sharepoint. I do have the internal ID that sharepoint creates as the unique column.  For this dataset, it is actually 5856 rows.  

[CNT]

@ruth Please make sure that this setting is correct,

Could you try this,

Add a button and place this code in the OnSelect property ,

ClearCollect(cEventLog, Filter(tblEventLog,ID >3856 && ID<=5856))

Place this code in the Text property of the Button,

"Count: " & CountRows(cEventLog) & " LastID: " & Last(cEventLog).ID

What do you see when you click the Button?

[ruth]

Settings confirmed:

Result 0 and blank

I event created a new app, modified my table to include a column w my own id, populated 200 rows w my own sequential id, changed the settings in the new app to 100 and only 100 rows is retrieved.

I am using Sharepoint Online. 

[CNT]

@ruth what you say is right. If you set the limit to 100 you will get only 100. If its 2000, you will get 2000. So you have to collect them in batches of 2k.

[ruth]

@CNT , right but that's what I was trying to do. I was trying to collect 200 rows - populated 200 rows w the unique id and then tried to load in batches using the same samples in this chain. And it's only retrieving 100.  So in all situations, I cannot access beyond the the data row limit.  It's consistent.

[CNT]

@ruth I understand your struggle.

Each batch should fetch 100 records. Then we put them together to get 200. Isn't that happening? 

[ruth]

No, that's not happening. It's consistent. I can't imagine this is a limitation of Sharepoint Online. Unless there is something I'm completely missing - which I hope to be the case.

[CNT]

@ruth the second image with the formula is not clear. Would you mind copying the formula into the chat box please. 

[ruth]

@CNT , actually there was an error in that code. Once I corrected it, that test scenario worked!!! So, it seems as if I cannot use the internal ID in Sharepoint as I would get a delegation warning and it won't work.  However, if I do use my own internal id defined as a number and use the same logic/approach, I won't get a delegation warning and it will work.

[CNT · Accepted solution]

@ruth Yea, I tested it and can confirm that when Collecting from a SP List based on the Built in ID it returns only the maximum number of records set in the Data Row Limit, even when collected in batches. But when we use another column (our own ID) to collect the records, we can go past the Data Row Limit and get all the records in batches.

To me this looks like a BUG. If possible, try and raise a ticket with Microsoft.