Pull child rows into report based on parent rows
Comments
-
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
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
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.
-
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
-
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
-
I saw that Mike answered already!
Let me know if I can help with anything else!
Best,
Andrée
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
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
-
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())))
-
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())
-
Happy to help!
Glad that you solved it and thanks for sharing!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 462 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives