Help

Gantt chart script: Adjusting successor to predecessor for no-coder

Topic Labels: Scripting extentions
2680 4
cancel
Showing results for 
Search instead for 
Did you mean: 
Sebastian
5 - Automation Enthusiast
5 - Automation Enthusiast

Hey everyone,

I’ve found this script online (raw version below; https://medium.com/@greg.business.automated/regaining-control-of-your-airtable-gantt-charts-with-thi...)

Basically it helps update Gantt chart columns (start & end date, duration) given a starting task. However its centered around using successor tasks, which for me is a bit impractial as I’m working with a ‘blocked by’ or ‘predecessor’ scheme. I was wondering what I needed to change in the code for this to allow using predecessor tasks. I’ve been trying to play around with it for the last hour but no luck…

Any help highly appreciated!!

//Copyright © 2020 by Business Automated
//Update dates of tasks in Gantt chart in Airtable. To be used together with the Airtable Gantt and Scripting block.

// Select the To Do table and records
let table = base.getTable("Table 1");  // <== change here the Table name
let toDoRecords = await table.selectRecordsAsync();

let nameOfStartDate  = "Start Date"; // <== change here the to the column name with start date
let nameOfEndDate = "End Date";       // <== change here the to the column name with end date
let nameOfDuration = "Duration" ;      // <== change here the to the column name with duration 
let nameOfSuccessor = "Successor Task" ; // <== change here the to the column name with successor task



// Prompt the user to pick a record 
// If this script is run from a button field, this will use the button's record instead.
let record = await input.recordAsync('Select the start record', table);

//check for no empty record
if (record) {
    // Customize this section to handle the selected record
    // You can use record.getCellValue("Field name") to access
    // cell values from the record
    output.text(`You selected this record: ${record.name}`);
} else {
    output.text('No record was selected');
    return;
}

// pick up data from the starting record 
let startDate = record.getCellValue(nameOfStartDate);  
let endDate = new Date(startDate); 
endDate.setDate(endDate.getDate() - 1);

// exit on start date

if (startDate == null){
    output.text("No start Date - exiting the script");
    return;
}

//creat Hash table for faster updates vs updates in the database
let ganttHashTable = {};
/**
 * @param {DetailedToDoTable_Record} record
 */
function createGanttHashTable(record){
    ganttHashTable[record.id] = {
        "Date Start": record.getCellValue(nameOfStartDate),   
        "Date End": record.getCellValue(nameOfEndDate),       
        "Duration": record.getCellValue(nameOfDuration),     
        "Successors": record.getCellValue(nameOfSuccessor) 
    }
    if(ganttHashTable[record.id]["Successors"]){
        for (let successor of ganttHashTable[record.id]["Successors"]){
            let newRecord = toDoRecords.getRecord(successor.id);
            createGanttHashTable(newRecord);
            //console.log(record);
        }
    }    
}

createGanttHashTable(record);
//console.log(Object.keys(ganttHashTable).length);


//declare main update fuction which will be use recurisviely 
/**
 * @param {string | number | Date} endDate
 * @param {Record} record
 * @param {string} level
 */


async function updateDates (endDate, record, level){
    
    //refresh 
    let recordNew = toDoRecords.getRecord(record.id);
    
    level = level + "=";
    //convert end date to start
    
    let newStartDate = new Date();
    let newEndDate = new Date(endDate);
    let daysToAdd = ganttHashTable[recordNew.id]["Duration"];
    //console.log("Dats to add ===" + daysToAdd);

    for (let day = 0 ; day < daysToAdd; day++){
        newEndDate.setDate(newEndDate.getDate() + 1);

        if (newEndDate.getDay() == 0){
            newEndDate.setDate(newEndDate.getDate() + 1);
        } else if (newEndDate.getDay() == 6){
            newEndDate.setDate(newEndDate.getDate() + 2);
        }
        if (day==0){
            newStartDate = new Date(newEndDate);
        };
        
        
    }
 
    //check current date
    let currentStartDate = new Date(ganttHashTable[recordNew.id]["Date Start"]);
    let currentEndDate = new Date(ganttHashTable[recordNew.id]["Date End"]);



    // see if already existing date is not further away
    if (currentStartDate.getTime() > newStartDate.getTime()){
        
        newEndDate = new Date(currentEndDate);
    } else {
        
        ganttHashTable[recordNew.id] = {
            "Date Start": newStartDate,
            "Date End": newEndDate,
            "Duration": ganttHashTable[recordNew.id]["Duration"],
            "Successors": ganttHashTable[recordNew.id]["Successors"] 
        }
        //update records Async in Airtable

        

    };
    let successorRecords = ganttHashTable[recordNew.id]["Successors"];
    //if successor Record exist iterate over the successors
    if (successorRecords){
        for (let successorRecord of successorRecords){
            //console.log(record);
            //adding fuctions and properties to the object            
            let newRecord = toDoRecords.getRecord(successorRecord.id);
            //console.log(record);
            //output.text(level+" Successor task: " + record.getCellValue("Name"));
            //pass current event end date and successor record
            await updateDates(newEndDate, newRecord, level);
        }
    }
        
};

//adjust start for the starting record to be similar in format(-1) to "end date" of predecessor

let level = ""; 
await updateDates(endDate,record, level);

let updateArray = []
for (let [key,value] of Object.entries(ganttHashTable)){
   updateArray.push({
       "id": key, 
        "fields": {
            [nameOfStartDate]: value["Date Start"],
            [nameOfEndDate] : value["Date End"] 
            }
   })

}

// batch update of exisitng records
let updateRecordsLenght = updateArray.length
while (updateArray.length > 0) {
    await table.updateRecordsAsync(updateArray.slice(0, 50));
    updateArray = updateArray.slice(50);
    output.text(`Updated time entries for ${updateRecordsLenght-updateArray.length}/${updateRecordsLenght} task`);
}
4 Replies 4

Hi Sebastian, and welcome to the community!

Wow, a whole hour!?! There’s enough code here to keep a volunteer busy for a day trying to frame it for your slightly different use case. I am not that volunteer. :winking_face:

But, I will say that (a) the Airtable Gantt Chart app (while pretty good itself) is designed to handle the basics, and (b) there is also the Vega-Lite beta that you can try. It does require a little more technical know-how because Vega is a JSON grammar. But I urge you to take a look and perhaps use this resource to guide you. Don’t be discouraged because of this simple Gantt-like example -

image

Vega-Lite can do stuff like this…

image

Like most vendor-provided charting features, they tend to be simple and lack extensible features. In my view, Airtable should set aside all trying to be data visualization experts and instead become experts at making any form of visualization easily possible using powerful charting platforms like Vega.

Thanks Bill! Will definitely check those out. And sorry, didn’t know it would take that long, haha. My main concern was how I could easily push the whole schedule back x days, in case one predecessing task changes. Right now I have to change everything manually. Any ideas on an easy workaround?

It’s a universal law - everything about code written by another engineer takes far longer than anticipated. And it will seem much longer than it actually is when you don’t get paid for it. :winking_face:

Hi Sebastian, I just put the tasks into a grouping by linking them to an overarching project. That project then could be grouped in the Gantt view where all the tasks collapse down into it if you choose to. Then you can move the project along the Gantt chart and everything else moves with it