[SalesForce] Picklists not Supported in SOQL WHERE Clause (External Objects)

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?

Best Answer

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 :)

Related Topic