Customer Forum

Compliance Records that will expire in the near future

Sam (Workbooks Online) Posted: 2018-04-19 13:48

NOTE: This report forms part of our GDPR Packaged Service. For more information on this service please contact our support team on support@workbooks.com. Alternatively, you can implement this report and the GDPR Solution yourself using the GDPR Plugin available from the Workbooks Script Library. If you are following the Plugin, please ensure you name this report exactly as specified otherwise you will not be able to complete the installation. Most of the fields used in this Report, reference Custom Fields that are created via the Plugin.

This Report is built from Compliance Records and allows you to see which Records will expire within the next 90 days or this month on separate Summary Views. It provides one way to stay on top of your GDPR compliance monitoring by showing you which Compliance Records will expire in the near future.

Below we outline how to build the Report as well as some ideas for Summary Views that can also be added to present other useful bits of information. We have provided a Template Report with all of these views created for you which is available on Report Library. Click New -> Report -> Create a report from a template report and select "Template - GDPR - Compliance Record Management.

From here, if you are using the GDPR Plugin, please save the report as "GDPR Compliance Record Management". The following steps describe how each of the views on this report works.

TIP: We recommend that you add this report to a GDPR Compliance Dashboard to help you better manage GDPR Compliance in Workbooks.

All Compliance Records (Details View)

The details view does not have any criteria added, thus shows all of the Compliance Records in your database and when they expire.

Compliance records that will expire this Month (Summary View)

The first Summary View you can build is to look at all Compliance Records that will expire this month using a criteria to look within the calendar range of this month only.

Compliance Records that will expire in the next 90 days (Summary View)

This Summary View is a direct copy of the first Summary View with a minor tweak to the criteria. We now use a calculated column to find any Compliance records that expire in the next 90 days. This number could easily be any other figure as required. By default our report will be set to 90 days.

IF(PARENT('Compliant Until Date') <= DATE_ADD(CURDATE(), INTERVAL 90 DAY) AND PARENT('Compliant Until Date') > CURDATE() , 1,0)

This calculation means that if the Compliant Until Date is less than or equal to ninety days from today then output a 1, otherwise output 0.

Once created we can hide this Column as it is purely used for calculation purposes.

The only criteria we need on this Summary view is a calculated criteria of “Compliance expiry” = 1. This will now only output People or Leads where there Compliance will expire in 90 days or less.

Compliance Records that have Expired (Summary View)

This Summary View is again a direct copy of the first one (people or Leads that will lose compliance this month) however we change the Criteria to the Compliant Until Date is before today, showing us all of the Compliance Records that have expired or been revoked.