Finding second highest date
I have setup a calculation which finds the Max() date house would I go about finding the previous date before the max?
I could script it.
Sort your records by date in descending order.
Go To Record ( 2 )
Will put you on the second most recent date.
He asked for a calculation and your script doesn't guarantee the second most recent date ( think about 3 records with the same Max date )
Good point. This script could, of course step down through the sorted records until the date changes.
Some self join relationships based on date might also work in a straight calculation.
Table::ID x Table2::ID
Table::ID x Table 3::ID ANDTable::MaxDate ≠ Table 3::DateField
Sort this second relationship by date, descending and define MaxDate as Max ( Table2::DateFIeld )
Now a refrenced to Table 3::DateField from the context of Table should return the second largest date.
That could be made w/o any additional relationship.
1) Create in the related table a calculation field ( cDateAsNumber ), result number and calc:
2) Create in the main table a calculation like this, result date:
Let([v = TrimAll ( Substitute ( List ( Related::cDateAsNumber ) ; [ Max ( Related::cDateAsNumber ) ; "" ] ; [ ¶ ; " " ]) ; 1 ; 1 ) ;v = Substitute ( v ; " " ; ";" )];If ( v ; Evaluate ( "GetAsDate ( Max(" & v & "; 0 ) )" ) ))
Just came across your script for finding the second newest date. I tested it and it works fine
How would you adjust it to get the first (not second) highest date.
I have a facility actions required data bases
Sections - Activities needed to be done - 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 "Task Audit" table (main table) adds a record of a task being carried out by individual.
And the date that was carried out.
Back in the tasks to be done table I want to reference the tasks audit table and find out the last date (newest date) 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.
I then tried your script but it gives me the second highest date a related task was done in the tasks audit trail table.
Also if there has only been one entry of the task in the "Task Audit Trail" table it doesnt show any date from your script.
Any help or advice would be appreciated.
Thanks in advance
If in the relationship graph you sort the tasks audit table descending by that date field, all you need to do is to place that date field in the related table and change the behavior to not let enter it in browse mode.
Sorry that doesn't seem to work...
See screen grab attached
I think I might need start a 'new post' looking for an way to get the highest date of the related record.
Any further advise ?
Retrieving data ...