+ Start a Discussion
DoctorDoctor 

Custom Search to ignore null values

I am developing a custom search page based on the Contact object.
At the moment, the search is recognizing empty search fields as having a blank value, so if the search field is left blank the results only show records that have a blank value in the field.
I need it to work so that, essentially if the search field is left blank, the search ignores it and shows results for all values.
I hope that makes sense. Any help would be greatly appreciated.
Controller Code: 

public with sharing class contactSearchExtension { public contactSearchExtension(ApexPages.StandardSetController controller) { } public contactSearchExtension(ApexPages.StandardController controller) { } public String getName() { return null; } public String getState() { return null; } public List<Contact> result = new List<Contact>(); private String Name; private String State; public contactSearchExtension(){ } public List<Contact> getResult() { return result; } public void setName(String Name) { this.Name = Name; } public void setState(String State) { this.State = State; } public void search() { String queryName = '%' + Name + '%'; String queryState = '%' + State + '%'; result = [ SELECT Account.Name, Name, MailingState FROM Contact WHERE Account.Name like :queryName AND MailingState like :queryState]; } }

 

Message Edited by Doctor on 01-12-2010 02:44 PM
Best Answer chosen by Admin (Salesforce Developers) 
mikefmikef

Almost try this.

 

public void search() {
String queryString = 'SELECT Account.Name, Name, MailingState FROM Contact ';
if (Name != null && State == null){
queryString += WHERE Account.Name like %\'' + Name + '\'%';
}else if(Name != null && State != null){
queryString += WHERE Account.Name like %\'' + Name + '\'and MailingState like '\'% + State + '\'%';
}else if(Name == null && State != null){
queryString += WHERE MailingState like %\'' + State + '\'%';
}else{
queryString += 'Limit 10';//this limits the query so they get something back. You can change this to return an error.
}

try{
result = Database.query(queryString);
}catch(QueryException e){
ApexPages.addMessages(e);
}
}

Please keep in mind this code is not tested and I did not try to compile it.

This should put you on the right track. 

 

Message Edited by mikef on 01-12-2010 02:55 PM
Message Edited by mikef on 01-12-2010 02:56 PM

All Answers

bob_buzzardbob_buzzard
I've had a similar requirement in the past (although that was a multi-criteria search) and I went the route of building the SOQL query dynamically.  Thus I would check if there was a value supplied for one of the criteria before including it in the search query.  I seem to recall I went this route as I needed to check date values and the behaviour when they were blank wasn't helpful.
mikefmikef

The Apex feature you need to use is called Dynamic SOQL. Please review this part of the Apex docs.

What you want to do is create the query string variable dynamically based off of what the user fills out or not.

Please post any issues or questions you have when trying to use dynamic soql.

DoctorDoctor

Thanks for the advice.

Am I on the right track with something like this?

It still hasnt given me the result I am after, but am I getting close or completely off target?:

 

public void search() { String queryName; if (Name == null){ queryName = ''; } else { queryName = '%' + Name + '%'; } String queryState; if (State == null){ queryState = ''; } else { queryState = '%' + State + '%'; } result = [ SELECT Account.Name, Name, MailingState FROM Contact WHERE Account.Name like :queryName AND MailingState like :queryState]; }

 

Message Edited by Doctor on 01-12-2010 02:43 PM
mikefmikef

Almost try this.

 

public void search() {
String queryString = 'SELECT Account.Name, Name, MailingState FROM Contact ';
if (Name != null && State == null){
queryString += WHERE Account.Name like %\'' + Name + '\'%';
}else if(Name != null && State != null){
queryString += WHERE Account.Name like %\'' + Name + '\'and MailingState like '\'% + State + '\'%';
}else if(Name == null && State != null){
queryString += WHERE MailingState like %\'' + State + '\'%';
}else{
queryString += 'Limit 10';//this limits the query so they get something back. You can change this to return an error.
}

try{
result = Database.query(queryString);
}catch(QueryException e){
ApexPages.addMessages(e);
}
}

Please keep in mind this code is not tested and I did not try to compile it.

This should put you on the right track. 

 

Message Edited by mikef on 01-12-2010 02:55 PM
Message Edited by mikef on 01-12-2010 02:56 PM
This was selected as the best answer
DoctorDoctor

I'll have a a play around with that and let you know how I go.

 

Thanks!

DoctorDoctor

Thanks so much. I am fairly new to developing, so this was a great help.

I got it to work perfectly.

In the end I took your concept and did it the long way (it just made more sense to me as a newbie).

 

Do you see any issues with running it this way?

public void search() { String queryName = '%' + Name + '%'; String queryState = '%' + State + '%'; if (Name == '' && State == ''){ result = [SELECT Account.Name, Name, MailingState FROM Contact]; } else if (Name != '' && State == ''){ result = [SELECT Account.Name, Name, MailingState FROM Contact WHERE Account.Name LIKE :queryName]; } else if (Name != '' && State != ''){ result = [SELECT Account.Name, Name, MailingState FROM Contact WHERE Account.Name LIKE :queryName AND MailingState like :queryState]; } else if (Name == '' && State != ''){ result = [SELECT Account.Name, Name, MailingState FROM Contact WHERE MailingState like :queryState]; } }

 

mikefmikef
there is nothing "wrong" with your way, if it works it works.
DoctorDoctor

Thanks again for your help.

You've saved me a lot of time and probably from going prematurely grey/bald/both!