One very difficult task in SharePoint is to create a list entry form which gives a properly formatted table view of data, during entry. Here I’ll demonstrate how this can be done to give the form user, an easy way to view the data selected, before submitting the form.. You can compare it with the ASP.NET repeater control, an auto expanding table view of data. This control gives the entered data, a table format which is easy to read. The table auto expands as needed. This article shows how to accomplish this same view of the data using the rules and format controls in InfoPath.
I am using SharePoint 2010 Enterprise and Microsoft InfoPath Designer 2010.
Requirements for this project: A SharePoint list entry form with 4 fields pulling data from another source, in this instance I’m using another SharePoint list. The table must expand from 1 to a known number of maximum entries. The data must be in a table view for ease of reading. To accomplish the requirements, we’ll need to use a combination of rules, list columns, and a very small piece of XML to accomplish. As in all scripted rule controlled, and out of the box functions there are limitations, but we’ll work with what’s available.
Let’s setup our list first.
Now we have our list, so open the list form in InfoPath. I usually delete the entire boiler plate form and start from scratch. I’ve placed all my list columns into my form.
Out of good form design, I typically lead the user through the form by locking each control. I do this until a required control meets its requirements for selected data.
Here the Location control is the only active control when the form is initially opened.
This rule disables the Name (Well Name Selection) drop down until the Location (Location Selection Filter) contains data. The rule is then copied and applied to each additional selection and pointed to the previous entry for validation.
I also usually use some sort of filter to limit the selection list length. In this project, there are over 6000 items in the SharePoint list I’m pulling the “Names” from. Pulling a drop down selection with 6000+ items would immensely slow the selection process. In the form I’ve used a name “Location Selection Filter”. The same data in the list where I’m pulling the data from uses “MeterField” as the column name where the data I need to compare is at.
This means I want the form to display all the “Names” where the “Location” I selected in the form, is equal to the “MeterField” column in the list that contains the “Names”. This causes the drop down to contain only the “Names” for the “Location” selection. It’s a simple filter and easy to build.
InfoPath allows the dragging of fields onto the form. By doing this, InfoPath sets the box to a default size. The default size from InfoPath is 50px in height.
Unless you’ve worked with form designers, you may not realize that this is adjustable by more than just entering a fixed number. This includes not just using the drop down selection, because there is no selection to allow the box to expand. What will end up happening is that data entered will wrap in the box and a slider bar will appear on the right hand side of the box. This is not the results we want. This sounds somewhat silly but just type “auto” in the Height box. The box resets to 1 line and will auto expand as data is entered into the box.
I set up the columns I want in the same way so they auto expand in the form.
To get these fields populated, I use a single rule with 9 actions. The rule is setup on the “Still Down” (Yes/No) selection.
Upon selecting Yes or No, the rule is activated.
Here’s the rule and its actions.
I use the InfoPath concat function to format and join the data in each one of the four columns.
Here’s a problem with using concat, it joins data end to end, so I end up with a continual string of data instead of separate lines. Also, how do you keep each data set submitted from over writing the previous data?
Both of these problems are in the concat line above if you look close enough. The resolution was really easy enough to figure out. To keep the data from becoming one continual string, a CR/LF must be inserted into the concat statement. However, CR/LF or control characters can’t be typed into the concat statement. That would be too easy. Here’s how you get this done. Create a text file with the following content:
<?xml version="1.0" encoding="UTF-8"?>
Rename it to whatever you wish. I named the file CRLF.XML. In your InfoPath form, select the Data tab and Data Connections. Next select add a connection with the Add button. Click Next on the next screen to Create a new connection to > and Receive data. On the next screen, InfoPath needs to know what the data source is. Select XML document. Select Resource Files…, Add, and navigate to the folder where the .XML file is located. Follow the prompts.
Now when building a concat statement that needs line feeds to separate each line of data, use the Insert Field or Group… button.
In the window that opens, in the lower left select the “Show advanced view” option. From there, select the Fields drop down and the CRLF will be displayed.
Selecting it and then selecting the CR/LF that will display the Field selection, will insert a carriage return/line feed into the concat statement. This stops the line wrapping and makes each data set individual lines.
Next problem – each time a new selection is made, the prior data is overwritten. The data is being concat into the field. The solution - why not just concat what’s already in the field? With this simple setup, any data that was already in the field is now appended.
In this instance, all new data ends up at the top of the field. Reversing this would put it at the bottom. IE..concat(Wells Down Column1, @crlf, Well Name Selection).
Now repeat this same action for the Wells Down Column2, 3, and 4 in the rule.
And we end up with this in the form:
This will continue to expand up to the maximum limit of entries allowed in the Wells Down1, 2, 3, and 4 boxes. In this project up to 20 entries can be made. The InfoPath form expands cleanly and maintains an easily viewable format. We’re not done yet though. The selection fields must be cleared otherwise incorrect data may be selected. This is accomplished with the last four actions in the rule.
The only field that is not cleared is the Location. This speeds entry for this project if the location selection is the same for the next entry.
Total time to build once the technique is learned is about 15 minutes. Using the rules, actions, and functions built into InfoPath and adding in the .XML CR/LF capability makes creating good looking InfoPath forms for SharePoint a breeze. The Power User is a highly underutilized strength in the SharePoint field. By learning techniques such as the one outlined here, even small companies can produce highly functional SharePoint entry forms. In future posts I’ll include what I call near application level site design. These use 3rd party workflow extensions, email parsing, and data sync tools to process data, much as an application would. These include a work order system, fleet vehicle management system, fleet pool reservation system, automated security for records and data management, and much more, all without .NET code. This demonstrates that the simpler SharePoint project requirements can be met with current, off the shelf tools, at a lower cost and can typically be deployed in 1/3rd the time.