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 ::..