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\LAY
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 GetNextSequenceNumberRespo
nse and select GetNextSequenceNumberResul
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.