Bobby's SharePoint World

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
      AND
      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
      OR
      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:

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.

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:

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
   
    
2. Once you have the form opened in InfoPath, click Data Connections (located within Data tab)
   
    
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”
      
       
   3. Click “Finish”
       
4. Add another data connection to your source list
   1. Click “Add”
      
       
   2. Select “Create a new connection” > “Receive Data”. Then click “Next”.
      
       
   3. Select “SharePoint library or list”. Then click “Next”
      
       
   4. Enter the URL of site where the Source list is located in. Then click “Next”
       
   5. Select your Source List. Then click “Next”.
      
       
   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.
        
         
   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”
        
   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
      
       
   3. Click the icon next to Entries
      
       
   4. Select “Filter Data”
      
       
   5. Add your filter criteria here. Click “Add”. Then select your 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.

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:
   
  
   
• When editing the contact, the user will see the following below:
   
  
   

To achieve this, use the following steps:

1. Access the SharePoint Contact List. Then click “Customize Form” on the ribbon above
   
    
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”)
      
       
   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
        
         
   4. Once your design is complete, click 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
          
   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”
      
       
   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
      
       
   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.

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.

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”
   
    
• Create a new Form Load rule
  
   
• 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
   
2. Assign the value with the following:
   1. Click button
   2. Insert function of concat
   3. Replace the inside of the concat function with
          “YOURDOMAINNAME\”,
   4. Then insert function of username
   5. Your formula should like the following below
      
   6. Click OK twice to save your form load rule
      
       
• Publish your form to SharePoint

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

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