Add an Auto-numbering field to an InfoPath Form

Thanks to some Twitter help from Clayton Cobb and Saed Shela and Clayton’s blog post on Auto-Numbering InfoPath Forms, I got a solution that works perfectly and requires nothing but the InfoPath form itself. I previously had a solution using the Do Calculation method of a SharePoint Designer workflow, but I would much rather do it all in InfoPath.
What I needed was to automatically number the InfoPath Form in a SharePoint 2010 Form Library. I could’ve just used the ID field, but my client is replacing a process that was previously manually numbered from 1 all the way up to the 4500+ range (and increasing daily). I’m preparing this process so that we can do a cutover and start numbering where the ‘old’ process left off.
So basically for my testing and development I wanted to add 5000 to the current item ID and put that into another field, we’ll call it Form Number.
In order to pull this off, I did the following:
  1. Create 2 hidden fields in InfoPath, I called them NumID and NumberToAdd. NumID will contain the ID of the item, NumberToAdd is the amount to add to the NumID – my default value is set at 5000.
  2. Set up the Submit button in the form to perform the following steps when clicked:
    1) Submit to the Form library
    2) Query a data connection that pulls the ID from my Form Library
    3) Set the NumID field to the ID of the item
    4) Set the Form Number field to NumID + NumberToAdd
    5) Submit to the Form library
    6) Close the form

The query data connection is simply a receive connection to my form lib, and it pulls the ID field only. Since I have the NumberToAdd field set at 5000, it adds 5000 to the current ID and I end up with the Form Number field showing a value of 5001, 5002, etc.

Pretty simple, but sometimes the simple things give me the most problems! Thanks again to Clayton and Saed for the assistance via Twitter!

InfoPath Designer 2010 and SharePoint 2010 are a great team!

In case you’re not as fortunate as me and you haven’t had the privilege of experimenting with SharePoint 2010 and the new Form Templates available in InfoPath Designer 2010, here are some cool things you can do without code!

  1. Create a custom branded User Profile Lookup Form to talk to the UserProfileService.asmx web service. Pull in data such as Preferred Name, E-mail address, Profile Picture, Department, Manager, etc. Put this in an InfoPath Form Web Part and give the user a nice UI for querying your Active Directory.
  2. Create a custom Site Provisioning Form to talk to the Sites.asmx web service. Wrap an approval workflow around it so that when the manager approves the site request it gets auto-provisioned. The user doesn’t need permission to create sites because you can run it with elevated privileges. The manager doesn’t need to manually create the site for the user after approving it; everyone wins!

These are just two of the cool things you can do with SharePoint 2010 and InfoPath 2010.

Current configuration settings prohibit embedding username and password in the database connection string

My migration to SharePoint 2010 from SharePoint 2007 went mostly smoothly. One exception to that statement was getting my data connections in InfoPath Forms Services to work again.

I came across this error today:
Current configuration settings prohibit embedding username and password in the database connection string

A quick check of the InfoPath Form Services settings in Central Administration>General Application Settings>InfoPath Form Services>Configure InfoPath Form Services resolved my problem…

I needed to allow:
Embedded SQL authentication
User form templates to use authentication information contained in data connection files
Cross-domain data access for user form templates that use connection settings in a data connection file
Saving those settings got my data connections working again!