I am trying to search records based on the 3 input strings.

But the record is not getting If the requested record columns don't have this any one of the values(Aadhar_ID__c, name, First_Name__c).

Please see the below image:

See in the below table, Let's assume, the database having 2 records. The 1st record having data with all the columns, the 2nd record doesn't have data in all columns i.e: don't have Adharcard value.

If I search 1st record with 'paul' using input string lastname (string we can in below code), The record is displaying. But if I search 2nd record with 'john' using input string lastname. The record is not displaying. Because 2nd record doesn't have adharcard value which we are querying in the search.

enter image description here

How to get the record even record columns don't have 1 or 2 values of 3. If we search with existing data.


[SELECT Aadhar_ID__c,name,First_Name__c  
FROM object__c 
WHERE Aadhar_ID__c LIKE :'%'+adharcard+'%' 
   AND name LIKE :'%'+lastname+'%' 
   AND First_Name__c LIKE :'%'+surname+'%'];
  • 1
    I think simply appending this condition to your query should solve your problem: OR (Aadhar_ID__c = '' AND name = '' AND First_Name__c ='') – Rohit Mourya May 25 '17 at 10:25
  • @RohitMourya - You should post this as an answer. – Samir May 25 '17 at 10:27
  • @Samir Initially, I wasn't sure. Posted it – Rohit Mourya May 25 '17 at 10:30
  • @ravindar-admin Please make your question clearer so that community can understand better and you will get solution asap. Have a look at how to ask a good question – Reshma May 25 '17 at 12:17
  • Hi@Reshma & @RohitMourya, I have made changes. Please check, Thank you. – Ravindar Admin May 26 '17 at 3:57

You need to use OR not AND

    [SELECT Aadhar_ID__c,name,First_Name__c  
FROM object__c 
WHERE Aadhar_ID__c LIKE :'%'+adharcard+'%' 
   OR name LIKE :'%'+lastname+'%' 
   OR First_Name__c LIKE :'%'+surname+'%'];

Using AND means all three must match. Using OR means only 1 needs to match

| improve this answer | |
  • @ Eric, Thanks for the reply. But it is displaying all the records. – Ravindar Admin May 25 '17 at 14:05
  • @RavindarAdmin - It is not possible for that query to display "all the record' unless all the records match at least one of the criteria which it your stated goal. Return records where any one of the filters are true – Eric May 25 '17 at 14:06
  • Hi@ Eric, Please check the question, I made changes. Thank you. – Ravindar Admin May 26 '17 at 3:58
  • @RavindarAdmin your updates don't change my answer. If you want records where at least one value matches then this answer is correct imho. Maybe show an example of s record it gets that it should not and the values you are filtering on – Eric May 26 '17 at 4:02

Simply append this condition to your query:OR (Aadhar_ID__c = '' AND name = '' AND First_Name__c ='')

So this is how your final query should be:

[select Name,Aadhar_ID__c,First_Name__c from Account Where (Aadhar_ID__c Like :'%'+adharcard+'%' OR Name Like :'%'+lastname+'%' OR First_Name__c Like :'%'+surname+'%' )];
| improve this answer | |
  • Hi@Rohit, Getting 4 errors: unexpected syntax: 'mismatched input ')' expecting SEMICOLON' , unexpected syntax: 'mismatched input 'AND' expecting RPAREN', , unexpected syntax: 'mismatched input 'AND' expecting SEMICOLON', unexpected syntax: 'mismatched input 'OR' expecting RSQUARE' – Ravindar Admin May 25 '17 at 10:51
  • @RavindarAdmin It should be fine now – Rohit Mourya May 25 '17 at 10:59
  • Thank you for the reply, I think you didn't get my query. If i search with 1 input or more than 1 input, I am getting record. That's fine. But it is looking for the record which must having 3 field values. If not, it is not fetching the record. – Ravindar Admin May 25 '17 at 11:33
  • @RavindarAdmin - Please update your question with an example. – Samir May 25 '17 at 11:58
  • @Samir, I shared sf record with image, also when the record is displaying and when not displaying. – Ravindar Admin May 26 '17 at 4:31

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.