Introduction
By default, a form library with a form template associated (or a form library hosting content types with associated form templates) allows multiple new submissions of the same form. This means you can have multiple forms for the same user in various states throughout a business process.In many cases, this is not desirable behaviour. If you have created a Survey, for example, you only wish your users to fill out the survey once and not allow multiple active versions. There are other form types, such as Advance Request forms, that you may also wish to only have one active copy.
The approach used here uses a second “query” data connection to the same form library as the hosted form. This query will extract the username and status of the current users records in that form library. If we have a value in that query, we know that the user already has at least one other form in the library.
Once a user has completed the form process, there may be cases where you want the user to now be able to fill out an additional form, such as the case of an advance request. You only want one active advance request at one time, but once it has been approved or denied, then the user should be able to fill out a new form. Even with a survey, you may wish to be able at some time to have users retake the same survey, such as a survey that examines employee satisfaction before and after a new process is put in place.
We handle this requirement by filtering the query connection on status, so that we will only have a value returned by the query if two conditions are true:
1. The user has a current form in the form library.
2. The form is not in a final status.
Form Library Settings
Content ApprovalThe majority of InfoPath form systems require the following simple functionality:
1. Allow a user to submit a new form
2. Allow a user to see and update their own form
3. Prevent users from seeing or accessing other forms in the library
By default however, a form library allows contributors to see ALL forms in the form library, not just their own. There are a number of ways to tackle this, including using workflows and item level permissions, in our case, we are going to use the “Content Approval” functions of the form library since that functionality meets our needs.
What content approval does is it prevents users from seeing any items in the form library before they have been officially “Approved”. For our purposes, the form will never be approved; it will always remain in the “pending” status. Do not confuse this status with the custom status we are creating for our own purposes (and be sure to name your custom status value something more specific than “Status” to avoid confusion).
Enable Content Approval
Ingredients:
1. Form Library
2. Design or greater permissions to form library and hosting SharePoint site
3. A “Contribute” group on your site
Steps:
1. Navigate to your InfoPath form library.
2. Click the “library” tab, “Library Settings” button.
3. Click “Versioning Settings”.
4. Choose “yes” for requiring content approval for submitted items.
5. Leave “No versioning” as the selected item in “Document Version History.
6. In “Draft Item Security” select “Only users who can approve items (and the author of the item)”. This is the step that sets up the permissions functionality that we wish.
7. Ensure “No” is selected in “Require Documents to be checked out before they can be edited”.
8. Your library settings should look like this:
9. (Optional) I like to try and prevent confusion with this built in status setting and the custom status setting. For this reason, I remove the SharePoint created “Approval Status” column from the default view on the form library. While still in “Library Settings”, scroll down to the “Views” section and click the “All Documents” view link.
10. Remove the checkbox from “Approval Status” in the “Columns” section.
Add a Status Value to the Form and Expose
We now need to add a status value to your form and expose that value to the form library. This assumes you have already created your form, and that the form is already saving data to your form library.
Ingredients
1. SharePoint form library set up using content approval.
2. InfoPath form template associated with Library.
3. Sufficient permissions to publish to the library.
4. A data connection library.
Steps:
1. Open your Form in InfoPath Designer 2010.
2. In the “Fields” section, right click on the root folder and choose “Add”.
3. Name your field “SurveyStatus” or similar. Do not simply use “status” as there is a site column type of “Status” as well that can cause confusion. Leave the default setting of Data Type, “Text(string). Click “OK”.
4. We are now going to expose this field to the form library. Click the “File” tab.
5. Click “Advanced Form Options”.
6. Click the “Property Promotion” Category.
7. Click “Add”.
8. Select your status field from the list of fields. Click “OK”.
9. Your form options should now look similar to:
11. Publish your form to instantiate these changes.
Deciding which status values to use, which buttons to provide your end user, and how the status values are determined and changed is not the focus of this post. A system that at minimum saves a form status on save is required for the duplicate detection outlined in this post to function correctly.
Create the Query Data Connection
Ingredients:
1. URL of site that hosts form and data connections
2. URL of data connection library
3. InfoPath Designer 2010
Steps:
1. Open InfoPath Designer 2010, New->Blank Form->Design Form
2. On the “Data” tab, click “Data Connections”
3. Click “Add”
4. In the data connection wizard, choose “Create a new connection to:” -> Receive Data ->Next
5. Select “SharePoint library or list” ->Next
6. Enter in the URL of your SharePoint site that has the form and connection library ->Next
7. Select your form library from the list ->Next
8. Select “Created_By” and “Status” from the list fields. ->Next
9. Leave “Store a copy of the data in the form template” blank
10. Name your data connection and check “Automatically retrieve data when form is opened” -> Finish
11. Click “Convert to Connection File”
12. Paste in the URL of your data connection library. Something like:
http://www.mysharepoint.com/sites/ISTT/Form%20Data%20Connections
Then append a forward slash and a filename for your data connection, such as:
http://www.mysharepoint.com/sites/ISTT/Form%20Data%20Connections/QueryDataConnection
This will create a data connection file called “QueryDataConnection.udcx”
13. Leave Connection link type as “Relative to site collection”, click OK.
14. Once processing has completed, navigate to the Data Connection Library you created, you should see your data connection.
15. Click the checkbox beside your data connection, and choose “Approve/Reject” in the office ribbon (or click the dropdown arrow beside the connection name and select “Approve/Reject” from there).
16. Choose “Approved” and click “OK”. Your connection is now available for use.
Add the Data Connection to your Form
Ingredients:
1. Data Connection
2. InfoPath form with Status Exposed
Steps:
1. Open the form you wish to connect to this data connection in InfoPath Designer.
2. “Data” tab, click “Data Connections”.
3. Click “Add”.
4. Choose “Search for connections on a Microsoft SharePoint Server”.
5. Choose “select site” to see if your site is already listed in the “Site:” drop down list. If it is, go to step 11. If not, click “Manage Sites”.
6. Click “Add”.
7. Enter in the URL of the site that hosts your data connection library, for example:
http://www.mysharepoint.com/sites/MyForms.
8. Enter a display name.
9. Click “OK”.
10. You should see your site name in the list of sites. Click “Close”.
11. After a moment or two, you should be able to select your site from the list, and then see the name of your data connection library displayed in the dialog window. Click the “+” beside the data connection library and select your query data connection:
12. Select both your status value and “Created_By” , click “Next”
13. Click “Next”
14. Name your data connection with a clear name for its purpose, such as “SharePoint Library Query Connection”. Check “automatically retrieve data when form is opened” (IMPORTANT). Click “Finish”
15. You should now have your data connection viewable in the Data Connections Window:
Create an Error View
The error view is the view that the form will change to when the user already has a form in active status. Create a new view by:
1. “Page Design” tab.
2. Click “New View”.
3. Name your view “Active Form Error”.
4. Create an error page similar to below:
Configure Form Load
Ingredients:
All prior steps completed.
Add a hidden query variable
A hidden query variable is added to the form that potentially holds the current users username, if they have another form in progress (and it’s not in a filtered status).
Add a field to your form in the “Fields” section to hold the query variable, I called mine “strQueryUser”. Do not expose the field to the form by dragging it onto the form, we want this field to remain hidden (unless for testing purposes of course).
Configure Form Load
The form load event sets the value of our hidden query variable filtering on status, and then tests against that variable and the status of the form to detect if the user already has another current active form.
Since we have turned on content approval, the query connection we have set up to the Form Library is going to obey those permissions. That means the query connection is only ever going to return the current users forms (the only ones content approval functionality gives them permission for), rather than all of the forms within the form library (for non admin users).
We also want users to be able to fill out new forms after the form is in “Complete” status. We will use filters to meet this condition.
1. Open your form in InfoPath designer 2010
2. Click the “Data” tab.
3. Click the “Form Load” folder:
5. Click “New” and select “Action”.
6. Rename your rule “Set Hidden User Variable”.
7. Leave “condition” as it is, we want this rule to run on every form load.
8. Click “Add” in the “Run these actions:”
9. Choose “Set a fields value”.
10. For the “Field” section, choose the hidden field you just created:
13. The “Insert Formula” window appears. Click “Insert field or group”.
14. In the “Select a field or group” window that appears, change the data connection used to extract the fields from “Main” to the second query you created earlier “SharePoint Library Query Connection (Secondary)”:
17. Click “Add”:
20. Click the arrow on the third drop down, and choose “Type Text”.
21. Type in the name of the status category you wish to exclude. This is usually the final status value of the form, in our case, its “Complete”. Click OK:
NOTE: If you wish to also filter on other status values such as “archive” or “rejected”. Remember, these are the form statuses that we do NOT want to prevent a user creating another form with. So if a form exists in one of these status values, we don’t want to prevent the user from entering a new form.
23. Click OK again until you return to the form screen.
24. We will now add the conditions that test against the status value and queried user value to determine if they already have an active form. While still in “Form Load”, Click “New”.
25. Select “Action”.
26. Name your rule “Detect Active Condition”.
27. In the “Condition:” section, click “None-Rule runs when form is opened” link.
28. For the first drop down, select “strQueryUser” and for the second drop down, select “is not blank”. Since we know that only the current users forms are going to be returned, and we have filtered on the complete status, a value here indicates an active form for this user in a status other than “complete”.
29. For our second condition, select your status field and choose “is blank”. The status field for a new form is always blank, we set the form up so that status is set on save or submit (If you set the status on the form load, run these rules before the status setting form load rules and check "Don't run remaining rules if the condition of this rule is met" checkbox on "Detect Active Condition" test). A blank status field indicates this form is new, rather than updating an existing. Your conditions should now look like:
31. In the “Run these actions” section, of the “Form Load” rules, click “Add” and select “Switch Views”.
32. In the rule details panel, if it isn’t by default, select your “Active Form Error” view.
33. Click OK. Your form load rules should now look like:
You should be able to create a new record, but the second time you create a new record; you should instead be redirected to the error view. Log in as a different user and try the same tests to verify functionality.
Custom Permissions
There are two custom permissions that are useful in a form library system like this one. The first is the "Approvers" permission level that allows a specific group (managers, form reviewers) to view all forms without needing site owner or full control permission levels. The second is a custom contribute permission level that excludes the ability to delete, this prevents users from being able to delete their form data after submitting, which they are permitted to do by default.
Both of these scenarios are covered in the "Custom Permission Levels" post.
Multiple Content Types
This approach has been tested and proved on a form library with a single template associated. If instead you have a form library with multiple content types associated, you will also need to filter on the content type on the query connection to restrict results to just the current content type selected by the user when they decide which form to fill out.
This approach on multiple content types has not yet been tested.
