I came across something weird, SOQL which has a filter as cross-Object picklist field doesn't return rows, even if records exist.
Minimum viable code to replicate:
- Create a new custom metadata type, Name it as 'Parent'
- Create a picklist filed on it, name it as 'Type__C' and provide 2 value choices for it namely 'Option1' and 'Option2'
- Create a new record for Parent, name it as 'Dad' and chose 'Type__c' as 'Option1'
- Create a new custom metadata type , Name it as 'Child'
- On child, create a new custom metadata relationship field name it as 'Parent__c'
- Create a new record of Child and select parent as 'Dad' record (As in step 3)
Now run this SOQl
SELECT DeveloperName,Id,Parent__r.Type__c FROM Child__mdt where Parent__r.Type__c='Option1'
It will return zero rows, even if It has records that meet these criteria.
Converting that field (Type__c) from Picklist to free-text makes it work, but then I cannot use the restricted picklist feature and it is now more prone to errors.
I cannot use, validation rule as well because it has 20+ values so it will be overhead to do.
Does anyone know about this bug? I couldn't find it on known issue as well. Is there any workaround for this?
Edit: If I direct query parent object with the same filter, it works. :(
Edit (18/12/2018) : After days of back and forth, they have recognized this as a bug (W-5709686 ), and there is No ETA for the fix.