How to send automated task summary mail to users?

Hi. I want to create a automatic rule to send mail notifications each week about upcoming and expired due dates in our task table. But I’m unable to find much useful.

I’ve installed the gmail pack and set the admin account as sender. But I’m having a hard time figuring out the formulas. I want to send a mail to the Responsible user, with all tasks that are not marked as completed. Quick example below:

Test Table:

Automation Rule

When:
Once a week (triggered manually for testing)

If:
Tasktable.Filter([Completed?]= False() AND
IsBlank(Tasktable.Responsible).Count() > 0)

This returns only true, but no items I believe.

Then:
Gmail::SendEmail([mail@xxxxx.no], Tasktable.Responsible, “Tasks not done:”, Concatenate(Tasktable.Filter([Completed?]= False())))

This fails hard as well. Not sure where to take it from here. The above code is not complete, but I just tried to make the parts work first.
Any suggestions?

1 Like

Hello @Carl_Haugen ,

I tried to realize the exercise with my poor knowledge ^^ and here is my proposal that can be refined according to the exact criteria. A button approach would have been a good idea too but you wanted an automation :slight_smile:

Sincerely,

Thierry

1 Like

Thanks for the suggestion, Thierry.

Unfortunately that does not do what I want it to :confused:

What I need is to…

  1. Filter all taks which is “not completed” and have a passed “Due date”. I suspect we also need to filter for each user here, so we can send only one mail per user. This summary of their tasks is what we want to send in the next step of the automation task.

  2. Send the summary mail to the address associated with the username from the “Responsible” column.

2 Likes

Hello,

Another approach would be to use a filter on the table directly already to sort the completed tasks and then create a column with buttons to send an email when necessary.

Sincerely,

Thierry

2 Likes

Hey there!

First you will want to modify your if() condition so the count() is outside the filter()

  • task.filter(completed=false and responsible.isBlank()).count()>0

That should be a fine if() condition either run or not run the action

After that you have LOTS of options with email

You could run this formula:

  • withName(task.filter(completed=false), targetTasks, targetTasks.formulaMap( format(“{1} - Due Date: {2}, currentValue.task, currentValue.DueDate)).bulletedList()

That should return a bulleted list of every undone task formatted for an email

You could also run a similar formula inside a table of users (one row per user) and then have an email button column but add a (and peopleColumn=Responsible) in your filter

Then have your automation push that Column of buttons to send individualized emails to users with tasks.

Writing this out while my son chucks wood chips around, so if you need more help I’m happy to jump in a computer tomorrow and throw an example doc together!

5 Likes

I wanted to surface this possible solution by Codan @BenLee:

2 Likes

Thanks @Scott_Collier-Weir. You’re really on to it. I got the first part right, but I think you lost me on the second formula.

I would very much appreciate an example if you have the time! This goes a bit over my head. I’m just used to simpler excel formulas and powershell scripts at work :stuck_out_tongue:

A key part of this would be to filter all tasks for each person, and send each person a summary of the tasks if some are not marked as completed and are reaching the due date soon. I get the feeling that we can’t do this without a Button in the task table? It would be simpler without, as we sync this task table to several docs. But the “send mail button” column could be added and adjusted individually to the synced tables if needed I suppose.

This is a part of a weekly summary for all our users for them to plan the upcoming week.

@steph Thanks for the link. This is an excellent example of email usage in coda. We will be using this for for news letters and some other things!

2 Likes

Certainly! You’re welcome. Keep us posted on how it turns out.

1 Like

@Scott_Collier-Weir I made a page here with the basic setup and my poor attempt of the automation. Should be open for anyone to edit. Would appreciate it very much if you have some tips on how to proceed. The gmail pack is set up and should be usable for everyone.

I managed to create a button to trigger mail for individual buttons with a button column. But I have no idea how to turn that into one summary mail per user.

Also tried to use the suggested formula from Scott to create a summary email with another button, but there seems to be a very slim chance I can make it without some help.

Anyone able to see what I’m missing here? Sample page above is open for all to edit.

1 Like