2

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.

  • Welcome to SFSE Rakesh. It would be easier for others to try to help if you showed your code and the error message. – Chris Duncombe Mar 19 '15 at 10:54
  • SELECT ExternalId,Id,orderID__c,orderLine__c,product__c,quantity__c FROM OrderDetails__x WHERE product__c LIKE '%geo%' . This is my Query but i am getting error " 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." – Rakesh Mar 19 '15 at 11:12
3

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.

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.