Because rollup() in formula field can’t deal with date field, I made a formula filed for calculating a duration between a due date and dummy date(this is date field) in a project
I created a formula field in a portfolio, that calculates the due date of a specific task with adding dummy date and the duration.
A trick is used to refresh rollup() automatically.(see No.7)
rollup() doesn’t refresh itself automatically when ‘due date - dummy date’ field of subtask is changed.
The detail is below;
Create a single-select field below in a project. But this is COMPLETELY an option to clarify which task is specific task. You don’t need this if you want.
Append ‘dummy date’ field created at No.2 in a portfolio.
Set the same date to ‘dummy date’ field in a project and a portfolio. You can choose any date you want. Notes that in a project, you need to set the date to only the task you want to watch in a portfolio.
It normally doesn’t make sense. But in this case, it seems to force Asana to refresh rollup(due date - dummy date) in this formula field. Therefore it’s so important😏.
Set ‘specific task’ field of No.1 to ‘specific task’
That’s it.
After setting above,a project and a portfolio look like below.
for a specific task.
I’ve tested it on my side. When I set it up for the first time, specific date on portfolio is correct but if I try to change my task due date - the portfolio date doesn’t change.
YES! I was needing something like this this week and was really struggling. Moving the value to the dummy field is genius!!! Thank you so much! We have been building out new Project templates and Portfolios for our work flow, and only being able to manage/see the project due date and not one of the ones from a task inside the project (The count down is actually better!) has been a huge stumbling block to get the visibility for my team leads we need at a glance! I knew there had to be a way. Thanks again!!
This has stopped working… It looks like the rule that copies the ‘due date - today’ value to the ‘due date-today 2’ field no longer works? I cannot seem to recreate it either, but notice asana has added some new ‘options’. Anyone else have this issue? A fix?
It seems that when I make this rule as part of my template for making a new project, that is when this does not work. If I write it for each project (A LOT!) that works. The problem seems to revolve around the rule. I look at the rule when a project is created from the template, and the rule ‘looks’ right, but nothing happens. If I delete the rule and rewrite it, then it works…???
@Jay_Miller
Thank you for your comment.
I confirmed that.
When using a template, we need to use bundle to avoid that.
If you make the rule in a bundle and set this bundle to a template , the rule will work.
I revised the original post. so it doesn’t make sense below.
I recognized this post is wrong.
“specific due date” changes day by day.
To avoid that, we need to append a date custom field for dummy in projects and portfolios.
I’ll revise this post later.but I write a summary below;
Append a date custom field named “dummy date” to both projects and portfolios
Set any date as you like to “dummy date” for the specific task.But you need to set the same date in a portfolio.
Change the definition of “due date - today” to (Due date - dummy date)/1440+0
Change the name of “due date - today” to “due date - dummy date”
Change the name of “due date - today2” to “due date -dummy date2”
Change the definition of “specific due date” to DateAdd(dummy date,Rollup(due date - dummy date2))
Tetsuo,
I appreciate your work on this. It seems like it should be simpler I thought the old rule worked, but it only works when the due date is actually changed. As the days go by, it needs to update daily so the Due to Engineering date at the Portfolio level continues to match the specific task due date on the project level.
Can I show our use case and perhaps there is a simpler version of this.
Our design team manages an ‘install date’ with our client, this is in a task on the project level.
Depending on the project size we have different duration dependancies that get created in our project template at creation of when the project needs to get to our engineering teams (due to Engineering date) This date is driven by the ‘Install date’ above.
For our use case, the Due to Engineering date is what we need to see on the Portfolio level:
The old rule / sequence you created above works when we change the ‘anticipated install date’ but if we don’t change if for a while the date does not update, in fact it moves in the opposite direction.
@Jay_Miller
I’m sorry, but I cannot understand what you mean.
Could you show me your screenshot of the project including ‘specific task field’ and the rule?
In my solution, the trigger for the rule is ‘due date is changed’. And the check if is ‘specific task is set to specific task’.
therefore, you need to set ‘specific task’ to specific task field of only engineering task if you want to watch the due date of engineering.
Tetsuo, thank you for your reply. When the due date actually changes, it changes, that part works fine. What does not keep up is the the DueDate-Today2 field on a daily basis, (Which is what puts it up in the Portfolio), (The actual, when it needs to happen, due date should remain fixed, unless deliberately changed by a human). In this example it has already slipped a day from yesterday when I posted this. Instead of being 69, it is 70 which actually throws my due date that I want to see on the Portfolio level back a day (Showing Apr 30 at Portfolio), which is not what we want to happen. That should still say Apr 29 which is what is showing down in the Project.
Project: (edited post first image was wrong)
Tetsuo,
I realized I my post may be potentially confusing because of how we talk about things internally with our team and I accidentally referenced that in the second image 2 days ago. (I have also edited that post as I incorrectly referenced it as a portfolio view and it is a project view! ) Sorry for the confusion!
The 'Hand off to Engineering Date (red oval) is the only date that I am working with in the rule to get to the portfolio level. I talk in terms of managing the ‘Anticipated install date’ with my team with clients, (Red Box). I am not trying to use that date to run the rule or anything like that.