Expand my Community achievements bar.

Calculated field based on options in a multi-select field

Avatar

Level 4

Hi,

I want to add a calculated (currency) field to my custom form that calculates a sum value based on the options selected in the multi-select field.

SUM(IF(Field A multiselect="NL 01",25,IF(Field A multiselect="NL 02",50,0)))

When I select one option, the calculation works. However, when I select multiple options, the calculation fails.

Does anyone have an idea how to solve this riddle ;)?

Topics

Topics help categorize Community content and increase your ability to discover relevant content.

3 Replies

Avatar

Community Advisor

I think probably consult the help article for calculated data expressions ( https://one.workfront.com/s/document-item?bundleId=the-new-workfront-experience&topicId=Content%2FRe... ) and see if you can find some other commands to help achieve this (maybe "if contains" rather than a straight-up "if"? -- a field with multiple options selected feels like more of a "contains" than an "equals" ... in my mind, anyway)

Also, not that you asked, I confess I'm not the best at calculated expressions, but something about your calculation definitely looks a bit wonky. To me, it's saying "if field A is equal to NL 01, then the value is 25, OTHERWISE if field A is NL 02 then the value is 50 and if it's not NL 02, the value is 0." So to me it feels like there's no actual addition going on. Maybe if you play around with different combinations of parentheses I'll feel differently.

Avatar

Level 4

I can't get it to work, but thanks for your help anyway!

Avatar

Level 4

This is a late response, but I actually got this calculation working. Maybe I can help someone else with this calculation as well.

 

SUM(IF(CONTAINS("Option 1",{DE:custom field A}),DIV({DE:custom field B},10),0),IF(CONTAINS("Option 2",{DE:custom field A}),DIV({DE:custom field C},5),0)"")