Adding Columns to Capture Parent tasks with formula
I am needing to show the parent task on a My Tasks report and was given a suggestion to add a column and use the following formula to pull it in. However, this formula is not working for me.
=JOIN(ANCESTORS([Primary Column]@row), " / ")
I have 2 columns I need to be able to show for a row that comes from a parent task. Tactic Name and Tactic #. We have a lot of Tactic tasks that are identical and without the Tactic name, it is hard for team members to know which item they are assigned and need to work on without the parent on the My Tasks report.
Thanks for any suggestions here.
Answers
-
Hi @KCurry
Hope you are fine, Please add a copy of your sheet or a screenshot (after removing or replacing any sensitive information). This will make it easier for me to provide the appropriate answer to your question.
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
-
Hi @KCurry
When you say the formula "does not work", could you explain a bit further? Are you receiving an error or is the wrong information being output?
The Ancestors function will look into a specific column, and the Join function will bring together all the parent details from that column. In this instance, you're looking into the "Primary Column" to return data. Instead, you may want to look at the Tactic Name column, like so:
=JOIN(ANCESTORS([Tactic Name]@row), " / ")
Does that make sense?
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
I have tried the actual column name in the formula and am getting an UNPARSABLE error message.
=JOIN(ANCESTORS([Task Name]@row, "/")
-
This is a campaign template sample.
This is a My Tasks report sample showing how confusing it can be without knowing the parent tactic or tactic names. There is no way for individual to know which Launch item the report is talking about without clicking into each plan. These users can be assigned to several plans and several tactics within a single plan. We need a good way to give users a report of their assignments.
Our users need to know which tactic number the tactic name/parent the subtasks belong to since many of the tasks are the same across tactics. This has been a challenge with giving users a report of all their assigned work across a portfolio.
-
Hi @KCurry
You're missing a closing parentheses in your formula, which is why you're getting an error.
First you want to have the Ancestors function:
ANCESTORS([Task Name]@row)
Then you wrap the JOIN around this:
=JOIN(ANCESTORS([Task Name]@row), "/")
^ Notice the closing ) after the @row
Tactic Number:
In your Tactic Number column you can input
=PARENT()
in all of the child rows. This will enable you to include the two columns into your Report.
Both:
If you want both the Tactic Number and the Task Name in the same cell, try this:
=JOIN(ANCESTORS([Task Name]@row), "/") + CHAR(10) + JOIN(ANCESTORS([Tactic Number]@row), "/")
The CHAR(10) function creates a line break so if you Wrap-Text on this formula column the numbers will appear below the Task Names. You can convert this to a Column Formula to make sure it's applied for every row.
Let me know if this works for you!
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 352 Global Discussions
- 198 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 443 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!