如何设置工作流，以便在前一个任务已经完成时通知某人?

格雷森Gammill · 02/24/21

我目前正试图开发一个项目计划表，我想能够通知某人当前任任务完成，所以他们知道他们可以开始工作。

例如，如果John必须进行数据源标识和验证，但在Mark转换和清理数据之前他不能这样做，我希望当Mark将他的转换和数据清理行标记为完成时能够自动通知John。我该怎么做呢?

希瑟·达夫 · 02/24/21

你好,格雷森!看起来这个话题已经在社区上出现过几次了。这里有一个:

I have seen many posts about people wanting the ability to indicate a particular task\/row is ready to begin because all of the predecessors are complete (many of them archived). All of the proposed solutions had short comings (e.g. only works on rows with a single FS predecessor with no lag). Here is my full solution to the problem.<\/p>

Note 1: I have not tested this extensively. It may not perform well on large sheets.<\/p>

Note 2: In this example, I have chosen for this to only work with FS predecessors (because that’s what makes sense to me). However, I indicate below which formula to change so that this can work with any predecessor type.<\/p>

This<\/p>

solution can be done by adding a minimum of 2 columns to your existing sheet,<\/p>

but I'm going to layout this explanation using 6 columns for ease of following.<\/p>

I will provide the 2 column solution at the end.<\/p>

Here<\/p>

are the 6 column names I used: Row ID, Row ID Comma, Pred Replace FS, Pred<\/p>

Count, Pred Done Count, and Ready Status<\/p>

The green columns can be converted to column formulas. Unfortunately, I don’t know of a way to get a reliable row ID column using a column formula.<\/p>

https:\/\/us.v-cdn.net\/6031209\/uploads\/NP86T060BMPE\/image.png<\/a><\/p>

Column 1<\/strong>: Row ID<\/p>

=COUNTIFS([Task Name]$1:[Task Name]@row, OR(@cell = "", @cell <> ""))<\/p>

In this example, I have referenced the [Task Name] column, but any column can be used. I stole this formula from another post thanks to Paul Newcome. This formula works regardless of how you move, copy, paste rows. The only downside is that it can’t be a column formula. https:\/\/community.smartsheet.com\/discussion\/69476\/row-numbers<\/a> <\/p>

Column 2<\/strong>: Row ID Comma<\/p>

=", " + [Row ID]@row + ","<\/p>

This column is the column that will actually be looked up by the counting formula. The commas are needed to avoid false positive matches (e.g. prevent finding “2” in “20”, looking up “, 2,” won’t be found in “, 20,”<\/p>

Column 3<\/strong>: Pred Replace FS<\/p>

=", " + SUBSTITUTE(Predecessors@row, "FS", ",") + ","<\/p>

This formula adds the needed commas at the beginning and end of Predecessor column, as well as changing “FS” into a comma (needed to ensure a FS predecessor with a lag will still work). Modify this formula with more nested substitute formulas to make it work with other predecessor types (FF, SS, SF) as you desire.<\/p>

Column 4<\/strong>: Pred Count<\/p>

=COUNT(COLLECT([Row ID Comma]:[Row ID Comma], [Row ID Comma]:[Row ID Comma], AND(NOT(ISBLANK(@cell)), FIND(@cell, [Pred Replace FS]@row) > 0)))<\/p>