[SalesForce] How to query on OData(External Object) in Visualforce search page. using search string

I am trying to write a query on External Object using LIKE operator but its showing as error like, it is limited to use LIKE operator for OData Query.
Is there any alternate way of querying?

SELECT ExternalId,Id,orderID__c,orderLine__c,product__c,quantity__c FROM OrderDetails__x WHERE product__c LIKE '%geo%'

UNKNOWN ERROR: EXTERNAL_OBJECT_UNSUPPORTED_EXCEPTION: This data isn't
available because the OData filter operator "LIKE" is not supported.
Contact your administrator for help.

Best Answer

The documentation for Lightning Connect is quite complete. The help page for Considerations for Lightning Connect has a list of things that have special limitations and considerations, including SOQL, which has its own considerations for SOQL page.

Amongst some other important considerations that make it well worth your while to read them yourself, those docs state the following:

External objects also don’t support the following.

  • EXCLUDES operator
  • FOR VIEW clause
  • FOR REFERENCE clause
  • INCLUDES operator
  • LIKE operator
  • toLabel() method
  • TYPEOF expression
  • WITH clause

As for alternative, the first choice is SOSL!

SOSL is the misunderstood, underused, underappreciated stepbrother of SOQL. And actually, it is designed for fuzzy text-based searches. Most of the time when a DEV wants to use LIKE as an operator, they should probably be thinking SOSL anyway. Don't forget to checkout the Lightning Connect Considerations for SOSL, too, though! And this will only help you if the external OData end point actually supports text searches in some fashion.

The last and probably messiest workaround might be to store some data in Salesforce. If data residency is not a regulatory concern, perhaps you could store some data in a related object on the platform with an indirect relationship to the OData source. And this is just me supposin' at this point as this is completely untested, but suppose you had relationship: 

Dummy_Internal_Data__c <--parent----child--> My_External_Obj__x

And you had a custom text field in the dummy object called: String_Query__c. You'd set up a sync job to populate that field on a periodic basis with updates from the OData source, and set it as ExternalId to get benefits of the field being indexed.

You might try: 

SELECT String_Query__c, ID, 
      (SELECT Field_1__c, etc. from My_External_Objs__r)
FROM Dummy_Internal_Data__c
WHERE String_Query__c like 'some-value%'

If you did this, you'd also need to create logic to sync the Salesforce dummy object on a regular basis, too.

Related Topic