Have you ever tried to run a dynamic SOQL query only to be met with the dreaded System.QueryException: field 'Description__c' can not be filtered in query call? If you are building a custom search page or a reporting tool, encountering this limitation can be a major roadblock. In Salesforce, Long Text Area and Rich Text Area fields are stored differently than standard text fields, which prevents them from being used directly in a WHERE clause.

In this guide, you will learn why this limitation exists and explore three proven workarounds to effectively filter your records based on Long Text Area content.

Understanding the SOQL Limitation

Salesforce restricts filtering on Long Text Area fields (fields with a capacity of up to 131,072 characters) because searching through such massive amounts of data is computationally expensive. Standard SOQL queries rely on indexes to maintain high performance. Since Long Text Area fields cannot be indexed, allowing them in a WHERE clause would lead to full table scans, significantly slowing down your org.

If you attempt a query like the one below, Salesforce will throw an exception:

// This will throw a System.QueryException
String query = 'SELECT Name, Status__c FROM Project__c WHERE Description__c LIKE \'%keyword%\'';
List<Project__c> projects = Database.query(query);

To solve this, we need to move the filtering logic out of the database layer and into the application layer, or prepare the data beforehand.

Method 1: Filtering Records in Memory with Apex

The most straightforward approach is to query the records based on other filterable criteria (like Status, Date, or Owner) and then use Apex to inspect the Long Text Area field.

By iterating through the results and using the String.contains() method, you can manually filter the list. This is ideal when the initial result set is relatively small (under a few thousand records).

String searchKeyword = 'Urgent';
List<Project__c> filteredProjects = new List<Project__c>();

// Query based on other indexed fields first
for(Project__c proj : [SELECT Name, Status__c, Description__c 
                       FROM Project__c 
                       WHERE Status__c = 'Active']) {

    // Use Apex String methods to filter the Long Text Area
    if(proj.Description__c != null && proj.Description__c.contains(searchKeyword)) {
        filteredProjects.add(proj);
    }
}

Pros: Simple to implement and requires no schema changes. Cons: You must be mindful of Heap Limits and CPU time if the query returns a large volume of data.

Method 2: The Formula Field Workaround

While you cannot filter a Long Text Area, you can filter a Formula Field. By creating a custom formula field that captures the first few hundred characters of your Long Text Area, you can use that formula in your WHERE clause.

For example, create a formula field named Description_Short__c with the following formula:

LEFT(Description__c, 255)

Now, you can modify your SOQL to filter against this new field:

String searchTag = 'testing%';
List<Project__c> results = [SELECT Name FROM Project__c WHERE Description_Short__c LIKE :searchTag];
System.debug('Results found: ' + results.size());

Pros: Allows for direct SOQL filtering and works with standard List Views. Cons: You are limited to the first 255-3900 characters (depending on formula limits), and formula fields are not indexed by default unless you request a functional index from Salesforce Support.

Method 3: The "Shadow Field" Pattern using Triggers

If you need to filter across the entire length of the text or require high-performance searching on large datasets, the "Shadow Field" pattern is the gold standard. This involves creating a secondary, filterable field (like a Text Area or Checkbox) that is populated automatically via an Apex Trigger or Flow.

For instance, if you frequently search for specific keywords like "Red Flag," you could create a checkbox named Has_Red_Flag__c.

trigger ProjectBeforeSave on Project__c (before insert, before update) {
    for(Project__c proj : Trigger.new) {
        if(proj.Description__c != null && proj.Description__c.contains('Red Flag')) {
            proj.Has_Red_Flag__c = true;
        } else {
            proj.Has_Red_Flag__c = false;
        }
    }
}

Now your query becomes extremely efficient:

List<Project__c> flaggedProjects = [SELECT Id FROM Project__c WHERE Has_Red_Flag__c = true];

Pros: Maximum performance; searchable and indexable. Cons: Requires additional fields and maintenance of trigger logic.

Frequently Asked Questions

Can I use SOSL to search Long Text Area fields?

Yes! Salesforce Object Search Language (SOSL) is designed for text searching and does support Long Text Area fields. However, SOSL returns a List<List<SObject>> and is generally used for global searches rather than specific filtered queries within a single logic flow.

Why does 'LIKE' work for some text fields but not others?

Standard Text and Text Area (255) fields are indexed differently than Long Text Areas (up to 128k+ characters). Salesforce prioritizes multi-tenant performance, and allowing unindexed LIKE operations on massive text blocks would risk performance degradation for all users on the instance.

Can I use the 'contains' operator in a Report filter for Long Text?

Yes, Salesforce Reports allow you to use "contains" on Long Text Area fields. This is because reports run in a different execution context than SOQL, though they may still time out if the data volume is excessively large.

Wrapping Up

While the inability to filter Long Text Area fields in SOQL can be frustrating, you have several tools at your disposal to solve the problem. Use Apex filtering for small, dynamic datasets, Formula fields for quick partial matches, and the Shadow Field pattern for high-performance, enterprise-grade solutions.

By choosing the right strategy for your data volume, you can ensure your Salesforce application remains both functional and fast. Always verify your implementation against the latest Salesforce Release Notes, as platform limits and capabilities continue to evolve.