Automation for contact column notication, once month by active, for only their projects.

I want to notify everyone in my "Project Leader" column of only their own projects that are active once a month.

An Automation like...

Run once a month on this day at this time,

If project status is active,

Notify Project Leader ofall rows assigned to them.

There doesn't seem to already be a way to group projects by contact for an automation.

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    You would set your trigger to be date based and then select the option for Custom Recurrence to set up the monthly portion.

    You would use a condition of the Status column being "Active" to apply the filter.

    Then the recipient for the Alert would be "Send To Contacts In A Cell" and you would select the Project Leader column which will only send rows meeting the previous condition to the appropriate Project Leader(s). So if I am leader on rows 1, 3, and 5 (assuming they are all active), I will only be alerted to rows 1, 3, and 5.


    image.png
    image.png


    10xViz

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    You would set your trigger to be date based and then select the option for Custom Recurrence to set up the monthly portion.

    You would use a condition of the Status column being "Active" to apply the filter.

    Then the recipient for the Alert would be "Send To Contacts In A Cell" and you would select the Project Leader column which will only send rows meeting the previous condition to the appropriate Project Leader(s). So if I am leader on rows 1, 3, and 5 (assuming they are all active), I will only be alerted to rows 1, 3, and 5.


    image.png
    image.png


    10xViz

  • I thought I needed to specify that only the projects assigned to them (by row) were to go to each project leader. I see this is built in without needed to specify. Thank you. I tried to run a test to send to myself, but it sent me everything active rather than my own. But how would it know it was mine if I didn't select the project leader column as a condition. Silly me.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the公式手册模板!
Try this:<\/p>

=IF(COUNT(CHILDREN()) = 0, IF(OR(Done@row = 1, [N\/A]@row = 1), 1, 0), AVG(CHILDREN()))<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":108195,"type":"question","name":"COUNTIF Function Not Working Correctly","excerpt":"Hello, I have a COUNTIF function I am using to count all cells that are 'Blank' in a column. We should have one result, but the formula is showing as zero. Any thoughts on this one? =COUNTIF([Account Dashboard]:[Account Dashboard], \" \")","snippet":"Hello, I have a COUNTIF function I am using to count all cells that are 'Blank' in a column. We should have one result, but the formula is showing as zero. Any thoughts on this…","categoryID":322,"dateInserted":"2023-07-27T16:41:32+00:00","dateUpdated":null,"dateLastComment":"2023-07-27T19:11:08+00:00","insertUserID":164192,"insertUser":{"userID":164192,"name":"ewitt19205","url":"https:\/\/community.smartsheet.com\/profile\/ewitt19205","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!mo_vH-5dWnI!5OcveUI6Zjo!UtWovaSRry_","dateLastActive":"2023-07-27T19:07:23+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":164192,"lastUser":{"userID":164192,"name":"ewitt19205","url":"https:\/\/community.smartsheet.com\/profile\/ewitt19205","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!mo_vH-5dWnI!5OcveUI6Zjo!UtWovaSRry_","dateLastActive":"2023-07-27T19:07:23+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":14,"score":null,"hot":3380962360,"url":"https:\/\/community.smartsheet.com\/discussion\/108195\/countif-function-not-working-correctly","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108195\/countif-function-not-working-correctly","format":"Rich","lastPost":{"discussionID":108195,"commentID":387638,"name":"Re: COUNTIF Function Not Working Correctly","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/387638#Comment_387638","dateInserted":"2023-07-27T19:11:08+00:00","insertUserID":164192,"insertUser":{"userID":164192,"name":"ewitt19205","url":"https:\/\/community.smartsheet.com\/profile\/ewitt19205","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!mo_vH-5dWnI!5OcveUI6Zjo!UtWovaSRry_","dateLastActive":"2023-07-27T19:07:23+00:00","banned":0,"punished":0,"private":false,"label":"✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-07-27T19:10:56+00:00","dateAnswered":"2023-07-27T16:47:29+00:00","acceptedAnswers":[{"commentID":387588,"body":"

Hi @ewitt19205<\/a> <\/p>

Try removing the space between the quotes. You are counting all cells with 1 space in the cell. Blanks are counted with the quotes together.<\/p>

When counting blanks though, its also good to use a COUNTIFS and compare the blank off another column that always has a value. Like Countifs when the Project name is not blank (<>\"//www.santa-greenland.com/community/discussion/107619/\") and the Account Dashboard is blank (\"//www.santa-greenland.com/community/discussion/107619/\").<\/p>

I hope that helps.<\/p>

Thanks,<\/p>

Matt<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":108186,"type":"question","name":"Index\/Collect does not work with < symbol","excerpt":"Hi there, I am using the INDEX COLLECT formulas to get a value if a number is between two ranges. The issue is that I'm getting an #INVALID VALUE error in the following formula: =INDEX(COLLECT({TotalRiskFactor}, {RangeStart}, >=[Risk Score]@row, {RangeEnd}, <=[Risk Score]@row), 1) The problem is with the \"<\" symbol in the…","snippet":"Hi there, I am using the INDEX COLLECT formulas to get a value if a number is between two ranges. The issue is that I'm getting an #INVALID VALUE error in the following formula:…","categoryID":322,"dateInserted":"2023-07-27T14:55:04+00:00","dateUpdated":null,"dateLastComment":"2023-07-27T18:38:27+00:00","insertUserID":122906,"insertUser":{"userID":122906,"name":"Cesar Perez","url":"https:\/\/community.smartsheet.com\/profile\/Cesar%20Perez","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!2uFV_3dM1no!ndnWXxsFlTw!JCwGNa_KQWy","dateLastActive":"2023-07-27T15:35:52+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":45516,"lastUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-07-27T19:11:10+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":27,"score":null,"hot":3380955211,"url":"https:\/\/community.smartsheet.com\/discussion\/108186\/index-collect-does-not-work-with-symbol","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108186\/index-collect-does-not-work-with-symbol","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":108186,"commentID":387609,"name":"Re: Index\/Collect does not work with < symbol","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/387609#Comment_387609","dateInserted":"2023-07-27T18:38:27+00:00","insertUserID":45516,"insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-07-27T19:11:10+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/JURDPT6GMX1Y\/formula.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"Formula.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-07-27T15:36:00+00:00","dateAnswered":"2023-07-27T15:02:42+00:00","acceptedAnswers":[{"commentID":387556,"body":"

The issue is with your arguments overall. Using the Score of 40 (as in your examples)...<\/p>


<\/p>

The score start range has to be 40 or greater, but the range end has to be less than or equal to 40.<\/p>


<\/p>

Try reversing it so that the start is less than the score and the end is greater than the score.<\/p>


<\/p>

Take a look at the second piece in your destination file. The formula is not throwing an error, but it is also not pulling the correct value in. It should be pulling in Moderate because 40 is between 35 and 54, but it is pulling in Moderate-High because that is the first entry where the Range Start is greater than 40.<\/p>"},{"commentID":387563,"body":"

You have your greater than and less than backwards. Your formula is saying to looking for the start date to be greater than 40 and the 35 is less than 40.<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":254,"urlcode":"Formulas","name":"Formulas"}]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=322&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&sort=-hot&limit=3&expand%5B0%5D=all&expand%5B1%5D=-body&expand%5B2%5D=insertUser&expand%5B3%5D=lastUser&status=accepted","prevURL":null,"currentPage":1,"total":10000,"limit":3},"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">

Trending in Formulas and Functions