Tracking C&Q Deviations with SharePoint

1. Deviations Home

Hello good people of the world! This post is the first in a series on tracking C&Q (Commissioning and Qualification) deviations in a SharePoint site. This implementation assumes approvals are still done with wet signatures so there are no 21CFR11/Annex 11 compliance concerns.

This first post covers how to set up functionality to automatically add an MS Word document created from a template to a library by specifying metadata in an HTML form using Javascript in a Content Editor web part.

It is based on the posts found here and here, and clarifies some steps and adds the HTML form and JavaScript for collecting metadata and adding the list item.

What you’ll need: O365 on SharePoint 2013 On-premise with SharePoint site administrator permissions, SharePoint Designer 2013 (get here).

Step #1: Create a content type.

  1. Go Settings -> Site Settings -> Site content types (under Web Designer Galleries)
  2. Click Create
  3. Enter a Name. I’ll use “Deviations Content Type” because I’m tracking deviations.
  4. Under Parent Content Type, select Document Content Types and Document.
  5. I recommend putting custom stuff into a New group, so it’s easy to find later. I’m going to create a New group called “Tools”.
  6. Your content type settings should look something like the below. Click OK.1. New Content Type

Step #2: Add columns to the content type. These are the “metadata” that will be displayed in your Word document.

  1. Select Add from new site column.
  2. Enter the Column name, select the type, add to the Existing group you created in step 1, and click OK. Repeat for all the metadata columns you desire. I’m adding “Dev Number”, “Dev Open”, “Dev Type”, “Dev Category”, “Dev Closed”, and “Document”, with the below settings if you’re interested.
    # Column Name Column Type Required Must be Unique Notes
    1 Dev Number Single line of text Yes Yes Auto-generated from document number
    2 Dev Open Date and Time Yes No Date deviation was discovered
    3 Dev Type Choice Yes No Minor
    4 Dev Category Choice Yes No Failure to Meet Acceptance Criterion
    Unexpected Conditions or Test Results
    Test Cannot Be Completed as Written
    Test Form Error
    5 Dev Closed Date and Time No No Date deviation was closed
    6 Document Lookup Yes No Lookup from C&Q Documents list

Step #3: Create your info list. This is the list where your metadata will be stored, and that will trigger the workflow to create your new word document.

  1. Settings -> Add an app
  2. Select Custom List
  3. Give your list a Name and select Create. I’m using “DevInfo”.
  4. Go to your list -> Settings.
  5. Select Add from existing site columns
  6. If you’ve been grouping (see Step #1, item 5), you can select your group from the Select site columns from dropdown. Move all the metadata columns created in step #2 to Columns to add and click OK. Mine looks like:2. Add Columns to List.PNG

Step #4: Create your document library. This is where your auto-generated MS Word documents will reside.

  1. Settings -> Add an app
  2. Select Document Library
  3. Give your library a Name and select Create. I’m using “Deviations”.
  4. Go to your library -> Settings.
  5. Select Advanced settings and set Allow management of content types? to Yes and click OK.
  6. Back on the Settings page, under Content Types, select the Document content type and then Delete this content type.
  7. Back on the Settings page, under Content Types, select Add from existing site content types, add the Content Type you created in Step #1, and click OK. Mine looks like:3. Add Content Type to Library.PNG

Step #5: Create your template. This is where you customize your MS Word template. There may be easier ways to do this, but I know these steps work.

  1. Go Settings -> Site Settings -> Site content types (under Web Designer Galleries)
  2. Find the content type you created in Step #1. This is easy if you select the Group it is in. Select it.
  3. Select Advanced Settings. What we’re looking to do is upload a customized template using the Upload a new document template. To ensure the SharePoint connection works, let’s work with the default template.
  4. Connect to your site using SharePoint designer, and find the default template template.dotx. It will be under All Files -> [My Library Name] -> Forms. Here’s what mine looks like:4. template.dotx file in SharePoint Designer.png
  5. Okay, now download this file to your hard drive, and change the name to whatever you want. Critical is that the file type/extension is changed from .dotx to .docx. Do this!
  6. Edit the document however you want. Mine looks like this:5. Word Template without Fields
  7. Now we can add fields to link to our metadata. I think you should be able to do this manually, but it’s been giving me some trouble. That’s why I start with the auto-generated template.dotx. If you know another/better way, please comment below.
  8. Add field in Word by going to Insert -> Quick Parts -> Document Property. You should see all your custom fields there. Mine looks like:6. Word Document Properties.png
  9. After adding all the fields, your document should look something like this:7. Word Template with Fields
  10. Save and close your Word document.
  11. Back to SharePoint, select the Upload a new document template radio button, Browse to your document, and click OK.

Step #6: Create the workflow. Now we will create a workflow that automatically adds a document to the Deviations library when an item is added to the DevInfo list. This will allow us to add a item to the list using JavaScript, and by extension create a new deviation automatically.

  1. In SharePoint Designer, click Workflows
  2. Select List Workflow and choose the list you created in Step #3. The list is “DevInfo” in my case.
  3. Give the workflow a Name and Description, and select the Platform Type “SharePoint 2010 Workflow” and click OK. I’m using the name “Generate New Deviation”.
  4. Click where it says “Start Typing…” and enter “Create List Item”.
  5. Click “this list” and select the library created in Step #4 from the List dropdown. For me it’s “Deviations”.
  6. You’ll see there’s one default Field called “Path and Name (*)”. Select this and click Modify….
  7. In the popup box, select the function icon and set the Data source to “Current Item” and Field from source to “Title (linked to item with edit menu) (old)”. Click OK and OK.
  8. Now add all your custom fields, using the Data source “Current Item” and matching field names. When done, mine looks like:8 Workflow Settings
  9. Click OK and Save.
  10. Go back to Workflows and select your workflow. Make sure Start workflow automatically when an item is created is checked. It is usually not by default. It should look like this:9. More Workflow Settings.png
  11. Click Save and then Publish.

Step #7: Manual test. Let’s make sure our workflow works as expected before proceeding.

  1. Go to the list you created in Step #3. For me it’s “DevInfo”.
  2. Click Add.
  3. Add your test info and click Save
  4. Refresh your list and you should see the status of the workflow. E.g.:10. Workflow in Progress
  5. Wait a bit, and if you refresh you should see “Completed.”
  6. Navigate to the library you created in Step #4 and verify your new Word document is there.
  7. Open it up and make sure your fields are populated as expected. Mine looks like:11. Autogenerated Word Document

This is as far as the blog posts mentioned above go. I’m going to take it a bit further by adding a simple HTML form to collect our field values, and then execute our workflow with a button click. This allows you to do custom validation and field value calculation. I’m doing it in this case so I can auto-generate the next deviation number for a given document to ensure no duplicates and give a better user experience.

Step #8: Upload the HTML file. You’ll need to store it in your SharePoint site somewhere. I prefer under Site Assets, but you could put it in a Document Library, for example.

  1. Download the file from AddNewDev.html. Remove the .doc extension. I have to add this so WordPress would let me upload the file.
  2. Have a look at it. You never want to upload something you haven’t at least looked at.
  3. Before uploading, there’s one variable you’ll need to customize. Look for var siteUrl = ‘’ and customize it for your site.
  4. In SharePoint Designer, go to Site Assets, click Import Files, Add File, browse to your file location, and click Open and OK.
  5. The file will be added to your Site Assets:12. HTML File in Site Assets

Step #9: Add the HTML form to a SharePoint site page.

  1. Now let’s create a landing page for our HTML form. Go to Setting -> Add a page and enter the New Page name. I’m going to call mine “DeviationsHome”.
  2. The page opens in edit mode. I’m going to change the Text Layout to “Two columns with header”.
  3. Now insert the HTML form in a web part. Click where you want to put it and select INSERT -> Web Part -> Media and Content -> Content Editor.
  4. The Content Editor should now be visible. In the upper-right hand corner, there is an obscure menu. Click the arrow and Edit Web Part.
  5. The Content Editor appears. There’s two things to modify here: set the Content Link to the URL of the file you uploaded in Step #8, and set the Chrome (under Appearance) to none. You can easily get the URL from SharePoint Designer by right-clicking your HTML file, selecting Properties, and copying in the Location property. We should look something like this:13. Deviations Home Content Editor.PNG
  6. Click OK.
  7. Now you should see the HTML form in the Content Editor. Click Save.
  8. You’ll see the HTML form rendered on the page, something like this:14. Add Devs Form
  9. Enter some data and click the Add button. If you navigate to DevInfo fast enough, you’ll see the workflow in progress:15. Auto-add in Progress
  10. And when the workflow is complete, you’ll have your new deviation, complete with specified metadata:16. Auto-added Deviation.PNG

Next step: display data in a graphic format.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.