Last week I showed you how to use a Word template as a form in a SharePoint library. I kept the form fairly basic, and did not have the time to cover some additional considerations when choosing to use Word to create your forms. This week I will cover a few of those considerations.
The first consideration is that since form text is stored in SharePoint lists, SharePoint 2013 does not support standard text columns of more than 255 characters by default. Sure you can use a single line of text data type or a multiple line of text, both of which default to 255 characters. At first I was surprised by this since I expected the multiple lines of text to automatically support substantially more text than 255 characters because by default, SharePoint 2010 does.
Therefore, when I created the SharePoint metadata in the library where the form would reside, I created the Lesson Learned Description as a multi-line text data type. Granted, I did not read the 2013 properties carefully because I fully expected that I would be able to store long detailed descriptions in that column. After I added the column to the Word form and saved the Word template back to the library, I created my first form record as shown in the following figure.
After entering the data I wanted into the form, I tried to save the data back to the data store, which in this case consists of the metadata columns in the SharePoint document library where I modified the form template. Notice that the value for the Lesson Learned Description column has a dotted red line around it. This means that there is an error with the value. There is nothing to indicate what the error is. However, since the column is a text field, there are very few things that can cause an error. The most likely cause for the error, and in this case the correct cause, is that the value supplied is larger (in total characters) than the program can save back to the data store.
To test this theory, I deleted a few characters from the description and tried to resave the form data. This time, as you can see in the following image, the save succeeded.
But wait just a second, this problem is even easier to solve. Just go back to the library settings and open the column settings for the multi-line column. By default this data type limits values to 255 characters or less. (No it really does not say that.) However, look for the Allow unlimited length in document libraries setting and change the selection to Yes. While you cannot specify an absolute column length in characters, you do have the ability to allow the column to accept as much data as you want to throw at it. (Note, this was done in SharePoint 2013. SharePoint 2010 by default allows multi-line text to be larger than 255 characters.)
However, some of the other column types present more of a problem. For example, it is not possible to surface a hyperlink field from SharePoint in a Word form. Perhaps this is because a hyperlink field actually consists of two values, the hyperlink itself and a description. You also cannot display in the form a multi-selection choice or lookup column. While these fields may appear in the Document Properties panel as shown in the following figure, I have had problems saving the result back to the SharePoint library even if the column in the library is defined as a multi-value choice field (or lookup field).
When attempting to define a multiple value choice data type, I do get the following message when creating the column.
Returning to the question of saving the data, let me show you what happens when you save the form. Unlike InfoPath which just saves an XML file containing the data. The XML definition of the form is saved separately and only once. While this greatly reduces the number of bytes saved with each instance of the form added to the library, it does limit your ability to make changes to the form fields without creating a new content type each time. On the other hand, since the entire Word form including the values are saved with each instance of the form in the library just like any other Word document, it is a simple matter to change the template to add, delete, or change any of the columns on the form. All new forms will use the new template when you create new documents. Furthermore, you can still open the old form instances since they are nothing more than regular .DOCX or .DOC files depending on the form template from which you started.
The following figure shows the first part of the Save As dialog in which I can select where I want to save the completed form. By default, the Current Folder points to the SharePoint library where the Word template has been saved. However, there is nothing to stop me from saving the document in a local drive or network drive. (I can also click on SharePoint to the left of this figure which should also show the current folder at the top of the list on the right.)
If I select the Current Folder, Word opens the Save As dialog as it usually does, but notice in the right panel that the default location points to the SharePoint library. Also note that the form is saved as a regular .docx file. The File Name defaults to XXX.docx where XXX is the first line of text in the template. You will probably want to change this as I have in the following figure. Note however that even here I can select any other location where I have permissions to save files including SkyDrive, Google Drive and other locations.
The last thing I want to point out before ending this week is that when you display the contents of the library, text strings, no matter how long their values may be, do not get any preference during display over columns that have smaller values (in terms of characters). In fact, as the number of columns that SharePoint attempts to display increases, the widths of the columns appear to be controlled more by the name of the column than the data included within the column as shown in the following figure.
If this is a problem because you need to display the data in a report, the easiest solution is to export the data to Excel. Just click on the Export to Excel button in the Connect & Export group of the Library ribbon of the library.
A dialog appears that prompts you whether you want to open the .iqy file or save it. Use the Save option only if you want to copy the file to open the Excel spreadsheet to a different computer.
Opening this file first opens Excel and then loads the data from the current library as shown in the following figure.
At this point, a few simple formatting adjustments to the columns in Excel including possibly the turning off of the grid lines, adding a style, removing some unnecessary columns and you have a presentable report.
Wait a second, what happened to the Lesson Learned Title? Remember that when you export data from a list or library to SharePoint, the export only includes the columns and rows you specify. In this case, the default view displayed the FileName so users could click on it to view the data in the form. However, you probably want to hide this field and display the Lesson Learned Title field. Note also that any filtering on rows carries through to the export. Even if you have your SharePoint view set up to only display pages of 30 rows at a time, export ignores this functionality and exports all rows that match the filter criteria, not just the first 30.
To take this example one step further, you can copy and paste the final Excel spreadsheet into a Word document if it is relatively small. Of course, you could also use Reporting Services to generate a report from the SharePoint library or even use Report Builder with the Excel spreadsheet to create multi-page reports.
Well, that’s it for this time. C’ya next week maybe. I’ll be at the SQL Saturday in Tampa, FL (http://www.sqlsaturday.com/273/eventhome.aspx ). If you are in the neighborhood, stop by to say, ‘Hi!’ I’ll be presenting in the afternoon on building cubes with either PowerPivot or Analysis Services Tabular model (I haven’t decided which way I’ll go yet.)