Incremental sequential numbering in InfoPath forms

A question that is asked often, is how to generate sequential numbers in InfoPath Forms. The best way to achieve this is to use a SQL database, along with a stored procedure and a web service to connect Forms Services to the DB.

The first thing to do is create a database table to store and update the numbers. Please see the image below.


The database needs nothing fancy, just a column I called CurrentNumber. The column type is an integer. I called the database table tblSequenceNumber.

The next thing to do is create a stored procedure that will be used to update the number. This will be a middle man between the web service we will create later and the numbering database. The stored procedure should look something like this:


As you can see, the stored procedure is now called spGetNextSequenceNumber.

The next step is to create a web service that will allow InfoPath/SharePoint to talk to the DB without running into any double-hop or authentication issues. Without the web service, this would not work. The web service should look something like this:


The web service will now connect to the stored procedure and update the number DB incrementally. The webservice should be called something.asmx and stored in the following location on the front end server(s):

%Program Files%\Common Files\Microsoft Shared\web server extensions\12\TEMPLATE\LAYOUTS\Webservices

Now we get to the fun part. Applying all of this to an InfoPath form.

The first thing to do is add the web service as a data connection within the form. We go to Tools>Data Connections


Then select Add>Create a new connection to>Receive data


The select Web service


You then need to enter the location of the web service file that we are going to read. In the image I have replaced my url with some text. You need to enter the url that you would use to access SharePoint, followed by _layouts/webservices/the name of your file.asmx?WSDL


If you have formatted all of  this correctly, on the next page you will be presented with the available actions from the web service file:


We need to select GetNextSequenceNumber and then click next


At this point it is ESSENTIAL that you unselect Automatically retrieve data when form is opened


If we don't, then every time we open the form, the number in the database table will increment by 1.

Now it gets REALLY fun. We need to use this data connection to populate a field on the form.

The first thing to do is add a text box to the form


Once we have done this, we go Tools>Form Option>Open and Save>Rules


For the purposes of this article, the filed is called Field1. We then select Add to add a new rule and then Set Condition


We then need to specify Field1 is blank


This is so we update the field the first time we create the form only, and NOTevery time we open the form.

We the select Add Action>Query using a data connection and select our data connection from the drop down


This gets the next number from the database and stores it in the cache. It also runs the stored procedure which increments the number in the database.

We then need to select Add Action once more and select Set A Fields Value, then select Field1. We the select the FX button and change the data source in the drop down at the top to our GetNextSequenceNumber data connection. Then expand the dataFields portion, expand GetNextSequenceNumberResponse and select GetNextSequenceNumberResult


This will then give us the following


We can then do some great stuff such as concatenating a prefix or suffix to the number for use in invoicing etc


We can then save out of everything and preview the form. It should look like this


And that, in a relatively large nutshell, is how to build a bulletproof, autoincrementing, sequential numbering system for InfoPath.

As always, and comments or feedback is welcome.