relationship top result

  • 0
  • 1
  • Question
  • Updated 4 years ago
  • Answered

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?

Photo of Kevin

Kevin

  • 0 Points

Posted 4 years ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 64,322 Points 50k badge 2x thumb
No problem.
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.
Photo of Kevin

Kevin

  • 0 Points
Holy cow, that worked... PERFECT! Thanks for the help!
Photo of Kevin

Kevin

  • 0 Points
As a followup to this... no relation pulls through if the Task Due Dates are blank. It appears the summary of Earliest Incomplete Task Due Date fails if the Due Dates are blank. Is there a workaround to force the summary to work, even if the fields are blank? Wouldn't a minimum of a blank date be '0'?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 64,322 Points 50k badge 2x thumb
Yes, I did a test and got your same results.  Not sure why, but we will just chalk that one up0 to one of life's unsolved mysteries and move on.

So, no problem.

I made this  formula checkbox field on the child record

IsNull([date]) and IsNull([due date of oldest task])
or
[date]=[due date of oldest task]

Then I filtered that second summary field on that being checked.