Generating Task from Task Template

[Randi_Travers]

I’m new to Airtable scripting but I have figured out quite a bit already. Using many different sources I have written a script that copies my task template into my task table bases on picking the name of the template you want to use. Now I’ve been asked to add some additional fields with different types and my script does not work anymore.

Let’s start with the first field type with an issue: Multi-select
Current error:
P: Can’t create records: invalid cell value for field ‘Test Multi-Select Copy’.
Could not find a choice with that ID or name
at main on line 54

To me this error means that the lists aren’t the same but I there are only three and I checked that yesterday

I’m a little confused on how to show everything that someone might need to assist me. I put a screen shot above of my task template and below is my script:

//declares and then gets the project from the select list or from the row the button is on

const projectsTable = base.getTable(“Projects”);

let record = await input.recordAsync(‘Current Record’, projectsTable)

if (!record) {

throw new Error("It looks like you haven't selected a Project record to create tasks for. Is your Projects table empty?")

}

else {

let projectNameString = record.getCellValueAsString("Project Name")

output.text('Project Name: '+projectNameString)

}

let buttonPressedString = record.getCellValueAsString(“Has Button Been Pressed”)

if (buttonPressedString ===‘YES’) {

throw new Error("Looks like you have already generated your tasks from your template and you can not do this twice.")

}

//makes sure that there is a template or it sends back an error

let taskTemplateName = record.getCellValueAsString(“Task Templates”)

if (!taskTemplateName) {

throw new Error("Your Task Template field is empty and this process cannot continue.")

}

else {

output.text('Task Template: '+taskTemplateName)

}

// Look up template records and filter to just the selected Template Header

const taskTemplateTable = base.getTable(“Task Templates”);

let taskTemplateQuery = await taskTemplateTable.selectRecordsAsync();

//trying to filter the results to just the task template header at hand

let filteredRecords = taskTemplateQuery.records.filter(record => {

let filterName = record.getCellValueAsString("Task Header Name");

return filterName === taskTemplateName

});

let recordsToCreate = filteredRecords.map((templateRecord) => ({

fields: {

    "Task Name": templateRecord.getCellValue("Task Name"),

    "Work Days from Start": templateRecord.getCellValue("WORK DAYS FROM START"),

    "Estimated Time (hr:mm)": templateRecord.getCellValue("Estimated Time (hr:mm)"),

    "Assignee": templateRecord.getCellValue("Assigned to"),

    "Task Status": {name: templateRecord.getCellValueAsString("Task Status")},

    "Task Type": {name: templateRecord.getCellValueAsString("Task Type")},

    "Task Notes": templateRecord.getCellValue("Task Notes"),

    "Project": [record],

// “Predecessor Task” : templateRecord.getCellValue(“Task Name (from Predecessor Task)”),

//Below is the way to format a URL field type

// “Test URL Copy”: templateRecord.getCellValue(“Test URL Copy”),

//as of 2021-04-06 I didn’t have this one working yet

//Below is the way to format a Multi-Select field type

   "Test Multi-Select Copy": templateRecord.getCellValue("Test Template Multi-Select Copy")

}}));

//load the templated tasks to the Task Table

const whereToPutTheTasks = base.getTable(“Tasks”);

await whereToPutTheTasks.createRecordsAsync(recordsToCreate);

//updates field that flags that the button has been pressed

await projectsTable.updateRecordAsync(record, {“Has Button Been Pressed” : ‘YES’} )

output.text(“Done!”);

[Justin_Barrett]

Welcome to the community, @Randi_Travers! :grinning_face_with_big_eyes:

Randi_Travers:

Let’s start with the first field type with an issue: Multi-select
Current error:
P: Can’t create records: invalid cell value for field ‘Test Multi-Select Copy’.
Could not find a choice with that ID or name
at main on line 54

I think that I see the problem. You’re directly passing the existing cell value to the new one, which (unfortunately) won’t work with multiple select fields:

Randi_Travers:

//Below is the way to format a Multi-Select field type
  "Test Multi-Select Copy": templateRecord.getCellValue("Test Template Multi-Select Copy")
}}));

(BTW: when sharing code, it’s helpful to format it as preformatted text using the </> button in the post editor toolbar)

Earlier in your script you’re doing the right thing with the {Task Status} and {Task Type} single select fields and passing objects with a name property. Multiple select fields are similar, but they require an array of such objects. To properly set that up, you’ll need to get the names of the chosen items from the original. The original cell value returns an array of objects that also contain an id property, and those IDs are unique to each field.

This should work:

"Test Multi-Select Copy": templateRecord.getCellValue("Test Template Multi-Select Copy") === null
  ? null
  : templateRecord.getCellValue("Test Template Multi-Select Copy").map(item => { return {name: item.name} })

This uses the ternary operator, which is a shortcut way of doing an if...else statement. In short it’s saying, “If the cell is empty, assign an empty value; otherwise map the existing array of objects to a new array of objects that will work for record creation.” (Ternary operators are not often split across multiple lines, but if that weren’t done the whole line would be crazy long. Most of the time for lengthy calculations like this I assign the result to a variable before building the object to use for record creation specifically to avoid such long lines. :slightly_smiling_face: )

[Randi_Travers]

Justin_Barrett:

"Test Multi-Select Copy": templateRecord.getCellValue("Test Template Multi-Select Copy") === null
  ? null
  : templateRecord.getCellValue("Test Template Multi-Select Copy").map(item => { return {name: item.name} })

@Justin_Barrett - thank you for your reply. Your line of code does the trick and now I know how to handle other data if we don’t require data to be in the template. Sorry about the messy post I couldn’t figure out why mine looked so different than the other posts but I also couldn’t delete it after I posted it either. Anyways, do you have any recommendations on how to code a linked field for predecessor information. We are planning on having a column in our Template Table to have the user pick (from the drop down) which row is the row before - but then when I use my script to ‘copy’ this over the record id won’t be the same? I’ve started reading through posts in the Airtable community to try to figure this out and I saved some code on Friday that may work but none of it was quite right. Not sure if we even need to have the user do that first part. Anyways - thanks in advance for any advice

[Randi_Travers]

Not sure if anyone has any good sample code for predecessor columns? I have the column set up in my task template but I can not figure out how to copy that information over in my script when I copy over all of the other tasks in my template and now my boss would like to know if I can figure out the due date of the current task by the start date of the next task - yes I know that both of these questions are going in different directions of looking forward and backwards but any ideas would be welcome. I have searched through the community and haven’t found anything yet so I’m thinking that I need to solve these out of the box as no one else has come across this yet. Thanks in advance for any advice

[Justin_Barrett]

Sorry for the delay in getting back to this. I’ll try to put some thought into this tomorrow and get back to you.

[Randi_Travers]

I’m so close (hopefully I’m formatting the code correctly) below is the new loop I created for filling in the predecessor information. I’m having the user tell me the task order in their template set up and then I’m using that number to figure out which rows should be linked to which row. If I’m on row one - I just need to save the information about the row and then if I’m at any row after that - update the row with the previous row’s id and then save it’s own id. But it throws no errors but doesn’t populate either. I’ve searched and searched for some coding error to why the data isn’t populating, I’m sure that it is something minor but I can’t find any example to assist me with this. This is the final piece for this script. So if anyone can help me that would be wonderful. Thanks in advance
…
//need to loop back through and set up the predecessor field if the column: Order of Task is not empty
const taskTable = base.getTable(‘Tasks’);
const taskView = taskTable.getView(‘Predecessor Column’);
let taskQuery = await taskView.selectRecordsAsync();

    let filteredTasks = taskQuery.records.filter(record => {
        let filteredProjectName = record.getCellValueAsString('Project')
        return projectName === filteredProjectName
    });
    let savedRecord
    if (filteredTasks.length > 1) {
        for (let recordLoop of taskQuery.records) {
            if (projectName === recordLoop.getCellValueAsString("Project")) {
                //do logic in here
                let taskOrder = recordLoop.getCellValueAsString('Order of Task')
                if (taskOrder = '1') {
                    //save record but don't do anything else
                    savedRecord = [{id: recordLoop.id}] ;
                }
                else {
                    //update current row with last row then save current id for next one
                    await taskTable.updateRecordAsync(recordLoop, { "Predecessor Task": savedRecord } )
                    savedRecord = recordLoop;
                }
            }
        }
    }
...

[Randi_Travers · Accepted solution]

//need to loop back through and set up the predecessor field if the column: Order of Task is not empty

const taskTable = base.getTable('Tasks');

const taskView = taskTable.getView('Predecessor Column');

let taskQuery = await taskView.selectRecordsAsync();

let filteredTasks = taskQuery.records.filter(record => {

    let filteredProjectName = record.getCellValueAsString('Project')

    return projectName === filteredProjectName

});

let savedRecord =[]

if (filteredTasks.length > 1) {

    for (let recordLoop of taskQuery.records) {

        if (projectName === recordLoop.getCellValueAsString("Project")) {

            //do logic in here

            let taskOrder = recordLoop.getCellValueAsString('Order of Task')

            if (taskOrder === '1') {

                //save record but don't do anything else

                savedRecord = [{id: recordLoop.id}] ;

            }

            else {

                //update current row with last row then save current id for next one

                await taskTable.updateRecordAsync(recordLoop, { "Predecessor Task": savedRecord } )

                savedRecord = [{id: recordLoop.id}];

            }

        }

    }

}

I figured out the couple of small mis-types that I did to solve my problem so this item is now fully closed