Previous vs. Current Values - Identifying Trends and Repeat Failures

­­­­­­Today I will be discussing how, when under a few assumptions you can pull though previous values to compare to the 'current' one, making things like score trends and repeat failures easier to identify immediately within the current Record. This has many potential applications but perhaps one of the most obvious is in the context of Auditing.

Assumptions:

• The Audit / Grading takes place in a consistent and periodic manner i.e. Monthly, Quarterly, Bi-Annually, etc...
• That the above are always created chronologically.
• Once established initially previous results are never altered.

Fields Required:

1. General Date Field (To be used in generating a unique Reference for each Record)
2. Unique Reference Current Month (Intended to be hidden)
3. Unique Reference Previous Month (Intended to be hidden)
4. Current Score
5. Previous Score

Expressions for Corresponding Fields:

2. 'REF' + string(year([DateFieldID])) + string(month([DateFieldID]))
3. 'REF' + string(year(addMonths([DateofInspection],-1))) + string(month(addMonths([DateofInspection],-1))) Note the 'add' and -1 to allow for the Previous

4. Current Score will be manually entered
5. lookup('AppID','[UniqueReferencePreviousMonth]','UniqueReference','CurrentScore',0,true)

Syntax breakdown for the above is as follows:

lookup('AppID','[ThisAppFieldID]','LOOKUPAppFieldID','LOOKUPFieldToReturn','ValueIfNotFound',ExactMatch?)