Here is the Custom Metadata I have set up so far:
My goal is to then show the custom picklist fields I have created ("Region used by Salesforce" and "Continent") on the Account object, with the values that correspond to the Country selected in the Billing Country address field.
I added a formula text field on the Account object. I've tried both the IF and CASE formula. The IF version works, but the CASE version gives me the default answer on a record with the country value set. (We do have State and Country/Territory Picklists turned on in our org).
CASE ( TEXT ( BillingCountryCode ) ,
$CustomMetadata.Countries_by_World_Region__mdt.US.DeveloperName ,
TEXT ( $CustomMetadata.Countries_by_World_Region__mdt.US.Region__c ),
$CustomMetadata.Countries_by_World_Region__mdt.UK.DeveloperName ,
TEXT ( $CustomMetadata.Countries_by_World_Region__mdt.UK.Region__c ),
"VERSION 1 not working"
)
​​​
IF (
TEXT ( BillingCountryCode ) =
$CustomMetadata.Countries_by_World_Region__mdt.US.DeveloperName ,
TEXT ( $CustomMetadata.Countries_by_World_Region__mdt.US.Region__c ),
IF (
TEXT ( BillingCountryCode ) =
$CustomMetadata.Countries_by_World_Region__mdt.UK.DeveloperName ,
TEXT ( $CustomMetadata.Countries_by_World_Region__mdt.UK.Region__c ),
"VERSION 2 not working"
)
)
ISSUE 1:
I tested both of these by dropping each API for the various fields I was using into the IF and CASE statement default (i.e. replaced the "VERSION 1 not working" text), so I know the correct values are getting compared with each other in my formula. But for some reason the CASE formula is performing as expected.
ISSUE 2:
Even if I manage to get this formula working correctly, our org has accounts in well over a hundred different countries, so it will be tedious to set this up as a CASE statement with over hundreds lines to read through (and I'll probably exceed the 5000 char limit). And as you can probably see from my Custom Metadata, my plan is to also display the Continent values on the Account object. It is likely that there will be additional Countries by World Regions custom fields we will add latter on (for example, I think I need to add another Transcontinental Region field that will include Central Asia, East Asia, Middle East, Caribbean islands etc). So I'll end up doing this for multiple fields.
I don't really see the advantage in using custom metadata types here. In my opinion this should be a custom object related via a lookup. You would still need to use a flow to match the account's country to the country record. The obvious advantage here is that you won't need to copy ALL the data into your account record like you'd have to with CMDT - only create a lookup. If you ever add new fields (like regional manager) or modify the data for a country (if you decide EMEA France is now just EMEA), it'll be a lot easier to do as well.
