1

Currently, we are trying to populate all users of salesforce in a Visualforce Page with page per limit of 1000 using Database.getQueryLocator (shown below)

Database.getQueryLocator('select Id,Name,UserType,UserRole.Name,Profile.Name,Username from User Where IsActive = true order by Name LIMIT 1000')

As the capacity to fetch the getQueryLocator is of 10,000 I can't event directly fire a query without using limit clause (shown below)

   String query= 'select Id,Name,UserType,UserRole.Name,Profile.Name,Username from User Where IsActive = true order by Name' ;
   StandardSetController  con = new ApexPages.StandardSetController(Database.getQueryLocator(query)); 

Now, the problem is currently in system there are around 62 thousand users active and in order to provide the next , prev , hasNext , hasPrev feature I need to perform the displacement of 1000 records every time user presses next or prev button on visualforce page

  1. Fetch first thousand records

select Id,Name,UserType,UserRole.Name,Profile.Name,Username from User Where IsActive = true order by Name LIMIT 1000 OFFSET 0

  1. Fetch next thousand records

select Id,Name,UserType,UserRole.Name,Profile.Name,Username from User Where IsActive = true order by Name LIMIT 1000 OFFSET 1000

  1. Fetch next thousand records

select Id,Name,UserType,UserRole.Name,Profile.Name,Username from User Where IsActive = true order by Name LIMIT 1000 OFFSET 2000

The last query will fail and throws following error. So , can any one help how would I float per page 1000 records and allow user to go till 62,000 by pressing next

enter image description here

  • As per my knowledge, best approach to show more number of records in VF Page is to implement "Iterator" . even then you will be able to show a maximum of 9999 records. So, you should reconsider your approach, by combining "Search", "Auto Complete" and "Pagination" Functionalities.. (Show 1k - 2k records with pagination and allow user to search using search bar, it's not a good experience for user to go through all the pages to check records) . Iterator eg., salesforcescool.blogspot.com/2017/03/… – Veera G Dec 11 '19 at 10:32
0

You can store the Name of the last record of your query result, and pass the name into your SOQL query when the Prev/Next button is clicked.

Also update the map values along when user clicks on the Next button.

Map<Integer, String> nameByPageNo = new Map<Integer, String>{
    1 => '<1000th Name>',
    2 => '<2000th Name>'
}

Next button SOQL would be:

SELECT Name 
FROM User 
WHERE Name > :nameByPageNo.get(pageNo)
ORDER BY Name LIMIT 1000

Prev button SOQL would be:

SELECT Name 
FROM User 
WHERE Name > :nameByPageNo.get(pageNo-1)
ORDER BY Name LIMIT 1000
  • So if i want to implement the same for account and contact then I have to apply the same logic – Hunt Dec 11 '19 at 10:30
  • @Hunt Yes, it that works for you. – Hengky Ilawan Dec 11 '19 at 14:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.