9

Background

I ran a SOQL query from the developer console on one of my external objects which has a picklist field (Object name Fixture__x, picklist field name Status__c).

The following query:

SELECT Id, Status__c from Fixture__x

returns a couple of records with a status of Scheduled:

enter image description here

Then I tried:

SELECT Id, Status__c FROM Fixture__x WHERE Status__c = 'Scheduled'

The Query editor threw an error:

sObject type 'Fixture__x' is not supported.

However, if I execute the very same query in APEX, I get no errors, rather this debug log:

enter image description here

Conclusion

Picklists aren't supported in the SOQL WHERE clause for external objects.

Question(s)

Is this documented anywhere? Any reason why they aren't supported? Is there anything I'm missing or it needs to be done differently in order to achieve this? Aren't the picklists treated as strings (text) anyway?

7

I suspect this might be down to how they're implemented on the external system itself. The where part of the query is not handled by Salesforce, but by the OData compliant API at the other end, the where basically defines a query for the REST response.

Dynamics CRM uses text based labels for what we know as picklists, but then has integers for the actual values stored in the database; so I suspect what is happening is that Salesforce doesn't allow such queries to be made because implementation details would vary. If Salesforce sends a text value to Dynamics that's not going to match on the other end, and I don't believe Salesforce have processing in place to import such values from the other system when configuring the object.

I could be completely wrong of course :)

|improve this answer|||||
  • 2
    Thing is, you create a custom field on the external object on the Salesforce end, I would imagine the way it's stored on the external system, should be determined by Salesforce. Also it would've been nice if it was documented as a consideration when implementing such functionality. (For an example it could be stated here - help.salesforce.com/…) – Boris Bachovski Mar 20 '17 at 2:03

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.