Put an end to NaN and other tips

When you are using InfoPath you will often come across fields being rendered as ‘NaN’ (Not a Number). This usually occurs when a repeating element does not exist.
I have seen a number of ‘solutions’ to this problem so I thought I would add my two penny worth:
If you convert the number to a string then you cannot get the ‘NaN’ error. Finally you may want to do something with the number so prefix it with a zero:

concat(‘0’,string(<element>))

The number always defaults to zero and you may use round, floor max with the warm feeling that NaN will no longer make an unwanted appearance.

... and what about another tip? The other day I was trying to put a validator on a multi-line textbox to restrict the number of characters, I have done this before but for the life of me I could not remember how I did it. Dutifully I added the regular expression which of course failed, after some puzzling I remembered you can simply use string-length(expression) > 255. Regular expressions in InfoPath should always be a last resort.

Give a bit of feedback

Ever had a InfoPath Form Services form with a simple Submit and Close mechanism but wanted to give the user that little bit more ? Wouldn’t be great if we could display a status message to the user ? Well you could create a separate form view to display a confirmation message or we could just display a SharePoint status message like the one below…

By using a custom link to launch the InfoPath form and including a query string within our source message we can trigger a status message within a content editor web part.

Just add a content editor web part to the page and add a little JavaScript magic:

   1:  <script type="text/javascript">

   2:   

   3:    var l = window.location.href;

   4:    l = l.replace('?','&');

   5:    var p = l.split('&');

   6:    

   7:    for(var i in p)

   8:      if (p[i].indexOf("status=") >= 0)

   9:      {

  10:        var values = p[i].split('=');      

  11:        var value = values[1];

  12:   

  13:        if (value=='ok')

  14:        {

  15:           ExecuteOrDelayUntilScriptLoaded(ShowStatusInfo, "sp.js");

  16:        }

  17:      }

  18:   

  19:     function ShowStatusInfo()

  20:     {

  21:        msg = SP.UI.Status.addStatus("Expense Form", "<img src='/_Layouts/Images/mewa_info.gif' align='absmiddle'> <font color='#AA0000'>Your expense claim was submitted successfully</font>", true);

  22:        SP.UI.Status.setStatusPriColor(msg, "yellow");

  23:     }

  24:   

  25:  </script>

 

 

Launch the page with a query string including &status=ok and hey presto you have a happy user.

Of course the close button will also display the same message but I leave that as an exercise for the reader…

User Conflict & InfoPath

Conflict? Users love InfoPath ! Well they don’t if their changes get overwritten by another user who opened and edited the form at the same time. What we need is some conflict resolution.
Conflict resolution comes in three flavours: None, Optimistic and Pessimistic:

• None, nothing, nada, zilch – this is the default conflict resolution that InfoPath Form Services provides out of the box. One user’s changes will simply overwrite another's.
  
• Optimistic resolution optimistically presumes that no-one would have the audacity to edit and change the form while another is editing it. Before saving the form it checks to ensure the form timestamps match and if they don’t breaks the bad news to user that it cannot save their changes. This is the default mechanism used by SharePoint lists.
  
• Pessimistic resolution takes no chances and locks the form as soon as the user opens it for edit. While this may sound like the obvious answer to our problem it does come with a few issues such as managing the locking. For example, imagine user A opens a form for editing and a lock is put in place. User A suddenly realises it is past 5pm, they close the lid on the laptop and leave work looking forward to their two week skiing holiday. Oops our form is now locked for two weeks. SharePoint implements this mechanism through Check In / Check Out.

So which mechanism should I choose ? If there is only one user editing the forms then no conflict resolution is required. If the form is not substantial or is rarely updated then optimistic should be sufficient. Large or heavily edited forms should take the pessimistic route.

Implementing Optimistic Locking within InfoPath

This is relatively straightforward create a SharePoint library data connection to retrieve the current item’s metadata (include data for the active form only) including the the modified date and who modified it. When the form is opened retrieve the information and store it within the Main data source. On submit query your data source and check the dates match, if they don’t break the news to the user, possibly on another view.

Pessimistic Locking

This is slightly more complicated.
Step 1: On opening the form we need to check that the form has not been checked out to another user, if available we can then present the user with an Edit button (we don’t want to check out the form just for a user wanting a quick look). If checked out to another user we can display a message to the user and disable or hide the edit button.
Step 2: With the form checked out the user can edit their form with confidence. The form could be automatically checked in on Submit or you could provide a Check In button so they can close the form and come back to it.
This can all be achieved without code thanks to the foresight of those chaps and chapesses that implemented the SharePoint web services. You will need to create three data connections:

• FormInfo : The same connection you created for optimistic locking to acquire the Checked Out To property and the name / title of the form.
  
• CheckOut : Add a data connection to the SOAP web service http://<site collection>/_vti_bin/lists.asmx?WSDL and select the CheckOutFile method. The method takes three parameters: PageUrl – the URL to the form; CheckOutToLocal – false; LastModified – this can be left blank.
  
• CheckIn : Add a data connection to the SOAP web service http://<site collection>/_vti_bin/lists.asmx?WSDL and select the CheckInFile method. The method takes three parameters: PageUrl – the URL to the form; Comment – Automatic Check Out; CheckInType – 1 (see link).
  

Add some rules

• Add a Form Load rule to query the FormInfo data connection and set the query field PageUrl for the CheckIn and Check Out data connections.
  
• Add a formatting rule to hide a section containing the checked out message if the FormInfo Checked Out To, Person count = 0 or the Checked Out To, Person, Account contains the current user.
  
• For the edit button we simply need to add an action to query the Check Out data connection.
  
• For the Submit or Check In button we add an action to query the Check In data connection.

That’s it… Well you may want to add a few more rules and potentially some security checks such as checking the SharePoint group the user belongs to (see this excellent article) or getting more information on the user using our old friend the userProfileService.

Note: Since SharePoint 2010 Service Pack 1 there may be issues with performing the above where Claims based security is enabled.