Setting default values for first row in InfoPath Repeating tables or sections

One of the interesting features that my team worked on recently was to assign the default value to a text box that was inside a repeating section. That’s easy correct? but we had to display this default value only on the first row. All the subsequent rows created by the user should not display the default values.

This is a well discussed topic on the forums, but one of the annoying part of internet forums is that people tend to show the code which is not always indexed correctly by the search engines. Well, we found the solution to this issue, but the actual solution was buried inside the InfoPath form template.

In this post let’s extract this so that it can help others:

Let’s consider our simple example where we would like to display – “Customer Notes should include their preferred contact name”.

  • Let’s start by creating a simple InfoPath template to display the repeating section.

image

  • Navigate to the Field Properties where you would like the default value. (field Notes in our example)

Field Group Properties

  • In order to selectively assign the default value, we can use preceding-sibling XPath expression like below:
substring("Customer Notes should include their preferred contact name", 1, (count(../preceding-sibling::my:Customer) = 0) * 1000)
  • The expression above should be entered in the Default value expression box.

Formula

  • Clicking Verify Formula button will parse the expression and allow you to save the default value.
  • Test the InfoPath template

Preview

Hopefully in future, people in forums post the actual solution so that we don’t need blog entries like this.

Audiobooks January 2013

A new year and a new set of topics to increase awareness about. This month was all about Physics and Cosmology and where better to start then Stephen Hawking’s classic books. Although these books are not for weak hearted, they do pack a lot of information that a novice like me can understand, that said, it helps if you have some back understanding of Physics and its principles.

 A Brief History of Time Universe in a nutshell

A Brief History of Time

A landmark volume in science writing by one of the great minds of our time, Stephen Hawking’s book explores such profound questions as: How did the universe begin—and what made its start possible? Does time always flow forward? Is the universe unending—or are there boundaries? Are there other dimensions in space? What will happen when it all ends?
Told in language we all can understand, A Brief History of Time plunges into the exotic realms of black holes and quarks, of antimatter and “arrows of time,” of the big bang and a bigger God—where the possibilities are wondrous and unexpected. With exciting images and profound imagination, Stephen Hawking brings us closer to the ultimate secrets at the very heart of creation.

Book Link | Audiobook Link

The Universe in a Nutshell

Stephen Hawking’s phenomenal, multimillion-copy bestseller, A Brief History of Time, introduced the ideas of this brilliant theoretical physicist to readers all over the world.
Now, in a major publishing event, Hawking returns with a lavishly illustrated sequel that unravels the mysteries of the major breakthroughs that have occurred in the years since the release of his acclaimed first book.

The Universe in a Nutshell is essential reading for all of us who want to understand the universe in which we live. Like its companion volume, A Brief History of Time, it conveys the excitement felt within the scientific community as the secrets of the cosmos reveal themselves.

Book Link | Audiobook Link

Conditional InfoPath file name based on form fields

InfoPath requires a file name for the forms that are being submitted to the SharePoint library. These file names should be unique (otherwise they will overwrite an existing form when submitted). If you are developing an InfoPath form in an enterprise environment, chances are that you would like to generate these names automatically.

These automatic file names are very easy to generate when you have simple forms that are submitted once but when you have other constraints where you have to generate names dynamically based on the fields in the form (i.e. Date, Department) and the stages of the form (i.e. Draft Stage, Final Stage etc.).

In one of the recent projects, we had a requirement to generate unique file names based on the existing form fields. Pretty easy right? But another requirement was to allow users to Create and Update these forms as often as they would like. Using the standard default values to calculate the file name would not work here, as the value would be calculated every time the form was opened/updated. We could have had static fields with rules or code-behind to achieve this but that would not be a “clean” implementation.

This is where conditional default values can be used. Let’s see how:

Example Setup: For this blog post, I have a simple InfoPath forms with three fields. We will use all these fields to generate a unique file name for the form. The data type of these fields are pretty self-explanatory.

Conditial Default Value Template

Step 1: Create InfoPath fields to store Form Status and File Name

Let’s create new InfoPath fields to store the form status and file name values. These will be internal fields and not displayed on the form. For the purpose of this post, I have created controls in the view, but you can choose to just create the files without associated controls in the view.

  • Create a new text field and name it _FormStatus. This field will be used to store the form stage. (i.e. New, Draft and Final). After creating this field, update the Default value to include a text string of “New”. This will ensure that a correct form status is set when a new form is created.

FormStatus Properties

  • Create a new text field and name it _FileName. This field will be used to store the name of the form.

(note: I use underscores before all internal field names. This allows me to visually group the fields that are not being displayed on the form).

Step 2: Create Conditional File Name

Let’s dive-in to the interesting bit. InfoPath path provides a way to define conditions using expression boxes for default values. Let’s look at the basic first (you can read more about it on the InfoPath Blog):

concat(
	substring(TrueResult, 1, (BoolCondition) * string-length(TrueResult)),
	substring(ElseResult, 1, (not(BoolCondition)) * string-length(ElseResult))
)

We will have to put our values in the above format.

concat(
 substring(
  (concat(normalize-space(translate(CurrentDate, "-", "")), Department, ProjectCode)), 
  1, 
  (_FormStatus = "New") * string-length(concat(normalize-space(translate(CurrentDate, "-", "")), Department, ProjectCode))
 ), 
 substring(., 1, (not(_FormStatus = "New")) * string-length(.))
)

Let’s look at the above code in detail:

The expression below can be used to concatenate all the form fields as one string result. This expression is specified as part of “TrueResult”.

(concat(normalize-space(translate(CurrentDate, "-", "")), Department, ProjectCode))

The expression below is used to specify the “BoolCondition”. When this condition is satisfied, the above TrueResult is returned as the default value. This is where we specify, if the FormStatus is New, a newly calculated default value should be returned. If you remember, we specified the default value of “New” to the _FormStatus field. Now, every time a new form is created, a default value is calculated based on the fields in the form.

(_FormStatus = "New")

The string length function users the same expression of TrueResult. This is required by the conditional expression.

string-length(concat(normalize-space(translate(CurrentDate, "-", "")), Department, ProjectCode))

The expression below forms the second part of the condition. A . (dot) here specifies that the current value should be returned, hence . (dot) becomes the ElseResult. The Boolean condition we specify here when the _FormStatus is not New. Now, every time a form is opened from the server, the currently  value will be returned (and the new value will not be calculated). One other thing to note here is that there is no field named . (dot). You will have to select _FileName) from the “Insert Field or Group…” button and it will be converted to dot after selection.

substring(., 1, (not(_FormStatus = "New")) * string-length(.))

This condition expression should be entered in the default value expression box of the Field Properties:

ExpressionBox

The expression box above can be accessed using the “FX” button from the dialog below. Please make sure that the “Refresh value when formula is recalculated” checked. This will ensure that when users change values while filling the form, the update values are used. If this is unchecked, a blank default value will be returned.

Field Properties

Press OK to save the expression.

Step 3: Prepare form with Submit Data Connection

This is a two part step. The first would be to create a Submit Data Connection, and the second will look at specifying rules on the SaveForm button to submit this data.

Creating a Submit Data Connection

Navigate to Data Ribbon Tab –> Data Connections –> Add –> Submit Data Connection.

  • Enter the location of your Document Library. This is where the forms will be saved.
  • Use the expression box “FX” and select _FileName field from the list of form fields.
  • Select “Allow overwrite if file exists” checkbox. After all this was one of my main requirements.
  • Click Next, Enter the name of data connection and click Finish.

New Submit Data Connection

Creating Form Rules to Submit Data

Now that we have setup our form, we will create rules to submit the form to SharePoint. There are two actions the button has to perform. The first is to set the Form Status to “Draft”. This is required as the status of the form needs to change when it is saved. Additionally, changing the status from New will ensure that the file name is not recalculated when the form is updated at the later stage. The second action will be to submit the form using the data connection that we created in the step above.

  • Select the Button (SaveForm in our example), Click Home Ribbon –> Manage Rules button. A blank rules window will appear.
  • Create a new Action rule by using the New button.
  • Enter the Name of the rule. (“Save Form as Draft” in our example).
  • Conditions. There are no conditions we need to specify. These rules will be executed when the button is clicked.
  • Create the first action of “Set a field’s value”. Select _FormStatus as Field and specify “Draft” as the Value. Click OK to save the action.

InfoPath Rule

  • Create a second action of “Submit data”. Select the submit data connection that you created in the step above. (“SubmitConnection” in our example). Click OK to save the action.

InfoPath Rule

  • The rules list for the SaveForm button will look something like below:

InfoPath Rules

Step 4: (Optional) – A few things you can do to make sure that users don’t bypass your workflow of Form Stages:

  1. Disable allowing to save form. From Form options, disable ribbon commands to Save the form.
  2. Disable submit options from Data ribbon. When you are generating file names with form stages, you will have to submit the form using rules. Doing this is very trivial and should not impact your business logic.
  3. Disable fields after the form has been saved for the first time. In our example above, it will be a good practice to disable Date, Department and Project Code fields once the form is saved as Draft. This can be done using rules on the SaveForm button. Doing this will ensure that users do not change the fields that are used for file name generation.

This is what is your form will look like in the Preview Mode. Note the Generated File Name control, This will become the name of the form when submitted.

Infopath Preview

Guide to publishing InfoPath Forms as admin-approved form templates

There is a lot of information on the web regarding how to publish InfoPath forms as full trust admin approved forms. This is just yet another step by step guide. So let’s get started:

The first step (and I am going to assume that you have already done that) is to create your InfoPath form as you would. Create local data connections, fields, views and layouts. I have always felt that doing everything up front (like making the template as admin-approved) before the development is complete is going to lead to a lot of head-aches and clicking that can be easily avoided.

The steps below will help you convert your local form to admin-approved.

Create a development certificate for your form template

For a form to run with full trust, it must be installed or digitally signed with a certificate. Web browser forms require full trust if they contain code, and must be deployed by a server administrator. As the forms I was deploying had custom code and was published on the SharePoint server, we were required to sign the form templates with security certificate.

For development, you can create a self-signed digital certificate using “Digital Certificate for VBA projects” application. This tool is installed with Microsoft Office and should be available on your development machine. Otherwise you can create a development certificate that is valid for 1 month from InfoPath designer.

Creating a digital certificate

Clicking OK on this dialog will create and install the certificate on your machine. When you are ready with your form to deploy to production, you will need to buy a “Code Signing” certificate from a certificate authority. I will have a follow up blog post about it soon.

Update InfoPath Form properties

Now that we have a development certificate, it is time to update our InfoPath form. You have a working form don’t you?

  • Open your InfoPath form in design mode.
  • Navigate to File ribbon tab and select Advanced form options.
  • Set the security level of the form to Full Trust.
  • Select “Sign this form template” checkbox and select the certificate we created in the section above.

Security settings in InfoPath 2010 form options

 

We could have created a development certificate from this dialog as well, but the certificates generated here are valid for 1 month only. This might be limiting on the long running projects where you are following the full software development lifecycle (SDLC).

  • Next step will be to ensure that the compatibility is set to “Web Browser Form”. This is essential if you would like to store the data connections on the SharePoint server. If this option is not configured properly, you will not be able to publish the data connections to the SharePoint server.

Compatibility settings in InfoPath 2010 form options

Click OK to save the changes you have made.

Convert InfoPath data connections

This is an optional step. I am guessing that if we are publishing the form template on SharePoint server and have multiple SharePoint environments (read DEV, TEST, UAT, PROD, DR) then it is a good practice to store the data connections at a central location. This will solve the issues with updating your forms once they have been tested. Publishing data connections to the Central Administration application will allow you to update the environment details without modifying your form.

  • We will create a new Data Connections Library on the SharePoint site. This is a temporary library where InfoPath will publish the data connections.
    • Navigate to “View All Site Content” page via the “Site Actions” menu.
    • Click the “Create” link to create a new library.
    • Under Libraries column select “Data Connection Library”.
    • Give the name “DataConnections” to the library and click the “Create” button.
    • Make a note of the URL. In my example here we have “http://sp2010/DataConnections/”.
  • Next, we will publish the local InfoPath data connections to SharePoint site. This is an interim step before we can publish these files to Central Administration.
    • Open your InfoPath form in Design mode.
    • Navigate to Data ribbon tab and open “Data Connections”.

InfoPath Data Connections

    • Click “Convert to Connection File…” button.
    • Enter the full URL for the connection we are trying to convert. This includes the URL of the “DataConnections” library we created in the step above, and the name of the data connection.
    • Leave the connection link type to default of “Relative to site collection”.

Convert Data Connection

    • Click OK to publish the file to the SharePoint library. Verify a connection file was created in the SharePoint library.

SharePoint Data Connections

    • Publish all the data connections in the form using the steps above. Once all the connections are published, navigate to Data Connections library and download all the files to your local drive. To download, click on the name link and you will be prompted to save the file.
    • After you have downloaded all the connection files, Navigate back to the Data Connections dialog in the InfoPath form.
    • Select a data connection and click “Modify…” button. The data Connection Wizard will appear.

Data Connection Wizard

    • Click “Connection Options…” button and modify the option to “Centrally managed connection library”.

Connection Options

    • Click OK to change the connection link type. On Data Connection Wizard dialog, click next a few times to finish the wizard and save the connection.
    • Perform the actions again for all remaining data connections.
  • Let’s upload our data connection files to Central Administration.
    • Open Central Administration and navigate to “General Application Settings” –> “InfoPath Forms Services” –> “Manage data connection files”.

InfoPath Form Services

    • Click the “Upload” link to upload a new data connection file.
    • Upload the data connection file that was downloaded from the DataConnection library of the site.
    • Enter the category name (optional) and select “Allow HTTP access to this file” checkbox.

Upload data connections

    • Click the “Upload” button to save the file.
    • Follow the steps again for all the remaining data connection files you have.
    • After uploading all the data connection files, navigate to the Data Connections library in your SharePoint site (used here: http://sp2010/DataConnections/) and delete all data connections from this library. This is not required as we have updated our form to look for data connections in Central Administration.

Publish InfoPath Form to Central Administration

The last step for this guide is to publish the InfoPath Form template to Central Administration. Doing so will make this template available as a “Content Type” in your SharePoint environment. If you don’t want to access these forms via SharePoint library, you can either publish this form template to Network folder or install it on user machines using traditional installers. In our exercise, we will be publishing this form to a Site Collection.

  • Open Central Administration and Navigate to “General Application Settings” –> “InfoPath Form Services” –> “Manage form templates”.
  • Click “Upload form template” link to upload the form.
  • Select the Form template from the published location and upload the form.

Upload Form Template

    • After uploading the form, wait till the form template status changes to “Ready”.
    • Using the form context menu (read ECB) select, “Activate to Site Collection” option and select the site collection where you would like this template to be available.
    • Click OK to activate the Form Template.

That’s it folks. You can now add this Form Template as content type to your SharePoint Libraries.

Audiobooks December 2012

The month of December was all about Star Trek. I was very interested in William Shatners books after hearing a lot of good reviews on This Week In Tech podcast. I finally got around to listening them this month. Star Trek “The Original Series” bring backs a lot of memories as this was the first Sci-Fi show I watched as a kid. There is something different about this Gene Roddenberry creation.

Up-Till-Now-291533Star Trek Memories Shatner

Up Till Now: The Autobiography

In this book Shatner discusses several aspects of his life including his childhood, early career struggles, time starring on Star Trek, his career after Star Trek and his marriages. Once again, this book has great reviews 4+ on Audible.com and is narrated brilliantly. If you have been a fan of William Shatner, this book will not disappoint.

Book Link | Audiobook Link

Star Trek Memories

Star Trek Memories is a William Shatner’s own behind-the-scene look at the legendary ‘60s television series. This book has a good insider view in the making of the series and the part that I loved the most was the references he has to specific episodes. It was fun going back and watching the episodes he discussed. Avid Trekkers are sure to be delighted with this first hand account from Captain Kirk Himself.

Book Link | Audiobook Link

Audiobooks November 2012

I have listened to a lot of audiobooks during my commute to work or while doing household chores. This will be a new series of blog posts that will catalogue the audiobooks I have listened to during the month with a short description and link to the reviews. This is by no means a review of the contents of the audiobook.

No Easy Day Audiobook CoverKilling Lincoln Audiobook Cover

No Easy Day: The Firsthand Account of the Mission That Killed Osama Bin Laden

This book is a military memoir of “Mark Owen”, a pseudonymous former member of the US SEAL Team 6. This team was involved in the operation that killed Osama Bin Laden. This book had great reviews on Audible and I think those reviews are spot on. This book was very engaging and you get a good feel about the lives of SEAL team members.

I would recommended everyone to either read or listen to this book as the unfortunate even of 911 is a part of our generation.

Killing Lincoln

A riveting historical narrative of the heart-stopping events surrounding the assassination of Abraham Lincoln, and the first work of history from mega best-selling author Bill O’Rielly. This book has top reviews on Audible and they are accurate. I found the initial chapters of this book a little dull, but that may just be because I didn’t have a lot of background knowledge about the cold war and what general reference to the key characters that ended the war. Following this book, I am now looking for books that can help me know more about the war and Lincoln as a leader.

If you like the History genre, or just would like to know more about the assassination this great man, I think this book will not disappoint. As always you can either read it or listen to it.

User selection screen on resume from standby

Microsoft Windows displays the lock screen when it resumes from standby. Apparently this is a design feature that enables users to just unlock their computer to get back to their desktops. But in a home environment where multiple users user the same computer, it can turn out to be a hassle and a “user training” issue when you have to teach your spouse to click on the “Switch User” link in order for her to get back into her account.

Turns out that since Windows 7, you can create a task it is possible to configure windows to display the Fast User Switching screen when coming out of standby.

Let’s start by creating a new Task in Task Scheduler (don’t create a Basic Task):

New Task

Enter basic details like the name of the Task. I recommend that you select the “Run with highest privileges” option in this screen.

Next, click on the Trigger tab and click the New button to define a new trigger for this task:

New Trigger

Next, update the options as show in the screen shot below:

Trigger Details

Move on to the Actions tab. We can now define what happens when the computer resumes from standby:

New Action

Next, update the options as shown in the screenshot below:

4

Explore other options and update as required.

Save the task and verify it runs properly.

Adding InfoPath Promoted Properties to Search Results in SharePoint 2010

There are times when the SharePoint crawled properties will be littered with so many items that it would end up being impossible to find the right column to include in your search results.

This is what happened recently when I was trying to identify one of the Promoted Properties from an InfoPath Form. Here is a handy way to identify the column id that is generated by SharePoint and map that to crawled properties.

Step 1: Navigate to SharePoint Document Library or List that has the column you are trying to identify.

Step 2: Click on the Content Type. This should open the List Content Type Information page.

SP2010FormLibrarySettings

Step 3: Start IE Developer Tools (F12) and select the “select tool” (This is the first icon in the toolbar). Click on the text of the property that you want to identify.

IEDeveloperToolsWindow

The GUID just after the “id_” prefix is the actual ID that is created and used by SharePoint. This GUID can be used to identify the crawled property.

Step 4: Navigate to the Metadata Properties page in Central Administration website and click on Crawled Properties.

CentralAdminMetaDataProperties

Step 5: Search for the crawled property and map to an existing or new Metadata property.

SearchCrawledProperty

If you choose, you can use this in your Search Results page or use Keyword Query to programmatically access the text that is crawled.

SharePoint Server 2007 Training for End Users

Recently, In one of the mailing list I came across a question that is very commonly asked by many enterprise customers:

Now that we have SharePoint deployed, How do I train my users?

These users can be Business end users, Helpdesk staff, IT teams etc.

Here are the three links that I think would act as a Starting Point for any good training. They are an excellent resource for Beginners and Novice users alike:

  1. Introduction to Microsoft Office SharePoint Server 2007
  2. Microsoft Office SharePoint Server 2007
  3. Roadmap of using SharePoint Server 2007