AnsweredAssumed Answered

Get oldest and newest date from related record

Question asked by BenjaminDestrempes on May 18, 2012
Latest reply on Aug 26, 2012 by K.Bell


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)
    • id
  • t_task (table)
    • d_task_start_date (date field)
    • d_task_end_date (date field)
    • k_consultant_id

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. 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.