12

I'm working on a custom search page, which will search through the fields of the Project object. I use dynamic SOQL query similar to that:

String query = 'SELECT Name, Status__c ... 
    FROM Project__c WHERE Name LIKE \'%' + String.escapeSingleQuotes(name)
     +'%\' + ' OR Status__c = \'' + String.escapeSingleQuotes(status) +'\'';
List<Project__c> projects = Database.query(query);

But when I try to add to filter criteria Description__c field, which is Long Text Area, get the following error:

System.QueryException: field 'Description__c' can not be filtered in query call

How can I use Long Text Area fields in filtering of results?

  • What values are in that field that you want to filter on ? Possible it shouldn't be a long text field, or you could optimize your data model and process by creating additional fields. – Samuel De Rycke Oct 29 '14 at 12:01
20

You can not use text area fields in SOQL and SOSL filter criterias.

Try this approach:

List<Project__c> projects = new List<Project__c>();
for(Project__c proj : [SELECT Name, Status__c, Description__c FROM Project__c WHERE Name LIKE '%' + String.escapeSingleQuotes(name) +'%' + OR Status__c = String.escapeSingleQuotes(status)]) {
    if(proj.Description__c.contains('filter string')) {
        projects.add(proj);
    }
}
7

There is an Idea to Allow filtering on Long Text Area, so you can't. In a pinch you could iterate over your results in a for loop and use String methods to filter out/in the desired results. Of course, that may not be reasonable depending on the number of records and whether or not what you are doing is needed in real time.

3

I just discovered a work around for this here. Create a Formula field that uses the LEFT formula and filter on that. You cannot filter on the ENTIRE long text but you can filter on part of it:

Legal_Street__c is a long text area. I created a formula field called StreetConc__c:

LEFT(Legal_Street__c, 40)

I then created a dummy record and put the text 'testingthis' in the Legal_Street__c field. From here I filtered on this formula field.

System.debug('answer = ' + [SELECT Name FROM Account WHERE StreetConc__c LIKE 'testing%']);

You should be able to apply this to both SOQL and SQL.

  • 2
    Can you still get this to work? It does not work for me, I'm getting a message telling me that I cannot use fields of type Long Text Area in a formula. – rael_kid Mar 22 '16 at 6:51
  • I dont think this is possible, are you sure your field Legal_Street__c is long text and not text area? – manza Jul 1 '17 at 5:53
  • 1
    An variant of this is to create a formula that 'implements the where clause' (or part of it). E.g if you have WHERE longtextarea__c like '%TEST%', you can create a formula field x__c with formula CONTAINS(longtextarea__c, "TEST") and make the query WHERE x__c = TRUE . – Legolas Dec 7 '17 at 13:25

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.