Note: I edited your post to fix the formatting. There is an HTML tag <pre> that screws up the line wrap when it is inserted before a paragraph in this forum. I used the HTML editor to find those tags and remove them.
Min and max should return the earliest and most recent dates. Are the start and end date fields in the related table fields of type date? Do they refer to the correct field in the related table? (Is the field you are using to record the start date really d_task_start_date? (sometimes we have to check the obvious.)
What version of FileMaker are you using? (If this is version 12, maybe we've discovered a new bug...)
Other methods for getting earliest and latest dates:
Define summary fields in the related table that compute the minimum and maximum dates. Put these summary fields on your parent table layout or refer to them in calculations.
Sort the relationship by date in ascending order. A direct reference to the date field in the related table will then refer to the earliest date. Last ( relatedTable::Datefield ) will refer to the most recent date.
The start and end date fields are of date type. They both refer to the correct field in the related table. I am using FileMaker version 11.
I have tried both methods suggested and, unfortunately, the fields remain empty. The summary fields display the correct date and yet, I cannot perform a lookup from the t_consultant table.
I will try removing the fields and relationship and adding them back. I know there's something I did wrong somewhere, but I'm running out of ideas. I will post the results once that is done.
Thanks for the help!
Compare what you have to this demo file: http://dl.dropbox.com/u/78737945/MinMaxRelatedDatesTest.fp7
Not sure what you mean by this sentence:
The summary fields display the correct date and yet, I cannot perform a lookup from the t_consultant table.
What I meant is that putting a summary field on the task table showed the right results, but performing a lookup to display that same field on the consultant table showed nothing.
I redid the fields and relationships, making sure to follow the model in your demo file. Data is being displayed in the min start date and max end date fields, but only the data from the first record in the task table instead of the results of the min/max calculation.
That is correct. The calculations ONLY extract the minimum and maximum dates. They do not extract references to the records that store these dates. There could even be more than one record with the same minimum or maximum date.
If you want to refer to the record that stores that minimum and/or maxium date, you'll need to set up a different layout approach for that.
but performing a lookup to display that same field on the consultant table showed nothing.
"Performing a lookup" is what I am asking you to describe in more detail. Are you performing a find? Using Field options to display a looked up value field option (That's normally what we mean by a "lookup") or did you try just putting the field directly on your layout and viewing it in browse mode?
The last option should work for displaying the date, but it will not get you to the related record for that date.
Either sorted or filtered portals can be used to display the record with the minimum or maximum date.
This isn't an answer but just a comment of thanks! Benjamin, you asked the question in such a way as to get the answer I needed from PhilModjunk to solve my problem! His answer ROCKS and was so simple to implement!