Building a Contracts Management Experience with SharePoint and Power Automate
We’ve built a few contract management solutions lately, and we thought we’d share some guidance if you’re looking to do the same.
A disclaimer – understand the problem first!
First a disclaimer…we base our contract management solution on industries we’ve worked with such as municipalities, energy companies, and other organizations. Some organizations might need more sophisticated management, and in that case, this SharePoint solution might not be for you.
However, in most cases our SharePoint Consulting can fine tune a solution simply using SharePoint sites, content types, and Power Automate that meets most of the requirements, is easy to use, and doesn’t require learning a new tool.
To start, take time upfront to understand the problem before diving into solving the problem. This is as simple as having a conversation with a representative sample of people who deal with contracts to discuss their workflow. If you can, have them visually walk through all the steps they go through to manage a contract from end-to-end. This could be from email, to a word processing tool, to a network file drive, to their own desktop, etc. Take time to understand what cause problems, what people procrastinate doing, and situations where mistakes happen. Understanding these pitfalls will help you design a more robust solution.
After a few interviews, present back a summary of the problems, findings and recommendations to make sure you understood everything. Typically, we see problems such as:
Contracts are managed in different places by various groups and people
No one knows who has the final copy, duplicates exist, and people keep a copy before sending to a central management group
When a contract is about to expire or renew, it's unclear who reminds who and deadlines are often missed
Similar types of contracts (such as leases) are not saved together, making them hard to find
Lack of a single source where people can share and draft contracts
Everyone who needs access can’t always access the contracts they need
Contracts Site Design
After you understand the contract management problems and desired outcomes, you can start to think about what the new system in SharePoint needs to do. Here is how we typically design and architect the solution in a centralized approach:
Consolidate all contracts into a site
We run into the question time and time again – is it better to store all like items, such as contracts, together in a site or keep them with the departments so they are spread over multiple sites? There are pros and cons to either approach.
We find that most organizations have a central person or group who needs to manage, review and signoff on contracts. In this case, it’s better to have all the contracts saved in a central site where they can easily view statuses across all contracts. It’s harder to roll up and view documents across many sites so we recommend for simplicity to store them all in one place. In that site, setup up a library and a document set content type for contracts. Department representatives or managers upload their contract when it’s in development or ready for review by the central team.
If you need to have different permissions on different types of contracts – for example, employee contracts – store those in a separate library or site with its own security, as we don’t recommend item level security for managing contracts.
Document sets
Use a document set for each contract for ease of holding all related documents together. Document sets are advantageous over folders because you can set metadata at the document set level, and it can inherit down to all the documents within, allowing you to only enter the metadata once for the document set, and not have to tag each individual document within.
Suggested content type metadata
The actual metadata you need for organizing and tracking contracts will vary depending on your organization and industry. Here’s a list of common set of metadata as a starting point:
Vendor: The vendor the contract is with
Vendor Contract: Contact info of the vendor
Originating Department: A managed metadata term set for the department that owns the contract
Contract Type: To differentiate between different types of contracts, such as lease, service contract, or something else.
Contract Status: Use this to track Pending, Active, and Expired contracts
Start Date: The start date of the contract
End Date: The end or expiry date of the contract
Project: Optional field to track a Project the contract is related to
Insurance Expiry Date: If you require insurance as part of the contract, this will help to track the expiry date
Automate metadata
As much as possible, you want to automate the setting of metadata. Humans are notoriously bad at tagging and assigning metadata to documents. For example, we can assign the Contract Status column automatically based on date – if today’s date is passed the expiry date, it’s automatically set to Expired.
Here’s a diagram to show how this works:
Workflow to automatically set Contract Status based on date
How to build in Power Automate
Here is how to schedule weekly runs in Power Automate to check if a contract has expired. Start with the Recurrence template:
Overview of the Power Automate flow that checks expiry every week
Each steps looks as follows:
1. Run the workflow at a specific time (i.e. Sundays at 10am
2. Get the Library list through a HTTP request
3. Get all the items that makes a specific requirement (i.e. Contract End date has passed)
The filter query for expired contracts is:
concat('EndDate',' lt ','''', utcNow('yyyy-MM-dd'),'''')4. Then loop through all the items that are found to update them as “Expired”
Similarly, to update the contract to Active, just change the filter query to:
concat(concat('StartDate',' le ', '''', utcNow('yyyy-MM-dd')),'''',' and ', concat('EndDate',' ge ','''', utcNow('yyyy-MM-dd')),'''')For Pending Start date, the query is:
concat('StartDate',' gt ','''', utcNow('yyyy-MM-dd'),'''')Power Automate reminder flows
The final piece is building flows to help users track and manage contracts.
You start an approval process and send an email when a new contract is ready for review by selecting a contract and then clicking “Automate” -> “Request sign-off” and create the flow:
Request sign-off flow
You can also customize the built in “Set a reminder” flow on the contract end date to send an automatic reminder email to specified people based on the originating department 30 days before a contract or the insurance is about to expire.
To customize the built-in reminder flow for the Contract End Date:
1. Create the flow directly from your contracts library by selecting “Automate” -> “Set a reminder” -> End Date
2. Open up Power Automate, find your flow, and click edit, it should look like this:
3. Remove the “Get my profile” and “Couldn’t send a reminder” step, as you will want to send the notification to a specific person or group of people, and not yourself
4. Update the “Days to remind me in” to 30
5. Open up the “Scope to get items expiring in 30 days” and add a condition after the “Get Items” step to filter by the Originating Department the contract belongs to, so you can send the reminder to the corresponding manager or representative. Since we are using managed metadata for the Originating Department, our condition is based on the TermGuid of the value.
6. Add a “Send an email notification” as your last step and make sure it goes to the right person!
The entire “Scope to get Items expiring in 30 days” step looks should look like this:
In summary
That’s it! We find that the process from start to finish takes about 6 weeks, but it can be faster if the users you’re working with are responsive and engaged during the rollout. Our clients find that it’s easier to filter and find contracts with a solution like this, and contracts are less likely to fall through the cracks and expire.
We offer SharePoint and Microsoft 365 consulting support if you need help building out your organization’s contract management solution. Reach out if you need any advice!
