Checking for a Resolved People Picker Before Submit

The Situation

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.
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.

');
errorCount++;

}

if (errorCount == 0){
return true;
}
else {
return false;
}

} // end presaveaction

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
});

});

Advertisements

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?