Skip to main content

Hi, 

I am trying to add on more function to the existing auto set date script I found on Airtable extension (please see below). 

I am hoping to incorporate into the code below that if there is a task that has been marked it should start 20 days before the entered project start date, and another task that is marked 30 days after project start date but it will not have a specific dependant controlling it. Is this possible?

Best regards,

Eve

//PART 1: Script Settings

const config = input.config({
    title: 'Gantt: automatically set start/end dates from dependencies',
    description: `A script that automatically adds start and end dates to a table of tasks. Use in conjuntion with a Gantt View.
 
Please select the table and fields that correspond with the following:`,
    items: [
        input.config.table('table', {
            label: 'Table',

        }),
        input.config.field('task', {
            label: 'Task Name',
            description: 'String',
            parentTable: 'table',
        }),
        input.config.field('duration',{
            label: 'Duration',
            description: `Integer - Contains a tasks's duration in number of weeks.`,
            parentTable: 'table',
        }),
        input.config.field('antecedent',{
            label: 'Preceding Tasks',
            description: 'Linked Record - Contains the task(s) that must be complete for a given task to begin.',
            parentTable: 'table',
        }),
        input.config.field('startDate',{
            label: '📝 Start Date',
            description: 'Date - Will be automatically set and overwritten by script!',
            parentTable: 'table',
        }),
        input.config.field('endDate',{
            label: '📝 Deadline',
            description: 'Date - Will be automatically set and overwritten by script!',
            parentTable: 'table',
        }),
    ]
});

let tableStr = config.table;
let taskField = config.task;
let startField = config.startDate;
let endField = config.endDate;
let durationField = config.duration;
let antecedentField = config.antecedent;

let table = base.getTable(tableStr.name);


//PART 2: The functions

//This function converts a string to a date object.
function convertToDate(str){

    let array = str.split('/');
    let month = Number(array[0])-1;
    let day = Number(array[1]);
    let year = Number(array[2]);

    let newDate = new Date(year, month, day);

    return newDate;

}

//Bumps a day forward to avoid weekends.
function bumpForwardIfWeekend(weekday, day){

    if(weekday === 0){
        day = day + 1;
    }
    if(weekday === 6){
        day = day + 2;
    }

    return day;
}

//Bumps a date forward one day plus more if it is a weekend day.
function bumpForward(date){

        let day = date.getDate();
        let weekday = date.getDay();
        let month = date.getMonth();
        let year = date.getFullYear();

        day = day + 1;
        weekday = weekday + 1;

        day = bumpForwardIfWeekend(weekday, day);

        let newDate = new Date(year, month, day);

        return newDate;
}

//This function will generate an array containing the start date, end date, and all intermediary dates for
// a given task. This will be used to check for holidays.
function getDateStrArray(date, duration){

    let endDate = getEndDate(date, duration, 0);

    let day = date.getDate();
    let month = date.getMonth();
    let year = date.getFullYear();

    let newDate = date;
    let dateStrArray = [];
    let dateStr = String(date);
    dateStrArray.push(dateStr);

    while(newDate < endDate){
        day = day + 1;
        newDate = new Date(year, month, day);
        let newDateStr = String(newDate);
        dateStrArray.push(newDateStr);
    }

    return dateStrArray;
}

//This function takes an array of holiday dates and counts how many of them show up on a second array,
//in this case our array of dates connected to a given task.
function countHolidaysinRange(holidays, dateArray){

    let numHolidays = 0;

    for(let i = 0; i<holidays.length; i++){
        let match = dateArray.includes(holidays[i]);
        if(match){
            numHolidays = numHolidays + 1;
        }
    }
    return numHolidays;
}
//Given a start date, returns a task's end date based on the duration.
function getEndDate(date, duration, numHolidays){
 
    let endDay = date.getDate();
    let endMonth = date.getMonth();
    let endYear = date.getFullYear();
 
    if(!numHolidays){
        let numHolidays = 0;
    }
 
    endDay = endDay + duration + numHolidays;
 
    let endDate = new Date(endYear, endMonth, endDay);
    let weekday = endDate.getDay();
 
    endDay = bumpForwardIfWeekend(weekday, endDay);
    endDate = new Date(endYear, endMonth, endDay);
 
    return endDate;
}

//Compares two dates and returns the later one.
function returnLaterDate(date1, date2){

    if (date1 > date2) {
        return date1;
    } else if (date2 > date1) {
        return date2;
    }
}

//This function finds the child tasks for a given parent task and completes them with the appropriate start/end dates.
async function filterAndFill(parentName, parentEndDate){
    let query = await table.selectRecordsAsync({sorts: [{field: antecedentField.id}]});
    let children = query.records.filter(record => record.getCellValueAsString(antecedentField.name) === parentName);
    if (children.length > 0){

        for(let i = 0; i < children.length; i++){

            let child = children[i];
            let duration = child.getCellValue(durationField.name);
            let childtask = child.getCellValueAsString(taskField.name);

            let newStart = bumpForward(parentEndDate);
            let dateArray = getDateStrArray(newStart, duration);
            let numHolidays = countHolidaysinRange(holidays, dateArray);

            let newEndDate = getEndDate(newStart, duration, numHolidays);

            await table.updateRecordAsync(child, {[startField.id]: newStart, [endField.id]: newEndDate});
            await filterAndFill(childtask, newEndDate);
        }
    }

    let childrenWithMore = query.records.filter(record => (record.getCellValue(antecedentField)?.length ?? 0) > 1);

    if (childrenWithMore.length > 0){
        await filterAndFillMulti(childrenWithMore, parentName, parentEndDate);
    }
}

//If a given task has multiple antecedents, this function will add the appropriate start/end dates.
async function filterAndFillMulti(childrenWithMore, parentName, parentEndDate){

    for(let i = 0; i < childrenWithMore.length; i++){

            let child = childrenWithMore[i];

            let duration = child.getCellValue(durationField.name);
            let childtask = child.getCellValueAsString(taskField.name);
            let antecedent = child.getCellValueAsString(antecedentField.name);

            if (antecedent.includes(parentName)) {

                let iteration = completeMulti.filter(element => element.childtask === childtask);

                if (iteration.length === 0) {

                    let newStart = bumpForward(parentEndDate);
                    let dateArray = getDateStrArray(newStart, duration);
                    let numHolidays = countHolidaysinRange(holidays, dateArray);

                    let newEndDate = getEndDate(newStart, duration, numHolidays);

                    await table.updateRecordAsync(child, {[startField.id]: newStart, [endField.id]: newEndDate});

                    let obj = {childtask, newStart};
                    completeMulti.push(obj);

                    await filterAndFill(childtask, newEndDate);
                } else {

                    let currentStart = iteration[0].newStart;
                    let newStart = returnLaterDate(currentStart, parentEndDate);

                    if(newStart === currentStart){

                    }else{

                        let newStart = bumpForward(parentEndDate);
                        let dateArray = getDateStrArray(newStart, duration);
                        let numHolidays = countHolidaysinRange(holidays, dateArray);

                        let newEndDate = getEndDate(newStart, duration, numHolidays);

                        await table.updateRecordAsync(child, {[startField.id]: newStart, [endField.id]: newEndDate});
                        await filterAndFill(childtask, newEndDate);

                        let index = completeMulti.findIndex(element => element.childtask === childtask);
                        let obj = {childtask, newStart};

                        completeMulti.splice(index,1,obj);

                    }

                }
            }
        }
}

//PART 3: Main Program

//This array will store any record with multiple antecedents after the initial pass over.
let completeMulti = [];

//Get the launch date for the project.
let userDateStr = await input.textAsync("Enter project launch date (mm/dd/yyyy):");
let launchDate = convertToDate(userDateStr);

let holidayStr;
let holidays = [];

//Get any holidays.

while (holidayStr != "done") {

    holidayStr = await input.textAsync("Enter a holiday date that should extend deadlines (mm/dd/yyyy) or Enter 'done' to exit:");
    let holidayDate = convertToDate(holidayStr);
    let holiday = String(holidayDate);

    if (holidayStr != "done") {
        holidays.push(holiday);
    }
}

output.markdown("Thank you! Your sequence is being generated....");
let query = await table.selectRecordsAsync({sorts: [{field: antecedentField.id}]});
for (let record of query.records) {

    let antecedentsNum = record.getCellValue(antecedentField.name)?.length ?? 0;
    let task = record.getCellValueAsString(taskField.name);
    let duration = record.getCellValue(durationField.name);

    if (antecedentsNum === 0) {

        let dateArray = getDateStrArray(launchDate, duration);

        let numHolidays = countHolidaysinRange(holidays, dateArray);

        let endDate = getEndDate(launchDate, duration, numHolidays);

        await table.updateRecordAsync(record,{[startField.id]: launchDate, [endField.id]: endDate});
        await filterAndFill(task, endDate);

    }
}

output.text("Sequence complete. Good bye!");

 

Yikes. Calculating dates is possible with scripting, but it requires a certain level of familiarity with JavaScript and Airtable. I haven’t read the script you posted because it is kinda long and reading code on my phone is hard.

But a couple things come to mind. 

The script you posted runs on button press, not automatically. Do you want the script to run as an automation or when a user presses a button?

Are the tasks linked to the project? Is there a single select determining the offset from the project start date? You could have a rollup or lookup field of the project start date, and a formula field that calculates task start date from the project start date and the task offset. If you need this date in an editable field, use an automation to copy it over.

How do these two fixed dates interact with dates for other tasks?

 

Yikes. Calculating dates is possible with scripting, but it requires a certain level of familiarity with JavaScript and Airtable. I haven’t read the script you posted because it is kinda long and reading code on my phone is hard.

But a couple things come to mind. 

The script you posted runs on button press, not automatically. Do you want the script to run as an automation or when a user presses a button?

Are the tasks linked to the project? Is there a single select determining the offset from the project start date? You could have a rollup or lookup field of the project start date, and a formula field that calculates task start date from the project start date and the task offset. If you need this date in an editable field, use an automation to copy it over.

How do these two fixed dates interact with dates for other tasks?


Hi @kuovonne 

Yes it is long! 😂

I would like the script to run on button press. 

Currently trying to set up as a base where we have got template task linked to a template project. The coding that I am trying to customise is for the "Task" table where each task will have an auto set start date input into the extension and its end date calculated by adding the duration to start date. And subsequent task that has a dependency, will have their start date right after the end date of its predecessor. But there are a few task (for example the screenshot below), I want it to start 30 days before the "launch date" entered if that makes sense?

 

 

Reply


Terms & Conditions