Generating a unique form number

I’ve had someone ask me about automatically giving InfoPath forms a unique number. The scenario in question was one where the forms were being saved to a SharePoint document library with a unique number attached.

Documents in a SharePoint library do get given an ID, which is unique within document library. This can be used as the unique identifier of the forms out of the box with no additional work. The problem is, this only works so long as your forms remain in that document library. Once you start moving or copying forms (perhaps as part of a workflow) the ID will change.

In those situations, you will have to add a bit of work to your form to generate this number. You just have to be careful about when you generate it. If you have the possibility of multiple people filling out the form at the same time, you need to make sure the number is created in such a way that the method doesn’t result in forms being given the same number. If the form were to create this number on open, basing it on the number of files in the library, you would have issues when someone else opens the form template before the first form has been saved to the library. The easiest way round this issue is to have the number generated on submission.

Here I will describe a simple method of creating this auto number.

Firstly, you will need to create a data connection. If your forms (or a copy of them) are going remain in the document library they are submitted to, then you simply need to create a connection to retrieve data from that library. You only need to retrieve the data from one column. For the sake of argument, we’ll say the unique_number column. I’ll discuss another possibility in a bit for the scenario where forms will be moved or deleted from the library.

Set up the data connection using the wizard as you normally would, but uncheck the box when it asks whether to retrieve data when the form opens. You won’t want to get this information until the end. You will also want to set up the data connection to submit the form to the appropriate form library. Do this as you normally would, but make sure that one of your promoted fields is the unique_number field where you’ll be storing the unique ID of the form. From the submit options menu, uncheck the box labelled Show the Submit menu item and the Submit toolbar button. This is because you don’t want your users to just submit the form; you need the form to perform other actions first.

To allow your users to submit, add a button to the form. You’ll want this button to be set to trigger rules and custom code. Click on the Rules button, then Add, then Add Action. The action you want first is to query using a data connection. Choose the data connection you created to retrieve data from the form library.

 The second action is to set a field’s value. The field will be your unique_number field. Click on the function button next to the value field. Insert a function. You have a couple of different options here. You can choose to perform the count function on the items in any field, or the max function on the unique_number field. Either way, you should end up with the most recent unique number assigned. So now, you just add one to get your new one.

The third and final action you need to add to this button is to submit the form.

So there you have it. Your form will now check how many items there are in the form library, create a unique number based on that value and save the form back. Brilliant!

But you can probably see why this method only works for scenarios where you leave the forms in the library. If you have a process that involves moving the form from that library to another, the count value will almost certainly be wrong and the max value might well be. There are probably several different ways to work around this. One simple way is to create a SharePoint list to act as a counter.

Have a list with one numeric element. You’ll want to set this list up so that it’s not visible from the menus on the site since you’ll only want to access it through the form and workflows. Initially, this will have the value of 1. Create a one step workflow using SharePoint Designer and attach this workflow to the form library. The workflow should trigger every time an item is created in the form library and all it should do is add 1 to the value in the counter (using the calculate value and update list item inbuilt actions). Then your form should retrieve data from this list instead of the document library and use the counter value as the unique_number.