In SharePoint 2010, when you have a list that allows attachments and also has required fields, if not all the required fields are filled out, the attachments disappear when the page reloads to display error text. This is a known bug, but you can work around it by doing custom validation with PreSaveAction(), ensuring that all required fields are filled out before submission. But, if you have a people picker field, the resolution of the entered name happens asynchronously, and might not resolve before PreSaveAction runs.
The Solution
By combining SpServices.SPFindPeoplePicker and some custom jQuery, you can make sure that the people picker has resolved before the form submits.
The Code
Assuming you have a people picker field names “Contact Person” use SpServices to get a handle on the field and call checkNames to start resolving the people picker.
var contactPerson = $().SPServices.SPFindPeoplePicker({
peoplePickerDisplayName: "Contact Person",
checkNames: true
});
When a people picker resolves, it adds a span with the class “ms-entity-resolved” to the row. You can then look for that span to make sure that a name has been entered and resolved.
if (typeof contactPerson.row.find("span[class='ms-entity-resolved']").html() == 'undefined') {
contactPerson.row.find("td[class='ms-formbody']").append('
Please enter a contact person. Click the checkmark and wait for the name to resolve before saving your work.
');
errorCount++;
}
The code above will add a descriptive error after the description for the people picker.
Be sure to give your users a descriptive error so that if the people picker resolves in the meantime, they’ll know what to do.
Assuming this is your only required field, here’s the entire PreSaveAction function.
function PreSaveAction() {
//clear the errors
$('div.ms-formvalidation').remove();
// set an error count;
var errorCount = 0;
//check for contact person
var contactPerson = $().SPServices.SPFindPeoplePicker({
peoplePickerDisplayName: "Contact Person",
checkNames: true
});
if (typeof contactPerson.row.find("span[class='ms-entity-resolved']").html() == 'undefined') {
contactPerson.row.find("td[class='ms-formbody']").append('
Please enter a contact person. Click the checkmark and wait for the name to resolve before saving your work.
I also run check names on change of the field following the people picker in my form, which hopefully gives the async function time to return before you get to PreSaveAction. In this example, the following field was a table of radio buttons, and I ran check names on click:
// try to resolve the contact person whenever someone clicks a button in the approval group
$('table[title="Theme"] :radio').bind("click", function(){
//check for contact person
var contactPerson = $().SPServices.SPFindPeoplePicker({
peoplePickerDisplayName: "Contact Person",
checkNames: true
});
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.
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!
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.
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.
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
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”
Set your formula to be:
translate(@*[name()=$fieldtitle], ',', '')
Like so:
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.
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
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
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.
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.)
Inside the Impersonation Step, click Action -> Set a Workflow Variable.
Name your variable “County” to match your lookup list.
Click on “value” to set the value. Click the “fx” button.
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.”
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.
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.
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
Publish and test your workflow. I set mine to run automatically whenever a new item is created. Test your permissions.
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 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.
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
The InfoPath Magic
Once in InfoPath, there are a few steps to get this all working.
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.
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
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.
Repeat process to create a “closed” field.
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
Set Form Load Rules
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
In the “Run these actions” section, click “Add” and select “Switch Views.”
Select your “Registration Closed” view.
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.
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
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.
Check the “Hide this control” checkbox.
Create a “Registration Closed” notice on the form.
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.
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
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.
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.
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?
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.
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>
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>
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;
}
}
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
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
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.
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.
Calculated columns don’t show validation formatting and text tips when using browser-enabled forms.
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 Formatting
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.
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 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
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.
There may be other gotchas I haven’t found yet. Have you found any?
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
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
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
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
And, the finished view of the list, 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.
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
So, if you’re playing along, we’ll need 3 lists:
Skills/Asset (1 column: Title)
Program Area (1 column: Title)
Interview Questions (3 columns: Title, Program Area, Skill/Asset)
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
Click the link that says, “Click here to select a data source.” and select the linked data source we created earlier.
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
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
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
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:
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:
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
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
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.
As I’m working more and more with SharePoint Designer 2010, I’m finding that I’m developing a library of XSLT snippets that might be useful for others. This is one example. (Note, all screenshots are from SPD2010. Click the images to view a larger version.)
The Scenario
You have a list that uses another list as a look up and allows for multiple values. You want to display that list in a Data View Web Part. The out of the box behavior is to display the multiple values as a semi-colon delimited string of text. Like so:
Default Behavior – an unreadable string of text
We can make the text readable by adding a parameter to the select statement – disable-output-escaping=”yes.” That will render a functional list of links.
But, a semi-colon delimited list of links isn’t exactly the most user-friendly presentation.
The Solution
Thankfully, there’s a fairly simple way to fix it, though it does require diving into XSLT. I grabbed the base template from kicktech, and simply implemented it in SharePoint Designer.
In split view, find the XSLT in your page. You can put this extra template anywhere outside of the existing templates. I like to add my templates at the end of the stylesheet. So, search for </xsl:stylesheet></XSL> and paste the following right above it.
<xsl:template name="string-replace-all">
<xsl:param name="text" />
<xsl:param name="replace" />
<xsl:param name="by" />
<xsl:choose>
<xsl:when test="contains($text, $replace)">
<xsl:value-of select="substring-before($text,$replace)" />
<xsl:value-of select="$by" />
<xsl:call-template name="string-replace-all">
<xsl:with-param name="text"
select="substring-after($text,$replace)" />
<xsl:with-param name="replace" select="$replace" />
<xsl:with-param name="by" select="$by" />
</xsl:call-template>
</xsl:when>
<xsl:otherwise>
<xsl:value-of select="$text" />
</xsl:otherwise>
</xsl:choose>
</xsl:template>
Now, find the column where you are outputting your multiple select lookup. It should look something like this: <xsl:value-of select="@YourColumnName" disable-output-escaping="yes"/>
We are going to replace that simple select with a call to our custom template. But, we need to wrap it with variable tags, so that when we’re done, we can once again disable the output escaping. Then, we’ll do a simple select on our new variable. Here’s an example of replacing the semi-colon with a line break (change “@YourColumnName” to the column you want to display): <xsl:variable name="thisRow">
<xsl:call-template name="string-replace-all">
<xsl:with-param name="text" select="@YourColumnName" />
<xsl:with-param name="replace" select="';'" />
<xsl:with-param name="by" select="'<br/>'" />
</xsl:call-template>
</xsl:variable>
<xsl:value-of select="$thisRow" disable-output-escaping="yes"/>
The final result is a one-per-line list of the items selected in the multiple select column.
Final Result – One Item Per Line
You might note that XLST has a built-in translate function that would do the same thing if you’re only wanting to replace one character with another. However, it doesn’t work to replace a single character with a set of characters.
Delving Into Data 