Is it possible to query an objects data against a custom metadata type? For instance, I'm working on a data cleanup project and need to find every lead where the value in the State field is not one of the values we can map to. I was thinking of creating a CMT of the allowable State values and writing something like:

select id from lead where (not state in @CMT).

For some context, we're talking 3million+ leads with a global database.


1 Answer 1


With pure SOQL, it is not possible. What you want only works as a table join, which requires a relationship between the two objects (either Lookup or Master-Detail). No such relationship is possible in either direction between a Standard Object/Custom Object and Custom Metadata. One is data, whereas the other is metadata.

If you're in Apex, then you can pull the values out of the Custom Metadata and into an allowlist/blocklist to use as a filter value.

  • 3
    and even if you decide to query for your Custom Metadata Type data, it doesn't count against the SOQL query limit for the transaction (but you should prefer the methods oulined in the Apex Reference Guide). Using a query does count against the query rows limit though.
    – Derek F
    Commented Jan 22 at 17:42

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .