You need to sign in to do that
Don't have an account?
Brendan Dunham
Creating a Dynamic Search bar for a table exceeding 1000 records.
I have had multiple issues creating a dynamic search bar(searching more than one field) that will allow me to search a table over 1000 records. I'm beginning to wonder if it is even possible or no matter what is Salesforce going to give me that error. This is what I have right now in my controller:
public with sharing class modemSearchController {
private String soql {get;set;}
public List<Modem__c> modemz {get;set;}
public Integer noOfRecords{get; set;}
public Integer size{get; set;}
public String sortDir {
get { if (sortDir == null) { sortDir = 'asc'; } return sortDir; }
set;
}
// the current field to sort by. defaults to name
public String sortField {
get { if (sortField == null) {sortField = 'Name'; } return sortField; }
set;
}
// format the soql for display on the visualforce page
public String debugSoql {
get { return soql + ' order by ' + sortField + ' ' + sortDir + ' limit 1500'; }
set;
}
// init the controller and display some sample data when the page loads
public modemSearchController() {
soql = 'select Name, ModemIPEXT__c, ModemICCID__c, ModemCarrier__c, ModemActive__c from Modem__c';
runQuery();
}
public PageReference create()
{
return Page.modemCreate;
}
// toggles the sorting of query from asc<-->desc
public void toggleSort() {
// simply toggle the direction
sortDir = sortDir.equals('asc') ? 'desc' : 'asc';
// run the query again
runQuery();
}
// runs the actual query
public void runQuery() {
try {
modemz = Database.query(soql + ' order by ' + sortField + ' ' + sortDir + ' limit 1000');
} catch (Exception e) {
ApexPages.addMessage(new ApexPages.Message(ApexPages.Severity.ERROR, 'I cant seem to find that record!'));
}
}
// runs the search with parameters passed via Javascript
public PageReference runSearch() {
String name = Apexpages.currentPage().getParameters().get('name');
String IP = Apexpages.currentPage().getParameters().get('IP');
String ICCID = Apexpages.currentPage().getParameters().get('ICCID');
String carrier = Apexpages.currentPage().getParameters().get('carrier');
soql = 'select Name, ModemIPEXT__c, ModemICCID__c, ModemCarrier__c, ModemActive__c from Modem__c';
if (!name.equals(''))
soql += ' and Name LIKE \''+String.escapeSingleQuotes(name)+'%';
if (!IP.equals(''))
soql += ' and ModemIPEXT__c LIKE \''+String.escapeSingleQuotes(IP)+'%';
if (!ICCID.equals(''))
soql += ' and ModemICCID__c LIKE \''+String.escapeSingleQuotes(ICCID)+'%';
if (!carrier.equals(''))
soql += ' and ModemCarrier__c includes \"(+carrier+)';
// run the query again
runQuery();
return null;
}
// use apex describe to build the picklist values
public List<String> carriers {
get {
if (carriers == null) {
carriers = new List<String>();
Schema.DescribeFieldResult field = Modem__c.ModemCarrier__c.getDescribe();
for (Schema.PicklistEntry f : field.getPicklistValues())
carriers.add(f.getLabel());
}
return carriers;
}
set;
}
public ApexPages.StandardSetController setCon {
get{
if(setCon == null){
size = 15;
string queryString = 'SELECT Name, ModemManufacturer__c, ModemModel__c, ModemICCID__c, ModemIPEXT__c, ModemCarrier__c, ModemActive__c FROM Modem__c ORDER BY Name';
setCon = new ApexPages.StandardSetController(Database.getQueryLocator(queryString));
setCon.setPageSize(size);
noOfRecords = setCon.getResultSize();
}
return setCon;
}
set;
}
public List<Modem__c> getModem()
{
List<Modem__c> modemList = new List<Modem__c>();
for(Modem__c m : (List<Modem__c>)setCon.getRecords())
modemList.add(m);
return modemList;
}
public PageReference refresh() {
setCon = null;
getModem();
setCon.setPageNumber(1);
return null;
}
public Boolean hasNext
{
get {
return setCon.getHasNext();
}
set;
}
public Boolean hasPrevious
{
get {
return setCon.getHasPrevious();
}
set;
}
public Integer pageNumber
{
get {
return setCon.getPageNumber();
}
set;
}
public void first()
{
setCon.first();
}
public void last()
{
setCon.last();
}
public void previous()
{
setCon.previous();
}
public void next()
{
setCon.next();
}
}
My biggest issue here is 1. It will not allow me to display over 1000 records and I couldnt find a work around. and 2. It will not perform the search within the data, it comes up with an error and displays that it is finding Querying correct at the bottom but it will not actually display the records related to what is being searched.
public with sharing class modemSearchController {
private String soql {get;set;}
public List<Modem__c> modemz {get;set;}
public Integer noOfRecords{get; set;}
public Integer size{get; set;}
public String sortDir {
get { if (sortDir == null) { sortDir = 'asc'; } return sortDir; }
set;
}
// the current field to sort by. defaults to name
public String sortField {
get { if (sortField == null) {sortField = 'Name'; } return sortField; }
set;
}
// format the soql for display on the visualforce page
public String debugSoql {
get { return soql + ' order by ' + sortField + ' ' + sortDir + ' limit 1500'; }
set;
}
// init the controller and display some sample data when the page loads
public modemSearchController() {
soql = 'select Name, ModemIPEXT__c, ModemICCID__c, ModemCarrier__c, ModemActive__c from Modem__c';
runQuery();
}
public PageReference create()
{
return Page.modemCreate;
}
// toggles the sorting of query from asc<-->desc
public void toggleSort() {
// simply toggle the direction
sortDir = sortDir.equals('asc') ? 'desc' : 'asc';
// run the query again
runQuery();
}
// runs the actual query
public void runQuery() {
try {
modemz = Database.query(soql + ' order by ' + sortField + ' ' + sortDir + ' limit 1000');
} catch (Exception e) {
ApexPages.addMessage(new ApexPages.Message(ApexPages.Severity.ERROR, 'I cant seem to find that record!'));
}
}
// runs the search with parameters passed via Javascript
public PageReference runSearch() {
String name = Apexpages.currentPage().getParameters().get('name');
String IP = Apexpages.currentPage().getParameters().get('IP');
String ICCID = Apexpages.currentPage().getParameters().get('ICCID');
String carrier = Apexpages.currentPage().getParameters().get('carrier');
soql = 'select Name, ModemIPEXT__c, ModemICCID__c, ModemCarrier__c, ModemActive__c from Modem__c';
if (!name.equals(''))
soql += ' and Name LIKE \''+String.escapeSingleQuotes(name)+'%';
if (!IP.equals(''))
soql += ' and ModemIPEXT__c LIKE \''+String.escapeSingleQuotes(IP)+'%';
if (!ICCID.equals(''))
soql += ' and ModemICCID__c LIKE \''+String.escapeSingleQuotes(ICCID)+'%';
if (!carrier.equals(''))
soql += ' and ModemCarrier__c includes \"(+carrier+)';
// run the query again
runQuery();
return null;
}
// use apex describe to build the picklist values
public List<String> carriers {
get {
if (carriers == null) {
carriers = new List<String>();
Schema.DescribeFieldResult field = Modem__c.ModemCarrier__c.getDescribe();
for (Schema.PicklistEntry f : field.getPicklistValues())
carriers.add(f.getLabel());
}
return carriers;
}
set;
}
public ApexPages.StandardSetController setCon {
get{
if(setCon == null){
size = 15;
string queryString = 'SELECT Name, ModemManufacturer__c, ModemModel__c, ModemICCID__c, ModemIPEXT__c, ModemCarrier__c, ModemActive__c FROM Modem__c ORDER BY Name';
setCon = new ApexPages.StandardSetController(Database.getQueryLocator(queryString));
setCon.setPageSize(size);
noOfRecords = setCon.getResultSize();
}
return setCon;
}
set;
}
public List<Modem__c> getModem()
{
List<Modem__c> modemList = new List<Modem__c>();
for(Modem__c m : (List<Modem__c>)setCon.getRecords())
modemList.add(m);
return modemList;
}
public PageReference refresh() {
setCon = null;
getModem();
setCon.setPageNumber(1);
return null;
}
public Boolean hasNext
{
get {
return setCon.getHasNext();
}
set;
}
public Boolean hasPrevious
{
get {
return setCon.getHasPrevious();
}
set;
}
public Integer pageNumber
{
get {
return setCon.getPageNumber();
}
set;
}
public void first()
{
setCon.first();
}
public void last()
{
setCon.last();
}
public void previous()
{
setCon.previous();
}
public void next()
{
setCon.next();
}
}
My biggest issue here is 1. It will not allow me to display over 1000 records and I couldnt find a work around. and 2. It will not perform the search within the data, it comes up with an error and displays that it is finding Querying correct at the bottom but it will not actually display the records related to what is being searched.
Second. It seems that your query is correct and it will show data accordingly with given inputs. I'll suggest you to use debug log to view what's final query is build with the data input provided by you and if possible then paste exact error/exception with debug log of final query on which you are receiving that error so that we can help you.