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.

Find more posts tagged with

Accepted answers

All comments

Bassam Khalil

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.

Genevieve P.

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

KCurry

I have tried the actual column name in the formula and am getting an UNPARSABLE error message.

=JOIN(ANCESTORS([Task Name]@row, "/")

KCurry

This is a campaign template sample.

Screen Shot 2021-02-24 at 6.06.54 AM.png

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.

Screen Shot 2021-02-24 at 6.09.40 AM.png

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.

Genevieve P.

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.