Creating report to show Parent Row based off Child Row

Travis Myers

I am trying to design a report to list to our outside salesman. I need to filter it based on contractor names, but those are child rows to the parent row which is the job name.

So in the picture, I would want the criteria to be "ROMANOFF", so I could pull all jobs they are on, which in this case, would be 12th Avenue South.

Is this possible? I do currently have a helper column that allows me to identify parent/child rows by numbering them. Parents are 1 then children are nothing, but don't know if that is enough to achieve this.

Paul Newcome

I would suggest a helper column that replicates the contractor in the child rows and joins the contractors for the parent rows.

=IF(COUNT(CHILDREN([Contractor Name Column]@row = 0, [Contractor Name Column]@row, JOIN(CHILDREN([Contractor Name Column]@row), ", ")

Then you can build your report to pull rows where this helper column contains "Romanoff" or whatever other name you are pulling the report for.

Paul Newcome

I would suggest a helper column that replicates the contractor in the child rows and joins the contractors for the parent rows.

=IF(COUNT(CHILDREN([Contractor Name Column]@row = 0, [Contractor Name Column]@row, JOIN(CHILDREN([Contractor Name Column]@row), ", ")

Then you can build your report to pull rows where this helper column contains "Romanoff" or whatever other name you are pulling the report for.

Travis Myers

I am getting #UNPARSEABLE. Did I do it incorrectly?

=IF(COUNT(CHILDREN([Job Name]@row = 0, [Job Name]@row, JOIN(CHILDREN([Job Name]@row), ","))))

Travis Myers

I think I may have the column references messed up. Here is another snippet of my sheet.

The left column is my helper to determine parent/child, both the job name and contractors are in the same column with the contractors being children to a job.

Travis Myers

Got it! Missed some parenthesis while typing it out (annoying little buggers).

=IF(COUNT(CHILDREN([Job Name]@row)) = 0, [Job Name]@row, JOIN(CHILDREN([Job Name]@row), ","))

Paul Newcome

Yep. That was exactly it. Glad you got it figured out. Is the report side working now as well?

Travis Myers

Yup!

Paul Newcome

Excellent. And sorry about the parenthesis issue. My fingers must not have been traveling at the same speed as my brain. Haha