How to Exclude Keywords in SOQL?

Question

I need to write a SOQL query in Power Automate that retrieves the IDs of Cases where the comment text does not contain specific keywords. Specifically, I want to exclude cases where the comment includes any of these words: “freigabe”, “Freigabe”, or “DocCheck_approved”.

However, my current attempt does not work as expected. Instead of excluding these cases, the query returns cases that contain the keywords I want to filter out. Here is the query I tried:

AND Id NOT IN (WHERE Comment LIKE '%Freigabe%'
OR Comment LIKE '%freigabe%'
OR Comment LIKE '%DocCheck_approved%'
)

How should I modify my query to properly exclude cases with these keywords in their comments?

Answer

Fixing SOQL Syntax for Excluding Keywords

To correctly filter out cases where the Comment__c field contains specific keywords, you should use the NOT operator with multiple LIKE conditions. Your original query structure was incorrect because NOT IN (WHERE …) is not valid SOQL syntax. Instead, you need to apply the NOT condition directly to the LIKE filters inside parentheses.

The corrected query looks like this:

SELECT Id 
FROM Case 
WHERE NOT (Comment__c LIKE '%Freigabe%' 
OR Comment__c LIKE '%freigabe%' 
OR Comment__c LIKE '%DocCheck_approved%')

Code Snippet Explanation

• SELECT Id FROM Case: Retrieves the Id of cases from the Case object.
• WHERE NOT (...): Ensures that the results exclude cases matching the conditions inside the parentheses.
• Comment__c LIKE '%Freigabe%': Checks if the Comment__c field contains the word “Freigabe” anywhere within the text.
• OR Comment__c LIKE '%freigabe%': Similar to the previous condition but accounts for a lowercase “freigabe”.
• OR Comment__c LIKE '%DocCheck_approved%': Excludes cases where “DocCheck_approved” appears in the comment text.
• The NOT operator ensures that only cases where Comment__c does not match any of these patterns are returned.

Handling Comments in a Related Object

If your comments are stored in a related object (such as CaseComment), filtering directly on the Case object will not work. Instead, you need to check if any related CaseComment contains the keywords and exclude those cases. The following subquery helps with that:

SELECT Id 
FROM Case 
WHERE Id NOT IN (
    SELECT ParentId 
    FROM CaseComment 
    WHERE CommentBody LIKE '%Freigabe%' 
    OR CommentBody LIKE '%freigabe%' 
    OR CommentBody LIKE '%DocCheck_approved%'
)

Code Snippet Explanation

• SELECT Id FROM Case: Retrieves case IDs from the Case object.
• WHERE Id NOT IN (...): Ensures that cases appearing in the subquery results are excluded.
• (SELECT ParentId FROM CaseComment WHERE ...): Retrieves case IDs (ParentId) where related comments contain the excluded keywords.
• CommentBody LIKE '%Freigabe%' OR ...: Filters out comments that include the specified keywords.
• NOT IN: Ensures that cases with matching comments are excluded from the final results.

This approach works best when comments are stored in the CaseComment object and linked to Case via ParentId.

Considerations for Power Automate

If you are using Power Automate’s Dataverse connector, ensure that:

1. The field names are correct (Comment__c vs. CommentBody may differ depending on your schema).
2. You are using the correct SOQL syntax, as Power Automate sometimes uses FetchXML or OData instead of SOQL.
3. If filtering related records, ensure that your setup allows queries across related objects.

Kick Start Your Career with Real-Time Project-Based Salesforce Training

Our Salesforce course is designed to provide an in-depth understanding of the Salesforce platform, equipping you with the essential skills to succeed in the CRM industry. The program covers fundamental modules like Salesforce Admin, Developer, and AI, combining theoretical learning with hands-on experience. Through real-world projects and practical exercises, you’ll develop the expertise to address complex business challenges using Salesforce solutions. Our experienced instructors ensure you gain both technical proficiency and industry knowledge to thrive in the Salesforce ecosystem.

In addition to technical training, our Salesforce training in Bangalore offers personalized mentorship, certification support, and interview preparation to enhance your career prospects. You’ll benefit from comprehensive study materials, hands-on project experience, and dedicated guidance throughout the course. By the end of the program, you’ll be fully prepared for certification exams and real-world applications, possessing the problem-solving skills that employers value. Begin your Salesforce journey today and unlock exciting career opportunities. Sign up for a Free Demo now!