Script to calculate due date

Welcome to the Airtable community!

What you are trying to accomplish is very difficult for someone new to both Airtable and coding.

If your primary goal is to get a working script that does what you want, especially if you have a deadline, you should consider hiring someone to write the script.

If your primary goal is to learn Airtable and scripting, and you have lots of time, you should start with smaller projects and gradually work your way up to this project.

You say you’ve modified a pre-existing script. How much of the script do you understand? If there are parts you do not understand, that is going to get in your way of adapting the script. It may be that a totally different approach would be appropriate giving your desired end goal. (I haven’t examined the script, and I am having trouble picturing how you want to calculate dates, but back when I was an Airtable consultant, I saw many situations where people were trying to adapt existing scripts that needed so much changing that they were better off starting from scratch.

I appreciate the response. The script I’ve modified does exactly what I need it to do as far as creating the new tasks. The only thing I’m trying to add is an additional block to calculate the due date for each task that gets added to my task table.

How far have you gotten in writing this portion of code? What specific step are you having trouble with? What error messages are you getting?

I’ve tried a million different ways to make this work and I’m still getting nowhere. Here’s what I have, but I’m sure I probably screwed it up in more than one place!

// 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 = 'Transactions';
const new_project_view_name = 'New Projects';
const project_template_link_field_name = 'Action Plan Template';

const task_table_name = 'To-Do';
const task_project_link_field_name = 'Property';
const task_due_date_field_name = 'Due';
const task_primary_field_name = 'Task Name';
const daysFrom = 'Days From Dependency Date';

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 = 'Action Plan Template';
const task_template_table_name = 'Tasks';
const proj_temp_task_temp_link_field_name = 'Tasks';
const task_temp_primary_field_name = 'Task';


/********************************************************/
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);
const task_temp_dependency_field_name = task_temp_table.getField('Due Date Dependency');
const depName = task_temp_dependency_field_name.options.choices.map(x => x.name)
const contractDate = project_table.getField('Contract date');
const closingDate = project_table.getField('Closing Date');
const emDue = project_table.getField('EM Due');
const dueDiligence = project_table.getField('Due Diligence Deadline');
const cl100 = project_table.getField('CL-100 Deadline');
const inspectionDate = project_table.getField('Home Inspection Date');


// 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({fields:project_table.fields});

// pull all of our project templates and all task templates
const project_temp_results = await project_temp_table.selectRecordsAsync({fields:project_temp_table.fields})
const task_temp_results = await task_temp_table.selectRecordsAsync({fields:task_temp_table.fields});

console.log(task_temp_results)

// 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);
    temp_tasks = temp_tasks === null ? [] : temp_tasks;

    project_task_temp_map[r.id] = temp_tasks.map((t)=>{
        return t.id;
    }) 
}
output.inspect(project_task_temp_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('Action Plan Template')[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
            },
        });
      }

    let depType = [...new Set(task_temp_results.records.map(taskType => taskType.getCellValue('Due Date Dependency')))]
    let relDate = null
    for(let d of new_project_results.records){
    depType.forEach((taskType) => {
        task_temp_results.records.map(task => {
                if(taskType.name === 'CL-100 Deadline'){
                    relDate = d.getCellValue('CL-100 Deadline')}
                else if(taskType.name === 'Closing Date'){
                    relDate = d.getCellValue('Closing Date')}
                else if(taskType.name === 'Due Diligence Deadline'){
                    relDate = d.getCellValue('Due Diligence Deadline')}
                else if(taskType.name === 'EM Due'){
                        relDate = d.getCellValue('EM Due')} 
                else if(taskType.name === 'Home Inspection Date'){
                        relDate = d.getCellValue('Home Inspection Date')}
    })

      
    

// 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, 
        daysFrom,
        task_temp_dependency_field_name,
        relDate
    ]
});


// 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 = depName[temp_task];
    let dependent_task_id = new_task_to_template_map[p].template_to_task[temp_task_dependency];
    let dueDate = new Date()
        var year = relDate.getFullYear();
        var month = relDate.getMonth();
        var day = relDate.getDate();
        var daysFrom = r.getCellValue('Days From Dependency Date')
        dueDate = new Date(year, month, day + daysFrom)
   

    if(dependent_task_id === undefined) {
       return undefined;
    }

    return {
        id: r.id,
        fields: {
            [task_due_date_field_name]: dueDate
            
        }
    }
}).filter((r)=>{
    return r !== undefined;
});
output.inspect(payloads);

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

while(payloads.length > 0) {
    await task_table.updateRecordsAsync(payloads.slice(0,50));
    payloads = payloads.slice(50);
}
output.markdown(`### Done`);

The error I’m getting is

SyntaxError: await is only valid in async functions and the top level bodies of modules
    on line 1
    at l on line 1
    at Generator._invoke on line 1
    at Generator.next on line 1
    at r on line 1
    at s on line 1
    on line 1
    on line 1

But the only thing underlined is (payloads.slice(0,50)) at the very end.

// 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 = 'Transactions';
const new_project_view_name = 'New Projects';
const project_template_link_field_name = 'Action Plan Template';

const task_table_name = 'To-Do';
const task_project_link_field_name = 'Property';
const task_due_date_field_name = 'Due';
const task_primary_field_name = 'Task Name';
const daysFrom = 'Days From Dependency Date';

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 = 'Action Plan Template';
const task_template_table_name = 'Tasks';
const proj_temp_task_temp_link_field_name = 'Tasks';
const task_temp_primary_field_name = 'Task';


/********************************************************/
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);
const task_temp_dependency_field_name = task_temp_table.getField('Due Date Dependency');
const depName = task_temp_dependency_field_name.options.choices.map(x => x.name)
const contractDate = project_table.getField('Contract date');
const closingDate = project_table.getField('Closing Date');
const emDue = project_table.getField('EM Due');
const dueDiligence = project_table.getField('Due Diligence Deadline');
const cl100 = project_table.getField('CL-100 Deadline');
const inspectionDate = project_table.getField('Home Inspection Date');


// 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({fields:project_table.fields});

// pull all of our project templates and all task templates
const project_temp_results = await project_temp_table.selectRecordsAsync({fields:project_temp_table.fields})
const task_temp_results = await task_temp_table.selectRecordsAsync({fields:task_temp_table.fields});

console.log(task_temp_results)

// 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);
    temp_tasks = temp_tasks === null ? [] : temp_tasks;

    project_task_temp_map[r.id] = temp_tasks.map((t)=>{
        return t.id;
    }) 
}
output.inspect(project_task_temp_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('Action Plan Template')[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
            },
        });
      }

    let depType = [...new Set(task_temp_results.records.map(taskType => taskType.getCellValue('Due Date Dependency')))]
    let relDate = null
    for(let d of new_project_results.records){
    depType.forEach((taskType) => {
        task_temp_results.records.map(task => {
                if(taskType.name === 'CL-100 Deadline'){
                    relDate = d.getCellValue('CL-100 Deadline')}
                else if(taskType.name === 'Closing Date'){
                    relDate = d.getCellValue('Closing Date')}
                else if(taskType.name === 'Due Diligence Deadline'){
                    relDate = d.getCellValue('Due Diligence Deadline')}
                else if(taskType.name === 'EM Due'){
                        relDate = d.getCellValue('EM Due')} 
                else if(taskType.name === 'Home Inspection Date'){
                        relDate = d.getCellValue('Home Inspection Date')}
    })

      
    

// 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, 
        daysFrom,
        task_temp_dependency_field_name,
        relDate
    ]
});


// 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 = depName[temp_task];
    let dependent_task_id = new_task_to_template_map[p].template_to_task[temp_task_dependency];
    let dueDate = new Date()
        var year = relDate.getFullYear();
        var month = relDate.getMonth();
        var day = relDate.getDate();
        var daysFrom = r.getCellValue('Days From Dependency Date')
        dueDate = new Date(year, month, day + daysFrom)
   

    if(dependent_task_id === undefined) {
       return undefined;
    }

    return {
        id: r.id,
        fields: {
            [task_due_date_field_name]: dueDate
            
        }
    }
}).filter((r)=>{
    return r !== undefined;
});
output.inspect(payloads);

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

while(payloads.length > 0) {
    await task_table.updateRecordsAsync(payloads.slice(0,50));
    payloads = payloads.slice(50);
}
output.markdown(`### Done`);

SyntaxError: await is only valid in async functions and the top level bodies of modules
    on line 1
    at l on line 1
    at Generator._invoke on line 1
    at Generator.next on line 1
    at r on line 1
    at s on line 1
    on line 1
    on line 1

You are not closing this code block where you should.

The last }) closes the line with task_temp_results.records.map(task => {.

You need another }) to close the line with depType.forEach((taskType) => {. The editor thinks that your forEach continues on to at least the next time you use await.

You should be able to identify this by looking at the bracket matching done by the code editor. When you put the cursor at an opening bracket, the editor highlights the matching closing bracket.

This is quite possible.

Your code was far too long for me to read to figure out what it does. Finding the syntax error was the easy compared to understanding and/or debugging the algorithm.

It also sounds like you don’t need the part of the code that establishes linked dependencies and need totally new code that calculates dates. This does not sound like a beginner coding project to me.