I am currently using Custom Metadata Types to store offset values. The Name field will be a TimeZoneSidKey (with _ instead of /). Then I am using a formula field that will basically retrieve a value and add it to the corresponding offset hours according to the user in context timezone settings.
What I am trying to prevent is having a bunch of if conditions to verify the user's TimeZoneSidKey and would like to dynamically build the path to the correct CMT record.
So instead of this:
IF( $User.TimeZoneSidKey = 'Europe/Amsterdam' , $CustomMetadata.Timezone_Setting__mdt.Europe_Amsterdam.GMT_Offset__c, other_conditions )
I would like to have something like:
$CustomMetadata.Timezone_Setting__mdt.TEXT(SUBSTITUTE(TEXT($User.TimeZoneSidKey), '/', '_')).GMT_Offset__c
Is this remotely possible or am I asking too much?
Best regards and thank you in advance!