Welcome to the Smartsheet Forum Archives


The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Do not sent Alerts if the row is marked as complete

Options

We use SS for task / acrion lists for various teams, projects etc. and reports to consolidate everything into a single action list for the individual. We use Alerts to remind people. 

 

How could I stop the Alert sending if the item is marked as complete - we use a drop down list for the item status - In Pogress, On Hold, Complete etc. We cannot delete the date as we need this information.

 

Ideal would be configuring Alerts to (not) send if COLUMN = VALUE

 

Thanks

Comments

  • Marcus Odum
    Marcus Odum ✭✭✭✭
    Options

    James,

     

    As a workaround create 2 columns:

    1. Modified - System Type - Modified(Date)

    2. Completed Today - Checkbox

     

    From there you can use a formula like this below:

     

    =IF(AND([% Complete]1 = 100%, Modified1 = TODAY()), 1)

     

    Create an alert on your Completed Today column.

     

    I hope that helps.

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭
    Options

     Marcus,

     

    I believe what James is looking for is an Alert that sends out for changes until the task is complete and then stops. 

     

    Your formula will trigger once when marked complete, but all the previous changes will not trigger an alert. You did, however spark this:

     

    =IF(NOT(Done23), Modified23 + "", "")

     

    where [Done] is a check box column.

    This:

     

    =IF([% Complete]23 < 1, Modified23 + "", "")

     

    would be the corresponding formula for the [% Complete] column.

     

    This will display the date and time from the Modified column until the task is marked complete.

     

    An Alert is set up to trigger when the column that holds the formula is changed.

    And it will change, whenever there is a change (limited by time interval of minutes in the Modified column). It will change one last time when the task is completed to blank and then remain that way ... unless the task is later marked incomplete.

     

    Craig

     

     

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭
    Options

    James, 

     

    The other question that occurs to me is why you are receiving alerts on rows after they are complete - someone must be updating something in them or is it a formula that is doing it?

     

    Craig

  • Thanks Craig

     

    This relates to REMINDERS - e.g. send a reminder to the name in the Contact List 1 day before the date oin column 'due date'. If the person has already marked the item as complete, the reminder isnt needed. 

     

    Thise formulas wont help in this scenario as we sent reminders 14, 7 & 1 days out to a group of people and the status of the task is a drop down - for various other reasons. 

     

     

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭
    Options

    James,

     

    Are you sending group Reminders or setting them up on a case-by-case basis?

    The  reminder date is based on a [Due Date] or [End Date] column?

     

    You can get this  by creating a new [Reminder Date] column and use my formulas to either have the [Due Date] or blank, depending on completion.

     

    Does that make sense?

     

    Craig

  • Hi

     

    Please see below - there are currelt 6 Contact Lists - the person assigned and then up to 5 people involved. There is a Start and End Date (it is a project sheet). We send reminders to all of the contacts 5 & 1 day before the task is due to start (99% of tasks are 0 or 1 days). 

     

    Thanks

    James

     

     

     

    ss reminders.png

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭
    Options

    James,

     

    1. Create a new column [Reminder Start Date]

    Make it a Date field.

     

    2. In that column, put this formula:

     

    =IF(NOT(Done23), [Start Date]23, "")

     

    or

     

    =IF([% Complete]23 < 1, [Start Date]23, "")

     

    for row 23. Depending on your completion method

     

    3. Copy the formula to the rest of the column.

     

    4. Change your Reminders to point to [Reminder Start Date] instead of [Start Date]

     

    and Bob's your uncle.

     

    Craig

     

This discussion has been closed.