parent child report

  • 0
  • 1
  • Question
  • Updated 9 months ago
  • In Progress
Have 2 tables:Project and Tasks
Relationship: One Project can have multiple Tasks.

I want to be able to display child records in the parent report without having to display them using a link. Is there a way I could show the child records associated with the parent directly in a report. That being said, there might be cases when a Project doesn't have any tasks, I also want to show those projects in there.

Any recommendations?

Thanks,
Photo of Angel

Angel

  • 784 Points 500 badge 2x thumb

Posted 9 months ago

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

QuickBaseCoach App Dev./Training, Champion

  • 62,448 Points 50k badge 2x thumb
How many tasks might you need to handle? This can be done, but for a limited number of tasks say Max 6-10
Photo of Angel

Angel

  • 784 Points 500 badge 2x thumb
yeah- i dont think there will be more than 10 tasks. What are you thinking?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 62,448 Points 50k badge 2x thumb
Here is an answer to a similar question.

You have 1 Project has Many Tasks.
This was an answer to 1 Job has Many Operators.

The way to do this is with as many "reverse relationships" as you guess will be enough to cover off 99% if the situation.

So, let talk about Operators. One Job has many Operators. Make a summary field on that relationship of the Minimum of the Record ID# of Operators. Call it [Record ID# of Operator 1].
Then do a lookup of that down to the child Operator table as we will need it later.

Then do a new Relationship where one Operator has many jobs. For the reference field (the field on the right side), use that new Summary field called [Record ID of Operator 1]. Get rid of the extra fields that get created on the left side of the relationship (Add Operator and that report link field). Do a lookup from the Operator down to Jobs and bring in the Operator Name and call it Operator 1.

Wonderful, the Job now knows the first Operator.

Now, make a duplicate of that summary field, but in the duplicate add the extra condition that the [record ID#] is greater than the [Record ID# of Operator 1]. Call it [Record ID# of Operator 2]. Do a lookup of that field back down to the Child Operator table as we will need it later.

Now, in making that duplicate, conveniently, you will find that it duplicated the reverse relationship so now do a lookup in that relationship of the operator name called it [Operator 2]. Wonderful again, we now have Operator #2's name on the Job Record.

Then make another duplicate of that summary field and just keep going, each time changing the filter so that the record ID# is greater than the previously highest record ID of Operator.

In the end say you collect the names of the first 4 operators on a job, so then just string them together in a formula like

List ("\n",
[Operator 1],
[Operator 2],
[Operator 3],
[Operator 4])

and put that concatenated field list on your report.
Photo of Doug Swanson

Doug Swanson

  • 80 Points 75 badge 2x thumb
Add the fields you to report on to from the Project table to the Task table as Lookup fields....  The are part of the relationship.  Then create your report against the Task Table....and use the lookup fields that are created in the Task field from the Project table.  I would recommend to Group and Sort by the Project name (Lookup Field).  Let me know if that is enough for you to implement the report you want.
Photo of Angel

Angel

  • 784 Points 500 badge 2x thumb
but this will not show projects which do not have a task associated with them. I am almost looking to do something like a left outer join on projects- if that makes sense.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 29,874 Points 20k badge 2x thumb
I love left outer joins as much as I love right outer joins:

Left Outer Join: ⟕ ⟕ ⟕ ⟕ ⟕ ⟕ ⟕ ⟕ ⟕ ⟕ ⟕ 
http://www.fileformat.info/info/unicode/char/27d5/index.htm

Right Outer Join: ⟖ ⟖ ⟖ ⟖ ⟖ ⟖ ⟖ ⟖ ⟖ ⟖ ⟖
http://www.fileformat.info/info/unicode/char/27d6/index.htm

See the demo (Concatenate Children To Parent) within this pastie:

Pastie Database
https://haversineconsulting.quickbase.com/db/bgcwm2m4g?a=dr&rid=463