SOQL LIKE operator on picklist field appears to fetch results based on matches on label not API value.
For example, take this query
SELECT Id, Name, RZone__Type__c FROM RZone__PSPayment__c
WHERE RZone__Type__c LIKE ‘%Migration%’
Here RZone__Type__c is a picklist
This query returns records with a Type API value of “Advance” because the label is “Balancing Payment Data Migration”. I would expect to see the like operator applied to the API value and not the label. From the documentation The LIKE operator can only query on the label of the picklist, not on its API name - This limitation applies only to translated picklist values. The doc doesn’t say why does the like operator queries on the labels even though translation used
I came across this post where it said If you have a scenario where LIKE needs to match the API name, you could create a formula field targeting the same picklist. Formula fields always return API name not label.
Can anyone please advise if this is working as designed? Thanks for your inputs