finding most recent date and comparing it

[Bluesummer]

I have 4 files: Donors, Donations, Orders, Letters

I have a date on each of these:

Donors - Creation date

Donations - Date of Donation (Auto enter, creation date)

Orders - Date of Order (Auto enter, creation date)

Letters - Date of Letter (Auto enter, creation date)

On the Donor file, I would like to create a field showing the date of the most recent donation, one showing the date of the most recent order, and one showing the date of the most recent letter. (I have all the relationships set up.)

Then I would like to compare the 4 dates and if all of them are 2 years or older, I want to be able to archive them.

Thanks so much for your help,

Blue

[Ugo DI LUCA]

Hi,

In each related file, create 2 calculated fields.

c_markrecord = Case(recorddate<(Year(Status(CurrentDate)-2),0,1)

c_markallrelated = Case(Sum(SelfjoinOnDonor_ID::c_markrecord)<1,0,1)

....where the SelfjoinOnDonor_ID is a relationship from the Donor_ID to the Donor_ID within the same file.

Then, in your DonorFile.fp5, use this calculation

c_archiverecord = Case(c_markallrelated(FileDonation) + c_markallrelated(FileOrder)+c_markallrelated(FileLetter)=0, "Inactive", "Active").

So you now can archive all c_archiverecord's where the result is "Inactive" (couldhave also be a boolean result (1 or 0).