I’m trying to create a tool to add recurring tasks to a schedule. I’ve seen many similar questions in this community, but unfortunately none that match my needs. I hope someone can point me in the right direction?
So there are 2 ingredients:
A table of recurring tasks and their interval. The interval is one or more Weekdays (Monday-Sunday) and whether the task occurs every week, only on even weeks, or only on odd weeks.
A table with the weekly schedule of tasks with employees assigned to it.
I’d like an action that adds the recurring tasks to the Schedule table on the corresponding Date in that Week#.
First you select the Week# where you want the recurring tasks copied to.
Then the action needs to verify each Task in the table Recurring tasks :
Does the task already exist in the Schedule table in the selected week#?
Is Include when copying tasks? checked?
Does the Every week? value match the selected week#?
Add tasks for every checked weekday on the corresponding dates of the week# (so for instance Monday in week 7 means create the task with date 17/02/2021 (DD/MM/YYYY)
To be sure, I’m not looking for an automation. I want to manually kick off this action when needed (once a week, but not on a set day/time)
I’ve made a test doc so you can see what I mean:
I doubt this can be done “in one go” with RunActions? I’m pretty sure I need to cut it up in several actions, maybe with additional columns? Not sure where to even start though…
I hope someone can point me in the right direction
Hi @Bas_de_Bruijn ,
while not super-trivial, I wanted to give it a try to show the great power of Coda capabilities.
It can be done with a single action (I already put the code in the button) thanks to the features that WithName() now enables, and this is the formula:
hi @Federico_Stefanato , I am impressed and would welcome some additional feedback on how you came to this solution. The filter seems complicated, but is it and what does it do exactly?
I got the advice from Coda Support to use FormulaMap more than once, however I count 3 if I am not mistaken
I’ll try to provide an inline commented version of the above formula, based on @Bas_de_Bruijn requisites
[Recurring tasks].Filter( // start with the Recurring task table
[Include when copying tasks?] // take only selected tasks
&& ([Every week?]="Weekly" // that are recurring for every week or...
|| [Every week?]="Even weeks" && [Week select].IsEven() // ... match the even week or...
|| [Every week?]="Odd weeks" && [Week select].IsOdd())) // the odd one
.FormulaMap(CurrentValue.WithName(rec, // for each one of the records (currentValue) we assign the name 'rec'
If([Scheduled tasks].Filter( // we now check if the record in Scheduled tasks exists
[Week #]=[Week select] // same week...
&& Customer=rec.Customer // same Customer...
&& [Task description]=rec.[Task description]) // same description
.Count()>0,_Noop(), // if the record does exist, do nothing
// otherwise...
rec.Days.FormulaMap(CurrentValue.WithName(day, // we take the Days column (which is a list of days) and for each one we take the single value of the day and we assign the 'day' name (basically, day contains the weekday number, now). So: for each day of each task...
[Scheduled tasks].AddRow( // ... add a row
[Scheduled tasks].Customer, rec.Customer, // copy the relevant fields from the template
[Scheduled tasks].[Task description], rec.[Task description],
[Scheduled tasks].Hours, rec.Hours,
[Scheduled tasks].Start, rec.Start,
[Scheduled tasks].End, rec.End,
[Scheduled tasks].Employee, rec.Employee,
[Scheduled tasks].Date,
DateTimeTruncate((Date(Today().Year(), 1, 1) + ([Week select] ) * 7), 'week')+day) // find the date based on the selected week and add the number of days according to 'day'
// voilà! :)
)))))
Please, do let me know if something is still not clear.
Thank you so much @Federico_Stefanato! This is definitely a lot more complicated than I thought. Very impressive.
It took me a while to understand what is going on in the formula. Your updated version with the inline comments is very helpful. More thanks!
In terms of “solution strategy”, I sort of thought about doing what you did with the additional Days column (create a list with the weekdays of that task), but I could never come up with that formula. Oh well, I’ll just take it as a tiny personal moral victory that at least my thinking pattern was not wrong
Also, I’ve read about the WithName() feature before, but didn’t really understand how or when to use it. Now I finally get it. Combined with the FormulaMap (like you did) it is indeed a very powerful feature.
Again, thank you so much for taking the time & effort to write the formula and explaining the logic behind it. Much appreciated! A real eyeopener.
Yes, I think that the two combined allow what before was impossible (because of the overriding of nested CurrentValue-s).
Also, it add clarity when writing the code.
Play around with it and let me know if you need some more insights.
Enjoy Coda!
Just to throw out another technique - I was just working through a similar scheduler builder and found it helpful to break down the logic into smaller parts:
Use buttons in a table to help split up function into smaller elements. This actually gives you 2 functions to split the logic into - the button action itself and the disable function. Then you can add a button (outside the table) that just pushes all the buttons in the table button column (note that disabled buttons will not be executed)
Extract logic into “helper columns” for improved visibility and reuse (I like to color these grey as well to indicate that they are computed / not editable)
Use views to differentiate between data/config input v.s. button actions (and associated helper columns) you want to perform against the same rows
Here’s what that could look like adapting the test doc above
Hi @Ben_Lieber1, thanks for adding another solution!
Your technique (break down the logic into smaller parts) is how I initially thought the solution should be. I just wasn’t sure which parts to break down into which smaller parts. But then @Federico_Stefanato showed me his “1-step-solution” and I was blown away. I never could have thought of that myself, but once he showed me, it opened my eyes to, well, a whole new world of possibilities.
Federico’s method is super powerful, but more complicated to come up with/execute. Your method achieves the same goal, just in a few more steps, making it more “accessible” to non-power users I think. I appreciate both methods though!
