Expand my Community achievements bar.

Reporting on Multi Select fields

Avatar

Level 2
When reporting on a Multi Select field is it possible to add a carriage return between each option? LIke in Char(10) in excel? Thanks Sharon Watt ANZCA
Topics

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

13 Replies

Avatar

Community Advisor
Did you ever find a solution for this? I would like to do the same thing. :) Sheri Whitten Gordon Food Service

Avatar

Level 10
This article gives the codes - but i don't know the syntax to use these. Anyone can help by showing an example of how to use these in text mode. View this on Workfront > Benetta Perry APS

Avatar

Former Community Member
Tricky One :) displayname=Systems List linkedname=direct namekey=Systems Effected textmode=true valueexpression=REPLACE({DE:Systems Effected},","," \ \ u000A") valueformat=HTML So you need to replace the commas in your string with the unicode character u000A. To escape into the unicode character you need two \ \ without a space. It will save as only one \ so you'll need to add back the second slash each time you edit to prevent the interpreter from inserting the line break in the text mode instead of in the column. Melinda Layten Technical Project Manager - API and Integration Workfront

Avatar

Community Advisor
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

Avatar

Former Community Member
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

Avatar

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

Avatar

Level 10
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!

Avatar

Level 1
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

Avatar

Former Community Member
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

Avatar

Level 4
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

Avatar

Level 5
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

Avatar

Level 3
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

Avatar

Level 4
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