We added several objects to support Assessments, primarily for client assessments but potentially for more. First we created a template object. They mostly hold the name and description. If heavily adopted additional fields may be needed over time. Then we created template elements in master/detail relationship (although similar elements may need to be created). The element mostly holds the type and subtype. Type and subtype are picklists designed to mesh with our existing Client Note object through good definition of values.
We created an Assessment object which points to an Assessment Template and Contact by lookups. It contains a picklist “Type” and a date and date span field to help define the assessment. Date is always needed. Date span guides an automatic assessment in gathering client notes. There are two automatic types defined for time spans: Auto Last in Period and Auto First in Period. Last is intended to define a date span and connect to the latest of any notes matching the template elements. Earliest is similar. Auto First and Auto Last have no time period but rather work on all notes. The assessment element object is in master-detail relationship with an assessment and contains a score. The M/D allows the assessment to have a count, total score, average score, and percent complete rollups or formula fields based on the current assessment elements attached. The assessment element is a junction object and also is in master-detail with a Client Note object. The assessment contains the score, intended to be based on the Client Note.
The five objects work together as shown in this ERD:
We intend to add a lookup from one assessment to another in future to allow comparison of one assessment to another as a baseline. This would be by formula fields comparing assessment element count, assessment improvement points, assessment average improvement.