Display options from Choice Multiple Select in separate columns
I recently came across a request to display the options from a choice multiple select across multiple columns Like this:.
When I began to work on this I had a totally differnt path. My initial thoughts were to create a calculated field, and then place the values into another calc field with an HTML table. I have used tables before in a calculated field with the help od a script from pathtosharepoint.com text to HTML. This direction was impossible. Nothing I was trying would even work. I knew that I could trick sharepoint into reading a Choice field, but this turned out only to be true when it was single select.
So to get started I had to create my choice multiple select field.
“WorkType” – When this is first created it must be created as a single line of text.
“calc” – Second field is a calculated field =[WorkType]
Delete “WorkType”
Create new field
“WorkType” choice, multiple select
The “calc” field now will read the the string from “WorkType” the output will be something like this
;#Choice1;#Choice 3;#
The next set of fields depends on the number of options you have. My demo has 5 options (opt1-5) so I created 5 calculated fields
opt1 =IF(FIND(“Choice 1″,[calc]),”Choice 1″,”")
opt2 =IF(FIND(“Choice 2″,[calc]),”Choice 2″,”")
opt3 =IF(FIND(“Choice 3″,[calc]),”Choice 3″,”")
opt4 =IF(FIND(“Choice 4″,[calc]),”Choice 4″,”")
opt5 =IF(FIND(“Choice 5″,[calc]),”Choice 5″,”")
The formulas are searching the string (in [calc] for a specific value. When you setup yours Choice 1 would be your option 1, same thing for the returned value if true.
This returns the values in each column as it should , but the find formula returns #VALUE! it the formual is false like this:
I could not figure out a way to remove the #VALUE!. So I deciede to go with a string replace.
<script type="text/javascript">
var theTDs = document.getElementsByTagName("TD");
var i=0;
var TDContent = " ";
while (i < theTDs.length)
{
try
{
TDContent = theTDs[i].innerText || theTDs[i].textContent;
if (TDContent.indexOf("#VALUE!") == 0)
{
theTDs[i].innerHTML = TDContent.replace(/#VALUE!/,"").replace(/#VALUE!/g,"");
}
}
catch(err){}
i=i+1;
}
</script>
This looked very clean and the options are displayed in individual column in order.
