- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Mar 22, 2020 04:54 AM
Hey Y’all,
So, I’d like to incorporate PTO into project planning - here’s how I’m thinking about it, but would love more ideas on how to make this most actionable.
I have a table for projects that has a Start Date and an estimated duration for how long the project should take. To incorporate PTO, I can use the Workday function, which allows me to include a list of holidays formatted as a comma-separated string of ISO-formatted dates.
To use the Workday function, I’ll need a list of every day that someone is off for PTO. In this example, I’m tracking PTO in a table with fields for Start and End dates. This is a great start, but I need every date in between - a perfect job for a script.
The below script calculates each day a person is on PTO. The “Days Off” then roll up to the person, which is then rolled up to the Projects table when someone is assigned to a project and passed through the Workday function.
Now we can calculate the End Date of a project based on the Start Date, Duration, and PTO for the assigned DRI. We can also create Alerts for when someone is assigned to start a project during their PTO.
Script below, and example base here. How would you use this? What would make this more usable?
let pto = base.getTable('PTO');
let ptoQuery = await pto.selectRecordsAsync();
let ptoRecords = ptoQuery.records;
// The Array to store all record updates
let ptoBackend = []
// For each PTO Record, calculate the PTO dates between the start and end dates, and add them to an array.
ptoRecords.forEach(function(c){
let d = []
for (let i = 0; i < c.getCellValue('Duration'); i++){
let startDate = new Date(c.getCellValue('Date Start'));
let newDate = new Date(startDate.setDate(startDate.getDate() + i));
d.push(newDate.toISOString().substring(0,10));
}
ptoBackend.push([c,d])
})
// Format the array for the UpdateRecordsAsync function
let updateRecords = ptoBackend.map( c => ({
id: c[0].id,
fields:{
'Days Off': c[1].join(),
},
})
);
// Update Records
await pto.updateRecordsAsync(updateRecords);
output.markdown('# Done! 🚀')