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 – Santanu Boral Aug 15 '17 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 Aug 15 '17 at 18:20
  • yes, thats what, can you post datamodel picture for better understanding – Santanu Boral Aug 15 '17 at 18:22
  • @SantanuBoral I've added a diagram now, is that what you were looking for? – Alex S Aug 15 '17 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? – Caspar Harmer Aug 15 '17 at 18:44

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 :).... – Caspar Harmer Aug 15 '17 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 Aug 15 '17 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 Aug 15 '17 at 21:06
  • Good point, I'll tidy that up. Was in a bit of a rush – Caspar Harmer Aug 15 '17 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 Aug 15 '17 at 20:00
  • This should work, but might be CPU intensive compared to a query solution. – sfdcfox Aug 15 '17 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 Aug 17 '17 at 15:26
  • we all defer to sfdcfox :) – BritishBoyinDC Aug 17 '17 at 16:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.