SharePoint 2010 & InfoPath – Creating an autoincrementing column as IDs

Sometimes it is necessary to have a column which functions as id for the element. Normally IDs should have some characteristics like being unique. Therefore it is easier to have an autoincrementing column which automatically creates new IDs. In that case the user do not need to create an ID manually which can of course cause mistakes.

The best way to offer a manually ID would be a textfield which only allows unique values and contains a special validation that the user cannot insert wrong items. How to do this, you can read in my previous post.

The autoincrementing way has some steps involved as well as InfoPath is needed. Another descibing post can be found here.

  1. Create a Query for retrieving last ID
  2. Create a hidden field for autoincrementing number
  3. Create a Save Button with custom rules
  4. Change settings for the form

The trick is to query the last highest available ID shortly before the user wants to store the item.

Step 1:  Create a data connection

Click on manage data connections, add a data connection, name it “retrieveMaxID” and then click on receive data. Important here is that you uncheck the checkbox that the data are retrieved at form loading. Choose the current list and you only need to select the ID column.

Step 2: Add a hidden field calling it “AutoID”

Step 3: Add a Save Button and custom rules

The save button you can choose what you would like to have. Important is that you add the validation to this butto. That means, that the field has to be filled out properly otherwise the rule will prevent saving actions.

1) Retrieve the highest ID now

2) Set the new ID to 1 if the there is no ID available from dataconnection RetrieveMaxId

3) If Autonumber is not set (cause we are in a new form instead of an edit form) set Autonumber =  max ID +1

4) Save the values with sending the data to the form and close the form

Step 3: Change settings of the form

In order to complete the whole story, it is necessary to deactivate the toolbar menu actions like save and cancel. You’ll find them File – Extended Form Options. Instead of the built-in buttons the save button we created in step 3 should be used. You can also create a Cancel Button with some rules.

Look at the result:

Extend it

Now you use this base for your autoincrementing ID. Maybe you would like to have a special ID like yyyy-AutoID (2012-01) or it should have a mix from autoincrementing numbers + date or another column value. It is now very flexible to create your own ID.

..:: I LIKE SHAREPOINT ::..

The article or information provided here represents completely my own personal view & thought. It is recommended to test the content or scripts of the site in the lab, before making use in the production environment & use it completely at your own risk. The articles, scripts, suggestions or tricks published on the site are provided AS-IS with no warranties or guarantees and confers no rights.

Karsten Pohnke About Karsten Pohnke
He is Consultant for SharePoint Solutions for collaboration, communication and business processes. He provides his customers applications based on standard features as well as development or combining the power of several microsoft tools like Dynamics CRM. In his free time he tries to collect tipps and worthy experience in this blog.

6 comments on “SharePoint 2010 & InfoPath – Creating an autoincrementing column as IDs

  1. Adrianne Woollgar

    Hi there,

    I’m brand new to using InfoPath and I was given a task that required auto-incrementing numbers in a SharePoint list. It would have been nice if the pictures were in English, but not a total train smash to use a translator for the the rules and what not.

    My question is this: what is the reason behind the hidden field?

    • Adrianne Woollgar

      The reason I ask is because it isn’t referenced after you say to create it.

    • Hi,
      the user normally do not need to see the auto ID column or value in the infopath form. So this is a recommendation, to hide the field. But don’t have to.

  2. Hi,
    leider erscheint in meiner Sharepoint-Liste keine Nummer, sondern “NaN”. Können Sie mir sagen, woran das liegen könnte?

    Viele Grüße

Submit comment

Allowed HTML tags: <a href="http://google.com">google</a> <strong>bold</strong> <em>emphasized</em> <code>code</code> <blockquote>
quote
</blockquote>

Please fill in the captcha: * Time limit is exhausted. Please reload CAPTCHA.