2 Replies Latest reply on Nov 19, 2013 7:55 AM by Insight

    Get the highest date of related records in related table

    Insight

      Title

      Get the highest date of related records in related table

      Post

            

           Hi 

            

           I'm looking for a way of getting the highest date of all related records in a related table

            

            

           I have a facility actions required FM data bases

            

           Three tables create the Tasks needed to be done

            

           1. Sections - 2. Activities needed to be done - 3. Tasks assigned to the activities

            

           Taskes related to a particular activity come for the "Tasks Required" table to the choice of task to do.

            

           -----

           New record in the  4.  "Task Audit" table (main table) adds a record of a task being carried out by individual.

           And the date that it was carried out.

           Back in the 3. 'tasks to be done' table I want to reference the tasks audit table and find out the last date (newest date) that the task was done in the task audit trail table.

            

           I used the 

           Max ( Audit of Tasks::Date Done to Numbers ) to get highest number and created a relationship back to this table x relationship

            

           I then made a relationship from the 'task audit table' back to the table of 'Tasks to be done'. to see the highest date of the task done.

           I then used a script that I picked up on this forum from someone trying to get the second highest date or a related table. It works but only for the second highest related record and no result shows if there is only one related record.

           ----- This is the script -----

            

           Let([
           v = TrimAll ( Substitute ( List ( Audit of Tasks Dot Highest Date::Date Done to Numbers ) ; [  Max ( Audit of Tasks Dot Highest Date::Date Done to Numbers)  ; "" ] ; [ ¶ ; " " ]) ; 0 ; 0 ) ;
           v = Substitute ( v ; " " ; ";" )
           ];
           If ( v ; Evaluate ( "GetAsDate ( Max(" & v & "; 1 ) )" ) )
           )
            
           ---------
            
           See attached screen grab or results giving the second highest date and not the highest... I cant seem to see how tho get the highest not the second highest.
            
           Any help or advice would be appreciated.
            
           All I want to do is get the most recent date the task was carried out and put it next to the Tasks require for a reference to when it was las done. I will use it for the calculation of overdue flag needs action.

            

           Thanks in advance

           Bob

            

      filemaker_screen.jpg

        • 1. Re: Get the highest date of related records in related table
          philmodjunk

               That's a very odd calculation and not needed but it is doing what you set it up to do. The substitute function takes the list of all related dates and then removes the maximum value from the list. Then returns a date from the other dates that remain in the list.

               But all you need to do to get the most recent date is  this:

               Max ( Audit of Tasks Dot Highest Date::Date Done to Numbers )

               No additional manipulation should be needed. This assumes that "Date Done to Numbers is a field of type date.

               You can also specify a sort order for your related records or a portal to the related records that sorts the related records by date in descending order. The most recent record will then be the first related record or the first record in the portal.

          • 2. Re: Get the highest date of related records in related table
            Insight

                 Perfect.

                  

                 I was over complicating it. I should have made that calculation field in the Tasks table. I had tried it in the Tasks Audit table and was entering it as a reference field but it was juts showing me the highest date on the Tasks Audit table without the filter.

                 It make perfect sense now...

                 Simple is better !

                 Thanks for your help Phil