Using an automatically incremented field value to identify InfoPath forms in a SharePoint form library

I was trying to automatically populate an (identifier) field in an InfoPath form such that the value would be an integer unique to the same field in all other forms published to the same library. This could be thought of as service call numbers in a service desk application.

A few people had the same question, but a quick Google only suggested that this doesn’t need to be done as each item in a SharePoint list already contains its own unique identifier. Several people used unique identifiers for items in a repeating table (see Yaniv Levi’s blog: Auto-Increment for InfoPath repeating tables/sections), however thy couldn't translate into this problem’s dimension.

I came up with a simple solution where the form reads current list of unique ID’s from the SharePoint list and increments the largest ID to obtain a new ID:

1. Create the field (Whole Number) which will be the identifier. Set the default value of this field to “1”. (In my case, the field was called “ID”.)
   
   
2. Create a text field which will be used to record the status of the form, and set the status to “New”. We use this to ensure that a new ID is not created when an existing form is not assigned a new ID when it is reopened. (Again, in my case, the field was called “FormStatus”.)
   
   
3. Publish the form to a SharePoint list, promoting the ID field to the list.
   
   
4. Now create a data source to receive data from the SharePoint list that the form is published to. We want to receive the ID column from the list. (Note that you may be able to recycle an existing data connection that already connects to the list by adding the ID column to the data connection, or vice-versa, adding extra columns to the new data connection for later use.)
   
   
5. Next we need to create a rule that creates the ID when the form is opened for the first time:
   
   • Click Tools > Form Options. Then select the Open and Save category, and in the Open Behavior section click Rules.
     
   • Click Add, and click Set Condition. We need two conditions:
     
     1. FormStatus is equal to “New”,
        
     2. The number of items in the datasource that contains these submitted forms is greater than 0. (i.e. count(@ID) > 0)
        
     
     
   • Click Add Action and add an action that sets the ID field.
     
     1. Set the action to “Set a field’s value”
        
     2. Set the field to the ID field.
        
     3. In the value section, click the expression editor and enter the expression “max(@ID) + 1”. To do this:
        · Select Insert Function, and select “max” (from Math)
        · Double click the underlined text, select the data source created earlier and drill down and select the ID field.
        · Add “+ 1” to the expression.
        
     
     
   • [Optional]Add a second action – this time changing the value of the FormStatus field created in step 2.
     
     1. Set the action to “Set a field’s value”
        
     2. Set the field to the FormStatus field.
        
     3. In the value section, enter “Created”.
        
     
     
   • Add a third action– which submits this form to the SharePoint library. This will remove (well reduce) the likelihood of issues caused by concurrent use.
     
     1. Set the action to “Submit form”
        
     2. Select (or create) the data source used to submit the form to the SharePoint list.
        
     
     
   • Click OK on all of the open dialogs.
     
   
   
6. Publish the form again and test!
   
   

Notes:

• It is almost always good practice to disable the default save options and create a control on the form itself that saves (or submits) the form using a data source like the one at the end of step 5.
  
• If the form already has a status field or any other measure that can be used to declare whether a new form has been opened (i.e. without an ID assigned) or an existing form has been reopened from the SharePoint list (i.e. with an ID already), then the [optional] steps can be omitted.
  
• WARNING! By submitting the form as soon as the ID has been created, the chances of issues occurring (i.e. two different forms with the same ID) is greatly reduced. However, the issue can still arise in high volumes of usage.