Hi guys,
I’m trying to make a button to create rows in Table A based on variables from Table B & Table C. The tricky part is to translate a list of weekdays to corresponding dates.
The set-up:
- Tasks table with recurring tasks. Every task (row) consists of a Project, Description, Start time, End time and a list of Weekdays on which this task should be done.
- Employee availability table that tells you when which employee is available to work. It consist of an employee, a start date, an end date, and a list of dates between them.
- Schedule table. Every row is a “shift”; it tells you where (which project), what (description), who (employee) and when (date, start time, end time).
Goal: I want the schedule button in Employee availability table to create rows in Schedule table
1: Check if there are tasks for this employee in Task table
2: Check if these tasks are already in Schedule table on any of the dates (all dates between Start date and End date in Employee availability). If yes: skip, if not exist:
3: Create a row for every task on every date
4: Check the Scheduled column in Employee availability table to mark that it has been scheduled. This checkmark triggers the “disable if” rule of the Schedule button.
Bonus goal: add a copy of each created row in Schedule table with a different variable
Not sure if this is even doable, but ideally in the same flow (push of the Schedule button) I would like to create a copy of each created row in Schedule table with the same values except for “Extra variable”. The first created row (from “Goal 1”) should always have Extra variable “A”, the copy should always have Extra variable “B”.
Current status of the goal:
1: Match the Employee from “Employee availability” with “Tasks”: works 
2: Check if the Task already exists on that date in “Schedule”: works
3: It creates the correct number of rows in “Schedule” , but it doesn’t add the correct date 
4: Add checkmark at Scheduled afterwards: works
I need a formula to translate the weekdays on which the tasks should be done (Weekdays list from Tasks) to the corresponding dates from Employee availability (from Start date up to (including) End date).
I created a results column with all these dates as a list (Date list column on Employee availability). Maybe I can use that list to match the weekdays against? Maybe I don’t need this column? How to include this date-matching in the formula though? Can you add a FormulaMap inside a FormulaMap?
Current status of Bonus Goal: non existent
I have no idea where to start. How to tell Coda to copy the exact row it just created with “ FormulaMap() + WithName() + AddRow () ”? To be more precise: where/when to include this in the formula?
I hope someone can point me in the right direction…?
