Posted in Records management, Electronic records, Products and applications, Legal, Training and education, Information Management, SharePoint Online, SharePoint Designer

Auto-populating document templates via a form in SharePoint

Most organisations have standard agreements or contracts or similar types of documents.

The common factor between them is that the original template remains the same while elements within the document change. For example, a client name, address and phone number, or differing contract terms.

There are several different ways this is achieved, including:

  • Printing the form and completing it manually.
    • This is time-consuming, handwriting can be difficult to read or require the form to be re-completed, and there is no easy way to extract the data. These types of forms are often scanned for storage.
  • Completing a digital version in Word (and sometimes printing/scanning or saving as a PDF).
    • This is also time consuming and in many cases it can be faster to print the form to fill it in by hand. Errors and omissions are possible and if the metadata appears in more than one place it must be re-typed. There is no easy way to extract the data.
  • Using editable PDF forms, sometimes using (Adobe or other) digital signatures.
    • These are very common (and very useful for specific purposes such as simple forms, less so for common agreements). They are time consuming, errors and omissions are possible and metadata must be re-typed. There is no easy (or cheap) way to extract the data.

Common factors in all of the above are that they are time-consuming and the data is hard to extract from the form.

A better and more efficient option

This post describes how to create a form in SharePoint that, via a very simple workflow:

  • Auto-creates one or more Word documents (multiple based on metadata choices contained in the form).
  • Auto-populates the Word documents where required with the metadata in the form. Where the same metadata value (e.g., ‘Client Name’) appears more than once, that value appears throughout the document where required at the same time.
  • Stores that document (or documents) in a folder (actually a document set) that can be used to add other content.

Additional benefits are that:

  • The metadata is easily accessible for export and other uses.
  • The Word document can be ‘signed’ with a touchscreen computer.
  • The Word document can be saved as a PDF.
  • Other documents can be added to the same folder.

This post is based on several actual examples that I developed (with the assistance of our SharePoint Developer) in a very large (9,000 staff) organisation.

The primary uses were for client agreements based on standard templates, including up to 10 different documents per client. We also deployed other designs that used a similar methodology, but the underlying principle was the same.

Note that, while the model is actually simple to implement, this post contains all of the details to follow step by step. I’m not a fan of posts that only provide part of the details and leave the rest to the imagination.

Setting up the model

Important note: The SharePoint site MUST have the document set feature enabled in the Site Collection Administration settings. Otherwise, the option to create a custom document set will not appear.

The model consists of the following elements that can be created by a SharePoint Administrator, a Site Collection Administrator or a Site Owner.

  • New site columns that will map to the elements in the form. For example, ‘Client Name’, ‘Client Address’, ‘Client Phone Number’. Note that every SP site has a lot of standard site columns so some of these can be used instead of creating new ones.
  • A new document set site content type containing all the site columns that should appear in the form. (‘Add from existing site columns’ option). It is recommended you give the document set a name that will be clear to end users as they will select this from a list. For example ‘Client Folder’ or ‘Agreement folder’.
  • A new document site content type for every template that is needed. The actual document template are not added now, only after the content type has been added to the document library – see below. It is recommended that you give each of these document CTs a name that is similar to the name of the document template.
  • A document library. It is recommended that you create a dedicated library for this purpose with a name that makes it very clear what it houses, for example ‘Client Agreements’. See below for the set up of the library.

Once all of these options are in place, the SharePoint Designer workflow can be set up – see below.

Setting up the document library

Library settings

The document library needs to be set up as follows in the Library Settings section.

  • In Advanced settings, enable the option ‘Enable management of content types’. This will make a new section ‘Content Types’ appear in the Library Settings.
  • In the newly visible ‘Content Types‘ section and choose ‘Add from existing site content types’ and add all the new site Content Types that were created.
  • The newly added CTs will now be visible, along with the default ‘document’ content type.

Document set CT settings

Click on the new document set CT. The metadata site columns that were added should be visible in the ‘Columns’ section.

Click on ‘Document Set settings’. In the section ‘Allowed content types’, click then use the ‘Add’ option to add all the document CTs that are required. These will now appear in the right-hand section.

 

O365_SPO_AddDocCTtoDocSetCT

Scroll down to ‘Shared columns’ and select all the document set columns. It does not matter that these will be shared with document CTs that don’t use the columns, as we will see below.

Click OK and return to the library settings area.

Adding the templates

At this point it is assumed that you have one or more document templates ready to upload. The template/s should be in a newer version of Word (e.g., .docx NOT .doc).

The ‘Content Types’ section of Library Settings displays a list of all the CTs that were added, including the document set CT (which will not be changed).

To add the template, click on the name of the (document) CT. In the new page that opens, you will see the list of site columns that have been shared from the document set.

Click on ‘Advanced settings’, where you will see the ‘Document Template’ section. Click the ‘upload a new document template’ option, choose your document template, and click OK.

O365_SPO_AddTemplatetoDocCT

Link the metadata columns with the template

Now, return back to the document CT ‘Advanced Settings’ (if you are not still there) and click on ‘Edit Template’ to open the template document in Word.

Now, add the metadata site columns where they are required in the template. For example, next to ‘Client Name’, place the cursor where you want the metadata to appear (don’t forget to include a space!).

In Word, go to the ‘Insert’ option on the ribbon menu and then go to the ‘Text’ section. Choose the ‘Quick Parts’ > Document Property and you should see the metadata columns as shown below.

O365_SPO_InsertMetadatainDocTemplate

Add the relevant document metadata where it should appear in the Word template. You will notice that the same metadata element can be used in multiple locations throughout the document. You can also use these in the header and footer and apply different formatting as required.

If you have made an error, do not ‘delete’ the added metadata in square brackets, instead right click and choose ‘Remove content control’. Be careful of formatting too especially different fonts and font sizes. Some of these will be more visible once you create the first document (see below).

The finished template will look something like the screenshot below.

 

 

O365_SPO_MetadataInWordTemplate3

Repeat for each content type template.

Summary and outcomes of the first stage

The site and library set up stage is now complete. The new content types now appear in the ‘New’ menu as shown below. You may want to edit the new menu options to remove any option you don’t want to appear, such as ‘Folder’ and ‘Document’ (you cannot remove ‘Link’).

O365_SPO_CustomCTDocLibNew.JPG

If the end user selects ‘Client Agreements’, they will be presented with a form to complete such as the example below – but this does NOT yet create the template document. That’s the next step below.

 

O365_SPO_CT_DocSet_NewForm3.JPG

 

Note that the order of these metadata elements can be moved around as required via the document set settings.

Create the workflow

You will need access to and be able to use SharePoint Designer to complete this section.

Remember: The workflow is based on the end user selecting and completing a new (document set by completing the form as shown above. The workflow is triggered by the fact that a new item has been created, which in turn creates and saves a new document (or documents as required) with the metadata populated automatically ‘inside’ the new document set.

Open SharePoint Designer

First, click on ‘Lists and Libraries’, choose the library that the workflow will be associated with, then click on ‘List Workflow’ as shown in the ribbon menu below.

SPD_NewListWorkflow

Give the workflow a name that will help to identify it in future – in this example, ‘Create Client Agreement’ would be a suitable name. Note:

  • You must create this as a SharePoint 2010 workflow.
  • The workflow can create one or more documents. In this example, only one document is created.

New workflow settings

A new tab will open. On the top right of the ribbon menu, click on ‘Workflow Settings’.

In the ‘Start Options’ section, check the box to start the workflow automatically when an item is created. The manual start checkbox should already be checked. This will allow the end user to run it again if required.

SPD_StartOptionsSP2010

Note – Some organisations may prefer not to allow the workflow to start automatically because they want to check the form first. In this case, the document set-based form can be created, but only after it is created the end-user must choose to run the workflow via the ‘More – Workflows’ option from the 3-dot menu.

 

Create local variables

Click on the ‘Local variables’ option on the top right of the ribbon menu to create (Add) two local variables:

  • DocSetName < this one is used to record the name on the document set.
  • DocumentPathforClientAgreement < this one is used to save the new document ‘under’ the document set.

Create the workflow

In the Workflow settings, click on ‘Edit workflow’ to create the workflow. For this example, there are two steps.

Click on ‘Step’ to change the name to something like ‘Initialisation’ or ‘Initialise variables’.

SPD_WorkflowNewStep1Blank.JPG

In this part we add and configure the two local variables that were created.

Click where it says (‘Start typing …’), click on on ‘Action’ in the ribbon menu, and choose ‘Set workflow variable’ to set the two variables.

  • Set Variable: DocSetName to Current Item:Name
  • Set Variable: DocumentPathforClientAgreement to [%Variable: DocSetName%]

Both of these will be set as a String value.

SPD_SetSP2010WorkflowVariable1.JPG

Click just underneath the step; a short orange line should appear. Click on ‘Step’ from the ribbon menu to create the next step.

(Note – a screenshot of all the following steps can be seen below)

  • Rename the step if required (e.g., to ‘Create Agreement’).
  • Click in this new step where it says (‘Start typing …’), then click on ‘Action’ (ribbon menu) and choose ‘Create List Item‘.
  • Click where the new action says ‘this list‘. A new dialogue box opens ‘Create new list item’. Select the name of the library from the drop down list in that dialogue box.
  • As soon as you do this, ‘Path and Name (*)’ appears below ‘Content Type ID’. You must complete the second part of this command before it can be saved.
  • Click on Path and Name (*) and click ‘Modify’. The ‘Set this field’ option should not be changed, only the option ‘To this value’. To the right of the blank field click the ‘fx’ option, then do the following.
    • For ‘Data Source’, choose ‘Workflow variables and parameters’.
    • For ‘Field from Source’, choose ‘Variable: DocumentPathforClientAgreement’
    • For ‘Return field as’, leave it as a ‘string’ value.
  • After you click save, the ‘Value assignment’ dialogue box should still be open. If not on the ‘Path and Name (*)’ option, then Modify, which will open the ‘Value assignment’ dialogue.
  • Click on the three dot menu option (to the left of fx) to open the ‘String Builder’ dialogue. Modify it as shown below by adding the prefix text. This puts the name given in the document as the first part of the document name: [%Current Item:Name%]/[%Variable: DocumentPathforClientAgreement%]
  • Note, you can add anything else you want after the last ‘]’, for example ‘- Client Agreement’, as a suffix to the document name.

SPD_WorkflowNewStep2CreateListItemA.JPG

Click OK (several times) to close the dialogue.

Add a ‘Stop the workflow and log’ option from the Action menu.

The final workflow is shown below:

SPD_TwoStepWorkflowFinal

Publish the workflow

Finally, publish the workflow. You can also press ‘Save’ to save without publishing. Publishing also saves any changes.

Allow some time for the workflow to appear in the document library. Generally this is fairly quick – refreshing the site page may assist.

Confirm the workflow is ready

To confirm the workflow is ready, click the three dot menu to the right of the document set and click on ‘More’, then ‘Workflow’.

The new workflow should appear similar to the screenshot below.

Note that this is the primary interface for most actions relating to the workflow. From here you can click the workflow to run it again any time (Manual start). If the workflow has a problem you will see that message here under ‘Running Workflows’; from there you can terminate the workflow if it has a problem (which sometimes happens – the clue is that the document was not created).

SPD_LibraryWorkflowReady

End result

When the end user completes and saves the form, the workflow will run, creating one or more documents (based on the template) ‘inside’ the document set. Each document will have the correct metadata based on the template.

O365_SPO_MetadataInWordTemplateFilled3.JPG

 

Benefits

There are many benefits to creating this model to manage common document agreements, contracts and other templates.

  • The document template always remains the same and can be updated at any time (but note that entire template updates require re-connecting all the metadata elements).
  • If a mistake is made in the metadata, the end user can simply delete the documents that were created and re-run the workflow as many times as required, saving a lot of effort in having to re-populate an entire document. If there is concern about deleting documents, the manager can set an alert on the library. The Recycle Bin keeps deleted documents for 90 days.
  • All Word documents created this way include the metadata from the library in their properties (the ‘metadata payload’). This includes the Document ID (if enabled).
  • Once the Word document has been created it can be ‘signed’ electronically using touch screen technology. If you really need a more sophisticated signing process, consider acquiring a third-party product.
  • Once the Word document has been signed in this way, it can be saved as a PDF, preventing changes.
  • If saved as a PDF, the defaults save location is the same location. Saving to PDF is a three step process: Open the Word document, click ‘Save as’, and change the option to PDF.
  • All the metadata site columns can be exported for analysis and reporting purposes. It may be also be used to created groupings of records for example ‘All contracts created by users’, or ‘All contracts that have a specific metadata choice option’.
  • The newly created Word or PDF documents can be shared, including with external people if required.

Negatives

In practice we found that there were not many negatives associated with this model and it brought considerable productivity benefits to the business areas that regularly created multiple agreements with clients, based on standard templates.

The primary negatives we found were:

  • Poor bandwidth meant that the new Word agreement may not create as quickly as required. Business areas with this problem kept both digital copies of the agreement to complete or printed versions.
  • If the entire template had to be changed, all the metadata links had to be re-connected. It was usually much easier only to update the part of the document that needed to be updated, including by adding new pages.
  • Every once in a while the workflow would not work. Our first clue to this was that an end user would call to say the document was not created or a metadata field was blank. We could usually track this problem down to either a network ‘glitch’ or other minor issue.
  • If metadata fields are left blank in the form, the square brackets metadata option remained visible. This then had to be deleted from the final.
  • From time to time, for various reasons, the end user would create a second copy of the document template without deleting the first. This simply creates a new document with the date and time as a suffix to the document name.

Author:

I am an experienced information management professional based in Melbourne, Australia. I have had close to 40 years of practical working knowledge across the full spectrum of information, records and content management issues, and direct and practical experience with contemporary and emerging business and information and enterprise content management systems. My product knowledge includes SharePoint 2010/2013/Online and OneDrive (SharePoint Administrator), Office 365 (including as a Global Administrator), Yammer, Sway, TRIM Context (R6.2 & 7.1), ECM Documentum, Alfresco Share; and other online systems. www.andrewwarland.com.au

Leave a Reply

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

WordPress.com Logo

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

Google photo

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