I'm a 3rd year OB/Gyn resident and I've created a simple patient db. I have two tables -- patient info and pending issues. The patient info includes the usual stuff (name, resident physician, complications, etc.). The issues table includes pending labs and/or follow-up tests, therefore each patient can have several "issues". End users can add "pending issues" for a patient via a portal from within the "patient info" table.

What I would like to do is create a field which represents the most recent modification date for a patient, i.e. either the modification date for the "patient info" table or the most recent "pending issue" mod date for a particular patient. I can't figure out how to do this within a single field calculation, so I'm thinking I might have to write a script. Is there an easier way?

Thanks in advance!

Create a calculation (date) within Patients. It will automatically be unstored:

If(Last(Issues::ModDate) > ModDate; Last(Issues::ModDate); ModDate)

Max( Last(Issues::ModDate); ModDate ) may be faster.

In either case, the Issues relationship should be sorted by ModDate to ensure Last does indeed retrieve the most recent one.

If Issues is sorted in ModDate descending order, you could use Max( Issues::ModDate; ModDate ).

In either case, the Issues relationship should be sorted by ModDate to ensure Last does indeed retrieve the most recent one.

Thanks for the catch Queue. I tend to think of date as Activity Date or Creation Date; usually appearing in ascending date order. Modification date would indeed be out of sequence.

Thanks for the input -- works like a champ!