6 Replies Latest reply on Aug 26, 2012 12:29 PM by K.Bell

    Get oldest and newest date from related record

    BenjaminDestrempes

      Title

      Get oldest and newest date from related record

      Post

      Greetings,

      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.

      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.

        • 1. Re: Get oldest and newest date from related record
          philmodjunk

          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.

          Or

          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.

          • 2. Re: Get oldest and newest date from related record
            BenjaminDestrempes

            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!

            • 3. Re: Get oldest and newest date from related record
              philmodjunk

              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.

              • 4. Re: Get oldest and newest date from related record
                BenjaminDestrempes

                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.

                • 5. Re: Get oldest and newest date from related record
                  philmodjunk

                  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.

                  • 6. Re: Get oldest and newest date from related record
                    K.Bell

                    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!

                    I have been pulling my hair out creating a report in which I needed to show the latest date contained within a portal. This was the solution I needed in order to solve my issue! Thanks! Smile