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