How to use SharePoint to track annual competency tests: Part 1 – Lists

How do you ensure that your employees know and understand key policies and regulations?

For a lot of organizations, the answer is to have employees take a test or exam each year. Taking a test is a reliable way of checking understanding – more reliable than simply tracking training or checking people have read documents.  And by testing every year, you ensure that people refresh their memory and still know the policy.

But how do you keep track of whether your employees have passed the test each year, and prove it to an auditor? It’s not as simple as it sounds – you have employees joining at different times in the year, people off on maternity and other leave, employees who fail, employees who prefer not to be tested and other exceptions. Many organizations use compliance management systems or learning management systems (e.g. SAP LSO). Others use spreadsheets which can also do the job, but involve a lot of manual work.

This blog series will show how to do this in SharePoint, so your tracking is integrated with the rest of your organization’s IT. The example I’ll use to illustrate in this post is a simple one of a company that needs to run data security tests on its employees and contractors once a year.  This example is close to heart as I run a similar data security programme at my company! The needs are:

  • When an employee joins, they have to pass a data security test in their first week
  • And every employee has to pass the test at least once a year

Here is one way to do this in a SharePoint list.

1. Create a new SharePoint list. Either as a new custom list, or if you have an Excel spreadsheet with pre-existing data, import it from there.

2. I suggest the following columns:

  • Employee name (I’m using text in this example, but using a SharePoint person field type is preferred if you have your employees set up in SharePoint)
  • Department or other location data
  • Joining date
  • Date of last test
  • Date of next test


3. Use a calculated column for the date the next test is due – set this to 365 days after the last test date or 7 days after joining, see the screenshot below for how to set this. The formula says that if the date of last test is not set, the next test is due 7 days from the date of joining, otherwise 365 days from the last test.

calculated column

4. Then put in your data (datasheet view can be a great way of doing this quickly)

5. Set version history on, that way you can always look back at the history of someone’s test taking for audit purposes.

version history on

6. Turn off attachments, you don’t need them in this list.

7. You can then make one view containing all the items and one view “Need action” of people who need to retake the test in the next month. You can filter a view to just those that need action using a filter as shown in the screenshot below. This shows all people who have a test due within 30 days of today.


In the screenshot below, the dates are shown in European format: Sam Brown and Jane Shepherd need to take tests in early September, and Basil Moriata needs to take a test by next week.

list view

Using a list in SharePoint improves a lot on using a spreadsheet or a wiki page to keep track of assessments. There is a lot more you can do to make things easier, all without programming or custom software; in future parts of this blog series, I’ll show how to use workflow to send reminders so you don’t need to check the site to see what tests are due, and how else to use SharePoint out of the box to automate test and certification management.

In the meantime, check out the category list on the right to see other articles in this blog to help you get more value from SharePoint.

This entry was posted in Compliance, How-To and tagged , , , , . Bookmark the permalink.

4 Responses to How to use SharePoint to track annual competency tests: Part 1 – Lists

  1. Mark says:

    I have found your blog to be extremely helpful. I am using Nintex workflow and have developed a few workflows but some have flaws. They work but could be smoother. I am looking forward to Part 2 of this post. When do you expect it to be available please? Thanks.

  2. johnkleeman says:


    Thanks, I’ve been exploring how to do reminders best in Workflow, it’s not quite as simple as I’d like it to be, and still doing a bit of experimentation. Thanks for the push, will try to bring part 2 out reasonably soon.


  3. Pingback: SharePoint to track competency tests - Elusive reminders | SharePoint and Assessment Blog

  4. Emeline says:

    It’s great to read something that’s both enjoyable and provides pragatmsidc solutions.

Comments are closed.