I’m working on a project management doc and want to be able to quickly apply a template of applications to a project.
That part is pretty simple - I made a template selector drop-down and then a button that runs ForEach to duplicate all the rows from the template, reassigning them to the new project in the process.
My problem is that the applications in my template project require dependencies between each other. When I click my button, if I simply map current values my new applications end up being dependent on the original rows from the template.
I have a faint inkling that I would probably have to approach this as a two-step process, since it doesn’t really make sense to try and create dependencies between rows WHILE they are being newly created. It’s going to be difficult as well to figure out a way to keep that dependency structure intact and then apply it in the second step - probably going to have to venture into creating temporary “keys” to map out the dependency structure and using sort orders or something to then re-apply the dependencies in the same way…? Still not really sure, but in any case its going to get quite complicated.
But before I go deeper down this path I was wondering if anyone could offer some advice on how to approach this problem? I feel like its a fairly common use case so hoping someone could share their own solution. If a completely different tactic would end up with better results I’m all ears.
Attached is a quick test doc to illustrate my dilemma.
I am very close to being able to implement my aforementioned kind of sketchy two-step solution:
I managed to make a button that successfully re-associates one dependent at a time by comparing it against joined values of other parts of the row. (I do recognize this is maybe a little sloppy, but I’m thinking if I cram it all into a one button procedure to duplicate and then re-associate everything in one press it might be fine.)
However, this only works correctly if its only having to pair one row with one other row. I unfortunately do need it to be able to handle re-associating any number of rows.
I’ve tried a little bit with the ForEach formula within my button, but I’m now thinking that might be heading in a dead end direction. Instead of compiling and keeping each iteration of the formula it instead just goes through all rows and the result is only the last dependent being re-associated.
My skills always fall short as soon as formulas have to start working with lists of data. If anyone could lend me some advice that would be great!
What you are trying to build is not trivial, specially the part about recreating the dependencies between template tasks and actual tasks. It was a fun little challange.
I rebuilt the system with 4 main tables: Projects, Project tasks, Templates and Template tasks.
The action to create a new project has theser rough steps:
Create a project and store it in a helper variable
Create tasks based on the selected template and store them in a helper table
For each of the recently created tasks, check if the template is based on has dependencies
and if so, identify the correct dependent tasks and assign them
Reset selectors and helpers
Here’s a working example, you can copy it to your workspace and take a look at the code
This is awesome! Thanks so much for putting the time in to help me out. I had a faint idea of trying to use a unique row ID to make the reassociation a bit more secure/reliable, but I wasn’t aware of this formula you used for that so you’ve saved me big time!
Also lightly familiar with helper tables thanks to @Paul_Danyliuk but have had trouble putting them into practice in the past.
I’ve taken a brief look through and I think I understand how I can mesh this with my document. I’ll report back if I hit any stumbling blocks.
Just reporting in that @Pablo_DV 's solution worked beautifully! One little adaptation I had to work out is that I personally did not want a dedicated DB.Template table separate from the actual DB.Tasks, as maintaining both tables to match each other would be onerous over time.
To do this, I added both “ID” columns to my one DB.Tasks table. One using the Unique ID formula, and then adding another empty text column called Pair ID.
Another little thought experiment, if anyone is willing to take on the challenge, is maybe making this work using the “Duplicate Rows” function instead of AddRow/ModifyRow. Pablo’s solution works great but one small weakness is that each value you want to be copied over needs to be added to the formulas. For my purposes it would be preferential to have the opposite approach - copy everything over verbatim and then change what is needed. Overall would just be a bit easier to maintain.
I guess the rewrite would be something like running DuplicateRow() early on and then going back over with ModifyRows() to change any values.
I’m a little intimidated by the challenge of trying to make this happen myself so I am more than satisfied with this solution for now. But just thought I’d mention.
Once again thank you so much @Pablo_DV for your help, you’re a life saver!