I have an app where Projects have many Tasks. I want to, on a project, have a "Stage" field that is value of the Stage field of the top-most related "Task", sorted by Task "Due Date", where Task "Status" is not "Completed". I'm looking at the Project => Task relationship, and tried to define a 'summary field' and I feel like I am close... but I don't see any way to sort, nor select the topmost record from the relation. Am I looking in the wrong place?
1. Summary Minimum (or Maximum), but I think you want the oldest (Minimum) due date which is not completed. Call it [Due Date of oldest incomplete Task]
2. Look that Date up down to the child Task Table.
3. Summary minimum Record ID where [Due Date of oldest incomplete Task] equals Due Date. There will likely only be one that matches. Call it [Record ID of oldest incomplete task].
4. Now, do a reverse relationship where 1 Task has Many projects and use this field [Record ID of oldest incomplete task] as the reference field in the right.
5. Now just look up that status field or any other fields like task Name to the Parent Project.
So, no problem.
I made this formula checkbox field on the child record
IsNull([date]) and IsNull([due date of oldest task])
[date]=[due date of oldest task]
Then I filtered that second summary field on that being checked.