Choice field Multiple select, multiple columns – Part 2

Previously I left off discussing a request I received. Is it possible to parse the data from a Choice multiple select across multiple columns? The answer is yes and today we will discuss some of the nuts and bolts. By default SharePoint (SP) combines options. Although there are probably many ways to achive this out come today we will walk through how to implement this through the use of calculated formula and a single script.

Once I understood that using two Calculated Columns(CC) the process became much easier to achive.

When I tried this with only one CC with the calculated formula I would get an error applying the SP CC trick. Selecting any of the field types returned an error until I created the second CC.

My list has four fields related to this post. Create each one following the directions below.

1. Name – single lin text
2. WorkType - Choice – checkboxed(allow multiple select) no default option – No allow fill in
3. WorkTypeChoices - calculated field
4. Display - calculated field

Name field could be substituted as Title it is only a reference we use later in the CC Display output.

WorkType field contains 5 choices. For this demo the options used:
Choice 1
Choice 2
Choice 3
Choice 4
Choice 5

Note: If any more choices are added this could task the limits of the CC and other options may need to be implemented.

WorkTypeChoices is set equal to WorkType. To make this work So has to be tricked into reading the choice field. The reason for this is SP wasn’t designed to read any field type but a single line text. To begin:

1. create WorkType as a single line text field
2. create WorkTypeChoice as a calculated field
3. set WorkTypeChoice equal to WorkType (=[WorkType])
4. delete WorkType field
5. recreate WorkType field with the same name
6. select Choice – checkboxed(allow multiple select) No default value – No Allow ‘Fill-in’
7. Click OK

Any items selected and posted from Worktype will display in WorkTypeChoices as such:

#;Choice 1#;Choice 3#;Choice 4#;

Display field has a complex formula. The best part about this field is we will get the value from WorkTypeChoice. No other tricks or work arounds will need to be applied.

You should expect already that to achieve multiple columns we will need some form of a table. The formula below will build that table, set column width, apply style and account for SP errors. To manage the character limitation I added styles.

If we break down the formula to one IF statement this is what we have:

IF(NOT(ISERROR(FIND("Choice 1",[WorkTypeChoice]))),"<span style='width:75px;'>Choice 1</span>","<img src='/_layouts/images/blank.gif' height='0' width='75px'>")

If value or option 1 (Choice 1) is found then add a span tag with a set width and return the value, else if false it returns a blank.gif with a set width and height. Just setting a column width did not produce the same results. This image was necessary to retain the width for empty or blank cells. Now the formula is applied for each option (Choice). When the value is found return the value, else return the blank image.

="<DIV><table Class='table ms-vb2'><tr>
<td class='name'>"&IF(Name<>"",Name,"")&"</td>
<td class='opts'>"&IF(NOT(ISERROR(FIND("Choice 1",[WorkTypeChoice]))),"<span style='width:75px;'>Choice 1</span>","<img src='/_layouts/images/blank.gif' height='0' width='75px'>")&"</td>
<td class='opts'>"&IF(NOT(ISERROR(FIND("Choice 2",[WorkTypeChoice]))),"<span style='width:75px;'>Choice 2</span>","<img src='/_layouts/images/blank.gif' height='0' width='75px'>")&"</td>
<td class='opts'>"&IF(NOT(ISERROR(FIND("Choice 3",[WorkTypeChoice]))),"<span style='width:75px;'>Choice 3</span>","<img src='/_layouts/images/blank.gif' height='0' width='75px'>")&"</td>
<td class='opts'>"&IF(NOT(ISERROR(FIND("Choice 4",[WorkTypeChoice]))),"<span style='width:75px;'>Choice 4</span>","<img src='/_layouts/images/blank.gif' height='0' width='75px'>")&"</td>
<td class='opts'>"&IF(NOT(ISERROR(FIND("Choice 5",[WorkTypeChoice]))),"<span style='width:75px;'>Choice 5</span>","<img src='/_layouts/images/blank.gif' height='0' width='75px'>")&"</td></tr>
</table></DIV>"

Applying styles also help with control of the table output. Without the styles the formatting does not match up. Here are the styles used for this table output.

<style type="text/css">
.opts {
width:75px;
border-right:1px solid #c0c0c0;
padding:2px;
background-color:transparent;
}
.name {
width:100px;
border-right:1px solid #c0c0c0;
padding:2px;
background-color:transparent;
}
.table {
background-color:transparent;
border-collapse:collapse;
}
</style>

Today we discussed the formula to output choice multiple select across multiple columns and the styles applied for the table. In the wrap up I will discuss how to put all the pieces together and also how to break the header cell up to have matching headers for each column.

Explore posts in the same categories: jquery