Get oldest and newest date from related record
I am fairly new with FMPro so this may seem like a silly question but it has been bugging me for a while, so I turn to you guys. I am trying to get the oldest and newest date from a related record. Here is what the basic structure looks like:
- t_consultant (table)
- d_consultant_start_date (date field)
- d_consultant_end_date (date field)
- t_task (table)
- d_task_start_date (date field)
- d_task_end_date (date field)
Basically, each entry in the consultant table can be related to multiple entries in the task table. What I would like to do, is to have the d_consultant_start_date field in t_consultant look through all the related t_task entries and return the earliest date it can find. Likewise, d_consultant_end_date should display the latest date it can find.
t_consultant.id and t_task.k_consultant_id are linked by a relationship.
t_consultant.d_consultant_start_date is a calculation (Min ( t_task::d_task_start_date )) that returns a date value. t_consultant.d_consultant_end_date is the same thing with Max instead of Min. Unfortunately, the two fields will not display anything. All fields contain appropriate values so I assume I am doing something wrong either with the relationship or with the calculation.
Any help will be appreciated. Thanks for your time.
EDIT: Gee, formatting is all over the place. My apologies.