Do not sent Alerts if the row is marked as complete

James Johnson-Miller

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

Marcus Odum

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

 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

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

James Johnson-Miller

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

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

James Johnson-Miller

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

 

 

 

J. Craig Williams

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