5

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!

1 Answer 1

6

No, you can't do this dynamically. The formula creates a dependency on the metadata, so this metadata must exist in order to be used. You'd probably want to use a Before Save Flow or Trigger to populate a custom field with the appropriate value from the CMDT object dynamically.

You must log in to answer this question.

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