1977803 - Error: "INVALID_QUERY_FILTER_OPERATOR", when refreshing a report based on Salesforce.com in Crystal Reports

• Error: "Failed to retreive data from the database".

• When reporting on Salesforce.com in Crystal Reports, and filtering the data on an ID Field, it fails with the following errors:
  
  "SAP Crystal Reports: Failed to retrieve data from the database"
  
  SAP Crystal Reports: Database Connector Error: ‘HY000:INVALID_QUERY_FILTER_OPERATOR:
  Id, Name FROM Account WHERE (id = ‘1’)
  Error at Row:1:Column:38
  Invalid ID filed: ‘1’ [Database Vendor Code: 47]’ "

• Note: Images and data in this SAP Knowledge Base Article is from SAP internal systems, sample data, or demo systems. Any resemblance to real data is purely coincidental.

• SAP Crystal Reports 2008
• SAP Crystal Reports 2011
• SAP Crystal Reports 2013
• SAP Crystal Reports 2016

1. In Crystal Reports, create a report off Salesforce.com
2. Create a Record Selection Formula to filter the data on an ID database field, like:
      
        {Account.Id} = "1"
     
3. Refresh the report, and it will display the following error messages like:
     
   "SAP Crystal Reports: Failed to retrieve data from the database"
       
   SAP Crystal Reports: Database Connector Error: ‘HY000:INVALID_QUERY_FILTER_OPERATOR:
   Id, Name FROM Account WHERE (id = ‘1’)
   Error at Row:1:Column:38   
   Invalid ID filed: ‘1’ [Database Vendor Code: 47]’ "
      
       
   
     
   

• The error occur due to Salesforce.com limitation when using a Record Selection on a ID Database Field in the following situation:
    
• When using the operator equal (=), and the value compared with the ID Database Field, does not equal to the number of characters expected by Salesforce.com of 18 characters long.
   
  For example:  If we have a Record Selection like:  {Account.Id} = "123",  it fails, because it has only 3 characters, when Salesforce.com expect 18 characters ID for the external application like Crystal Reports.
         
• When using the operator: "Like", "Startswith",.. on a Salesforce.com ID database field, it will be translated in SOQL using the "LIKE" operator, and it will fail because the "LIKE" operator is not supported by Salesforce.com on ID Database Field. It is only supported for string fields that are not ID field.
   
  For example:  If we have a Record Selection like:  {Account.Id} Like "123",  it fails, because Salesforce.com does not support the LIKE operator on an ID Database Field.

• To filter a Salesforce.com data source on an ID Database Field:
    
• When using the equal sign (=), like:  
  
       {Account.Id} = "12348"
   
  Then ensure the value entered in the Record Selection Formula contains exactly 18 characters.
    
  For example: To filter on the Account ID, create a Record Selection Formula with a value that contains 18 characters like:
     
        {Account.Id} = "123456789012345678"
    
• When using the operators: Like, StartsWith,... 
      
  It is translated using the "LIKE" operator in SOQL, and it is not supported by Salesforce.com to use the LIKE operators in SOQL for ID database fields, therefore, you can filter the data locally in Crystal Reports, by adding the filtering to the Save Data Record Selection:
    
1. Open the report in Crystal Reports, and under the menu "Report - Selection Formulas - Record...", copy the filter based on the ID Database fields that uses the operators Like, StartsWith.  
         
        Example: {Account.Id} StartsWith ("123456")
     
2. Remove the filter from the Record Selection Formula
3. Save and Close the Record Selection Formula
4. Create a Saved  Data Record Selection Formula, by selecting under "Report", "Selection Formulas - Saved Data..."
5. Copy the filter that was deleted from the Record Selection Formula to the Saved Data Selection Formula.
6. Save and Close the Record Selection Formula
     
        Example: {Account.Id} StartsWith ("123456")
    
   Now, when refreshing the report, it will not send the filter for the ID Database Field to Salesforce.com, and it will filter the data locally in Crystal Reports, therefore the report will now refresh successfully.
           
     
• Note: Generally, ID Database Fields in Salesforce.com are not used for filtering, but for linking tables together.

CR, Salesforce.com, sforce , KBA , BI-RA-CR , Crystal Reports designer or Business View Manager , Problem

Crystal Reports 2008 V1 ; SAP Crystal Reports 2011 ; SAP Crystal Reports 2013 ; SAP Crystal Reports 2016