Alert/Notification when line item hasn't been added

crodts

I am building a sheet in which 6 managers provide updates in the form of line items. Each week our Director reviews this sheet for the latest updates.

I am looking to create a notification system that would go out to each manager that doesn't provide at least one update (add one line or modify an existing line) in the last 7 days.

There is a contacts column titled Managers that contains each of the 6 managers contact cards, so I would like the notifications to be based on that list of managers.

Does anyone have any solution for this?

I have come across a lot of suggestions that use a helper column for 'days since last update', but that really only works if they are updating the same line item each week. In my case, they will often be adding their updates as new line items, so I want to make sure it only sends them a notification if they haven't added a new line item (or updated an existing one) in the last 7 days.

Thanks in advance for the assistance!

James Keuning

You can do this with two formulas:

Max column:

=IF(MAX(COLLECT([Update Date]:[Update Date], Person:Person, Person@row)) = [Update Date]@row, MAX(COLLECT([Update Date]:[Update Date], Person:Person, Person@row)))

Late:

=IF(Max@row = [Update Date]@row, (TODAY() - Max@row) > 7)

And yeah you can actually smoosh those together and add some error handling and get this result with one checkbox field:

=IFERROR(IF(IF(MAX(COLLECT([Update Date]:[Update Date], Person:Person, Person@row)) = [Update Date]@row, MAX(COLLECT([Update Date]:[Update Date], Person:Person, Person@row))) = [Update Date]@row, (TODAY() - IF(MAX(COLLECT([Update Date]:[Update Date], Person:Person, Person@row)) = [Update Date]@row, MAX(COLLECT([Update Date]:[Update Date], Person:Person, Person@row)))) > 7), "")

Edit to add: The way this works is it looks for the last update the person did, and if that update was greater than 7 days, they get the checkbox. And this runs against the entire list - it looks at the entire list to find the latest date for each person. There are some extra steps here to ensure that the "Max" date only appears on the lines that are the Max, but this step is not needed, it just looks cleaner. And since the whole thing can happen without the Max column, it's doubly moot.

Zachary Hall

@crodts

There is a way to have the 'days since last update' with updates being on different lines. You would need to use something like:

• =TODAY()-MAX(COLLECT([Changed Date]:[Changed Date], [Changed By]:[Changed By], Manager))

This formula could give you the number of days since that manager had made a change.