000038698 - How to Calculate the Most Recent Date from list of Sub-Form records in RSA Archer

Document created by RSA Customer Support Employee on Apr 14, 2020
Version 1Show Document
  • View in full screen mode

Article Content

Article Number000038698
Applies ToRSA Product Set: RSA Archer
RSA Version/Condition: 6.x
IssueUser needs to build a calculated field to pull the most recent end date from the records in the subform.

For example, a user wants to track the most recent item end date for a subscription contract. Each purchase would be documented in the "purchasing" subform with a new "Item End Date", but we want the parent to track the newest "Item End Date" in the "End Date" field for easier visibility.

User-added image
  1. Edit the parent field, in this case, "End Date", and change it to a calculated field.
  2. Using the MOSTRECENTVALUE function, check the "Item End Date" value for the most recently updated value.  Each time a new record is added to the subform, the calculation will pull the newest date from those records.  

    MOSTRECENTVALUE(REF([Purchasing],[Item End Date]),REF([Purchasing],[Item End Date]))

MOSTRECENTVALUE function syntax: 

MOSTRECENTVALUE(field_to_display, date_criteria_field)