AnsweredAssumed Answered

Get the highest date of related records in related table

Question asked by Insight on Nov 19, 2013
Latest reply on Nov 19, 2013 by Insight


Get the highest date of related records in related table





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


     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