Current status and current user

  • 0
  • 1
  • Question
  • Updated 8 months ago
  • In Progress
I have relationships setup between projects and tasks. I'm trying to pull a report so it shows the project's current status based on who currently owns a task (who the active task owner is). For example - show me a list of my projects with the current task status and current owner.
Photo of Mark W

Mark W

  • 90 Points 75 badge 2x thumb

Posted 8 months ago

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

QuickBaseCoach App Dev./Training, Champion

  • 51,436 Points 50k badge 2x thumb
Hmmm, so say 1 Project has 10 tasks.  What is your definition of the "Active Task"
Photo of Mark W

Mark W

  • 90 Points 75 badge 2x thumb
Say tasks 1-5 are Complete and the 6th task is any status but Complete. The active task is the 6th task.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,436 Points 50k badge 2x thumb
What is the definition of Tasks 1-5.  Are these the tasks with the lowest Record ID#?
Photo of Mark W

Mark W

  • 90 Points 75 badge 2x thumb
Yes, lowest record ID. First could be Prep Package, second Field Engineer, third Drafting, etc. There is a flow from top to bottom, top being the lowest Record ID.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,436 Points 50k badge 2x thumb
OK, so we can do with with a reverse relationship.  You want to lookup data from a child record to a parent record, so we need to figure out who the lucky Task child is which is Active and and then make him a Parent.

First we figure out who the lucky child is.

On the relationship where 1 Project has Many Tasks, make a summary minimum of the [Record ID#] where the  the filter is that the Task is Active.  Call it [Record ID# of Active Task]

The Project now knows the [Record ID#] of the Task that has the info we need.

Make a Relationship where 1 Task has many Projects.  Yes that sounds backwards, so that is why it is called a reverse relationship.  On the right side, do not let it create a new field, use the field we just made called [Record ID# of Active Task].

Then just lookup any fields you need from the Active Task to the Project, such as Task Owner and Task Status