Button: Add dates to each task based on offset

Hi. I’m looking for some help for a formula.

We’ve created a button that adds several pre defined tasks to our local organization table, and we currently have a date offset on each task that defines the number of days before the event that the tasks should be performed (due date). I’d like to automatically add the Due dates for each task based on this offset. Preferably with a popup text box or a date input when hitting the automation button.

Here is what I’ve got so far. This is a table of my Norwegian table of project (column 2), task name (column 1), due date (column 3) and offset (column 4).

My current “Create event” button does the following (I’ll translate to English to make it more understandable)

[DB Master Task].Filter(CurrentValue.Project=[External event]).FormulaMap(
[DB Local Task Table].AddRow(
[DB Local Task Table].Project, CurrentValue.Project,
[DB Local Task Table].Title, CurrentValue.Title,
[DB Local Task Table].Due Date, CurrentValue.Due Date,
[DB Local Task Table].Offset, CurrentValue.Offset
)
)

Are there any way to prompt the user for an input with a text box? Or would I have to add a date picker field that you use as an input before the button is hit?

And how would you suggest that I incorporate the “add due date based on offset and user input”?

Would it be as simple as something like this (replacing just the one line above)?
[DB Local Task Table].Due Date, CurrentValue.Offset + DateInputfield,

Any suggestions are appreciated, as I’m a pretty novice scripter :slight_smile:

Hey Carl,

Your formula looks great, and your hunch about replacing the

  • [DB Local Task Table].Due Date, CurrentValue.Due Date
    with
  • [DB Local Task Table].Due Date, CurrentValue.Offset + DateInputfield

is also spot on :wink:

Here’s a trick I learned from Paul_Danyliuk that might help you: using a modal view of a row as an intermediary dialogue for cases just like this — when you want to create a row (or rows) with some added input from the user.

Check out this example:

Thanks @Ryan_Martens2 ! This is super interesting. And thanks for adding that example. That’s very helpful :slight_smile:

This would probably solve a couple of issues for us. Like setting date for offset to be counted from, as well as naming the projects with different names in case there are more than one of the same project at the same time.

I spent some time testing with your example and I have a couple of issues you could probably help me grasp.

I would ideally like to add a “Create Project x” buttons that opens the modal screen with input fields as well as the project and accompanying tasks, but do not create the tasks until you have entered the event date, name and hit the Create button.

I’m not sure if this is possible or not. I have a problem grasping the code for the buttons and how I could make a “create” event button in the modal screen, so I’ll do this in pictures to illustrate my ideal setup.

Here’s our current Project and task list source. It can not be altered locally. It’s synced from a national task list. It contains the columns: Project, Task, Due Date, Offset:

We’ve got these buttons corresponding to the different projects:
image

Ideally it would pop up a modal box looking like this (preferably with task check marks so some organizations could drop a task or two if they are not neccessary):

I’m not sure how much of this is possible? I kind of have the feeling that I at least have to add that Create button to all of the tasks and have the same code on if for each project. Any suggestions or examples on how to modify things for this outcome would be fantastic.

Hey Carl, happy to help :slightly_smiling_face:

One clarifying question then, since you say that the template projects, along with their tasks, are coming in via Cross-Doc and cannot be altered locally, where are you putting the newly created tasks? Are you placing them in a separate table in the current doc? And if so, will they be synced back to this master table somehow?

The doc I made puts the new tasks right back into the same table that the template tasks are stored in. It could easily be made a separate table as well, if that’s what you want to do. Otherwise, I think it already does what you want you explained to me you want to do. I put in some more clarifying notes into the bottom of the page there, I hope that helps. :slightly_smiling_face:

By the way, that style of explaining formulae I learned from @Connor_McCormick1 :wink:

1 Like

Ok, I just changed the structure of the doc to help match your scenario, and make it a little bit more logical, albeit a little more complicated. Notes in the doc.

You’re correct. The tasks created should be put into a new table. We don’t care to sync anything back. It’s a strict “provide decent templates to make them start of with best practices”. And then leave them to their own with their unique table.

Your examples does what we want, but in a slightly more complicated way than I hoped. If this example could be reduced to:

  • Having the functionality of hitting the Create button in the “Helper Modal Dialogue”, but getting the Tasks from the Master Task table. Our tasks is just a set of items in a table that are group together with a Project name. That would be perfect.

The way it’s set up now, it uses an extra table called Projects it seems. As well as the extra Helper Modal Dialogue. Of course, if we have to have this in place, then we would make it work. However, I think that there’s a super high risk that the new board members in my organizations might not be as tech savvy as me. So I need to try to keep it as simple as possible.

I’ll try to illustrate with some pseudo code.
Prerequisite:

  • One “Master Task” table with synced tasks. Project name, tasks and offset. Like my previous picture.
  • And a “Local Task” table for the newly created project and tasks.

Add a Button somewhere inn the doc: “Create - External Event”
“Master Task” table.Project.“External Event”.Activate

  • Add NewProjectName field
  • Add EventStartDate field
  • List tasks for current project
  • Add a Create Event button to the form

“Create” button pseudo code:

  • CurrentProject.create tasks in “Local Tasks” table with
  • Project Name = NewProject.Name
  • Due Date = EventStartDate - Master Task.Offset
  • TaskName = TaskName

I fully realize that this might be impossible. I see some obstacles, as far as my knowledge of Coda goes:

I might need to add fields to all the tasks to be able to list them with the “Activate” command (form popup). This includes things like

  • NewProjectName could be an empty field in Master Task table. Just not visible in normal views.
  • NewStartDate could likewise be an empty Date field.
  • As well as the “Create” button to actually create the tasks and add them to the “Local Task” table.

That part is doable. I also suspect that I’m not going to get a list of the project Tasks in the “activate” form, if we don’t have a relational database adding the tasks to the projects.

If there are any way of getting closer to my goal here, it might be worth it. Because a hard to understand system with several tables might be a too big upkeep for us. We change board members each year :confused:

Hi Carl,

Of course you can change where the tasks are from — that is as simple as changing the formula for the default value of tasks.
output

You can also easily do without the project table — then just skip the blue part of the formula, and instead of [new project] put a string like "My Cool Project". Then you would have to go back to the formula you wrote for determining task due date, since it can no longer get the start date from the Project. Specifically, the last purple line would change to:

Tasks.[Due Date], thisRow.[Start Date] + CurrentValue.Offset

The last table, called “Helper Modal Dialogue” is the one that is giving you the pop-up form, so you can’t get rid of that one. But you can definitely hide it away by putting it on another page and the user never needs to see it. And by the way, you can also do the same thing with the project table, which might be better than deleting it altogether.

I think we’ll end up with a somewhat different solution in the end. But non the less, this have been a huge help getting me in the right direction and checking out the different ways of solving this.

I spent several hours to check for alternatives to opening modal and THEN creating tasks. Here’s a full test environment that shows the end result based on @Ryan_Martens2 tips, as well as a rather simple Link opening trick. Full explanation, possible alternative solutions and examples can be found here:

The base functionality I ended up with (so far) are as follows…

  • Two Source tables. “Source Tasks” and “Source Projects” (they are synced in our case)
  • One Local Task table that need a copy of a template project from the source tables.
  • One button is added for each Project template. Link opens up a Modal table view with only the New Project Name, New Event Date, Task List as well as Create New Event button.
  • Users enter New Project Name, set Date for the actual event and hit the Create Event Button
  • Script goes through all tasks linked to the old Project Name, copies them to the local table with…
    – New Project Name
    – New Due Dates based on “New Event Date” pluss the “Offset” set on each task.

Problem 1: This works wonderfully, except for the last part.
[Local Task].[Due Date],(thisRow.[Input: New Event Date]+[Source Task].Offset),

I end up with crazy looking Due Date values like this. And it’s all the same for all taks.
image

Anyone know how I have to write this line to get the correct Due date + offset value as a real date?

Here’s the full script in case it makes more sense:

RunActions(
thisRow.[Project Tasks].FormulaMap(
[Local Task].AddRow(
[Local Task].Project,thisRow.[Input: New Project Name],
[Local Task].[Due Date],(thisRow.[Input: New Event Date]+[Source Task].Offset),
[Local Task].Task, CurrentValue.Task
)
),
RunActions(
[Source Project].ModifyRows(thisRow.[Input: New Project Name],“”,thisRow.[Input: New Event Date],“”)
)
)

Problem 2: The task list that is shown in the Modal popup are not selectable.
Thus, we can not remove certain tasks from the list before new tasks are created. Even if we could add the checkboxes, we could not deselect any of them, as the Tasks are a lookup field and can’t be edited I guess. This is not a huge problem. People can just delete the few tasks they want after creation.

After a bunch of trial and errors I figured it out. This solution if just fantastically good :slight_smile: Our members will be pleased.

What I did wrong was to add the Source Task table Offset, instead of Currentvalue.Offset. Like this:

RunActions(
thisRow.[Project Tasks].FormulaMap(
[Local Task].AddRow(
[Local Task].Project,thisRow.[New Project Name],
[Local Task].[Due Date],thisRow.[New Event Date]+**CurrentValue.**Offset,
[Local Task].Offset, CurrentValue.Offset,
[Local Task].Task, CurrentValue.Task
)
),
RunActions(
[Source Project].ModifyRows(
thisRow.[New Project Name],“”,
thisRow.[New Event Date],“”
)
)
)

Thanks a bunch for your help Ryan!