0

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.

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 = '[email protected]' 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?

3
  • does the email = [email protected] as only those records would be included.
    – Eric
    Commented Jul 31, 2017 at 21:22
  • Eric. It was a typo and updated the question. Thanks :). yes, it should be [email protected]
    – goodForce
    Commented Aug 1, 2017 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
    Commented Aug 1, 2017 at 18:07

5 Answers 5

2

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 = '[email protected]' AND (
        (NOT APISource__c LIKE : excludeAPISource) 
        OR APISource__c IN :includeAPISource
    )
];
system.debug(lstLeads);

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 protected], APISource__c=From Web}, Lead:{Id=00Q3600000eRDTzEAO, Name=Record A, Status=New - Not Contacted, [email protected], APISource__c=Customer System})

1
  • 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
    Commented Aug 1, 2017 at 21:05
1

Just filter on the literal value in this case:

WHERE Email = '...' AND APISource__c = 'Customer System'
3
  • 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
    Commented Jul 31, 2017 at 19:54
  • 2
    It would help if you edit your question to clarify expected inputs/outputs.
    – Adrian Larson
    Commented Jul 31, 2017 at 19:55
  • I have updated with sample. Please let me know if it helps to understand my question.
    – goodForce
    Commented Jul 31, 2017 at 19:58
1

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

... 
AND (
    NOT(...)
    OR ...
)

not operators have often a very high priority.

1
  • yes.I have tried, but it did not help.
    – goodForce
    Commented Jul 31, 2017 at 20:25
0
Name     | API Source     | Email
-------------------------------------------
Record 1 | Customer System|[email protected]
Record 2 | Customer       |[email protected]
Record 3 | From Web       |[email protected]
Record 4 | System A       |[email protected]

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 
WHERE  
    email = '[email protected]' 
    AND NOT APISource__c = 'Customer' 
    AND NOT APISource__c LIKE 'System%'
3
  • 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
    Commented Aug 1, 2017 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 Commented Aug 1, 2017 at 22:58
  • I mentioned it as exclude and return all other records. so the answer is correct.
    – goodForce
    Commented Aug 2, 2017 at 19:35
-2

you can try Like operator of SOQL.

LIKE '%Customer System%'
0

You must log in to answer this question.

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