Is there a way of automating that the person who marks a task as complete is populated in the Person column?
Not the creator of the task - the person who changed the status.
I have a “Maintenance Board” that through a series of automations creates tasks for some of the routine reporting items that people to do throughout the month. So on the 17th, it will create an item to “Do Report X” and send out a notification to three people to do the needful. 1 of those 3 people will complete the task. I’m looking to track the person who completed them -aka the person who marked them as complete.
I’d like to track the hours for these and combine them with the hours on my project overview on the Workload dashboard widget. Unfortunately you can’t track by “Last Updated” in the Workload dashboard widget.
Yes I know, the person could assign themselves under a person column and then just mark the item as complete. But where’s the fun in solving the problem that way? In my mind either the dashboard widget should allow you to pull by the last updated column, or there should be an automation that is able to pull the person into a column based on the selection of a status.
Hi @loonjas - while I cannot think of a stock automation to do this you could utilize the Last Updated column to know who was the last person to update the column in concert with the board activity log to know who did what.
Now this isn’t specifically tied to a status column. Another option is to notify someone when the status column changes.
hi @loonjas
Welcome to the community. As @mark.anley states this is not possible out-of-the-box. You can achieve this with Integromat of a custom app. In that scenario you add a webhook integration that will trigger on status column change. The receiving party of that webhook (either a custom build app or Integromat) receives (among others) the userId of the user that made the change. From there you can assign the user in a person column.
In a custom integration app scenario I would use a recipe sentence like:
When this status is changed to this value assign myself in this column.
hey @loonjas! We are using a similar workflow as you in one of our boards. Our solution was to use the “create update automation” There you can create an update and populate the update with “user” so the update says something like “Looney just completed this amazing task”. Then you’ll have an entry in the convesation of whom and when. Hope that helps!
Hi @loonjas , I have a partial solution for you that uses a Formula column. Maybe it’ll give you an idea how to get the results you want.
What works:
It runs only if item is marked Done.
It gets name of person who just changed item (pulls it from “Last Updated,” could not find any way to have it grab the User marking the item Done. Of course if we had that ability in the first place, none of this would be a problem.)
It shows that name.
Chart views recognize the column and name.
Issues where my formula skills are failing me:
Can’t figure out how to specify trigger of WHEN item is marked Done, not just “check if it is Done every time you run.”
If status is later changed to anything else, it deletes the name that was in the column.
This is due to my IF formulation which leaves the alternate option blank.
It will continue to update the name every time someone subsequently changes the item (as long as it’s still marked Done), such as posting an Update, changing any other column.
I’ve tried to limit this by using dates:
Added a column + automation to post the Completed Date when the item is marked Done.
Tried to create formula to check Completed Date, compare it to Now, and ONLY change the name if it’s just been a few moments since item was marked complete (have to give it some allowance due to lags), or, inversely, tell it to NOT change the name if it’s been longer than a certain amount of time.
I was not successful in working this bit out, but maybe you will be.
Here is the formula so far, which works except for the part about limiting futures changes:
IF(AND({Status}=“Done”,NOT(NOW()>{Completed Date}))=TRUE,{Last Updated#Name},"")
Here is a prior version where I was trying to limit the time from the positive direction. When I did a formula to show me the time difference between when it was marked Completed and NOW, it kept giving back 1.187 (which doesn’t seem right at all for almost immediately), so I was trying to cut as close to that as I could. 1.2 was the closest that still let it post a name, however it then keeps changing the name forever. I could not figure out how to convert a date/time to a total number, such as minutes, so I could define a cutoff in minutes, but that’s the way I’d prefer to do it, if I knew how.)
IF(AND(DAYS(NOW(),{Completed Date})>1.2,{Status}=“Done”)=TRUE,{Last Updated#Name},"")
My other idea was to use General Caster for all this formulating, because then it can be cast to a Text, People, or Status column, which might be more useful than a Formula column result. However since the Chart view recognizes the Formula column results, maybe it’s not an issue. In any case, my formulating skills failed me there, too; it works in principle but needs actual correct formulation.
You know, now that I think about it, a much simpler method (though admittedly less fun) that still only requires your staff to do one action is to have them mark an item as Done in a different way - use a People column called “Completed,” and have them assigned themselves when done. Then automations can mark the status done based on that, post date, etc. Or use a Status column with their names as labels, rather than a People column.
