Skip to main content

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:

  1. Allow user to select the campaign (webinar, whitepaper, etc)
  2. Filter the template table by the selected campaign, which reveals matching tasks to be done.
  3. 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:

  1. Setting the date offset from “Launch Date” rather than “today’s date” (see comments)
  2. 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(deps[0])
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:

As you can see from the screenshot that shows the record ID, the record “does not exist” because its copying the record over and surely, the unique record ID from the template does not match the ‘newly’ created record in the ‘live table’, so the error makes sense logically, but I don’t know how to fix it! Thanks for any help with these issues!

As you can see from the screenshot that shows the record ID, the record “does not exist” because its copying the record over and surely, the unique record ID from the template does not match the ‘newly’ created record in the ‘live table’, so the error makes sense logically, but I don’t know how to fix it! Thanks for any help with these issues!


Hi,

in your code deps is useless. it connect linkfield values to static word ‘Dependencies’. to be usable, it should connect for example, record name to it’s linked value names:

let deps = subtaskItems.map(r =>({'name':r.name, 'links':r.getCellValue("Dependencies").map(el=>el.name)}))

value of linkfield is array of {'name':'xyz', 'id':'rec..'}.
we don’t need template ids, so I took only array of names

next, lets save IDs of new records and query them, so edit this line in the end of your script

let newIdList=await liveTable.createRecordsAsync(liveSubtaskItems);
let newQuery=await liveTable.selectRecordsAsync({recordIds:newIdList})
let idmap=new Map(newQuery.records.map(r=>[r.name,r.id]))

//using Map (get id by name) to avoid “okay, i need to connect with record named X. which ID it has?”

then most tricky part - describe how element of deps transformed into element of update. we cannot set self-table links during creation of new records, because their ID known only after creation. so we need to create new records and then update dependencies:

let setLinks=({'name':x, 'links':arr})=>({'id': idmap.get(x), 'fields':{'Dependencies':arr.map(el=>({'id':idmap.get(el)}))}})

other part is simple:

let updates=deps.map(setLinks)
while (updates.length) await liveTable.updateRecordsAsync(updates.splice(0,50))

sorry I didn’t test but I hope it should work
regarding first issue, I’m still learning JS and had not much work with dates

Hi,

in your code deps is useless. it connect linkfield values to static word ‘Dependencies’. to be usable, it should connect for example, record name to it’s linked value names:

let deps = subtaskItems.map(r =>({'name':r.name, 'links':r.getCellValue("Dependencies").map(el=>el.name)}))

value of linkfield is array of {'name':'xyz', 'id':'rec..'}.
we don’t need template ids, so I took only array of names

next, lets save IDs of new records and query them, so edit this line in the end of your script

let newIdList=await liveTable.createRecordsAsync(liveSubtaskItems);
let newQuery=await liveTable.selectRecordsAsync({recordIds:newIdList})
let idmap=new Map(newQuery.records.map(r=>[r.name,r.id]))

//using Map (get id by name) to avoid “okay, i need to connect with record named X. which ID it has?”

then most tricky part - describe how element of deps transformed into element of update. we cannot set self-table links during creation of new records, because their ID known only after creation. so we need to create new records and then update dependencies:

let setLinks=({'name':x, 'links':arr})=>({'id': idmap.get(x), 'fields':{'Dependencies':arr.map(el=>({'id':idmap.get(el)}))}})

other part is simple:

let updates=deps.map(setLinks)
while (updates.length) await liveTable.updateRecordsAsync(updates.splice(0,50))

sorry I didn’t test but I hope it should work
regarding first issue, I’m still learning JS and had not much work with dates


Thank you @Alexey_Gusev ! I will try then changes you suggested and mark the solution if it works!

Thank you @Alexey_Gusev ! I will try then changes you suggested and mark the solution if it works!


np, feel free to ask if any error or you didn’t catch something in code, and have questions.
btw, => { return means you can omit word return and type expression right after =>

except that when you return Object, it should be enclosed in round brackets:
=>({ }), so it ‘knows’ - that’s object, not a beginning of statements block
I was confused for a long time until somebody told me that.

Terms & ConditionsAccessibility statement