Reporting on Multi Select fields

Thanks Dan - I'm set up like your second option with the value saved as a number. I was fearing the nested IF/CONTAINS. Makes it a bit more difficult to add options to the selectable list. I'll just need to remember to add it to the list and the IF/CONTAINS calculation as well. :( Rick MacDuffie Symetra Life Insurance Company

Hi Rick, How you set up your calculation depends on the whether you are change the each choice's value (5) or leave it the same as the label (Product A). Here are a couple examples. Number OR Text Format without values In this case, the Format of the multi-select is Text and each choice's label is the same as the value. The format doesn't matter as much here for this formula (see notes below). (I named my field Relevance1) In your calculated field, I recommend you change the format to Number. Then use this formula: SUM(IF(CONTAINS("Product A",Relevance1),5),IF(CONTAINS("Product B",Relevance1),4),IF(CONTAINS("Product C",Relevance1),3),IF(CONTAINS("Product D",Relevance1),2),IF(CONTAINS("Product E",Relevance1),1)) Notice that you'll need to add an option in the SUM() for each of the available choices in the Relevance1 field. SUM(choice 1, choice 2, choice 3, choice 4, choice 5). Number OR Text Format with values For this example, the format of the multi-select is Number and the choice values are different than the choice labels. The calculation is pretty much the same, but with one change in the formula. (I named this field Relevance2) Then in your calculated field, change the format to Number. Then use this formula: SUM(IF(CONTAINS(5,Relevance2),5),IF(CONTAINS(4,Relevance2),4),IF(CONTAINS(3,Relevance2),3),IF(CONTAINS(2,Relevance2),2),IF(CONTAINS(1,Relevance2),1)) Just like above, you need to add an IF(CONTAINS()) statement in the SUM() for each of the available options from the Relevance1 field. SUM(choice 1, choice 2, choice 3, choice 4, choice 5). Notes: If either field (multi-select or calculated field) are formatted as Text you cannot Aggregate that field up to groupings within a report (Sum, Average, Count, etc.) If the multi-select field is formatted as Number you CAN Aggregate, but because the field is a multi-select field, it will only look at the first value like you were seeing when you were trying to SUM("your fields name"), and the aggregation ignores the rest. If the calculated field's format is Number you CAN Aggregate. The multi-select format can be changed once the form it is on is closed, but the calculated field format cannot. Dan Perkins Rego Consulting

Are you trying to add the Value Field from the selected option? If so, what syntax are you using to get the Value Field to pull rather than the Value Label? This is a guess but I think the multi select values are technically text values and not number values, so maybe that is why the SUM function is not working? Kristy Musgrove Havertys

Nifty! I'm working on a custom form that will be used to assess a requests relevance / priority. In that custom form I need to have several fields that collect multi-select values that get stored as numbers. Something like: What product requires this piece? Product A is equivalent to 5 points Product B is equivalent to 4 points Product C is equivalent to 3 points Product D is equivalent to 2 points Product E is equivalent to 1 point A user might select Product A and Product D. I hoped to be able to add the selected values up using the SUM function in a custom field, but that seems to grab the first item picked from the list and ignore every other option selected. Anyone done something similar? Ideas? Rick MacDuffie Symetra Life Insurance Company

No you can not edit any field that uses a valueexpression. For that you need to stick to the standard list. Melinda Layten Technical Project Manager - API and Integration Workfront

Thank you, I have switched to valueexpression from valuefield to sort multi-select values alphabetically but after the switch i am unable to inline edit the field. Is there any solution to inline edit the field in a view/report at the same time sort the multi selected values? Thanks, Pradeep Pradeep Sundharagiri Deere GIT

I've added "https://wf-pro.com/textmode/text-mode-views-collections/#multiselect-list"> Multi-Select as Alphabetized List to the WFPro site. Thank you for sharing Melinda!

I noticed that too: bonus! Regards, Doug Doug Den Hoed - AtAppStore Got Skills? Lend a hand! https://community.workfront.com/participate/unanswered-threads

Just noticed, switching from valuefield to valueexpression is sorting the list alphabetically! Underlying field isn't sorted at all. Melinda Layten Technical Project Manager - API and Integration Workfront

Very nice Melinda, That one looks like it might have been a morning's worth of noodling. It's going in the vault. Thanks for sharing. Regards, Doug Doug Den Hoed - AtAppStore Got Skills? Lend a hand! https://community.workfront.com/participate/unanswered-threads