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?
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.
I recently completed a project that involved joining a list (Appendix A) and a document library (Appendix A documents). Each document, when added to the library, was associated with an item from the list. There were three document types and each item in Appendix A needed to have 3 matching documents in the library. The project administrators needed a quick way to scan for missing documents.
The obvious solution was to put a data view web part on a page with a linked datasource and a joined subview (displaying all the documents associated with each list item).
I was going for something like this:
A report showing items from the list and their associated documents
I figured it should be fairly easy, considering I already had a lookup column on the Appendix A Documents library, linking it to the list.
But, you know, this is SharePoint. It’s never quite THAT straightforward, right? Look what happens when you try to do a joined subview on your lookup column:
The look up column adds extra information - so no match!
The lookup column adds extra information – notable the ID of the item, followed by a semi-colon and a # sign.
Now, if you want to be an XSLT ninja and do some string manipulation, you can get around this issue the hard way. But, the easy way is to just go back to your original lookup column, and use the new SharePoint 2010 feature to “Add a column to show each of these additional fields.” Just make sure that whichever column you’re adding and joining is unique. I used the ID in this example, as that is always unique.
Include A Second Unique Column
Now, go back to your joined subview and select your additional column. Voila!
A master calender is a common request for team sites that have sub-sites. If you are running SharePoint 2010, there’s now a built-in way to do a roll-up master calendar using the calendar overlay feature.
The Steps
At the site on which you want to display the master calendar, either create a calendar list or use the existing team site calendar.
On the Calendar tab in the ribbon, click Calendars Overlay.
Calendar Overlay Button on the Ribbon
Click the New Calendar link to add a new calendar.
Click the New Calendar Link
In the next screen, you will need to provide information about the calendar you are adding (name, description, etc.) plus the URL of the site that houses the calendar. *(Note that the URL should be to the site, not to the calendar itself. SharePoint will find all the possible lists and views once you click the Resolve button after entering the URL address.) If the site has multiple calendar lists or views, you will need to select the one you wish to overlay from the drop down boxes. Check the box next to Always Show. Click OK.
Enter Calendar Information
Repeat Steps 3-4 for each calendar you wish to add.
When all the calendars are added, click “Ok.”
Now all the calendars will show on the main calendar.
Combined Calendar View
The Caveats
You knew there were some, right?
You can only include 10 calendars.
You only have 8 colors to choose from.
You can include calendars that are above the site of the “master calendar” and that are siblings to the master calendar, and even that aren’t in the same site collection (Yay!). But, if the person visiting the site doesn’t have permissions to view the other calendar, you’ll get this error:
Permissions Error
Oops. So, be careful with permissions on your overlays.
If you connect to Outlook on the main calendar, it will not include the overlaid calendars.
If you add a web part of the master calendar to a page, it will not include the overlaid items you have to switch to “calendar” view in the web part properties to see the overlaid items.
You can overlay lists that are not of the calendar type, as long as they have a calendar view. (This isn’t really a caveat – just a good thing to note.)
Delving Into Data 