Calculations - select a value from a specific cross referenced record
I am trying to solve a calculation problem.
In Federal Assessment and Authorization, one Application called Authorization Packages, cross references a set of records in another application called Allocated Controls. The Allocated control records contain a date, Assessment Date. We would like to make value of assessment date from a specific cross referenced record, that is a specific allocated control, available as a field in the Authorization package. The specific Allocated control containing the date we wish to see in the Authorization Package, would be selected based on the value of another field, say the date_flag, in the allocated control.
So the table layout would look like this:
ISSP_Assessment_Date this is set to the assessment date from the selected cross ref alloc cntl
cross referenced allocated control records
date_flag test this to select the record containing the date
assessment date contains the date
I'm looking for something like a "Selectif" calculation function e.g.
selectif ( TESTED_FIELD, SELECT_IF_TEST_VALUE, SELECTED_FIELD)
selectif (REF([allocated_controls],[date_flag],[control]), "ISSP", REF([allocated_controls],[assessment date],[control])
The closest thing i have found is "sumif". That sums numeric values from records that meet a selection criteria.
Any thoughts how I could accomplish this?
Thanks in advance.