Daily Notification for Due date expiry

[Carl_Haugen]

I’ve tried to find examples in this community and on the help page, but have not had much luck so far. So I hope you could help me in the right direction. We have tried to create automations to to send notifications each day, but have failed to find the right set of formulas.

We have a task table for our organization, and we would like to have two kind of notifications for expired Due dates. Sent to the assigned persons for the task. This is what we’d like to achieve.

Regular daily notification

• Each day at 10.00 send notification if…
• Tasktable.Done=false and Tasktable.DueDate<DateToday (for each task)
• Send notification to Assigned person

All my attempts to produce a working Automation based on this has failed. I suspect that it’s mostly because I don’t know how to transfer the tasks from the If part to the notification part I guess.

Weekly mail summary

• Each week on Sunday send summary of tasks for each assigned person if…
• Task Due date is within next 7 days (sorted on due dates, with Task name)

I tired with the following automation.
when: weekly schedule
if: [Tasktable].Filter([Tasktable].Duedate> Today())
then:
Notify, Tasktable.Responsible.
Concatenate([TaskTable.task, ’ has expired’)

This just gave me expired task notification, but I got a list of all the tasks in the table, for each of the two notifications (had two tasks expired). I have yet to add the gmail pack to send this as a mail. But I’ll do that shortly.

Anyone have some advice, examples or references to how this could be done?

[Carl_Haugen]

Are there so few that use the Notification feature based on Due dates? Or perhaps I’ve written this so complicated that few get my question?

Anyone have an idea as how to create the formula to notify on taks from a table with passed dues dates?

[Jean_Pierre_Traets]

Dear @Carl_Haugen

May I refer you to this post, credits to @Christiaan_Huizer

If your notifications are time sensitive, I recommend to check this post, credits to @Paul_Danyliuk

[Carl_Haugen]

Thanks Jean Pierre.

The problem here is that we don’t want users to set their own dates or use buttons for adding stuff. We already have an automation button to create a set of tasks from a template. And each task has it’s programmed Due date based on an offset from the starting date set.

So, we have a list of lots of different tasks for different people. All with a specific Due date that should never be broken.

The examples you gave did not help in understanding how I could do what I need:
Add a daily automation to go through all the tasks and notify each user assigned, on each and every that is coming up with a due date for instance within the next 7 days.

This is not very time specific. The notification could be delayed with an hour or 4. No problem, as long as it’s ran each day. I can’t seem to find any examples of this. I’m quite stunned by that.

[Pch]

Hi @Carl_Haugen !

It has been a very long time since I used automations (I prefer buttons ) but maybe this “setup” could help (or at least give you ideas, maybe) :

• Step 1 : When

It should run “Daily” (at the time of your choosing)
I guess you could choose here between Day or Week and repeats on the days of the week of your choosing

• Step 2 : If

[Tasks List].Filter(
  Due >= Today() 
  AND Due <= (Today() + 7)
).Count() > 0 

This will go through your Tasks table to see if there are Due dates within a range of Today() and Today() + 7 and count them. If the Count() is over 0 it return True (otherwise False and in this case, if I’m not mistaken, the automation should stop there)

Note that Due is in fact CurrentValue.Due.
Also, this won’t stop the automation during weekends (As I didn’t know if it would matter or not )

• Step 3 : Then

[Tasks List].Filter(Due >= Today() AND Due <= (Today() + 7)).Sort(true,[Tasks List].Due).FormulaMap(
     RunActions(
      Notify(CurrentValue.Assignee,
        Format("Due soon : {1} on {2}",CurrentValue,CurrentValue.Due)
    )
  )
)

This will once again go through the Tasks table, check if any due dates are within the range Today() & Today() + 7 which would return a list of rows (in this case a list of tasks, as the column Tasks is my Display Column) and sort this list by Due dates.

And now that we have a list of Tasks (each Tasks in this list being stored/represented by CurrentValue) : For each tasks in the list (FormulaMap()) it will run this actions (RunActions()) : Notify()

• People in this case is CurrentValue.Assignee
  In other words, the Assignee of the task in the list of Tasks
• Message is Format("Due soon : {1} on {2}",CurrentValue,CurrentValue.Due)
  Which, for each task in the list returns something like : Due soon : Task X on DueDate Y
  
  

Note that Due is still CurrentValue.Due

This is really just an idea to explore … and might need adjustments to work in your doc .

Like for instance, I couldn’t test how it would go if you have multiple assignees for a task (as I’m the only person I can notify in my docs )… And I didn’t had quite the time to calculate Due dates based on an offset …

Another possibility would be to use a Notify() button and have the automation push it when needed .

But I hope this helps a little

[Carl_Haugen]

Wooow! Thanks alot @Pch
This is just what I was looking for!

This seems to be working regarding the number of notifications, but the output it self if not good. It says “User notified you with ‘model’”. I do not know what model means in this case.

It’s obviously getting the number of tasks right, so I suspect it’s something wrong in the “Then” part of the automation.
I suspect there’s an issue with the Format part.

Tasks.Filter(
  Due >= Today() 
  AND Due <= (Today() + 7)).Sort(true,Tasks.Due).FormulaMap(
      RunActions(
        Notify(CurrentValue.Responsible,
          Format("Due soon : {1} on {2}",CurrentValue.Name,CurrentValue.Due**)
        )
      )
  )

I’ve shared a test page just in case. I think it should be open for edits by anyone. Only problem could be to add your self as a user to test the result perhaps?

[Pch]

No problem @Carl_Haugen !

And I found where the problem came … I’m sorry, I should have been more careful here when giving the explanations .

The Then part I gave is for the whole step… It’s the complete action to run … and should be used as a formula by clicking on the ƒ just next to the Then section title

So, in your doc, instead of having something like this :

This step should look like …

If I decompose the Formula for the step :

Tasks.Filter(
  Due >= Today() 
  AND Due <= (Today() + 7)).Sort(true,Tasks.Due).FormulaMap(
      RunActions(
        Notify(CurrentValue.Responsible,
          Format("Due soon : {1} on {2}",CurrentValue.Name,CurrentValue.Due)
        )
      )
  )

The 1st part :

Tasks.Filter(
  Due >= Today() 
  AND Due <= (Today() + 7)).Sort(true,Tasks.Due)

Give you the sorted list of tasks within the appropriate range

and the 2nd part :

[ ...]
.FormulaMap(
      RunActions(
        Notify(CurrentValue.Responsible,
          Format("Due soon : {1} on {2}",CurrentValue.Name,CurrentValue.Due**)
        )
      )
  )

Runs the appropriate actions (Notify people, in this case) based on the result of the filter … Including retrieving who needs to be notified (CurrentValue.Responsible in Notify ) and what message to send . (which is this : Format("Due soon : {1} on {2}",CurrentValue.Name,CurrentValue.Due))

The place where you put the formula in your test doc is meant to be used only for the message to send … (which is why it didn’t really go as expected )

I’ve edited the Then part in you Test doc (Thanks for sharing it ) and tested it while I was there . It seemed to work now …

Don’t hesitate, if you still need some explanations (I’m not sure this is really clear … Sorry, it’s getting late where I stand ).

[Carl_Haugen]

Yey! That worked I did not really notice the UI elements with the “Notify” part. It would of course mess up the pure formula.

Thanks a lot for the help!

For quick reference for other users that might need something similar:

Solution
Daily Notification of not completed tasks with due date within next week.

Table name: Tasks
Table columns: Name (text), Done (checkbox), Responsible (assignee), Due (date)

Automation Rule
When:
Daily timer or what ever

If:

Tasks.Filter(
  Done=false
  AND Due >=Today()
  AND Due <= Today() +7).Count() > 0

Then:

Tasks.Filter(
  Due >= Today() 
  AND Due <= (Today() + 7)).Sort(true,Tasks.Due).FormulaMap(
      RunActions(
        Notify(CurrentValue.Responsible,
          Format("Due soon: {1} on {2}",CurrentValue.Name,CurrentValue.Due)
        )
      )
  )