Pull child rows into report based on parent rows

Sean Duggan

Hello, would much appreciate it if anyone is able to help me out.

I would like to be able to pull both parent and child rows of a sheet into a report based on a criteria which only exists in the parent row, is this possible? I'm not sure if there's just a setting I missed.

Thanks,

Sean

Andrée Starå

Hi Sean,

Yes, it's possible.

Can you describe your process in more detail and maybe share some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help.

Have a fantastic weekend!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Mike Wilday

IT sounds like you would might want to use the Parent function to determine if the parent has certain data in it and flag a row if it does. There isn't a lot of data on the parent but it might function similarly to the children function which has more information. 

• https://help.smartsheet.com/function/parent
• https://help.smartsheet.com/function/children

Kyle Furner

Hi Andree,

Seeing as Sean didn't respond, can you please link me to the article that describes how to achieve this report?

I have multiple reports that I would like to create where I am wanting to include all parent tasks that meet X criteria, and also include any children, regardless of whether the child meets the criteria or now.

A very basic example would be a report that looks through multiple project schedules for any parent task with "Procurement" in the name. I can currently pull these specific rows into a report, but would be ecstatic if there was a way to include the children rows for each task that meets this criteria.

Kyle

 

Mike Wilday

Hi Kyle, at this time, reports don't show or pull in hierarchy. You could probably create a helper column and use the Parent function to search the parent rows and see if they had procurement or other key words in the parent and then return them to the child rows. Then your report could include any rows where the helper column also included procurement. 

I am thinking a detailed IF statement looking into the parent task. Let me know if you'd like help figuring out the formula and I can see what I can come up with. 

These functions might help

• https://help.smartsheet.com/function/parent
• https://help.smartsheet.com/function/if
• https://help.smartsheet.com/function/contains

Andrée Starå

I saw that Mike answered already!

Let me know if I can help with anything else!

Best,

Andrée

 

Kyle Furner

Thanks for the replies Mike and Andrée.

I wound up using another solution to make it work for me and thought I would post it in case anyone has a similar case in the future.

I created an extra column which has a drop-down list of any internally sourced services that a project manager might need to use, such as our procurement, policy, or modelling teams. I have called this column "Shared Services".

 

Then I created another column which contains the formula below in every cell. 

=IF(COUNT(CHILDREN([Task Name]@row)) > 0, IF(ISBLANK([Shared Service]@row), " ", IF([Shared Service]@row = "Procurement", "PROC-" + [Project ID]@row + "-" + [Task Name]@row)), PARENT())

The formula does the following:

1. Counts how many children the row has. If Children = 0 the cell will duplicate the value in the Parent. If Children > 0 the formula continues.

2. Checks if the 'Shared Service' cell for the row is blank. If 'Shared Service' is blank, the cell will be blank, otherwise the formula continues.

3. Checks if the 'Shared Service' cell contains "Procurement". If it does, the cell generates an ID of "PROC-" due to the service type, plus the value from the 'Project ID' column, plus the value from the 'Task Name' column.

The resulting code looks something like "PROC-12345-Procurement #1", which can then be pulled into a report, keeping the parent and child relationship in tact for task hierarchy.

The only thing I can't resolve is being able to use the RIGHT function so that only the numeric value from the 'Task Name' column is captured. It would be much neater to have the code just populate as "PROC-12345-1". Using the code below only returns an INCORRECT ARGUMENT error.

=IF(COUNT(CHILDREN([Task Name]@row)) > 0, IF(ISBLANK([Shared Service]@row), " ", IF([Shared Service]@row = "Procurement", "PROC-" + [Project ID]@row + "-" + RIGHT([Task Name]@row, 1)), PARENT()))

This is more just an aesthetic thing though, as generating the code is what solves my issue, not having the code look nice

Kyle

 

Mike Wilday

Hi Kyle, try this one... 

It looks like you were closing the IF statement with the RIGHT function before the actual end of hte nested IF's. I moved the parenthesis out to the end of the formula and think it might work now. 

=IF(COUNT(CHILDREN([Task Name]@row)) > 0, IF(ISBLANK([Shared Service]@row), " ", IF([Shared Service]@row = "Procurement", "PROC-" + [Project ID]@row + "-" + RIGHT([Task Name]@row, 1), PARENT())))

Kyle Furner

Thanks Mike,

I actually figured it out using the REPLACE function but yours is slightly shorter and I'm always a fan of efficiency haha.

Also, in case anyone else wants to use this, the ",PARENT()" section needs to be inside the last parenthesis. See below final formula.

=IF(COUNT(CHILDREN([Task Name]@row)) > 0, IF(ISBLANK([Shared Service]@row), " ", IF([Shared Service]@row = "Procurement", "PROC-" + [Project ID]@row + "-" + RIGHT([Task Name]@row, 1))), PARENT())

Andrée Starå

Happy to help!

Glad that you solved it and thanks for sharing!