I need to retrieve certain Custom Metadata records from my Custom Metadata Type Milestone_Template__mdt, depending on which of my company's regions I am creating Projects & Milestones for in my code.

The Milestone_Template__mdt records have a tickbox on them, which are named like EU_Milestone__c, to identify the region that they belong too. I've deliberately not used a picklist for reasons that I'd be happy to explain the comments, if anyone's wondering.

To avoid wasting characters, I'd like to avoid writing a separate query for each of the different regions that I could potentially be creating those records for & deciding which query to execute via if / else statements.

Instead I'm hoping that there's a way to write my query to specify which region's records to select, depending on a region that's stored in a variable :region.

I had the not so bright idea of referencing the variable like this

projectTemplateWithMilestoneTemplates = [SELECT Project_Name__c,
                                        (SELECT Milestone_Name__c
                                           FROM Milestone_Templates__r
                                          WHERE ((:region = 'EU' AND EU_Milestone__c = TRUE) OR (:region = 'NA' AND NA_Milestone__c = TRUE)) AND Test_Record__c = FALSE)
                                           FROM Project_Template__mdt];

the idea being that the criteria would only evaluate to TRUE if the region was the one specified & the region's tickbox was ticked but it looks like that's not allowed.

Is there another way that I can check whether a certain tickbox is ticked on these records, depending on the value stored in my variable? Or failing that, is there another way to approach this process?

The data model looks like:

  • can you post your mdt structure in the question with data Commented Aug 15, 2017 at 18:08
  • There's just a metadata relationship from Milestone_Templates__r to Project_Template__mdt, if that's what you mean?
    – Alex S
    Commented Aug 15, 2017 at 18:20
  • yes, thats what, can you post datamodel picture for better understanding Commented Aug 15, 2017 at 18:22
  • @SantanuBoral I've added a diagram now, is that what you were looking for?
    – Alex S
    Commented Aug 15, 2017 at 18:41
  • If you had a field on the mdt called "region__c" then it'd be easy - any reason not to do that? Commented Aug 15, 2017 at 18:44

2 Answers 2


Ok, I think your best bet is to use a Dynamic SOQL query (assuming that Salesforce allows Dynamic MDT queries - if not, I'll remove this answer).

Seperate out your region condition and then just fold it into the main query, then execute like this:

String regionClause = ( region == 'EU' ? ' EU_Milestone__c = TRUE ' : 
                       (region == 'US' ? ' US_Milestone__c = TRUE ' :
                                         ' ASIA_Milestone__c = TRUE '));

String query =        'SELECT Project_Name__c,            ';
       query +=       '   (SELECT Milestone_Name__c       ';
       query +=       '    FROM Milestone_Templates__r    ';
       query +=       '    WHERE ' + regionClause + '     ';
       query +=       '       AND Test_Record__c = FALSE) ';
       query +=       'FROM Project_Template__mdt         ';

projectTemplateWithMilestoneTemplates = Database.query(query);

Good luck!

  • Apologise for the extended ternary operator - of course it could be a series of if elses, but I you wanted to save space :).... Commented Aug 15, 2017 at 19:03
  • Thanks, I'm running short of time but I'll get back to you about this tomorrow, if not before.
    – Alex S
    Commented Aug 15, 2017 at 20:00
  • Two minor details: (a) your ternary operators won't work as you expect/compile right without more parentheses, and (b) you can't have literal newlines in a string. Other than that, I believe it should work.
    – sfdcfox
    Commented Aug 15, 2017 at 21:06
  • Good point, I'll tidy that up. Was in a bit of a rush Commented Aug 15, 2017 at 21:30

I think would create a utility method that just builds a map of Region to List<Milestone_Template__mdt> by looping through all the records to create the map. Then the code can just pass in a region as a param and get back all the records as a list...

  • Thanks, I'm running short of time but I'll get back to you about this tomorrow, if not before.
    – Alex S
    Commented Aug 15, 2017 at 20:00
  • This should work, but might be CPU intensive compared to a query solution.
    – sfdcfox
    Commented Aug 15, 2017 at 21:07
  • I've accepted Caspar's answer now which is working for me. To be clear, I haven't found that yours doesn't work but I'll defer to SFDCFox's expertise that the query solution is the way to go.
    – Alex S
    Commented Aug 17, 2017 at 15:26
  • we all defer to sfdcfox :) Commented Aug 17, 2017 at 16:37

You must log in to answer this question.

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