Calculate field to return the Label from name/value field

[#1]

To integrate Workfront with AEM you need to utilize the "value" of a custom form (back-end name) instead of the "label" (front-end, user friendly name) because AEM requires a formatted string to feed the data.

This has an impact inside of Workfront on calculated fields as the calculated fields return the "value" (back-end name) and not the front-end name that users see. For example if you have a Region dropdown, the calculated field would return "regional-coverage/701782" instead of "US."

There is not a way to reference the dropdown option's label (front-end name) rather than the value (back-end name). The only workaround is to use a series of IF or REPLACE statements that generate a string equal to the label (front-end name) for the dropdown selection. This becomes highly cumbersome when there are a lot of options to maintain (a country option alone could have 195 options) and even more difficult when there is a multi-select field.

The ability to define whether or not we want to return the label or value would be beneficial and help to more seamlessly complete a Workfront & AEM integration and in general.

[#2]

Agree! This would make pulling calculations from a program to a project and project to a task much easier.

[#3]

This would be especially helpful for one of our use cases; to auto-generate standard naming conventions based on data inputs.  The issue is, we use the Value entry of our option list fields as unique IDs to synch our field data with other systems.  We would need to be able to return the Option Label in a Concat function if we wanted to make use of data driven calculated entries.

[#4]

I don't know if anyone else on this particular thread has seen a solution, but there is another thread in which a similar issue was reported with a solution that works - https://experienceleaguecommunities.adobe.com/t5/workfront-questions/calculated-field-on-selection-o...

It's not the most straight forward solution but it does work!  For option lists with more than a few values, creating some sort of repetitive builder for yourself.  The first use case I built was for a media channel field with 17 options.  Using excel I built a Concatenate Formula that would build a Giant Nested IF Statement for myself using the Field Name and ID as inputs, then turned the resulting list into a section of my calculated field formula.  The ending value should never come up unless yu add new Options within your option list field that are not reflected in the nested IF Statement.

IF({Custom FIeld}="{Option ID1}","{Option Name1}",IF({Custom FIeld}="{Option ID2}","{Option Name2}",IF({Custom FIeld}="{Option ID3}","{Option Name3}",IF({Custom FIeld}="{Option ID4}","{Option Name4}",IF({Custom FIeld}="{Option ID5}","{Option Name5}",IF({Custom FIeld}="{Option ID6}","{Option Name6}",IF({Custom FIeld}="{Option ID7}","{Option Name7}",IF({Custom FIeld}="{Option ID8}","{Option Name8}",IF({Custom FIeld}="{Option ID9}","{Option Name9}",IF({Custom FIeld}="{Option ID10}","{Option Name10}",IF({Custom FIeld}="{Option ID11}","{Option Name11}",IF({Custom FIeld}="{Option ID12}","{Option Name12}",IF({Custom FIeld}="{Option ID13}","{Option Name13}",IF({Custom FIeld}="{Option ID14}","{Option Name14}",IF({Custom FIeld}="{Option ID15}","{Option Name15}",IF({Custom FIeld}="{Option ID16}","{Option Name16}",IF({Custom FIeld}="{Option ID17}","{Option Name17}","Placeholder")))))))))))))))))

[#5]

Thank you for sharing @GregoryCr! We currently create the IF/REPLACE statements now, but love the idea of the repetitive builder in Excel.

Hopefully one day there won't be the manual need to do this too!

[#6]

Definitely Agree it wold be nice to have a more systematic value converter.  That being said, I'm always interested in different ways I can bring my data together.  How are you using the Replace function in this context?  Would it be instead of nesting an IF, having the same logic but using Replace to exchange each entries ID with their corresponding value?

If so, how do you account for different potential entries?  For Example, if you have a multiple potential entries from an option list field, how do you know which value to replace the variable retrieved ID?

[#7]

Yeah, using the same concept as the IF, just less lines to write out when it gets long. Using it for 1:1 value replacements.

REPLACE({DE:[Field Name]},"[Field Value]","[Field Label")

[#8]

Replace function and using If statements works best with 1:1 field value selections and we have used. It gets challenging with Multi select fields.

[#9]

That makes sense, but if there is no way to retrieve the proper label with a variable, how does the calculated field know which label to supply with which entry?  In my original example, we have 17 possible inputs.  If you write out 17 Replace scenarios for each possible option, is the calculated field smart enough to recognize only the valid entry while ignoring all other scenarios?