Hi Airtable Community!
I am creating a project management system, complete with Gantt charts and project templates. In summary, here is what this script is doing:
- Allow user to select the campaign (webinar, whitepaper, etc)
- Filter the template table by the selected campaign, which reveals matching tasks to be done.
- Duplicate the subtasks matching that campaign into the live task tracking table
I have 90% of the script working, but I am stuck on TWO things:
- Setting the date offset from “Launch Date” rather than “today’s date” (see comments)
- Creating a record with multiple record links (linked from the same table as dependencies)
creating a record from a template where that record template has multiple linked records. It might help to know that I do not have a strong background in javascript, but I am familiar with other languages. Here is the code below that was made possible by this webinar (thank you!):
///function to add days using Date Offset in Templates from "Launch Date" in Campaigns >> to >> Due Date in Subtasks, but only works on today's date currently (issue #1)
Date.prototype.addDays = function(days) {
var date = new Date(this.valueOf());
date.setDate(date.getDate() + days);
return date;
}
var dateObj = new Date();
//Allow user to select campaign to generate subtasks
let campaigns = base.getTable("Campaigns");
let campaignsView = campaigns.getView("Campaigns With No Subtasks");
let campaign = await input.recordAsync("Select Campaign to Create Subtasks", campaignsView)
//DEBUG: Verify correct campaign is in output
//console.log(campaign);
//Template tasks table selection
let subtasksTable = base.getTable("
Templates");
let allSubtaskItems = await subtasksTable.selectRecordsAsync();
//console.log(allSubtaskItems);
let subtaskItems = allSubtaskItems.records.filter(subtaskItem => {
//console.log(templateItem.getCellValue("Campaign Type"))
//console.log(campaign.getCellValue("Campaign Type"))
return subtaskItem.getCellValueAsString("Campaign Type") === campaign.getCellValueAsString("Campaign Type")
})
//DEBUG: debug output the dependencies field
let deps = subtaskItems.map(r => {
return {
fields: {
"Dependencies": r.getCellValue("Dependencies")
}
}
});
console.log(depsn0])
console.log(>campaign])
//use map to specify which key-value pairs to be written with createRecordsAsync below
let liveSubtaskItems = subtaskItems.map(subtaskItem => {
return {
fields: {
"Subtask Name": subtaskItem.getCellValue("Subtask Name"),
"Status": {
name: subtaskItem.getCellValue("Status").name
},
"Due Date": dateObj.addDays(subtaskItem.getCellValue("Date Offset")),
//"Due Date": campaign.getCellValue("Launch Date"), //ISSUE 1: Set date offset from this date value rather than "today's Date" which is set in the code at the top of this file
"Subtask Owner": {
id: subtaskItem.getCellValue("Subtask Owner").id
},
"Campaigns": campaign],
//"Dependencies": subtaskItem.getCellValue("Dependencies"), //ISSUE 2: Depenedencies, multiple record links
"LOE (Est. Hrs.)": subtaskItem.getCellValue("LOE (Est. Hrs.)")
},
}
});
//create records in the 'live' subtasks table
let liveTable = base.getTable("Subtasks");
await liveTable.createRecordsAsync(liveSubtaskItems);
console.log(liveSubtaskItems)
output.markdown('# Done
')
Screenshots to help add context: