Assign a number to an InfoPath form whenever it is submitted


Problem

You want to automatically assign a number to an InfoPath form whenever it is submitted to a SharePoint form library.

Solution

Use rules, formulas, and the max() function to generate incremental numbers that can be assigned to InfoPath forms when they are submitted to a form library.

When the form library is empty, i.e. when it contains no forms, the max() function will fail and the resulting number for the first form that is submitted to the form library will be NaN. The correct numbering for subsequent form submissions should succeed. As a workaround you can manually rename the first form to 1 in SharePoint.


Steps to Follow

1. In SharePoint, create a form library to submit your forms to.

2. In InfoPath, create a new (browser-compatible) InfoPath form template.

3. Add a Text Box control to the form template and name it Autonumber.

clip_image002

4. On the Tools menu, click Data Connections.

 

clip_image004

5. On the Data Connections dialog box, click Add, and create a Receive data connection to the SharePoint library that you created in step 1. In step 5 of the data connection wizard, uncheck all fields and then check the ID field. In the last step of the data connection wizard, uncheck Automatically retrieve data

clip_image006

6. When form is opened, name the data connection RetrieveIDs, and click Finish.

 

7. On  he Data Connections dialog box, click Add, and create a Submit data connection to the SharePoint library that you created in step 1. In step 3 of the data connection wizard, click the formula button behind the File name field.

 

clip_image007

8. On the Insert Formula dialog box, type max( and then click Insert Field or Group.

9. On the Select a Field or Group dialog box, select RetrieveIDs from the Data source drop-down list box, expand all the nodes, click ID, and then click OK.

10. On the Insert Formula dialog box, type ) + 1 and click OK.

11. On the Data Connection Wizard dialog box, the expression in the File name field should resemble: max(@ID) + 1

 

clip_image008

12. On the Data Connection Wizard dialog box, click Next, name the data connection Main submit, and then click Finish.

13. On the Data Connections dialog box, click Close.

14. On the Tools menu, click Submit Options.

15. On the Submit Options dialog box, check the Allow users to submit this form checkbox, select the Perform custom actions using rules option, click Advanced, select Close the form from the After submit drop-down list box, and then click Rules.

 

clip_image010

 

16. On the Rules dialog box, click Add.

17. On the Rule dialog box, add an Action that says: Query using data connection: RetrieveIDs

18. On the Rule dialog box, add a second Action that says: Set a field’s value: autonumber = max(@ID) + 1 Note: @ID is selected from the RetrieveIDs secondary data source in the same way you selected it in step 8.

19. On the Rule dialog box, add a third Action that says: Submit using data connection: Main submit

clip_image012

***The three actions are shows above.

20. Click OK when closing all dialog boxes that are currently open.

21. Publish the form template to the library you created in step 1.

One thought on “Assign a number to an InfoPath form whenever it is submitted

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s