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

1
  • 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, 2019 at 10:32

1 Answer 1

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
2
  • So if i want to implement the same for account and contact then I have to apply the same logic
    – Hunt
    Dec 11, 2019 at 10:30
  • @Hunt Yes, it that works for you. Dec 11, 2019 at 14:00

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .