I need help with a calculation that compares the content of three date fields in a related file. This may be the long way around a problem, so I'd like your help, please.

I have two related files named Parent and Child

Fields in Child are called Type1date, Type2date and Type3date.

I need to 1.) find the most recent date in each of the three fields in Child(which may have different dates on related records); and 2.) make the most recent date of the three appear in a separate field in Parent to be used in another calculation.

In the Parent file I created:

RecentT1date (calc field, date result) = Max(Parent to Child::Type1date)

Ditto for Type2date and Type3date

The calc field values work just fine. How do I now create a field called RecentTypeDate that will display the one date of the three that is the most recent? Is this one of those situations where I would use the "Case..." function? Seems like there must be an easier way than that.

Ideas and gentle critiques are welcome.

Mary Z

Version: v5.x

Platform: Windows XP

You could do it 2 ways:

1. Using your existing calcs: Max (RecentT1date, RecentT2date, RecentT3date)

or 2. Create a Max for each record in the child file: Max ( Type1date, Type2date, Type3date )

and then in the parent, instead of 3 calcs, you'd have one: Max ( Parent to Child::theMaxDate )

The second method seems more efficient, as it requires only one calc in the child and one in the parent vs. 4 calcs in the parent with your method. I would think it would be faster as well, although Max calcs in general are notoriously slow -- it depends on the number of related records.

Thanks, Fitch. I went with the first option and it worked.

For the sake of efficiency I tried the second one, but couldn't make it come up with the correct result. In the parent file, the Max calc would only show the first date (not the most recent) in each related record.

No matter. You gave me a solution that is much better than my Case function idea.

I didn't know you could have more than one field with the Max function. Where does one learn these little tidbits? Certainly not from the FM user's guide!

Thanks again,

Mary Z

It will work as described. Make sure you define the calcs to return a date result. Check the max calc in the related file to make sure it is correct. Then make sure you're setting the parent calc to the max of your new related max calc, not the max of the related date.

The Max function is described in the FileMaker online help, that's always a good place to start. FileMaker's online help is much more extensive and useful than in most other applications.