<

Incremental sequential numbering in InfoPath forms

Published on
14,012 Points
6,812 Views
2 Endorsements
Last Modified:
Approved
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

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

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

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

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

Preview
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.
2
Comment
5 Comments
LVL 13

Expert Comment

by:PillalamarriVenkateswaraRao
Hi Robbie,

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

Thanks
0

Expert Comment

by:Dr.Abdulwahab Al-anesi
Cant we do it direct from the InfoPath form design, I don't have experience in SQL
0
LVL 25

Author Comment

by:Robbie_Leggett
No. This is why I've posted step by step instructions, including the code required.
0

Expert Comment

by:Dr.Abdulwahab Al-anesi
Thanks
0

Expert Comment

by:Jason Baker
Can this method be adapted for SharePoint 2013?
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Join & Write a Comment

Did you know PowerShell can save you time with SaaS platforms? Simply leverage RESTfulAPIs to build your own PowerShell modules. These will kill repetitive tickets and tabs, using the command Invoke-RestMethod. Tune into this webinar to learn how…
Microsoft Office 365 Backup and Restore Solution by SysTools to export Office 365 mailbox to PST / EML file format on Windows OS. On Mac, tool backup O365 to PST / MBOX / MSG / EML / EMLX file formats. Not only this, restore option helps to import s…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month