canRetrieveMoreRows / loadNextOffsetPage not working as expected

  • 1
  • Problem
  • Updated 4 years ago
  • Solved
  • (Edited)
Hi!

I'm running into an issue with the canRetrieveMoreRows and loadNextOffsetPage() properties of the model object. They seem to give inaccurate/non-consistent results when the data set has 10000+ rows.

canRetrieveMoreRows seems to return false if the next "page" of data would push the total # of records over 10000. For example, if I run the model.soql query property I get back 10000 rows -but canRetrieveMoreRows is false when the model has only 9990 rows.

Additionally, the max # of rows seems to vary depending on the page size. If I use a page size of 2000, I'll max out at 9990 records. However, a page size of 1500 will max at 8501 records. In both situations, there should be 12201 total records.

Thanks for the help!
Kartik

Photo of Kartik Sethuraman

Kartik Sethuraman

  • 798 Points 500 badge 2x thumb

Posted 4 years ago

  • 1
Photo of Ben Hubbard

Ben Hubbard, Employee

  • 12,490 Points 10k badge 2x thumb
Hi Kartik,

It looks like there are 2 issues here and I'll try to explain them both.

1. The first issue is that you're not getting all 12201 records. Under the hood, Skuid is using Salesforce's StandardSetController to handle loading data when you use loadNextOffsetPage().  As you can see here, they only support loading in up to 10,000 rows.

2. The second issue is the inconsistent results.  I've seen this before and it seems to be a quirk with Salesforce's StandardSetController.  When Skuid paginates through the StandardSetController, sometimes the StandardSetController returns rows that it had already returned on the previous page.  Skuid models are smart enough to not let 2 records with the same id into them, but that's what is causing the less than expected numbers for your models.  One way that I've found to work around this issue is to put a good explicit order by on your model.  If your model had no order by originally, order by something like "SystemModStamp, Id".  If your model already had an order by on it, try adding "Id" to the end of your order by statement.  I have found this to fix the issue.

Just out of curiosity, are you using Javascript to call loadNextOffsetPage() after each chunk is returned until you get the full data set?  Unfortunately, because of Salesforce limitations I don't think you could get more than 10,000 rows this way.  One thing I have thought about though is changing a condition on your model such that you could divide your full data set into segments that would be less than 10,000 rows each.  For example you could get the accounts that start with the letter A-H first, then when you finished that query, go ahead and get I-Z.  This way you would work around Salesforce's limitations.
Photo of Kartik Sethuraman

Kartik Sethuraman

  • 798 Points 500 badge 2x thumb
Hi Ben,

Thanks for your response. I am using Javascript to call loadNextOffsetPage() until the full dataset is loaded, for the sole purpose of performing an export of the entire dataset. It's good to know that it's a Salesforce limitation that's limiting the number of records returned to 10000.


The second issue turned out to have two underlying problems. The first was the one you mentioned, and adding Id at the end of the order by seems to resolve that issue.

The other part was that the code I wrote increased the recordsLimit to 2000 (from 250) before loading the next offset page. I expected that that the StandardSetController would return records 251-2250 on the first load, when it actually loaded in rows 2001-4000. I altered my code to run updateData() once before loading in any of the offset pages and that resolved the problem.

Thanks for the help!
Kartik
(Edited)
Photo of Rob Hatch

Rob Hatch, Official Rep

  • 44,006 Points 20k badge 2x thumb
Glad to know you got this worked out...