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:
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?
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
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.
Send the summary mail to the address associated with the username from the “Responsible” column.
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.
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!
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
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!
@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.
Any chance you could give me a few pointers @Scott_Collier-Weir ? I’ve tried to search this forum and tested some solution, but have come up short of anything working as I hoped.
I have found examples of how to send info of each task with one mail via a button column (see example page above), but I have not managed to create a summary mail with the same method. I suspect that the first formula you suggested might be the closest thing so far to achieve that. It’s also added to the sample page.
However, there is one difficulty I’m still left with. I hope some of you can tell me what’s going on.
The solution is quite elaborate, but the main steps are as follows:
A Responsible table is created with the people in the organization
A table with All Tasks is created. With a Responsible column lookup to the Responsible table.
A template page with a view of the All Tasks table. Filter control is added with input of responsible from the Responsible page. Filtering the All Tasks table.
Canvas column is created in the Responsible table, tied to the Template page.
A Summary Mail button column in the Responsible table should send mail of all tasks for the responsible, through the Template page and table view of the expiring tasks.
The result should be a nicely crafted mail with a table of upcoming and expired tasks for each responsible.
The problem seems to be that changing responsible does not always update the responsible in the filter control of the template through the canvas field. And adding more people in the Responsible table seems to also show a different responsible in the filter control when hitting open on the canvas field. The result when actually sending the mail reflects this problem.
The problem
I do not know how this link between the Responsible table and the template and filter control are working exactly, but I suspect that this might have something to do with the canvas column being populated when created. The content might not be that flexible after initial setup. It asks to populate the canvas field for the missing columns initially, but no such message are presented when adding new persons later on.
Anyone know how to fix this? We really need to be able to change people responsible and add new people now and then.
I hadn’t figured out a way to automate a filter in a canvas column template. That part I was doing myself – When a new person is added to the table, I also edited the canvas column filter.
Ok. I see. So, the only thing we need to do is to open the Canvas for each person added or when people are switched in a row, and select the correct person in the canvas filter.
Should be doable to add a big warning with simple instructions for people to follow I guess. This is miles better than the alternatives I’ve been looking into anyway.
