Skip to main content

A common theme we are seeing with project management bases is the desire to have project and task templates. When you create a new project, it can then generate the corresponding set of tasks, and maintain the basic details about those tasks, including the relationships and dependencies amongst those tasks.



This is similar to Creating Tasks from a Template with a few differences. When creating projects, we often find we create a new record in our projects table, and then want to link it to a set of metadata (a project template). From there, each project template should know its associated set of tasks. This allows us to add more project templates over time, and change the set of tasks required to complete each of those projects.



Additionally, the script will ensure that the dependencies between the task templates are maintained when creating the tasks for a new project.



You can see examples of the base:





Full code below



// when a new asset is created, 


// there are a certain number of tasks that must be completed


// these tasks are dependent on the type of asset


// Given a list of project and task templates,


// when a new asset is created and has a project template assigned


// we can then create corresponding set of tasks for that asset





// define some of our initial variables


// these are the basic table, field and view names to define to create the script


const project_table_name = 'Projects';


const new_project_view_name = 'New Projects';


const project_template_link_field_name = 'Project Template';





const task_table_name = 'Tasks';


const task_project_link_field_name = 'Projects';


const task_dependency_field_name = 'Followed By';


const task_primary_field_name = 'Task Name';





const task_to_template_link_field_name = '_task_template_id';   // this is used to assist with creating the links between tasks after they are created.  We don't need to maintain a true linked record relationship, but temporarily storing the template's ID is helpful





const project_template_table_name = 'Project Templates';


const task_template_table_name = 'Task Templates';


const proj_temp_task_temp_link_field_name = 'Tasks';


const task_temp_dependency_field_name = 'Followed By';


const task_temp_primary_field_name = 'Task Name';





/********************************************************/


output.markdown('# Creating tasks for new assets and assigning dependencies');





// create our table objects


const project_table = base.getTable(project_table_name);


const task_table = base.getTable(task_table_name);


const project_temp_table = base.getTable(project_template_table_name);


const task_temp_table = base.getTable(task_template_table_name);





// get all new projects that have been assigned a template


// but do not yet have tasks


const new_project_view = project_table.getView(new_project_view_name);


const new_project_results = await new_project_view.selectRecordsAsync();





// pull all of our project templates and all task templates


const project_temp_results = await project_temp_table.selectRecordsAsync()


const task_temp_results = await task_temp_table.selectRecordsAsync();





// build a map of projects to tasks


output.markdown('### Setting up');


output.markdown('Building map of project templates and task templates');


var project_task_temp_map = {};


for (let r of project_temp_results.records) {


    let temp_tasks = r.getCellValue(proj_temp_task_temp_link_field_name);





    project_task_temp_map[r.id] = temp_tasks.map((t)=>{


        return t.id;


    }) 


}


output.inspect(project_task_temp_map);





// also build a map of task template to task template so we can resolve dependencies


output.markdown('Creating map of templated tasks to dependent tasks');


var task_temp_dependency_map = {};


for (let r of task_temp_results.records){


    let next_task = r.getCellValue(task_temp_dependency_field_name);


    if(next_task !== null) {


        task_temp_dependency_map[r.id] = next_task[0].id;


    } else {


        task_temp_dependency_map[r.id] = null;


    }


}


output.inspect(task_temp_dependency_map);





// for each new project


// get the set of tasks and create the creation payloads


// we will need to do a second pass of all of these records to then _update_


// the tasks with the corresponding dependencies


//


// THIS IS THE PART OF THE SCRIPT WHERE YOU ASSIGN WHAT DATA YOU WANT IN YOUR NEWLY CREATED TASKS


//


output.markdown('### Creating new tasks')


output.markdown(`Found **${new_project_results.records.length}** projects which need task assignment`)


var payloads = [];


for(let r of new_project_results.records){


    // there should only ever be one project template linked


    // so just take the first one


    let p_id = r.getCellValue(project_template_link_field_name)[0].id;





    let task_temp_ids = project_task_temp_map[p_id];


    let task_temps = task_temp_ids.map((i)=>{


        return task_temp_results.getRecord(i);


    });


    for(let t of task_temps) {





        payloads.push({


            fields: {


                [task_project_link_field_name]: [{id: r.id}],


                [task_primary_field_name]: t.getCellValueAsString(task_temp_primary_field_name),


                [task_to_template_link_field_name]: t.id


            }


        });


    }


}





// create all of the new tasks


// we should hold on to the created IDs


output.markdown(`Creating **${payloads.length}** tasks across these projects`);


var new_tasks = [];


while(payloads.length > 0){


    let n = await task_table.createRecordsAsync(payloads.slice(0,50));


    new_tasks = [...new_tasks, ...n];


    payloads = payloads.slice(50);


}





output.markdown('### Creating dependencies between new tasks');


output.markdown('Pulling newly created tasks')


// refetch these tasks so that we can update the dependencies


const task_results = await task_table.selectRecordsAsync({


    fields: [


        task_primary_field_name, 


        task_project_link_field_name, 


        task_to_template_link_field_name, 


        task_dependency_field_name


    ]


});





// pull out only the newly created tasks


const tasks_to_update = new_tasks.map((t)=>{


    return task_results.getRecord(t);


});





output.markdown('Creating map of new tasks to templated task ids to resolve dependencies')


// create a map of new task to templated task ids


// and templated tasks to new task ids


// group them by project since any given project may be using the same set of templated tasks


var new_task_to_template_map = {};


for(var r of tasks_to_update){


    let p = r.getCellValue(task_project_link_field_name)[0].id;


    let temp_t = r.getCellValue(task_to_template_link_field_name);


    if(new_task_to_template_map[p] === undefined){


        new_task_to_template_map[p] = {


            task_to_template: {},


            template_to_task: {}


        };


    }


    new_task_to_template_map[p].task_to_template[r.id] = temp_t;


    new_task_to_template_map[p].template_to_task[temp_t] = r.id;


}


output.inspect(new_task_to_template_map);





// now go back through the tasks one more time and actually build out the payloads to establish links


// not all tasks will have a dependent task


// we can filter these out afterwards


payloads = tasks_to_update.map((r)=>{


    let p = r.getCellValue(task_project_link_field_name)[0].id;


    let temp_task = r.getCellValue(task_to_template_link_field_name);


    let temp_task_dependency = task_temp_dependency_map[temp_task];


    let dependent_task_id = new_task_to_template_map[p].template_to_task[temp_task_dependency];


    if(dependent_task_id === undefined) {


       return undefined;


    }





    return {


        id: r.id,


        fields: {


            [task_dependency_field_name]: [{id: dependent_task_id}]


        }


    }


}).filter((r)=>{


    return r !== undefined;


});


output.inspect(payloads);





output.markdown(`Updating **${payloads.length}** tasks with required dependencies`);


while(payloads.length > 0) {


    await task_table.updateRecordsAsync(payloads.slice(0,50));


    payloads = payloads.slice(50);


}


output.markdown(`### Done`);

Hi @matt_stewart1 the code works on my end. You have to make sure the dependencies are mapped on your template table as well. It looks like you may have a problem with the structure of your tables.


I have my version of the code here:



// when a new asset is created, 


// there are a certain number of tasks that must be completed


// these tasks are dependent on the type of asset


// Given a list of project and task templates,


// when a new asset is created and has a project template assigned


// we can then create corresponding set of tasks for that asset





// define some of our initial variables


// these are the basic table, field and view names to define to create the script


const project_table_name = 'Deals';


const new_project_view_name = 'Create Tasks';


const project_template_link_field_name = 'HS Deal Link';





const task_table_name = 'Prelim Tasks';


const task_project_link_field_name = 'Deal';


const task_dependency_field_name = 'Dependencies';


const task_primary_field_name = 'Prelim Task';


const task_start_field_name = 'Start Date';


const task_end_field_name = 'End Date';


const task_status_field_name = 'Status';





const task_to_template_link_field_name = '_task_template_id';   // this is used to assist with creating the links between tasks after they are created.  We don't need to maintain a true linked record relationship, but temporarily storing the template's ID is helpful





const project_template_table_name = 'HS Deal Types';





const task_template_table_name = 'Prelim Task Templates';


const proj_temp_task_temp_link_field_name = 'Prelim Task Templates';


const task_temp_dependency_field_name = 'Dependencies';


const task_temp_primary_field_name = 'Name';


const task_temp_duration_field_name = 'Duration (Days)';


const task_temp_status_field_name = 'Status';





/********************************************************/


output.markdown('# Creating tasks for new assets and assigning dependencies');





// create our table objects


const project_table = base.getTable(project_table_name);


const task_table = base.getTable(task_table_name);


const project_temp_table = base.getTable(project_template_table_name);


const task_temp_table = base.getTable(task_template_table_name);





// get all new projects that have been assigned a template


// but do not yet have tasks


const new_project_view = project_table.getView(new_project_view_name);


const new_project_results = await new_project_view.selectRecordsAsync();





// pull all of our project templates and all task templates


const project_temp_results = await project_temp_table.selectRecordsAsync()


const task_temp_results = await task_temp_table.selectRecordsAsync();





// build a map of projects to tasks


output.markdown('### Setting up');


output.markdown('Building map of project templates and task templates');


var project_task_temp_map = {};


for (let r of project_temp_results.records) {


    let temp_tasks = r.getCellValue(proj_temp_task_temp_link_field_name);





    project_task_temp_map[r.id] = temp_tasks.map((t)=>{


        return t.id;


    }) 


}


output.inspect(project_task_temp_map);





// also build a map of task template to task template so we can resolve dependencies


output.markdown('Creating map of templated tasks to dependent tasks');


var task_temp_dependency_map = {};


for (let r of task_temp_results.records){


    let next_task = r.getCellValue(task_temp_dependency_field_name);


    if(next_task !== null) {


        task_temp_dependency_map[r.id] = next_task.map(({id}) => id);


    } else {


        task_temp_dependency_map[r.id] = [];


    }


}


output.inspect(task_temp_dependency_map);





// for each new project


// get the set of tasks and create the creation payloads


// we will need to do a second pass of all of these records to then _update_


// the tasks with the corresponding dependencies


//


// THIS IS THE PART OF THE SCRIPT WHERE YOU ASSIGN WHAT DATA YOU WANT IN YOUR NEWLY CREATED TASKS


//





output.markdown('### Creating new tasks')


output.markdown(`Found **${new_project_results.records.length}** projects which need task assignment`)


var payloads = [];


for(let r of new_project_results.records){


    // there should only ever be one project template linked


    // so just take the first one


    let p_id = r.getCellValue(project_template_link_field_name)[0].id;





    let task_temp_ids = project_task_temp_map[p_id];


    let task_temps = task_temp_ids.map((i)=>{


        return task_temp_results.getRecord(i);


    });


    for(let t of task_temps) {





        payloads.push({


            fields: {


                [task_project_link_field_name]: [{id: r.id}],


                [task_primary_field_name]: t.getCellValueAsString(task_temp_primary_field_name),


                [task_to_template_link_field_name]: t.id,


                [task_start_field_name]: typeof t.getCellValue(task_temp_duration_field_name) === "number" ? new Date() : "",


                [task_end_field_name]: new Date(new Date().setDate(new Date().getDate() - 1 + t.getCellValue(task_temp_duration_field_name))),


                [task_status_field_name]: {name: t.getCellValueAsString(task_temp_status_field_name)}


            }


        });


    }


}





// create all of the new tasks


// we should hold on to the created IDs


output.markdown(`Creating **${payloads.length}** tasks across these projects`);


var new_tasks = [];


while(payloads.length > 0){


    let n = await task_table.createRecordsAsync(payloads.slice(0,50));


    new_tasks = [...new_tasks, ...n];


    payloads = payloads.slice(50);


}





output.markdown('### Creating dependencies between new tasks');


output.markdown('Pulling newly created tasks')


// refetch these tasks so that we can update the dependencies


const task_results = await task_table.selectRecordsAsync({


    fields: [


        task_primary_field_name, 


        task_project_link_field_name, 


        task_to_template_link_field_name, 


        task_dependency_field_name


    ]


});





// pull out only the newly created tasks


const tasks_to_update = new_tasks.map((t)=>{


    return task_results.getRecord(t);


});





output.markdown('Creating map of new tasks to templated task ids to resolve dependencies')


// create a map of new task to templated task ids


// and templated tasks to new task ids


// group them by project since any given project may be using the same set of templated tasks


var new_task_to_template_map = {};


for(var r of tasks_to_update){


    let p = r.getCellValue(task_project_link_field_name)[0].id;


    let temp_t = r.getCellValue(task_to_template_link_field_name);


    if(new_task_to_template_map[p] === undefined){


        new_task_to_template_map[p] = {


            task_to_template: {},


            template_to_task: {}


        };


    }


    new_task_to_template_map[p].task_to_template[r.id] = temp_t;


    new_task_to_template_map[p].template_to_task[temp_t] = r.id;


}


output.inspect(new_task_to_template_map);





// now go back through the tasks one more time and actually build out the payloads to establish links


// not all tasks will have a dependent task


// we can filter these out afterwards


payloads = tasks_to_update.map((r)=>{


    let p = r.getCellValue(task_project_link_field_name)[0].id


    let temp_task = r.getCellValue(task_to_template_link_field_name);


    let temp_task_dependency = task_temp_dependency_map[temp_task];


    //output.inspect(temp_task_dependency)


    let dependent_task_ids = temp_task_dependency.map(id => new_task_to_template_map[p].template_to_task[id]) ;


    output.inspect(dependent_task_ids)





    if(!temp_task_dependency) {


       return undefined;


    }





    return {


        id: r.id,


        fields: {


            [task_dependency_field_name]: dependent_task_ids.map(id => ({ id }))


        }


    }


}).filter((r)=>{


    return r !== undefined;


});


output.inspect(payloads);





output.markdown(`Updating **${payloads.length}** tasks with required dependencies`);


while(payloads.length > 0) {


    await task_table.updateRecordsAsync(payloads.slice(0,50));


    payloads = payloads.slice(50);


}


output.markdown(`### Done`);

Even just pasting your code into a fresh script block gives me a red error indicator within the script block at the same spot. it is regarding the payloads.slice(0,50) on line 186, same as my script.


ive got dependencies mapped on template table, and am trying this on a simple base structure and it still errors out.



Hi @matt_stewart1 the code works on my end. You have to make sure the dependencies are mapped on your template table as well. It looks like you may have a problem with the structure of your tables.


I have my version of the code here:



// when a new asset is created, 


// there are a certain number of tasks that must be completed


// these tasks are dependent on the type of asset


// Given a list of project and task templates,


// when a new asset is created and has a project template assigned


// we can then create corresponding set of tasks for that asset





// define some of our initial variables


// these are the basic table, field and view names to define to create the script


const project_table_name = 'Deals';


const new_project_view_name = 'Create Tasks';


const project_template_link_field_name = 'HS Deal Link';





const task_table_name = 'Prelim Tasks';


const task_project_link_field_name = 'Deal';


const task_dependency_field_name = 'Dependencies';


const task_primary_field_name = 'Prelim Task';


const task_start_field_name = 'Start Date';


const task_end_field_name = 'End Date';


const task_status_field_name = 'Status';





const task_to_template_link_field_name = '_task_template_id';   // this is used to assist with creating the links between tasks after they are created.  We don't need to maintain a true linked record relationship, but temporarily storing the template's ID is helpful





const project_template_table_name = 'HS Deal Types';





const task_template_table_name = 'Prelim Task Templates';


const proj_temp_task_temp_link_field_name = 'Prelim Task Templates';


const task_temp_dependency_field_name = 'Dependencies';


const task_temp_primary_field_name = 'Name';


const task_temp_duration_field_name = 'Duration (Days)';


const task_temp_status_field_name = 'Status';





/********************************************************/


output.markdown('# Creating tasks for new assets and assigning dependencies');





// create our table objects


const project_table = base.getTable(project_table_name);


const task_table = base.getTable(task_table_name);


const project_temp_table = base.getTable(project_template_table_name);


const task_temp_table = base.getTable(task_template_table_name);





// get all new projects that have been assigned a template


// but do not yet have tasks


const new_project_view = project_table.getView(new_project_view_name);


const new_project_results = await new_project_view.selectRecordsAsync();





// pull all of our project templates and all task templates


const project_temp_results = await project_temp_table.selectRecordsAsync()


const task_temp_results = await task_temp_table.selectRecordsAsync();





// build a map of projects to tasks


output.markdown('### Setting up');


output.markdown('Building map of project templates and task templates');


var project_task_temp_map = {};


for (let r of project_temp_results.records) {


    let temp_tasks = r.getCellValue(proj_temp_task_temp_link_field_name);





    project_task_temp_map[r.id] = temp_tasks.map((t)=>{


        return t.id;


    }) 


}


output.inspect(project_task_temp_map);





// also build a map of task template to task template so we can resolve dependencies


output.markdown('Creating map of templated tasks to dependent tasks');


var task_temp_dependency_map = {};


for (let r of task_temp_results.records){


    let next_task = r.getCellValue(task_temp_dependency_field_name);


    if(next_task !== null) {


        task_temp_dependency_map[r.id] = next_task.map(({id}) => id);


    } else {


        task_temp_dependency_map[r.id] = [];


    }


}


output.inspect(task_temp_dependency_map);





// for each new project


// get the set of tasks and create the creation payloads


// we will need to do a second pass of all of these records to then _update_


// the tasks with the corresponding dependencies


//


// THIS IS THE PART OF THE SCRIPT WHERE YOU ASSIGN WHAT DATA YOU WANT IN YOUR NEWLY CREATED TASKS


//





output.markdown('### Creating new tasks')


output.markdown(`Found **${new_project_results.records.length}** projects which need task assignment`)


var payloads = [];


for(let r of new_project_results.records){


    // there should only ever be one project template linked


    // so just take the first one


    let p_id = r.getCellValue(project_template_link_field_name)[0].id;





    let task_temp_ids = project_task_temp_map[p_id];


    let task_temps = task_temp_ids.map((i)=>{


        return task_temp_results.getRecord(i);


    });


    for(let t of task_temps) {





        payloads.push({


            fields: {


                [task_project_link_field_name]: [{id: r.id}],


                [task_primary_field_name]: t.getCellValueAsString(task_temp_primary_field_name),


                [task_to_template_link_field_name]: t.id,


                [task_start_field_name]: typeof t.getCellValue(task_temp_duration_field_name) === "number" ? new Date() : "",


                [task_end_field_name]: new Date(new Date().setDate(new Date().getDate() - 1 + t.getCellValue(task_temp_duration_field_name))),


                [task_status_field_name]: {name: t.getCellValueAsString(task_temp_status_field_name)}


            }


        });


    }


}





// create all of the new tasks


// we should hold on to the created IDs


output.markdown(`Creating **${payloads.length}** tasks across these projects`);


var new_tasks = [];


while(payloads.length > 0){


    let n = await task_table.createRecordsAsync(payloads.slice(0,50));


    new_tasks = [...new_tasks, ...n];


    payloads = payloads.slice(50);


}





output.markdown('### Creating dependencies between new tasks');


output.markdown('Pulling newly created tasks')


// refetch these tasks so that we can update the dependencies


const task_results = await task_table.selectRecordsAsync({


    fields: [


        task_primary_field_name, 


        task_project_link_field_name, 


        task_to_template_link_field_name, 


        task_dependency_field_name


    ]


});





// pull out only the newly created tasks


const tasks_to_update = new_tasks.map((t)=>{


    return task_results.getRecord(t);


});





output.markdown('Creating map of new tasks to templated task ids to resolve dependencies')


// create a map of new task to templated task ids


// and templated tasks to new task ids


// group them by project since any given project may be using the same set of templated tasks


var new_task_to_template_map = {};


for(var r of tasks_to_update){


    let p = r.getCellValue(task_project_link_field_name)[0].id;


    let temp_t = r.getCellValue(task_to_template_link_field_name);


    if(new_task_to_template_map[p] === undefined){


        new_task_to_template_map[p] = {


            task_to_template: {},


            template_to_task: {}


        };


    }


    new_task_to_template_map[p].task_to_template[r.id] = temp_t;


    new_task_to_template_map[p].template_to_task[temp_t] = r.id;


}


output.inspect(new_task_to_template_map);





// now go back through the tasks one more time and actually build out the payloads to establish links


// not all tasks will have a dependent task


// we can filter these out afterwards


payloads = tasks_to_update.map((r)=>{


    let p = r.getCellValue(task_project_link_field_name)[0].id


    let temp_task = r.getCellValue(task_to_template_link_field_name);


    let temp_task_dependency = task_temp_dependency_map[temp_task];


    //output.inspect(temp_task_dependency)


    let dependent_task_ids = temp_task_dependency.map(id => new_task_to_template_map[p].template_to_task[id]) ;


    output.inspect(dependent_task_ids)





    if(!temp_task_dependency) {


       return undefined;


    }





    return {


        id: r.id,


        fields: {


            [task_dependency_field_name]: dependent_task_ids.map(id => ({ id }))


        }


    }


}).filter((r)=>{


    return r !== undefined;


});


output.inspect(payloads);





output.markdown(`Updating **${payloads.length}** tasks with required dependencies`);


while(payloads.length > 0) {


    await task_table.updateRecordsAsync(payloads.slice(0,50));


    payloads = payloads.slice(50);


}


output.markdown(`### Done`);

Hey @Sean_Wilson … never mind, I got it working! looks like there was a typo or something on my end.



Does anyone know how to check if tasks exist, and if they do just update them? For example, if we changed the templates to use different dependencies or added a new task to a project template, would like it to slip in these fixes to projects in motion already



im also having trouble passing a multi-select field from the task template table to the task table.

Hey @Sean_Wilson … never mind, I got it working! looks like there was a typo or something on my end.



Does anyone know how to check if tasks exist, and if they do just update them? For example, if we changed the templates to use different dependencies or added a new task to a project template, would like it to slip in these fixes to projects in motion already



im also having trouble passing a multi-select field from the task template table to the task table.


I believe we had a different topic to deal with multi select. I think it is up thread. Glad to hear it is working. The payload slice thing has to do with the fact you are not passing arrays - or objects? I forget what it is but Airtable only accepts either arrays or objects so if you pass in an object it has to be wrapped in an array OR if passing in an array it has to be wrapped in an object. I forget which of the two off the top of my head.

Hey @Sean_Wilson … never mind, I got it working! looks like there was a typo or something on my end.



Does anyone know how to check if tasks exist, and if they do just update them? For example, if we changed the templates to use different dependencies or added a new task to a project template, would like it to slip in these fixes to projects in motion already



im also having trouble passing a multi-select field from the task template table to the task table.




This script does not have that functionality as you can see by the source code. You could write another script to take care of that.

This script is wonderful, thank you for sharing @Giovanni_Briggs! I’ve got it functioning properly in my base.


However, I’d like to add the ability to calculate due dates for tasks based on certain criteria. My projects table has 5 different date fields and my tasks all correspond to one of the 5 fields.



Is there a way to map a single select field in the task template table with the corresponding dates the the record in the project? And then use a number field for the task in the task template table to calculate the due date for the task and output it into the task table?



It would be so much easier if I could access information from tables across the base and still use formulas as well!


Essentially for the added record, it would be IF(task type single select in tasks template table === A) use reference date A from projects table


then calculate due date using WORKDAY((date A from projects table), (value of number field in tasks template table), days)



Is there a way to do this or does anyone know of a script block that I can reference to add this in?

Hi guys,



I am trying to run this script using button. Which part of the script that I should modify?

Hello


I got this to work but I’m also looking for a way to pass a number over from the template to the tasks, so I can set start dates and end dates based on how long a task is going to take.


I have number fields in both bases and it showed up an error



j: Can’t create records: invalid cell value for field ‘Time’.


Cell value has invalid format: must be a number


at main on line 101



How can I arrange the code below to copy over a number?


>task_time_field_name]: {name: t.getCellValueAsString(task_temp_time_field_name)}



I’m assuming I need to change the ‘get Cell Value as String’ but I don’t know what goes there…


Thanks!

@Giovanni_Briggs This script is exactly what I need, thank you! There is one problem I’m running into that I’m wondering if you have any thoughts on.



The tasks that are linked to my new project are not in the order that they were in, in the template. Any thoughts on how to fix that?

@Giovanni_Briggs This script is exactly what I need, thank you! There is one problem I’m running into that I’m wondering if you have any thoughts on.



The tasks that are linked to my new project are not in the order that they were in, in the template. Any thoughts on how to fix that?


Hi Steve, did you figure out this issue? I'm having the same challenge, my tasks seem to be scrambled and it would save me time if the order could be followed through from the template. 

Reply


Terms & Conditions