Category Archives: SharePoint 2010

Validate Business Day in InfoPath Date Picker

User in a work setting often needs to exclude weekends and only account for business days when entering information.  This post will demonstrate how to design a SharePoint form that determines whether a business day was selected from the date picker.

  1. Have your Data Field ready
    You’ll need 3 data fields.  1st to capture the date; 2nd to find out the day of the selected date; 3rd to determine whether that day is a business day.  For this post, we’ll use these Data Fields as reference:

    • MyDate (Date field) = to capture user input
    • CalculatedDay (Whole Number field) = to determine the day
    • IsBusinessDay (True/False field) = to determine business day
  2. Assign a formula as a default value in CalculatedDay field
    1. Click fx in Default Value of the CalculatedDay field
    2. Click “Edit XPath (Advanced)”
    3. Paste the following formula (NOTE: I didn’t come up with this. I found it, but lost the reference.  If you know of the person who BRILLIANTLY came up with this, please let me know so I can give the proper respect/kudos/credit.):

      (number(substring(../my:MyDate, 9, 2)) + number(number(substring(../my:MyDate, 1, 4)) - floor((14 - number(substring(../my:MyDate, 6, 2))) div 12)) + floor(number(number(substring(../my:MyDate, 1, 4)) - floor((14 - number(substring(../my:MyDate, 6, 2))) div 12)) div 4) - floor(number(number(substring(../my:MyDate, 1, 4)) - floor((14 - number(substring(../my:MyDate, 6, 2))) div 12)) div 100) + floor(number(number(substring(../my:MyDate, 1, 4)) - floor((14 - number(substring(../my:MyDate, 6, 2))) div 12)) div 400) + floor(31 * number(number(substring(../my:MyDate, 6, 2)) + 12 * floor((14 - number(substring(../my:MyDate, 6, 2))) div 12) - 2) div 12)) mod 7

    4. Replace all of my data source with yours by doing the following:
      1. Highlight the first reference of “../my:MyDate”
      2. Click “Insert Field or Group”

      3. Select your date field then click OK
      4. Repeat the steps and replace all references of “../my:MyDate”
    5. Click “Verify Formula” and you want to see a no error confirmation like shown below

    6. When a date is selected by the user, this field will now generate a number from 0 to 6 that corresponds to a particular day (0 = Sunday; 1 = Monday; 2 = Tuesday; … 6=Saturday)
  3. Create a rule to account for Business Day
    1. Select CalculatedDay field
    2. Click Manage Rules on the ribbon
    3. Add a new action “Mark as Business Day”
    4. Create the following Condition:
        CalculatedDay “is greater than or equal to” 1
        CalculatedDay “is less than or equal to” 5
    5. Add the following action
      • “Set a field’s value”

      • Select field of “IsBusinessDay”
      • Assign the value of true
        1. Click fx
        2. Insert Function
        3. Find and select “true”
        4. Click OK
    6. Click OK
  4. Create a rule to account for Weekend
    1. Select CalculatedDay field
    2. Click Manage Rules on the ribbon
    3. Copy the Business Day rule from above and change the action name to “Mark as Weekend”
    4. Update the Condition to:
        CalculatedDay “is equal to” 0
        CalculatedDay “is equal to” 6
    5. Update the action to:
      • Field = “IsBusinessDay”
      • Assign the value of false
        1. Click fx
        2. Insert Function
        3. Find and select “false”
        4. Click OK
    6. Click OK
    7. The CalculateDay field rules should look as follow

And there you have it.

You now have a Boolean identifier in the IsBusinessDay field that you can use to validate the date input.  One potential scenario is to disable a submit button if the user were to select a non-business-day.  Another, shown in screenshot below, is to leverage an error message and a validation flag on the Date field, should a weekend date is selected by the user.  Here’s the form in Preview mode:


Filtered Lookup Field with No Code

SharePoint Lookup field allows you to render items from another list and displays them in a drop-down choice format. When working with a larger dataset in the source list, you may want to apply a filter to the Lookup field so that it displays only relevant items.

With SharePoint Server 2010 Enterprise, you can do so using InfoPath.

As an example, I have a “Source List” with an “Active” column. With this post, I will demonstrate how to exclude the last 2 list items below that are marked as inactive.

Source List

In another list called “My Demo List”, I have a Lookup field referring to the “Source List” item. By the end of the post, “My Demo List” would have excluded the inactive Source List item like displayed below:

My Demo List

So, here’s how I filtered the Lookup Field:

  1. Go to the corresponding list with the Lookup Field. Then click “Customize Form” on the ribbon
    Customize Form
  2. Once you have the form opened in InfoPath, click Data Connections (located within Data tab)
    Data Connections
  3. Disable automatic retrieval for the data connection of your source list:
    1. Find the source list of your Lookup field values. Then select “Modify”
    2. Deselect “Automatically retrieve data when form is opened”
      Disable Auto Retrieval
    3. Click “Finish”
  4. Add another data connection to your source list
    1. Click “Add”
      Click Add
    2. Select “Create a new connection” > “Receive Data”. Then click “Next”.
      Receive Data
    3. Select “SharePoint library or list”. Then click “Next”
      SharePoint library or list
    4. Enter the URL of site where the Source list is located in. Then click “Next”
    5. Select your Source List. Then click “Next”.
      Select Source List
    6. Select 2 of the following fields:
      • The field that you want to display in the drop-down. In this example, I want to display “Title”.
      • The filter that you want to apply. In this example, I want to filter against the “Active” field.
        Filter Field
    7. Click “Next” twice
    8. Before clicking “Finish”, do the following:
      • Rename your data connection for future references
      • Be sure that this field “Automatically retrieve data…” is selected
      • Then click “Finish”
        Steps Before Clicking Finish
    9. Close the Data Connections window
  5. Change Data source of Lookup field and apply filter
    1. Right click on Lookup Field. Then select “Dropdown List Box Properties”
    2. In “Data source” field, select your new data connection
      Data Source field
    3. Click the icon next to Entries
      Icon next to Entries
    4. Select “Filter Data”
      Filter Data
    5. Add your filter criteria here. Click “Add”. Then select your filter criteria.
      Add Filter Criteria
    6. Click Ok until the Drop-Down List Box Properties is closed.
    7. You’re almost done at this point. You can click Preview to see how you did.
    8. If all is kosher, click “Quick Publish” to save your changes.

How to Design Different Display for View & Edit List Item

There are certain user scenarios that would require to display a SharePoint List Item differently in View mode vs. in Edit mode. Good news is if you have SharePoint Server 2010 Enterprise, this can be achieved within a few steps.

In this post, I will use a standard Contact List as an example and demonstrate how to design different form displays for the View and the Edit mode of the Contact List.

The screenshots below are the end goal:

  • When viewing the contact, the user will see the following below:
    View Contact Display
  • When editing the contact, the user will see the following below:
    Edit Contact Display

To achieve this, use the following steps:

  1. Access the SharePoint Contact List. Then click “Customize Form” on the ribbon above
    Customize Form in Ribbon
  2. At this time, SharePoint will render the Contact List in InfoPath Designer
  3. The next step is to create and design your View List Item display
    1. Start by creating a New View (Click “Page Design” > “New View”)
      New View
    2. Give the new view a name (e.g.: “View item”). Then click OK
    3. Design your form accordingly
      • I recommend exploring the various “Page Layouts Templates” under “Page Design”
      • Once you have the layouts, you can also use the pre-designed table under “Insert” tab
      • Below is a snippet of my design, where I grouped a few Contact fields and added a picture placeholder
        Sample New Form View
    4. Once your design is complete, click Quick Publish Quick Publish in the top left hand corner of InfoPath
  4. Assign the new InfoPath View in the SharePoint List
    1. Go back to SharePoint Contact List. Then click “Form Web Parts” on the ribbon above
      • You should see 6 options
      • To make your changes, access the last 3 options under “Content Type Forms”
      • The difference between the 3 options are as follow:
        • New Form = customizes the display when you create a new list item
        • Display Form = customizes the display when you view the list item
        • Edit Form = customizes the display when you edit the list item
          Click Display Form
    2. For this example, we only need to make a change in the View mode, so select “Display Form”
    3. You should now see a Web Part Page with an InfoPath Form Web Part
    4. Click the down arrow in the InfoPath Form Web Part. Then select “Edit Web Part”
      Edit Web Part
    5. On the right hand side of the page, click the Views dropdown and change from “Edit item (default)” to “View item”. Then click Ok
      Edit Web Part
    6. You should now see your new form design in InfoPath Form Web Part
    7. Click “Stop Editing” on the top left hand corner of the page
  5. Review your changes. At this point, the View and Edit item displays should be different.

Dial # to remove Web Part Title URL

When you add a list or library web part through the SharePoint UI, the web part title is automatically hyperlinked to the SharePoint list or library. For example, in the screenshot below, I have a standard Team Site homepage with a Shared Documents web part. The title of the web part is linked to the Shared Documents Library.

Shared Documents Library and Web Part

If you edit the web part, under the “Advanced” group, you will see Title URL where you can change the hyperlink of the web part title heading to another URL location.

However, if you decide to remove this Title URL value and click OK. SharePoint won’t allow the update. It would instead automatically re-enter the original URL value. To quickly get around that, Edit the Web Part and replace the URL with #

# on Title URL

Once you click OK, you should be all set.

You may ask, “When do I ever need to do this?”

From my personal experience, this small tip might be helpful in a couple of ways. First, if you want to provide a specific view to a SharePoint audience. Hiding the link would allow you to highlight just the specified list view on the SharePoint page.

Another reason to remove Web Part Title URL is if you were to have a custom user permission level that restrict user to “View Application Pages”. The user would otherwise encounter an Access Denied page when clicking on that web part title heading.

InfoPath Gotcha: User Profile Displaying Admin User

One of the most commonly used SharePoint web services through InfoPath is the UserProfileService. This service can render information about the current user accessing the form. (click here if you need to find out more about how to use UserProfileService)

I was developing an InfoPath solution that required the logged-in-user’s Manager and Department information. Naturally, the UserProfileService is a great candidate to achieve this requirement. As a first step, I created the data connection and configured the form to display user’s first name. I verified by previewing the form in InfoPath Designer and saw that my first name was displayed properly. Everything was looking kosher, so I proceeded to publish the form onto SharePoint as a web-based-form. I then opened the form in SharePoint, and instead of seeing my user info, I saw the user info for a SharePoint admin.

InfoPath shows wrong user info

Confused Bobby

This dazed and confused look went on for a couple of weeks as I searched and searched the Interweb for answers. As luck would have it, I had the chance to sit in on Clayton Cobb‘s InfoPath session during SharePoint Saturday The Conference. I approached Clayton with this problem and he instantly knew the remedy.

Apparently in a claims-based-authentication farm, SharePoint Form Services mistakenly displays the service account that manages the application pool’s worker process. This means two things: (1) I’m not completely crazy (2) I need to find a workaround from Clayton. Thankfully there is one.

Here’s the workaround, if your UserProfileService is displaying an admin user account:

  • Disable auto-retrieval of the UserProfileService Data Connection
    1. Modify the Data Connection
    2. Get to the last step of the wizard and deselect “Automatically retrieve data when form is opened”
      Disable auto retrieval
  • Create a new Form Load rule
    Form Load ribbon
  • In the Form Load rule window, select “Set a field’s value” in Actions
  • Set the “Account Name” query field to the current username
    1. Assign your Field by selecting the User Profile data connection > queryFields > AccountName
      Query Account Name
    2. Assign the value with the following:
      1. Click fx button button
      2. Insert function of concat
      3. Replace the inside of the concat function with
      4. Then insert function of username
      5. Your formula should like the following below
        Value Formula
      6. Click OK twice to save your form load rule
        Form Load Rule
  • Publish your form to SharePoint

Now you should be able to see the correct user profile information.

User Profile shows correctly

Moral of this experience. Don’t bother troubleshooting it yourself, just talk to an MVP.

How to Create and Post SharePoint User Training Video

In today’s era of 140 characters of less, the typical users are generally less patient with heavy reading.  Training video can be a very effective tool to help increase user adoption and get your end users better acclimated with using SharePoint.

In this post, I will show how to create a user training video, and make that available to your users in SharePoint:

  1. Know exactly what you’re recording
    Draft the user scenario and write down each step that you will be demonstrating in the video.  You don’t want to second guess yourself midway through recording. Be sure to write a script if you’re including audio.
  2. Select your recording tool
    • If you own a preferred multimedia software (i.e.: Adobe Captivate, Camtasia Studio, etc.), then definitely utilize them to record your video
    • However, there are excellent free screen recording software.  The two that I often use are SMRecorder and CamStudio
  3. Record your training video
    • For best results, you may not want to include your entire desktop.  Focus only on the area that your user needs to see.  For example: you may only show a Document Library and hide the site navigation.
    • Write down the size of the video that you are recording as you may need to refer to them later
    • This goes without saying, but after you’re done recording, be sure to watch your video for quality assurance
  4. Convert recording format to mwv, if need be
    • Many free recording software only produce file in avi format.  If you are using a free software, you would most likely have to convert to a file that SharePoint plays well with (see all acceptable video formats here)
    • Luckily, there are also many free file converters.  I typically use FormatFactory to convert to mwv
  5. Post your training video to SharePoint
    Please note that the steps listed below are only available in SharePoint 2010 Standard or Enterprise.  If you have SharePoint Foundation, you can either use Content Editor Web Part (read details here) or upload to YouTube, then stream the video in SharePoint (watch that tutorial here)
    1. Upload your video to the Asset Library
    2. Edit your page and add the Media Web Part
      1. Edit the SharePoint Page
      2. Inside of the Page Content, click “Insert” > “Video and Audio” icon in the SharePoint ribbon
        Video and Audio icon
      3. Click on the Media Web Part. Then select “Change Media” icon in the SharePoint ribbon
        Change Media icon
      4. Find your Asset Library on the left navigation pane.  Then double click on the training video that you uploaded
        Select Asset
    3. At this point, you should be able to play your video in the SharePoint page.   However, you may want to give more context as the user would only see a film reel image, a title of “Media Web Part”, and a play button.
      Video with no title screen
    4. Here are some additional Media Web Part update that you may want to consider:
      1. Replace the film reel with another image as a title screen
        Create an image for your title screen and upload that image onto the Asset Library.  (See a simple image that I have created as an example).  Click on the Media Web Part and select “Change Image” icon on the SharePoint ribbon. Then find your Asset Library on the left navigation pane, and double click on the image.
        Change Image icon
      2. Replace the video title
        Click on the Media Web Part, then select change the Title
        Change Title
      3. Adjust video size as necessary
        Make sure that the video is not small nor grainy.  You can adjust the video by clicking on the Media Web Part, then change the horizontal and vertical size.  You may need to refer to the size of your video (see step 3 above).
        Change Size


  6. Last but not least, remember to save your SharePoint page

Below is the screenshot of the video that I created and published in SharePoint using all the steps listed above. Hope you found this post useful. And don’t forget to get your free SPC11 shirt from Jeremy Thake!

Published SPC11 Free Shirt Video