I have a scenario where I need to query all the records which do not have field value contains Customer , but need to include Customer System and other records.

  • Name | API Source| Email

  • Record 1 | Customer System|abc@example.com

  • Record 2 | Customer|abc@example.com

  • Record 3 | From Web|abc@example.com

  • Record 4 | System A|xyz@example.com

In the above example I need to exclude the record 2 and 4 as they do not match my criteria, but need to query all other records. I have tried something like this:

Set<String> excludeSource = new Set<String>{'%Customer%'};
Set<String> inlcudeSource = new Set<String>{'Customer System'};

List<lead> lst Leads = [
    SELECT Id, Name, Status 
    FROM lead 
    WHERE  email = 'abc@example.com' AND (
        (NOT APISource__c LIKE : excludeAPISource) 
        OR APISource__c IN :includeAPISource

but it does not work as exclude condition excludes all the records.is the a way to handle this scenario in single SOQL query?

  • does the email = email@example.com as only those records would be included. – Eric Jul 31 '17 at 21:22
  • Eric. It was a typo and updated the question. Thanks :). yes, it should be abc@example.com – goodForce Aug 1 '17 at 14:10
  • Added answer. Once your typos were fixed it appears to return the proper results. Maybe your data is not as you expect. – Eric Aug 1 '17 at 18:07

Executed the following in the dev console.

The following works just fine for me. It returns records A and C (1 and 3)

Set<String> excludeAPISource = new Set<String>{'%Customer%'};
Set<String> includeAPISource = new Set<String>{'Customer System'};

List<lead> lstLeads = [
    SELECT Id, Name, Status, email, APISource__c 
    FROM lead 
    WHERE  email = 'abc@example.com' AND (
        (NOT APISource__c LIKE : excludeAPISource) 
        OR APISource__c IN :includeAPISource

I set up 4 leads in my dev org with the criteria you outlined and the output was as expected:

14:06:38:028 USER_DEBUG [12]|DEBUG|(Lead:{Id=00Q3600000eRDUdEAO, Name=Record C, Status=New - Not Contacted, Email=abc@example.com, APISource__c=From Web}, Lead:{Id=00Q3600000eRDTzEAO, Name=Record A, Status=New - Not Contacted, Email=abc@example.com, APISource__c=Customer System})

|improve this answer|||||
  • My bad. Looks like I need fix my typos and i re-arranged the brackets as I had few more conditions. now it works. Thanks a lot. – goodForce Aug 1 '17 at 21:05

basically it should work. have you tried to move the NOT in front of the bracket like

    OR ...

not operators have often a very high priority.

|improve this answer|||||
  • yes.I have tried, but it did not help. – goodForce Jul 31 '17 at 20:25

Just filter on the literal value in this case:

WHERE Email = '...' AND APISource__c = 'Customer System'
|improve this answer|||||
  • It would help if need to filter only records which have APISource as Cusomer System, But I query other records as well which do not have Customer other than Cusomer System. – goodForce Jul 31 '17 at 19:54
  • 2
    It would help if you edit your question to clarify expected inputs/outputs. – Adrian Larson Jul 31 '17 at 19:55
  • I have updated with sample. Please let me know if it helps to understand my question. – goodForce Jul 31 '17 at 19:58
Name     | API Source     | Email
Record 1 | Customer System|abc@example.com
Record 2 | Customer       |abc@example.com
Record 3 | From Web       |abc@example.com
Record 4 | System A       |xyz@example.com

So based on your question you want to exclude where API source is Customer and System A (and I assume anything called System*), but want to include where API source is Customer System or Web (and in fact everything else).

Your query should look like this:

SELECT Id, Name, Status 
FROM Lead 
    email = 'abc@example.com' 
    AND NOT APISource__c = 'Customer' 
    AND NOT APISource__c LIKE 'System%'
|improve this answer|||||
  • I need to exclude all the records which contains 'Customer' as part of APISource except 'Customer System'. Given example have one record with 'Customer' as API source, but there might other records with different values. for example: Customer Input and etc.. – goodForce Aug 1 '17 at 15:19
  • You've got a badly worded question then. You said rows 2 and 4 should be returned, but then marked the answer that returns 1 and 3 as correct. Please try to be clear with your questions to prevent others from wasting time in giving you the wrong answer – sunnymatharuuk Aug 1 '17 at 22:58
  • I mentioned it as exclude and return all other records. so the answer is correct. – goodForce Aug 2 '17 at 19:35

you can try Like operator of SOQL.

LIKE '%Customer System%'
|improve this answer|||||

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.