Hello,
Unsure if this is possible but I will do my best to explain as clearly as I can.
We have a custom table called 'Risk' which contains all our Risk records that are regularly changed and updated.
One of the most common changes is to the 'Next Review Date' field. When a change to the record is made, the Governance team currently receive an email notification to state that the record has been amended. The email notification contains information on the 'Next Review Date field' and it shows what the value was and what the updated value is. The team then take this information and manually update an Excel spreadsheet so this update is tracked. They also track who made this change. Example email notification below:
Note: the previous date is logged in the email notification and this information is retrieved from a mail script, as shown below. This mail script is found in the Email Scripts table and called 'oldReviewDate'.
Their query to us was if this information can be automatically tracked more easily using ServiceNow. We suggested we can create a field in the form called 'Previous Next Review Date' which can be updated whenever someone goes to make a change to the 'Next Review Date' field and they can copy that date into the new field. This work has been done and is currently in our Dev environment while we look to make further enhancements.
My question is: is there a way to have the new 'Previous Next Review Date' field automatically update when the 'Next Review Date' field is amended, with the old value of the 'Next Review Date' field? Both of these fields are currently Date fields. So, whenever the date of the 'NRD' field is changed, the 'PNRD' field sees this and updates with whatever the old value of the 'NRD' field was.
Following on from this, is there a way of adding a field into the form that shows the user who specifically changed the data in that specific field. In the email notification it shows who updated the record, but this isn't specific to the 'NRD' field. Appreciate this might be even more of a stretch.
Thank you for reading and I hope you can share some insight into a potential solution? Any help with a script would be greatly appreciated too.
Kind regards,
Nathan Haywood
