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.

Image 1
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:

Stored Procedure
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:

Web Service
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

Tools>Data Connections
Then select Add>Create a new connection to>Receive data

Receive Data
The select Web service

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

Web service URL
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:

Web service list
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

Untick automatic
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

Text box
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

Add rule
We then need to specify Field1 is blank

Field1 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

Add action
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

Add second action
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.

Comments (5)

Top Expert 2008

Hi Robbie,

  This is Venkat. Do you any idea on how to do incremental Sequence number in our SharePoint Lists ?

Dr.Abdulwahab Al-anesiProjects Information Technology Manager

Cant we do it direct from the InfoPath form design, I don't have experience in SQL


No. This is why I've posted step by step instructions, including the code required.
Dr.Abdulwahab Al-anesiProjects Information Technology Manager

Can this method be adapted for SharePoint 2013?

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.