Date Comparison on a Cross-reference
I have a cross-reference where I am checking a values list field for a specific value. I need to check for this value if the date of that record is greater than a date recorded on the parent.
Engagement record has a field [Most Recent RAQ Approval]
Cross-reference to Engagement Questionnaire contains [Created Date] and [Pass/Fail]
I want to check for any [Pass/Fail] that = "Fail" only if the [Created Date] of that questionnaire is greater than the [Most Recent RAQ Approval].
My calc is successful using contains for the pass/fail. Not quite sure how to compare the date fields.
Appreciate the assistance!! 🙂
Jacqueline, what you can do is create a calculated date field in the questionnaire that sets the date from [Created Date] if [Pass/Fail] = 'Fail'
Then in the parent you can do a MAXA() on that calculated date field and compare it to the [Most Recent RAQ Approval]
I'm not quite sure I follow as I'm not quite sure how that will work. I attached a screenshot of my cross-reference for visualization. If any of the current EQs Fail, the overall status of the Engagement will be "Exception", similarly if all of the current EQs pass, the overall status of Engagement will be "Approved". This cross-reference will hold years of EQs, making it more difficult to address only the "current" - current EQs would have been created after the last RAQ was approved. Is your recommendation still as your proposed?
I would solve this as follows:
- In the child application, create a calculated date field that pulls in the [Most Recent RAQ Approval] from the Engagement record.
- In the child application, create a calculated Yes/No values list field. The calculation will check if Pass/Fail = Fail, and if [Created Date] is greater than [Most Recent RAQ Approval]. If these are true, set the field to "Yes", otherwise it will be "No".
Then in the parent application you can look for records from the child application with a value of "Yes".