function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
SiddharthSiddharth 

Paging with query more

Hi,
 
Urgent is required.
 
We have a situation where in we need to get 10000 contact records but in a batch of 50. We need to bind the datasource to a gridview (page size 50) control with paging. When we click on page 2 then it should get us only next 50 records and not the whole datatable from salesforce.
 
Thanks in advance,
Sidd.
 
DevAngelDevAngel
The minimum batch size will be 200, but this should work for you.  The key is to use the order by clause in your soql statement.  You will want to keep a variable that contains the current page and the record number range for that page.  So, page one has records 1 to 50, page 2 has 51 to 100 and so on.  You will also need to keep a variable that has the current batch.  1 for the first query, 2 for the second and so on.  When your user pages to the records 101 to 150, you can fetch the next batch of 200 and append them to the dataset.

The reason why order by is important is that if you don't use it you will get the records in an essentially random order.  If your use wants to resort your table, you will have to clear the data and issue a new query using the new order by column.
Scooter429Scooter429

Dave – Thanks as always for your thoughtful response.  We see how to get the first 200 records by applying “limit” and “order.”  From your response it was not clear how we would get records 201 to 400 even if we have current page and record number range.  Could you give a line of SOQL that demonstrates how to do this?

SQL Server 2005 supports the notion of a rownumber function.  Please see http://aspnet.4guysfromrolla.com/demos/printPage.aspx?path=/articles/031506-1.aspx to see what I am talking about.  So the question here is does the SFDC API support this type of functionality ? 

If the answer to the question about rownumber is that it’s not supported then do you have a recommendation about how we could provide the ability to efficiently page through large tables?  The answer may well be that we have to restrict the user to a manageable result set.

Many thanks,
Scott
DevAngelDevAngel
So, query more gets you the next batch of 200.  This supports the "next" page functionality.  There is an unsupported hack to jump around your batch.  If you look at a queryLocator you can see that it uses a kind of index or row number as the starting point for the next batch.  Be warned, it's not supported and may change.  You would also need to do a bit more management to be sure you have previous batches.  For instance, the user is looking at the first page and click the link to the 3rd page.  If the user clicks previous, you will need to know if you have that batch and if not fetch it and insert it into the proper location.


Cheers
manas.demanas.de

Hi Dev,

Thanks for the reply.

With SQL Server if we write query like this then we can get 10 records at a time. In this query we are getting records from 41-50. We can change this 40 to any number like 10, 20, 30, etc. Can we write a query like this with API.

SELECT * FROM           (SELECT TOP 10 *  FROM
                        (SELECT TOP 40 *
                        FROM TempData
                        ORDER BY CustomerId) A
                        ORDER BY CustomerId desc ) B
                        ORDER BY CustomerId

Assuming page size is 10. 40 is multiplication of page size and page number.

It would be a great help if you could provide an example.

Thanks again in advance.

 
ChampsChamps
Hello,
I have the similar require to fetch the next batch or previous batch from sf.com.
 
"

SELECT * FROM           (SELECT TOP 10 *  FROM
                        (SELECT TOP 40 *
                        FROM TempData
                        ORDER BY CustomerId) A
                        ORDER BY CustomerId desc ) B
                        ORDER BY CustomerId

"
I followed above statement converts to SOQL to execute via Apex Explorer 8.0.
but the statement in SOQL seems not support.
could you please help,
Thank you very much.
SuperfellSuperfell
SOQL has a LIMIT cluase that works the same as TOP does. e.g.

select id, name from account order by name desc limit 25
ChampsChamps
Hi Admin,
I have the contacts and leads data store problem, now I use viewstate to store in the client page, all sorting and paging via viewstate, but when the user has large data from sf.com. that is a problem.
this topic is the only way to solve?
if I don't want to use the server/Http cache, when I click the 2nd page, I expect to access sf.com the fetch the rows between 11-20. to display in the gridview, that means when sorting or paging events fired it needs fetch data from sf.com and responsed the expectation.
daksadaksa
I had posted some code to do paging in Salesforce using the APEX API. See if this will help you. it provides for a Previous and Next links. The example uses the "contacts" object - but you can use any custom object also.
 
<a href="http://daksatech.blogspot.com/2007/10/record-paging-in-salesforcecom.html">Daksa Technologies: Record Paging In SalesForce.com</a>