Replacing Multi-select Lookup field with Checkboxes

The Situation

In SharePoint 2010, when you create a lookup field that allows for multiple selections, you end up with a very non-standard html widget that includes 2 boxes for moving “possible” values to “selected” values. Like so:

Out of the box Multi-select UI

Out of the box Multi-select UI

That’s all well and good if you have short values and a long list of them. But, if you happen to have long values and a short list of them, SharePoint’s UI choice is less than spectacular. As you can see in the image, most of the values are hidden in the select box. Marc Anderson has a function in his popular SpServices library to resize the select boxes to show the complete value. But, that’s not always preferable – as that can make your page very, very wide.

The Solution

Wouldn’t it be nice if you could just make a checkbox interface instead of the multi-select interface? Why, yes, yes it would. And now you can. There are a few moving parts here. I’ll try to break it down.

First, we need a way to select and unselect choices in the multi-select UI. It’s not as easy as just highlighting a value and using jQuery to “click” the add or remove button. Believe me, I tried that. Happily, I found someone else that grappled with this issue, and he got me most of the way there. I had to tweak a couple of things, because I had multiple multi-select fields on a single form, and because I know my users well enough to know that they’ll go crazy checking and unchecking things. So, here are my add choice and remove choice functions.

Add Choice

function addChoice(text, columnName) {

	// let's get the id from the column name
	var thisID = $("[title='" + columnName + " possible values']").prop('id');
	thisID = thisID.substr(0, thisID.indexOf('_SelectCandidate'));
	selector = "[id$=" + thisID + "_MultiLookupPicker]";
    $("[title='" + columnName + " possible values'] option").each(function () {   
        if ($(this).text() == text) {
            $(this).appendTo($("[title='" + columnName + " selected values']"));

            var multilookupPickerVal = $(selector).val();
            if ($(selector).val() == undefined || $(selector).val().length == 0) {
                $(selector).val($(this).val() + "|t" + $(this).text());
            }
            else {
                $(selector).val(multilookupPickerVal + "|t" + $(this).val() + "|t" + $(this).text());
            }
        }
    });
}

Remove Choice

function removeChoice(text, columnName) {
// let's get the id from the column name
	var thisID = $("[title='" + columnName + " selected values']").prop('id');
	thisID = thisID.substr(0, thisID.indexOf('_SelectResult'));
	selector = "[id$=" + thisID + "_MultiLookupPicker]";

	// loop through the selected options
    $("[title='" + columnName + " selected values'] option").each(function () {

        if ($(this).text() == text) {
            $(this).appendTo($("[title='" + columnName + " possible values']"));

            var multilookupPickerVal = $(selector).val();

            // call the internal function which creates a clean array out of the weird string
            var splitValue = GipSplit(multilookupPickerVal);

            // set the 2 array nodes we want to remove
            var valToRemove = $(this).val();
            var textToRemove = $(this).text();

            // Kill the value and text in the array
            splitValue = jQuery.grep(splitValue, function(value) {
        		return value != valToRemove;
      		});

      		splitValue = jQuery.grep(splitValue, function(value) {
        		return value != textToRemove;
      		});

      		var newValue = '';

      		// loop through the cleaned up array and rebuild the selector value
      		for (var i = 0; i < splitValue.length; i++) {
      			if (newValue.length == 0) {
      				newValue = splitValue[i];
      			}
      			else {
      				newValue = newValue + '|t' + splitValue[i];

      			}

			 } // end for loop

          	// set the new value to the selector
            $(selector).val(newValue);

        }
    });
}

Next, we need a function to draw the checkboxes to the screen. I wanted to make this as simple as possible, so this same function draws the checkboxes, hides the old UI, and attaches the remove and add choice functions to the click events of the checkboxes.

Draw Checkboxes

// function used to draw the checkboxes instead of a multi-select lookup
function drawCheckboxes(columnName) {

	// remove spaces from columnName
	var divName = columnName.split(' ').join('') + 'Checkboxes';

	// find the parent td, hide the span, clear the div
	$('div[id="' + divName + '"]').remove();
	$("[title='" + columnName + " possible values']").closest('span').after('<div id="' + divName + '">New div</div>');
	$("[title='" + columnName + " possible values']").closest('span').hide();
	var thisDiv = $('div[id="' + divName + '"]').html('');

	// loop through all the possible options and draw the checkboxes
    $("[title='" + columnName + " possible values'] option").each(function () {
        var thisText = $(this).text();
        var thisVal = $(this).val()
        var thisSnippet = "<input type='checkbox' name='" + columnName + "' value='" + thisText + "' id='" + columnName + "Checkbox" + thisVal + "'/><label for='" + columnName + "Checkbox" + thisVal + "'>" + thisText + "</label><br/>";

        thisDiv.append(thisSnippet);
    });

    // loop through all the selected options and draw the checkboxes
    $("[title='" + columnName + " selected values'] option").each(function () {
        var thisText = $(this).text();
        var thisVal = $(this).val()
        var thisSnippet = "<input type='checkbox' name='" + columnName + "' value='" + thisText + "' id='" + columnName + "Checkbox" + thisVal + "'/><label for='" + columnName + "Checkbox" + thisVal + "'>" + thisText + "</label><br/>";

        thisDiv.append(thisSnippet);
    });

    // Loop through the checklist for outcome statement lookup 

	var boxes = $('input[name="' + columnName + '"]');
	boxes.each(function(index) {

	$(this).click(function() { 
		// get the label

		if( $(this).is(':checked')){
			addChoice($(this).val(), columnName);
		}
		else {
			removeChoice($(this).val(), columnName);
		}
	});

	});  // End loop

} //end draw checkboxes

The basic solution

Great – now we have functions that will draw checkboxes and add and remove choices when the checkboxes are clicked. What do you do with it? First put all your scripts together in a text file and upload your text file to your site. (Note, there is one line you’ll need to edit in this text file.) (I typically use the site assets library.) On your page with your data form web part, you’ll need to include a content editor web part and link to your text file.  Run your page, and voila – checkboxes! (Note that in this image, I’ve shown both interfaces.)

Showing both the standard and checkbox UI.

Showing both the standard and checkbox UI.

Of course, around here, we’re integrating this with SPServices, to filter the multi-select. For that, you need a few more steps. First, you’ll need a copy of SPServices in your site, and you’ll need to include a reference to SPServices in your text file. Next, you’ll need to make a function that will work as a call-back to the SPServices.SPFilterDropdown or SPCascadeDropdowns function. Functions passed as callbacks can’t have parameters (at least, I haven’t figured out how to do it). So, you’ll need a wrapper function for drawCheckboxes that passes in the column you want. This is convenient because you can also do other stuff at the same time, if need be. Here’s my example, using a filter.

Wrapper Function

// need this function to pass a parameter-free function to the complete function of the dropdown filter
function drawProgramAreaCheckboxes() {
	drawCheckboxes("Collaborators Program Area");	
}
//end drawProgramAreaCheckboxes

Calling the SpServices Filter function with callback

//Filter and draw checkboxes for program areas
$().SPServices.SPFilterDropdown({
  relationshipList: "Program Area",
  relationshipListColumn: "Title",
  relationshipListSortColumn: "ID",
  columnName: "Collaborators Program Area",
  CAMLQuery: " 1",
  completefunc: drawProgramAreaCheckboxes,
  debug: true
});

//End program areas

Here’s the complete file for using checkboxes with SpServices. Note, there are multiple parts you’ll need to customize in here.

Editing Forms

Now that we have adding forms figured out, we need to do something about editing. It’s essentially the same, but you need to set the checkboxes that require setting. Again, for this, we’re using SPServices, and we need a few more function calls.

Step one – helper functions to set checkbox values

We need a couple of helper functions to set the checkboxes. The first sets a checkbox’s selected property to true based on the value of the checkbox.

function checkByValue(value) {

	$(":checkbox").filter(function() {
        return this.value == value;
    }).prop("checked", "true");

}

The second takes in a string as returned by spSpervices GetListItems for multi-select fields and the column name and loops through the string, setting each checkbox. The string returned is a series of ID’s and values, separated by semi-colons and hash tags. So, we’ll split the string on the ;# combo, and then check only when we have a value, not a number. Caveat – if your values ARE numbers, this won’t work for you, and you’d need to do check every other value, instead of the textual values.

function setMultiSelectValueCheckboxes(inString, column) {

 	var outString = inString.split(";#");

 	$.each(outString, function( index, value ) {

		  if (isNaN(value)) {

		  	checkByValue(value);
		  }
	});

} // end set MultiSelectValueCheckboxes

Step two – get the ID from the query string.

SpServices has a helper function that lets you get parameters from the query string. SharePoint always passes an ID parameter to edit forms. So, you just need to grab it, like so:

// This function from SPServices gets all of the Query String parameters 
  	var queryStringVals = $().SPServices.SPGetQueryString();
  	var effortID = queryStringVals.ID;

Step three – build the checkboxes

Just like in the add scenario, you’ll need to build your checkboxes. You want to build them before you try to check them, or this won’t work. For brevity, you can refer to the completed text file for this section, since it’s no different than the add form.

Step four- use GetListItems to get the thing you’re editing

My test list is called “Educational Effort.” Here I’m passing in a CAML query to get just the item we’re editing. I’ve returned more viewfields than you’d really need. But, you get the idea. If we have a successful status, we then set the value of the checkboxes, using our helper function from above.

// Get the related effort
		$().SPServices({
		    operation: "GetListItems",
		    async: true,
		    listName: "Educational Effort",
		    CAMLQuery: "" + effortID + "",
		    CAMLViewFields: "",
		    completefunc: function (xData, Status) {

				if (Status == 'success') {

				$(xData.responseXML).SPFilterNode("z:row").each(function() {

 				// get outcome statement and program area values
		        var outcomeStatementLookup = $(this).attr("ows_OutcomeStatementLookup");
		        var collaboratorsProgramArea = $(this).attr("ows_CollaboratorsProgramArea");

		        // set checkboxes
				setMultiSelectValueCheckboxes(outcomeStatementLookup, 'Outcome Statement Lookup'); 
 				setMultiSelectValueCheckboxes(collaboratorsProgramArea , 'Collaborators Program Area'); 

		      });	// end each function	
		      } //end if status = success.		
		    } // end complete function
		  }); // end spservices
// End get the related effort

And, voila – automatically checked checkboxes.

Edit page with auto-checked checkboxes.

Edit page with auto-checked checkboxes.

Here’s the complete file for an edit form. Again, note that you’ll need to customize for your environment.

As usual, feel free to point out places where this could be optimized, tweaked, etc. I’ve testing in IE 10, Chrome and Firefox, and it seems to be working for me.

Linking a Multi-Select Lookup Column (XSLT Solution)

The Problem

A common issue in SharePoint 2010 is dealing with linking 2 lists via a look-up column. That’s easy-peasy.

But, what happens when you want to display a view of a record in the main list? By default, you get a semi-colon delimited list of the items from the look-up list, each linked to their record in the look-up list.

Here’s what the standard behavior looks like:

The standard behavior - opening the related item's display form.

The standard behavior – opening the related item’s display form.

That might be fine in some instances, but what if you want to do something a bit more user-friendly? For example, what if you want to link each look-up item to a page that displays the look-up item’s data and some related data?

Well, you could do that by customizing the standard display page of the look-up table. But, if you do that, you’ll still get the edit ribbon for the look-up item. I’m not a huge fan of the edit ribbon.

I’d rather my users have a more standard web experience. So, I want to link each look-up item to a custom web part page, that pulls in data based on the ID of the item.

The Solution

The best way that I’ve found to do this is to create a custom XSL template, and apply it to the output of your multi-select look-up column in a Data View Web Part. There are a couple of things to understand about how multi-select look-up columns work.

  • For each column of this type, there are 2 data fields returned in a DVWP datasource. One of the fields includes the links to the item’s display form. The other field does not.
  • The field you want to use with the custom XSL Template below is the one that does not already contain links.
  • The field you want will always be the field name with a period (.) appended to the end.
  • This field will contain data that looks like so: ID;Title – where the ID is the numeric ID of the item and Title is the actual title of the item. (or whichever field you used for the look-up).

The always helpful Marc Anderson already had a great sample template out there for handling a similar problem. I just took his template 1 step further.

Here’s the code:


<xsl:template name="MultiSelectDisplay">
<xsl:param name="MultiSelectValue"/>
<xsl:param name="MultiSelectDelimiter"/>
<xsl:param name="MultiSelectSeparator"/>
<xsl:param name="Link"/>
<xsl:param name="ThisID" select="0" />
<xsl:choose>
<xsl:when test="contains($MultiSelectValue, $MultiSelectDelimiter)">
<xsl:variable name="before" select="substring-before($MultiSelectValue, $MultiSelectDelimiter)"/>
<xsl:choose>
<!-- if we have a number, pass it back, but don't display anything -->
<xsl:when test= "string(number($before))!='NaN'">
<a>
<xsl:attribute name="href">
<xsl:value-of select="$Link"/><xsl:value-of select="$before"/>
</xsl:attribute>
<xsl:call-template name="MultiSelectDisplay">
<xsl:with-param name="MultiSelectValue" select="substring-after($MultiSelectValue, $MultiSelectDelimiter)"/>
<xsl:with-param name="MultiSelectDelimiter" select="$MultiSelectDelimiter"/>
<xsl:with-param name="MultiSelectSeparator" select="$MultiSelectSeparator"/>
<xsl:with-param name="Link" select="$Link"/>
<xsl:with-param name="ThisID" select="$before"/>
</xsl:call-template>
</a><br/>
</xsl:when>
<!--Otherwise, display and then pass back -->
<xsl:otherwise>
<xsl:value-of select="concat($before, $MultiSelectSeparator)" disable-output-escaping="yes"/>
<xsl:call-template name="MultiSelectDisplay">
<xsl:with-param name="MultiSelectValue" select="substring-after($MultiSelectValue, $MultiSelectDelimiter)"/>
<xsl:with-param name="MultiSelectDelimiter" select="$MultiSelectDelimiter"/>
<xsl:with-param name="MultiSelectSeparator" select="$MultiSelectSeparator"/>
<xsl:with-param name="Link" select="$Link"/>
</xsl:call-template>
</xsl:otherwise>
</xsl:choose>
</xsl:when>
<!-- Display the last item in the list -->
<xsl:otherwise>
<xsl:value-of select="$MultiSelectValue"/>
</xsl:otherwise>
</xsl:choose>
</xsl:template>

Or, in a pretty picture, because WordPress doesn’t do code includes very well:

Image Version of the Template - pretty colors!

Image Version of the Template – pretty colors!


To call the code, in your DVWP, include this bit, passing in the relevant parameters:

<xsl:call-template name="MultiSelectDisplay">
<xsl:with-param name="MultiSelectValue" select="@Audience1."/>
<xsl:with-param name="MultiSelectDelimiter" select="';#'"/>
<xsl:with-param name="MultiSelectSeparator" select="'<br />'"/>
<xsl:with-param name="Link">/tools/wnepresources/SitePages/Resources%20by%20Audience.aspx?AudienceID=</xsl:with-param>
</xsl:call-template>

And, again – the pretty picture of the code version:

How to call the XSLT from within your DVWP.

How to call the XSLT from within your DVWP.

When you include the “MultiSelectDisplay” template in the DVWP’s stylesheet, and call it, you will get output like so:

Finished Solution - 1 item per line, linked to a custom page.

Finished Solution – 1 item per line, linked to a custom page.

Happy Coding!

Removing the Comma from a Group Header in a SharePoint 2010 List View

This Situation

You have a list with a “year” column of type numeric, so that it will do a proper numeric sort. (While you can use calculated columns to format numeric columns like a text column, you lose your numeric sorting capabilities.) You have a view grouped by year. SharePoint displays the year with a comma. Like so:

Year Displayed with a Comma

Year Displayed with a Comma

The Solution

It’s time to open SharePoint Designer. Click “Lists and Libraries” in the Site Objects panel. Click your list. Find your view in the “views” panel. Click your view to edit it. Find your header. Right click the header and select “Edit Formula.”

Right Click Your Year and Select "Edit Formula"

Right Click Your Year and Select “Edit Formula”

Set your formula to be:


translate(@*[name()=$fieldtitle], ',', '')

Like so:

Edit Your Formula to Add the Translate Function

Edit Your Formula to Add the Translate Function

And, that should do it!
This adds a lot of XSLT to your page that is designed to allow for different types of group headers. In this case you know what your group header is going to be, and you could probably clean up some of the XSLT for faster performance. I’ve yet to do that, though. If you do, let me know!

Note: If you need to just remove the comma in a column of data (not a header) the approach here works. His number-format formulas did not work for me in the header.

Assigning Permissions to a Group Via SPD Workflow

The situation

You have a list or library. You want all authenticated users to be able to add items to the list. But, once the item is created, you need to limit permissions to a certain subset of users, based on a column in the list or library. In my example, I have an event registration list. When an item is added to the list, permissions should be restricted to a county security group based on the county chosen in the event registration.

The Solution

    1. Set up your county list. It should have 2 columns: title, group. Group is person column set to the security group.

      Look-up List - title and group columns

      Look-up List – title and group columns

    2. Set up your event registration list. Include a county column which is a lookup to the list you created in step 1. I based my list off of a content type, so that I could do a reusable workflow. You can also use a list workflow, though.

      The main list - including the lookup column.

      The main list – including the lookup column.

    3. In SharePoint Designer, create a workflow. Create an impersonation step. (This needs to be done outside step one. Also, it’s best practice to create a workflow user that has the appropriate permissions and to log in to SPD with the workflow user to author the workflow. Impersonation steps run as the workflow author.)
    4. Inside the Impersonation Step, click Action -> Set a Workflow Variable.
      1. Name your variable “County” to match your lookup list.
      2. Click on “value” to set the value. Click the “fx” button.
      3. In the “Find Data To Retrieve” section, set the Data source field to your lookup list. Set the Field from Source field to the security group column in your lookup list. Set the Return field as to “As String.”
      4. In the “Lookup for Single Line of text” section, set the Data source to “Current Item.” Set the Field from source field to your lookup column in the current item list (county in my case). Set the Return field as field to “Lookup Value (as Text).”
Using the lookup field to set the security group variable.

Using the lookup field to set the security group variable.

    1. Select Action -> Replace List Item Permissions. Click “these permissions.” Click “Add.” Click the permissions you want to replace. In my case, it was “Contribute.” Click “Choose.” Click “Workflow lookup for a user.” In the “Field Data to Retrieve” section, set the Data source to your lookup list. Set the Field from source to your “Group” column. Set Return field as to “String.” In the “Find the List Item” section, set your field to your “Group” field. Click the “fx” button. In the “Lookup for Person or Group” dialog, set your Data source to “Workflow Variables and Parameters.” Set your Field from source field to the variable set in step 4. Set Return field as to “As String.” Click “ok” on all dialog boxes. Click “this list” and select “Current Item.” Repeat these steps for each set of permissions you want to replace.

      Return the security group matched to the local variable.

      Return the security group matched to the local variable.

    2. Your complete workflow should look something like this. (Note, I added a couple of log messages not defined in the steps. Feel free to add them or leave them out.

      Complete Workflow

      Complete Workflow

    3. Publish and test your workflow. I set mine to run automatically whenever a new item is created. Test your permissions.

Creating Open & Close Dates for a Browser-based InfoPath Form in SharePoint 2010

The Situation

We often use forms to collect data. Sometimes, we need to limit the time during which people can submit data to a certain set of open and close dates. It’s certainly possible to go change permissions on the underlying list or library to take away contribute permissions when outside the open time period. But, that relies on a person remembering to do it on time, and it’s confusing for people to have the UI change (no more “add new item” option) with no explanation of what’s going on. Also, when building forms for less tech-savvy site owners, changing permissions can be confusing.

The Solution

Using a combination of SharePoint lists and InfoPath forms, it’s possible to create a solution that is easy for site owners to manage and gives clear and concise information to end-users.

The Set Up

You’ll need 2 lists.

The Open/Close List.

Create a custom list. I called mine “Open and Close Dates.” This list should have 3 columns – the title, the open date (date & time column) and the close date (date & time column). The title will be used to filter the open/close dates when we pull them into the InfoPath form, so that you can open and close more than 1 form with the same list.

The list for setting the open and close dates.

The list for setting the open and close dates.

The form list

In my case, I’m using a custom list with a custom content type. But, it should work for any custom list.  There are a couple of important settings here. First, on list settings -> advanced settings, make sure that Datasheets are disabled. Otherwise, users will be able to circumvent your InfoPath form.

Make sure bulk edit is not allowed.

Make sure bulk edit is not allowed.

Second, since none of this magic happens without InfoPath, we want to click the “Customize Form” button to customize the form used with the list in InfoPath Designer.

Click the "Customize Form" button to open InfoPath Designer

Click the “Customize Form” button to open InfoPath Designer

The InfoPath Magic

Once in InfoPath, there are a few steps to get this all working.

  1. Set up a data connection to your “Open and Close Dates” list. (For step-by-step directions with pictures for creating a connection to a SharePoint List, check out Wonder Laura’s post.)
    • Click the “Data” tab and click “From SharePoint List”.
    • Enter the site URL.
    • Select your open/close list.
    • Check off the following: Title, Open Time, Close Time (sorting is irrelevant in this case)
    • Name your data connection whatever you’d like.
  2. Create Open and Closed fields and set their default values
    While we can pull the open and closed fields from the data connection we created in step one, there are places where we can’t filter on the fly if we have more than one value in the open/close list. So, we need to create open and closed fields in our main data connection, or in a dummy one. I did it in the main data connection, because I don’t really care if we have a couple extra columns of data in our form list. But, if you’d prefer to set up a dummy data connection, John Liu has a great post on doing that. Here’s what I did.

    • In the main data connection, click “Add Field”.

      Add a Field to the Main Data Connection

      Add a Field to the Main Data Connection

    • Set the display name to “open.”
    • Click the “fx” button for the default value.
    • Select “Insert Field or Group.”
    • Click “Show Advanced View.”
    • Choose the Open and Close Dates datasource.
    • Open the “dataFields” folder.”
    • Select “Open Time.”
    • Click “Filter Data.”
    • Click “Add.”
    • Select “Title” in the first drop down and set it equal to whatever text you set in as the title in the list.
    • Click “Ok” on all the open dialog boxes.
    • Your field properties should look something like this:

      Example of the "open" field.

      Example of the “open” field.

    • Repeat process to create a “closed” field.
  3. Create a “Closed” view.
    • Click the “Page Design” tab.
    • Click “New View.
    • Enter a name. (I called mine “Registration Closed.”)
    • Type whatever “form closed” message you want. Mine pulled in the open close dates and displayed them, like so:

      Sample "Closed" View

      Sample “Closed” View

  4. Set Form Load Rules
    Steps to set a form load rule to switch views to "closed form" view.

    Steps to set a form load rule to switch views to “closed form” view.

    • Click the “Data” tab.
    • In the “Rules” section, click “Form Load.”
    • Click “New” and select “Action.”
    • In the “Conditions” section, click “None” to set conditions.
    • You need 3 conditions: open > now() OR close < now AND title is blank. (Open and Close are the fields in the main data connection that you set up in Step 2. Ensuring that title is blank means this will only run when a new form is loaded.)

      Form Load Conditions

      Form Load Conditions

    • In the “Run these actions” section, click “Add” and select “Switch Views.”
    • Select your “Registration Closed” view.
  5. Add a Submit Button to the “Edit Item (default)” View
    In our next step we’re going to remove the InfoPath buttons. So, our users need a way to submit their data, but only when the form is “open.”

    Add a Submit Button inside a section. Hide the section when form is closed.

    Add a Submit Button inside a section. Hide the section when form is closed.

    • At the bottom of the form, add a new section. (Home Tab -> Controls -> Containers -> Section)
    • In this section, add a button.
    • Right click on the button and select “Button Properties.”
    • Set the Action to “Submit.” You can modify the label if desired, or leave it as the default.

      Submit Button Properties

      Submit Button Properties

  6. Hide the Submit Button Section when form is closed.
      • Click on the section tab, and click “Manage Rules.”
      • Click “New” and select “Formatting.”
      • Click “None” under “Condition” to open a menu for setting conditions.
      • Here we need 2 (or possibly 3) conditions. The 2 that are a must are: open > now() AND close < now(). If you have an administrator that should always be able to edit data, you’ll want to add a third condition. This one is “OR xdUser:get-Username() != “youradminusername.”
    Set the rules to hide the submit button section when closed and not being viewed by an admin.

    Set the rules to hide the submit button section when closed and not being viewed by an admin.

    Check the “Hide this control” checkbox.

  7. Create a “Registration Closed” notice on the form.
    Create a section at the top of the form to indicate closed status.

    Create a section at the top of the form to indicate closed status.

    • At the top of your form, create another section. (Home Tab -> Controls -> Containers -> Section)
    • Add a message to indicate registration is closed. Mine says, “Registration is currently closed. You can not submit or edit registrations at this time.”
    • Click on the section tab, and click “Manage Rules.”
    • Click “New” and select “Formatting.”
    • Click “None” under “Condition” to open a menu for setting conditions.
    • On this section, you need the opposite conditions as the submit button, because you want this one hidden when the form is open. So, you need: open < now() AND close > now(). If you don’t want the administrator to see it, then you need the expression “xdUser:get-UserName() != ‘YourAdminUser’.”

      Conditions for the form closed notification.

      Conditions for the form closed notification.

  8. Remove the Infopath buttons.
    • Click the “File” tab.
    • Click “Form Options.”
    • Click “Web Browser.”
    • Uncheck “Show InfoPath commands in Ribbon or toolbar.

      Uncheck InfoPath Commands in the Form Options

      Uncheck InfoPath Commands in the Form Options

The Finished Product

With everything set up, when users come to the site, they will always be able to click the “Add New Item” button on the list, but if data collection is closed, they’ll receive a friendly message with an explanation.

Closed notice displayed when add new item is clicked.

Closed notice displayed when add new item is clicked.

When a user attempts to edit an existing registration, they’ll be notified that the registration is closed, and there will be no submit button for saving changes.

Editing a closed form shows the notice and removes the submit button.

Editing a closed form shows the notice and removes the submit button.

The Caveats

Hey, it’s SharePoint. You know there are some.

What I’d really like to be able to do is to determine the state of the form when it opens. Is it in edit or view mode? If in edit mode & registration is closed, switch to the closed view. If it’s in view mode, don’t. Alas, I’ve yet to be able to figure that one. Got any ideas?

SP2010: Using PreSaveAction on Custom Forms

The Situation

You have a list that has list-level validation. But, you need to use a customized new item or edit item form. The validation routine fires on the server, but the custom new and edit forms do not show the associated error. While you could handle your validation with a workflow or a custom event receiver, there’s a simpler way.

The Work-Around

In addition to the existing list-level validation, you can add client-side validation by hooking into the PreSaveAction function. In my particular case, I had three columns that needed to equal each other. There are several moving parts involved.

  1. In the custom new item and edit item forms, create a div to show or hide errors. Default it to not being shown when the page loads. Include your standard error text. (NOTE: You can modify the error text via your JavaScript function later on, if you have multiple validations to run.)
    <div class="ms-formvalidation FormErrors" style="display: none;">The total contacts by race, ethnicity, and gender must match.</div>
  2. In the custom new item and edit item forms, add a call to the PreSaveAction function in the code on the page. (NOTE: You can’t include this as an external file. It must be on the page itself.)
    <script>
    // Stupidly, this function can't be included in an external file, so we'll call the function here and just return another function from the external file.
    function PreSaveAction() {
    return PreSaveExternalAction();
    }
    </script>
  3. In a text file in SiteAssets, create your corresponding validation function. (Note: your function can not be inside a $(document).ready(function(). It won’t be called if it is. The name of the function in your external file should match the name of the function returned by the PreSaveAction function.)
    function PreSaveExternalAction() {
    if (($('#TotalContactsByRace').val() != $('#TotalContactsByEthnicity').val()) || ($('#TotalContactsByRace').val() != $('#TotalContactsByGender').val())) {
    $('.FormErrors').show();
    return false;
    }
    else{
    //if all is good, return true
    $('.FormErrors').hide();
    return true;
    }
    }
  4. Include a Content Editor Webpart on your custom new and edit item pages which links to your external text file.

    Create a link to the Javascript File

    Create a link to the Javascript File

When you test your page, you should see your validation working. In my TotalContacts.txt file, I do on-the-fly validation as well as the PreSaveAction validation. It’s always a good practice to let people know that there is an error before they try to submit.

Showing the validation error

Showing the validation error

While you could include all your validation in the PreSaveAction function itself, you’d need to repeat your validation code on both the new and edit forms. Anytime I find myself repeating code, I try to find a way to make that piece of code reusable. By having the PreSaveAction function just return another function, we can write that code once, and use it on both the new and edit forms.

Download a copy of the entire TotalContacts.txt file.

Calculated List Columns in InfoPath 2010

The Situation

When you use InfoPath 2010 to customize the forms associated with a SharePoint 2010 list, there are a few “gotchas” when it comes to calculated columns.

  1. Calculated columns don’t show validation formatting and text tips when using browser-enabled forms.
    The Validation Formatting is Missing from Calculated Fields.

    The Validation Formatting is Missing from Calculated Fields.

    This is fairly easy to fix. You can simply change the control to a text box and you’ll get your validation formatting back.

    Change the Calculated Control to a Text Box to Restore Validation

    Change the Calculated Control to a Text Box to Restore Validation Formatting

  2. But, then…. Text boxes bound to calculated columns of type “number” do not conform to the number formatting set in SharePoint.
    Text Boxes bound to calculated columns incorrectly showing decimal places.

    Text Boxes bound to calculated columns incorrectly showing decimal places.

    And, of course, you can’t control the formatting of a text box bound to a calculated column.

    The format button is grayed out on text boxes bound to calculated columns.

    The format button is grayed out on text boxes bound to calculated columns.

The Work-Around

There are a couple of potential work-arounds.

Option 1

Don’t use calculated columns when you’re customizing a list with InfoPath forms.
The benefit of doing this is that you can use text boxes set to read-only in the Infopath form and rules on another field or fields to run your calculations. You get all the formatting control you want, and validation formatting will display correctly. You will be storing a static value instead of having SharePoint run calculations behind the scenes. But, if you’re working with a content type and you’re not sure that every place it’s going to be utilized will be using InfoPath forms, you’re now relying on your users to do the calculations by hand, which is not ideal. And, you have to be very cautious with all your action rules in the InfoPath form to make sure you’re doing your calculations accurately. And, if a calculation changes, you’re updating an InfoPath form instead of a site or list column. Because I’m using content types and all these calculated columns are site columns, I chose to keep them as calculated columns.

Use Form Load rules to format numbers on load.

InfoPath doesn’t seem to have any problems with a new form. The issue only seems to arise with an edit or view form. So, you can add rules on form load that checks to see if “Created” is blank. It will always be blank when a new form is opened. When an edit form is opened, it will not be blank. If it is not blank, format each of your calculated columns.

Format Calculated Columns on Form Load

Format Calculated Columns on Form Load

In my case, I know that InfoPath is just padding my numbers with a decimal point and a bunch of zeros. So, I can use a simple floor() function to knock off all the decimal placings and return a whole number. (I could have also used ceiling().)

The edit form now correctly displays whole numbers for my calculated columns.

After adding form load rules, fields display correctly.

After adding form load rules, fields display correctly.

 

There may be other gotchas I haven’t found yet. Have you found any?

 

Joining the Task List with Related Content in a DVWP

The Situation

SharePoint 2010 lets you set up approval work flows on lists, and stores the approval tasks in the out of the box Task list. But, out-of-the-box, when you view the “My Tasks” view of the Tasks list, all you can see about the content you are supposed to approve is the title, which links to the view form for the item.

Out of the Box View of My Tasks

Out of the Box View of My Tasks

To be able to display more information about the item we are approving, we’re going to create a linked datasource and a DVWP with a joined sub-view. (More information on how to do this can be found in the posts “Display item counts in a Data View Web Part” and “Joined Subviews with Linked Datasources in SharePoint 2010.”)

Unlike in the examples in those other posts, we can’t control how SharePoint manages the look ups between the related content list and the task list. Unfortunately, it seems that the only “look up” provided is an ID at the end of the string in the “Related Content” field. No problem, we can work with that!

The Solution

Go ahead and create your dataview webpart and initially, join a subview based on the ID of the related content  and the WorkflowLink of the Task List. Find the dvt_2 template in your code view. You should see a select for rows that looks something like this:

<xsl:variable name="Rows" select="../../../YourListName/Rows/Row[@ID=$dvt_ParentRow/@WorkflowLink]" />

Change it to the following (replacing “YourListName” with your joined list).

<xsl:variable name="ChildID">
<xsl:value-of select="substring-after($dvt_ParentRow/@WorkflowLink, '=')" />
</xsl:variable>
<xsl:variable name="Rows" select="../../../YourListName/Rows/Row[@ID=$ChildID]" />

What does this code do? First, it creates a variable called “ChildID” and then it extracts the ID of the related content from the WorkflowLink column in the parent task list. Finally, it runs a select on your related content list matching your related content’s ID with the associated Task.

Doing so, you can output a view like so:

A View the Combines Task Information with Related Content

A View the Combines Task Information with Related Content

Voila. Easy way to connect a task list with its related content.

Modify Column Headers in XSLTListViewWebPart (SP2010)

The Situation

You would like to use an out-of-the-box view of a list. But, the list’s column names are not the most end-user friendly. You’d like to modify the column names without losing any of the functionality of a list view web part, such as sorting and filtering. While you theoretically can use SharePoint Designer 2010’s design view to change the column header text, I’ve found it to be fairly unreliable. In other words, when I’ve tried it, I’ve had less than successful results. Fortunately, it’s easy to modify the XSLT to accomplish what we want to do.

The Set Up

In my example, I’m using the “My Tasks” view of a task list that’s associated with an approval workflow. I want my users to be able to quickly get to the the item they need to approve before being asked to approve or reject it. The column name for the associated item is “Related Content” – not exactly an intuitive title for most users. Since I know that in this site, the only thing stored in this task list is the tasks for the approval workflow, I can make this view more specific. We’re going to change “Related Content” to “Review Funding Request” and change “Title” to “Approve/Reject Request.”

To follow along you can use the out of the box approval workflow on any list, using the task list to track tasks.

The Step-By-Step

Assuming you’ve set up your approval workflow, the first step is to get the “Related Content” column in our “My Task”s view. The “My Tasks” view is an out-of-the-box view on the task list. So, navigate to your tasks list, switch to the “My Tasks” view and choose “Modify this View.”

Modify the My Tasks View in the Browser

Modify the My Tasks View in the Browser

Add the “Related Content” column to the view. I made it the first column.

Add the Related Content column to get the link to the associated item

Add the Related Content column to get the link to the associated item

Save your view and open the site in SharePoint Designer 2010. Navigate to the Tasks list, and choose to edit the the “My Tasks” view. Switch to the Design tab and choose to “Customize XSLT” and select “Customize Entire View.”

Switch to Design Tab and Choose to Customize the XSLT

Switch to Design Tab and Choose to Customize the XSLT

Behold you will now have a couple thousand lines of XLST code in your code view. Don’t panic! Most of it can be ignored. You are going to do 2 things – add a custom template, and replace 18 instances of a specific line of code with a call to your custom template. (Find and Replace in SPD2010 is awesome.)

The Custom Template

Basically, instead of just outputting the field’s title, we want to output our custom text if the current field title matches the one we’re trying to replace. I’m replacing 2 field titles, so I’m passing in the current field title as a parameter, and then using a choose statement to do the comparison. If the current field title matches either of the 2 I’m replacing, I return my custom text. If not, I return the field title. Pretty simple. (Note that I begin the name of my custom XSLT templates with “CES” because that’s the organization I work for. You should have your own naming convention that ensures your template names will not conflict with Microsoft’s.)

<xsl:template name="CESDisplayCustomTitle">
<xsl:param name="fieldtitle" />
<xsl:choose>
<xsl:when test="$fieldtitle = 'Title'" >
<xsl:value-of select="'Approve/Reject Request'" />
</xsl:when>
<xsl:when test="$fieldtitle = 'Related Content'">
<xsl:value-of select="'Review Funding Request'" />
</xsl:when>
<xsl:otherwise>
<xsl:value-of select="$fieldtitle" />
</xsl:otherwise>
</xsl:choose>
</xsl:template>

I put my custom templates at the end of the existing XSLT code. To find the right spot, put your cursor in the code view in SPD2010 and search for

</xsl:stylesheet></Xsl>

Insert the custom template just before this code.

(Note: don’t be shocked when you open the listview later and your code has moved. SPD2010 does that. No worries.)

Now, we need to call our template. You’re looking for the following code to replace:

<xsl:value-of select="$fieldtitle" />

And you’re going to replace it with this code:

<xsl:call-template name="CESDisplayCustomTitle">
<xsl:with-param name="fieldtitle" select ="$fieldtitle" />
</xsl:call-template>

(Note: Update the template name to match your custom template name. Also,  make sure to highlight all of the XSLT EXCEPT your custom template when you do your find and replace selection. If you do the find and replace and include your custom template, you’ll get an infinite loop error.)

You should be able to run a find and replace procedure, and then click into the design view to have SPD2010 update the design view with your new column headers.

Perform the Find and Replace

Perform the Find and Replace

And, the finished view of the list, with all functionality intact.

New Column Headers with all Functionality Intact

New Column Headers with all Functionality Intact

Caveat

This doesn’t work for attachment columns. But, it would be easy to handle those, as well. Attachments are output with disable-output-escaping=”yes.” You’d need to account for that in your custom template and then replace all the instances of:

<xsl:value-of select="$fieldtitle" disable-output-escaping="yes"/>

with your call to the custom template. I’m not changing that column name, so I didn’t include that.

Happy coding!

Display item counts in a Data View Web Part

In SharePoint 2010, you can now do inner and left joins in CAML queries. But, if you can manage that via SharePoint Designer I’ve yet to figure out how. (I haven’t given up yet – anyone have any great ideas?) Instead, in SharePoint Designer all of our “joins” happen via XSLT.  It’s not really a “join” at all. Marc Anderson explains all this really nicely. So, if we want to get a count of all the child items in a parent/child relationship, we need to modify the XSLT.

The good news is that it’s actually really easy.

The Set Up

In my example, I have 3 lists – Interview Questions, Program Area, and Skills/Assets. The idea is that we have a list of questions that might be used, and a list of the skills or assets they assess and Program Areas they’re related to. One interview question can be relevant for multiple skills or program areas, so we have a look up columns in the Interview Questions table that allows multiple values from the Skills/Assets list or the Program Area list. Then, on a page in our site, we want to provide links for each skill and program area that goes to a page that filters the Interview Questions by that skill or program area. I’ve already done the skills. We’ll do the program area together.

We want to get item counts for Program Areas

We want to get item counts for Program Areas

So, if you’re playing along, we’ll need 3 lists:

  1. Skills/Asset (1 column: Title)
  2. Program Area (1 column: Title)
  3. Interview Questions (3 columns: Title, Program Area, Skill/Asset)
Columns in the Interview Questions Table

Columns in the Interview Questions Table

Add some data to each of those tables. Be sure to have more skills and/or program areas than you use. (This helps with finding the empty template later.)

Now create a page. This can be a wiki page or a web part page, and it doesn’t really matter what it’s called. I did this right on my site’s home page.

Fire up SharePoint Designer. Before we get into editing the page, we first have to create a linked data source. Microsoft has general information about doing this. But, I created a quick video to show you exactly what to do for this tutorial:

The Solution

Now that we have a linked data source and a page, it’s time to use our data source  in a Data View Web Part (DVWP) on our page.

So, find your page and jump into edit mode. I always use split mode (code and design visible). The first step is to insert an empty Data View Web Part. Place your cursor where you want the DVWP. Click Insert -> Data View -> Empty Dataview.

Insert an Empty Data View Web Part

Insert an Empty Data View Web Part

Click the link that says, “Click here to select a data source.” and select the linked data source we created earlier.

Choose our Linked Datasource

Choose our Linked Data source

The Data Source Details pane on the right side will populate with the columns and data from our linked data source. Select the Title column from the Program Area data source and select “Insert Selected Fields as…” -> “Multiple Item View.”

Insert the Program Area as a Multiple Item View

Insert the Program Area as a Multiple Item View

By default, SPD adds all DVWP’s as tables. For our purpose, we don’t want a table. We want a bulleted list. So, click the Design Tab and choose the Bulleted List style. This is also the point at which you can sort and filter, if you need to do any of that. I’m sorting by Title. You may get a message to “Click to set the display text if no matching items are found.” Go ahead and click it and set it to “(0).”

Setting the Design to Bulleted List

Setting the Design to Bulleted List

Okay, so far we haven’t done anything too impressive. Now is when the fun starts. Position your cursor at the end of one of the Program Area titles, add a space, and then move over to the Data Source Details panel. We’re going to add our Interview Questions Data Source as a Joined Subview. It doesn’t matter which field you choose, as we’re going to delete all the field information anyway. I’m using the title, since it’s a required field. A dialog box will pop up asking you to select the fields on which to join. In our case, we want the Title field from the left row and the “Program_x0020_Area.” field from the right row. You can click “More Fields” to get a secondary dialog box that will show you the actual field values. That can be very helpful. (Note that sometimes SPD will give you a blank dialog box for the joined subview. That’s SPD looking ahead and realizing there are no matches. Try placing the cursor after an item in your main list that you know is used in the look up column.)

Join the Subview

Join the Subview

Now we should see a table inserted after each item in our bulleted list, that displays either the matching rows or our “no match” text. But, wait – nothing matches! That’s okay. We can fix that. If we were using a single select look up, the best way to fix that is to follow these directions. But, since we’re using a multiple select look up, we need to modify the filter on the XSLT.

Click into the code view and do a search for <xsl:variable name=”Rows” select=. You’re looking for the following line of code:

<xsl:variable name="Rows" select="../../../Interview_Questions/Rows/Row[@Program_x0020_Area.=$dvt_ParentRow/@Title]" />

This line of code tells the XSLT to match any rows where the Program_x0020_Area. field equals the parent’s Title field. We’ll never return any rows this way, because they’ll never match exactly. However, the Program_x0020_Area. field will CONTAIN the parent’s Title field. So, we need to change this from an “equals” operation to a “contains” operation. The XSLT contains operation syntax looks like this:

<xsl:variable name="Rows" select="../../../Interview_Questions/Rows/Row[contains(@Program_x0020_Area.,$dvt_ParentRow/@Title)]" />

Change that field and you should see your DVWP update to display a table of rows under each bullet point for which there are matches. We don’t want a table of rows, so let’s modify our XSLT some more. Right below where you found the select statement we just modified, you should find an xsl:choose statement. Like so:

<xsl:choose>
<xsl:when test="$dvt_IsEmpty">
<xsl:call-template name="dvt_2.empty" />
</xsl:when>
<xsl:otherwise>
<table border="0" width="100%" cellpadding="2" cellspacing="0">
<tr valign="top">
<xsl:if test="$dvt_2_automode = '1'" ddwrt:cf_ignore="1">
<th class="ms-vh" width="1%" nowrap="nowrap"></th>
</xsl:if>
<th class="ms-vh" nowrap="nowrap">Title</th>
</tr>
<xsl:call-template name="dvt_2.body">
<xsl:with-param name="Rows" select="$Rows" />
<xsl:with-param name="dvt_ParentRow" select="$dvt_ParentRow" />
</xsl:call-template>
</table>
</xsl:otherwise>
</xsl:choose>

(Note: your template may be named dvt_1. SharePoint Designer increments template names as DVWPs are added to the page. It’s always a good idea to rename these to something more descriptive, such as dvt_[listname].)

The first thing we want to do is remove the call-template to the dvt_2.empty template and replace it with a simple text output of “(0)”. Like so:

<xsl:choose>
<xsl:when test="$dvt_IsEmpty">
(0)
</xsl:when>
<xsl:otherwise>

Next, we want to highlight everything between the <otherwise> tags, and replace it with the the following:

(<xsl:value-of select="$dvt_RowCount" />)

The dvt_RowCount is a variable that SharePoint Designer sets based on the count of the rows returned from our modified select statement.

Finally, delete the rest of the dvt_2 templates. Your complete dvt_2 template should now look like this:

<xsl:template name="dvt_2">
<xsl:variable name="dvt_StyleName">Table</xsl:variable>
<xsl:variable name="dvt_ParentRow" select="current()" />
<xsl:variable name="Rows" select="../../../Interview_Questions/Rows/Row[contains(@Program_x0020_Area.,$dvt_ParentRow/@Title)]" />
<xsl:variable name="dvt_RowCount" select="count($Rows)" />
<xsl:variable name="dvt_IsEmpty" select="$dvt_RowCount = 0" />
<xsl:choose>
<xsl:when test="$dvt_IsEmpty">
(0)
</xsl:when>
<xsl:otherwise>
(<xsl:value-of select="$dvt_RowCount" />)
</xsl:otherwise>
</xsl:choose>
</xsl:template>

And, your rendered DVWP should look something like this:

XSLT Rendering Child Item Counts

XSLT Rendering Child Item Counts

I also added a link to each Program Area Title, which passes the title as a query string parameter to another page.

Add a Link and Pass the Title as a Query String Parameter

Add a Link and Pass the Title as a Query String Parameter

While this tutorial has a lot of steps, once you’ve done it once, you’ll see how easy it really is – 15 lines of XSLT to generate the item count of your joined subview!

Hope that helps you out. And if any one figures out that CAML join syntax in SPD, let me know.

Follow

Get every new post delivered to your Inbox.