Field can not be filtered in SOQL query call

  • Here is my SOQL code for finding records to update but it is generating a compiler error like "field 'Chatter_Monitor_Policy_Keyword__c' can not be filtered in query call". Why can't I filter by this field?

    string abc = obj[0].Chatter_Monitor_Policy_Keyword__c;
    string s ='%'+ abc + '%'; 
    List<Chatter_Monitor_Policy__c> records = [SELECT Id, Chatter_Monitor_Policy_Keyword__c from Chatter_Monitor_Policy__c where Chatter_Monitor_Policy_Keyword__c Like:s and CreatedById =: UserInfo.getUserId()];
    if(records.size()!=null)
    {
      for(Integer i=0; i<records.size(); i++)
      {
        records[0].Chatter_Monitor_Policy_Keyword__c = s;
        update records[0];
      }
    }
    
  • Textarea field types are filterable, but it needs to be done in the QueryResult

    Quote from SOAP API page

    Textarea Field Type

    Textarea fields contain text that can be longer than 4000 bytes. Unlike string fields, textarea fields cannot be specified in the WHERE clause of a queryString of a query() call. To filter records on this field, you must do so while processing records in the QueryResult. For fields with this restriction, its filterable field in the Field type (described in the fields property of the DescribeSObjectResult) is false.

    So get the query results into a queryresult object, and filter over each of the returned objects.

    Great find but do you have any examples on how to use QueryResult? I can't seem to find any examples on that link ...

  • As indicated in the attached screenshot, check the "filterable" acces property of field in schema browser (Force.com IDE etc). This property indicates whether the field is filterable (true) or not (false). If true, then this field can be specified in the WHERE clause of a query string in a query() call. More details here : http://www.salesforce.com/us/developer/docs/api/Content/sforce_api_calls_describesobjects_describesobjectresult.htm

    Check the "filterable" acces property of field in schema browser

    Thanks. This was blocking me. You can also view the Schema in the Force Explorer AIR app if you don't want to use the Force.com IDE.

  • If Chatter_Monitor_Policy_Keyword__c is a text area then you can't filter by it, though if it truly is a keyword (or short list of keywords) then it should just be a text field which you will be able to filter by.

    Can you confirm what type of field you are using? If it is a text area then I'd suggest changing it to a text field if possible.

    If you absolutely can't change the field type, then you're probably limited to selecting all of the records and performing the search in code — to do this you'll likely need to leverage a batch apex class to process the data volumes involved.

    yes Chatter_Monitor_Policy_Keyword__c is text area

    That's your answer then — unfortunately you can't filter by text area fields in SOQL!

    Then what is the exactly solution for that???

    aah the problem is resolved here in the mean time, I was trying to figure out the answer :) SE rocks !

    @KishanTrambadiya - does it really need to be a TextArea? Can you make it a Text field instead?

    @KishanTrambadiya You need to make it something **other** than a text area!

    @KishanTrambadiya Text and TextArea are two different field types. You can filter on Text; you cannot filter on TextArea. Changing `Chatter_Monitor_Policy_Keyword__c ` to a Text field will allow you to filter on it.

    @metadaddy - i cant make it text field...

    @KishanTrambadiya - well you can't filter on it then

    @metadaddy - i asked question as i want any other alternative instead of this.

    @KishanTrambadiya As metadaddy has mentioned with textarea we cant filter, if you need to filter you need to use textfield.

  • This is an old thread but I hope my SOSL solution can help others. Actually, we can use SOSL as a workaround to turn Textarea field types into filterable/searchable field. See code sample below

    String fieldValue = 'somevalue';
    String fieldName = 'MyTextAreaField__c';
    String query =  = 'FIND {"'+ fieldValue + '"}  RETURNING ' +
                      ' YourCustomObject__c (id, ' + fieldName + ' ) LIMIT 2000';
    List<List<SObject>> searchList = Search.query(query);//Return List of SObject list
    system.debug('Search result: ' + searchList[0]);//Get search result
    if(searchList[0].size() > 0) {
       //You can cast your result and return other fields if needed
       List<YourCustomObject__c > objSearchResultsList = (List<YourCustomObject__c >) searchList[0];
       system.debug(fieldName +' with "' + objSearchResultsList[0].get(fieldName) + '" found. ');
     }
    

    If your search result has record, the sample output will be:

    Search result: (YourCustomObject__c:{Id=aeb02000000000LXXX, MyTextAreaField__c=somevalue})   
    MyTextAreaField__c with "somevalue" found. 
    

License under CC-BY-SA with attribution


Content dated before 7/24/2021 11:53 AM

Tags used