Help

New Script for multiple templates

Topic Labels: Scripting extentions
8368 28
cancel
Showing results for 
Search instead for 
Did you mean: 
Kendall_Shortt
6 - Interface Innovator
6 - Interface Innovator

Hi,

Could someone demo this script showing how to properly create the parent/child and template tabs to utilize this script?

I am having difficulty getting it to properly work.

Thanks.

28 Replies 28
Taylor_Savage
7 - App Architect
7 - App Architect

Hey there! We’re working on providing an updated version of this script that’s a little easier to get started with.

In the meantime, here’s some updated code for the script, as well as a walkthrough video that shows you how to use it.

You can copy-paste the code below into a new Scripting App, and then follow along with the video and/or follow the instructions when you go to run the script.

Let me know if you have any questions, or if we can clarify anything in the script or video - want to make sure it’s clear how to get up and running.

// Create settings
let settings = input.config({
    title: 'Create Records for Multiple Templates',
    description: `
## Instructions

This script will create records in a child table (e.g. "Tasks") that link back to a record in a parent table (e.g. "Projects") and are based on the template records that correspond to the parent record type.

For example, imagine your base has four tables:
\n
**Projects** - this is where you keep track of all your top-level projects. You'll add new projects here, and this script will generate the correct set of tasks depending on the type of project you specify. This table should have at least two fields:
* The name of the project
* A Linked Record field, that links to the **Project Types** table.
\n
**Project Types** - this is a reference table where you list out the different types of projects you'll have templates for. For example, if you're using this for software project management, this might include a "Product Launch" and a "Beta Release" project type. This table only needs one field:
* The name of the type of project.
\n
**Task Templates** - this is the table where you keep templates for the all the different tasks that should be associated with the various types of projects. For example, if one of your project types is "Pull off a heist," some task templates in this table might include "Assemble the crew" and "Scan the blueprints." This table needs the following fields:
* The name of the task.
* A Linked Record field, that links to the **Project Type** table for the type of project this task falls under.
* A number field, that represents where in the order tasks of this particular task should fall in the overall project.
\n
**Tasks** - this is the table where the actual tasks will be added, when you create a new project. Records from the "Task Templates" table will be copied to here. This table needs the following fields:
* The name of the task (which will be copied from **Task Templates** records).
* A Linked Record field, that links to the **Projects** table. This will specify which project this tasks was created for.
* A number field, that represents where in the order of tasks this particular task should fall in the overall project. (which will be copied from the **Task Templates** records).
\n
Using the interface below, link up which particular tables and fields in your base correspond to the tables and fields specified above. You can also have as many additional tables and fields as needed.
\n 
When you Run the script, it will ask you which record from your **Projects** table you'd like to generate tasks for. It will then copy the correct tasks from the **Task Templates** table based on the value of the Project record's associated **Project Type**.
\n
This script is also designed to be easily extended! If you'd like to customize it - for example, if you'd like to copy over additional fields from the **Task Templates** table, like "Duration" or "Assignee" - you're encouraged to look at and edit the underlying script. It's more approachable than it might look at first glance! 
\n
## Configuration
    `,
    items: [
        input.config.table('projectsTable', {
            label: 'Projects Table',
            description: 'The table where you keep track of actual projects, and where you\'ll be adding new projects that need associated tasks. (E.g. "Projects," "Campaigns")'
        }),
        input.config.table('projectTypesTable', {
            label: 'Project Types Table',
            description: 'A reference table where you list out the different potential types of projects. )'
        }),
        input.config.table('taskTemplatesTable', {
            label: 'Task Templates Table',
            description: 'Reference table with records for each task and a linked record to the Project Type Table (ex: Task templates; Activity templates)'
        }),
        input.config.table('tasksTable', {
            label: 'Tasks Table',
            description: 'Table in which you need to create records based on the Project Type linked record (ex: Tasks; Activities)'
        }),
        input.config.field('projectTypeField', {
            parentTable: 'projectsTable',
            label: 'Field in Projects linking to the Project Type',
            description: 'Linked record in Projects Table to Type Table (ex: Project type; Campaign size)'
        }),
        input.config.field('taskTemplateProjectTypeField', {
            parentTable: 'taskTemplatesTable',
            label: 'Field in Task Templates linking to the Project Type',
            description: 'Linked record in Template Table to Type Table (ex: Project type; Campaign size)'
        }),
        input.config.field('taskTemplateNameField', {
            parentTable: 'taskTemplatesTable',
            label: 'Field in Task Templates for the task name',
            description: 'Text field in Template Table to indicate name of record (ex: Task name; Activity name)'
        }),
        input.config.field('taskTemplateOrderField', {
            parentTable: 'taskTemplatesTable',
            label: 'Field in Task Templates representing the task order',
            description: 'Number field in Template Table to indicate order in which records should be executed (ex: Task order; Activity order)'
        }),
        input.config.field('taskNameField', {
            parentTable: 'tasksTable',
            label: 'Field in Tasks representing the task name',
            description: 'Text field in Child Table to indicate name of record (ex: Task Name; Activity Name)'
        }),
        input.config.field('taskOrderField', {
            parentTable: 'tasksTable',
            label: 'Field in Tasks representing the task order',
            description: 'Number field in Child Table to indicate order in which records should be executed (ex: Task order; Activity order)'
        }), 
        input.config.field('taskProjectField', {
            parentTable: 'tasksTable',
            label: 'Field in Tasks linking to the corresponding Project',
            description: 'Linked record in Tasks Table to indicate related record in the Projects table (ex: Project; Campaign)'
        })
    ],
})
// Define tables from script settings
let projectsTable = settings.projectsTable;
let projectTypesTable = settings.projectTypesTable;
let tasksTable = settings.tasksTable;
let taskTemplatesTable = settings.taskTemplatesTable;

if ([projectTypesTable, tasksTable, taskTemplatesTable].indexOf(projectsTable) != -1 ||
    [tasksTable, taskTemplatesTable, projectsTable].indexOf(projectTypesTable) != -1 ||
    [taskTemplatesTable, projectsTable, projectTypesTable].indexOf(tasksTable) != -1) {
    throw new Error("Projects Table, Project Types Table, Task Templates Table, and Tasks Table should all be different tables.")
}

// Define fields from script settings
let taskTemplateNameField = settings.taskTemplateNameField;
let taskNameField = settings.taskNameField;
let childOrder = settings.taskOrderField;
let taskTemplateOrderField = settings.taskTemplateOrderField;
let taskTemplateProjectTypeField = settings.taskTemplateProjectTypeField;
let taskProjectField = settings.taskProjectField;

// Select Project record to create Task records & select the Project Type
let selectedEvent = await input.recordAsync('Choose Record', projectsTable);
if (!selectedEvent) {
    throw new Error("It looks like you haven't selected a Project record to create tasks for. Is your Projects table empty?")
}
let projectTypeField = selectedEvent.getCellValue(settings.projectTypeField);

// Look up template records
let typesQuery = await taskTemplatesTable.selectRecordsAsync();
let typesRecords = typesQuery.records;

if (projectTypeField == null) {
    throw new Error("The selected record is missing a template type. Maybe you forgot to add a linked record to the type of project this should be?")
}

// Filter the template records to match the selected type & add the parent ID to the map
let types = typesRecords.map(c => ({
    'child': [c],
    'childName': c.getCellValue(taskTemplateNameField),
    'templateTypes': c.getCellValue(taskTemplateProjectTypeField).map(x => x.id),
    'templateOrder': c.getCellValue(taskTemplateOrderField)
    // Add additional template fields here and in section below using format below. 
    // Field names within c.getCellValue parentheticals should match field names in Task Templates table
    //  'templatePhase':c.getCellValue('Phase'),
    //  'templateDays': c.getCellValue('Days')
})).filter(x => x.templateTypes.includes(projectTypeField[0].id))

// Create the child records and sort them so that they are in order
let createRecords = types.map(c => ({
    fields: {
        [taskNameField.name]: c.childName,
        [taskProjectField.name]: [selectedEvent],
        [childOrder.name]: c.templateOrder
        // Add additional task template fields here and in section above using format below. 
        // Field names on the left should match field names in the Tasks table. 
        // Field names on the right following c. should match names created in section above.
        //  'Phase':c.templatePhase,
        //  'Days': c.templateDays
    }
})).sort((a, b) => {
    return a.fields[childOrder] - b.fields[childOrder];
});

// output.inspect(createRecords.map((c)=>{return c.fields[childOrder]}));
if (selectedEvent) {
    // create records in batches of 50
    while (createRecords.length > 0) {
        await tasksTable.createRecordsAsync(createRecords.slice(0, 50));
        createRecords = createRecords.slice(50);
    }
}

output.text('Done!');

Thanks so much Taylor. This explained it very well and I got it working.

Now I’m on to figuring out how to make this work with a Gantt chart for the different tasks my product managers have for each part of the product production phase.

Thanks again!

image001.jpg

Hi Taylor,

This is great.

Just wondering if there is a way to update this script so you can link projects to to multiple ‘project type’ templates for task generation. As of right now, it only pulls tasks for the first linked 'project type" record in the field.

Case use for this: We offer a standard set of services, but there are several add-on features we offer that require additional tasks for our production teams. It would be nice to link up the core tasks plus whatever add-ons needed w/ just a few clicks.

Thanks!

Taylor_Savage
7 - App Architect
7 - App Architect

Hey Susan! Great question.

Definitely possible. So the idea would be to let “Project Type” be a multi-select, and add on tasks for all of the project type templates that show up in the “Project Type” field?

@Taylor_Savage - In the example above it’s a linked field type, but essentially yes. It’d be great if it could act like a mutliselect and load all selected template items.

Hope this helps clarify!

Susan_Lanier
6 - Interface Innovator
6 - Interface Innovator

Another question…

I’ve updated the code per the documentation to copy over additional template fields but am getting this error:

TypeError: Cannot read property ‘map’ of null
at typesRecords.map.c on line 129
at main on line 126

However, I didn’t alter the code at line 126 or 129.

See:

// Filter the template records to match the selected type & add the parent ID to the map
let types = typesRecords.map(c => ({
‘child’: [c],
‘childName’: c.getCellValue(taskTemplateNameField),
‘templateTypes’: c.getCellValue(taskTemplateProjectTypeField).map(x => x.id),
‘templateOrder’: c.getCellValue(taskTemplateOrderField),
‘templatePhase’: c.getCellValue(‘Project Phase’),
‘templateAssigning’: c.getCellValue(‘Assigned To’),
‘templatePriority’: c.getCellValue(‘Priority Task’)
// Add additional template fields here and in section below using format below.
// Field names within c.getCellValue parentheticals should match field names in Task Templates table
// ‘templatePhase’:c.getCellValue(‘Phase’),
// ‘templateDays’: c.getCellValue(‘Days’)
})).filter(x => x.templateTypes.includes(projectTypeField[0].id))

Does this script work w/ all field types? I am trying to copy over a single select and a check box field from the template. Maybe that’s it?

Thank you!

Hey Susan,

Here’s an updated script that lets you assign multiple “types” to a particular project, and then it’ll copy over the child records from each of the “types” that are listed.

// Create settings
let settings = input.config({
    title: 'Create Records for Multiple Templates',
    description: 'This script will create records in a child table that link back to a parent record and are based on the template records that correspond to the parent record type',
    items: [
        input.config.table('parentTable', {
            label: 'Parent Table',
            description: 'Table from which you need to create template records (ex: Projects; Campaigns)'
        }),
        input.config.table('typeTable', {
            label: 'Type Table',
            description: 'Reference table with records for each template type (ex: Project categories; Campaign sizes)'
        }),
        input.config.table('templateTable', {
            label: 'Template Table',
            description: 'Reference table with records for each template record and a linked record to the Type Table (ex: Task templates; Activity templates)'
        }),
        input.config.table('childTable', {
            label: 'Child Table',
            description: 'Table in which you need to create records based on the Type linked record (ex: Tasks; Activities)'
        }),
        input.config.field('parentType', {
            parentTable: 'parentTable',
            label: 'Parent Type',
            description: 'Linked record in Parent Table to Type Table (ex: Project type; Campaign size)'
        }),
        input.config.field('templateType', {
            parentTable: 'templateTable',
            label: 'Template Type',
            description: 'Linked record in Template Table to Type Table (ex: Project type; Campaign size)'
        }),
        input.config.field('childFieldInTemplate', {
            parentTable: 'templateTable',
            label: 'Child Field in Template Table',
            description: 'Text field in Template Table to indicate name of record (ex: Task name; Activity name)'
        }),
        input.config.field('templateOrder', {
            parentTable: 'templateTable',
            label: 'Template Record Order',
            description: 'Number field in Template Table to indicate order in which records should be executed (ex: Task order; Activity order)'
        }),
        input.config.field('childOrder', {
            parentTable: 'childTable',
            label: 'Child Record Order',
            description: 'Number field in Child Table to indicate order in which records should be executed (ex: Task order; Activity order)'
        }),
        input.config.field('childNameInChild', {
            parentTable: 'childTable',
            label: 'Child Name',
            description: 'Text field in Child Table to indicate name of record (ex: Task Name; Activity Name)'
        }),
        input.config.field('parentFieldInChild', {
            parentTable: 'childTable',
            label: 'Parent Field in Child Table',
            description: 'Linked record in Child Table to indicate related parent record (ex: Project; Campaign)'
        })
    ],
})
// Define tables from script settings
let parentTable = settings.parentTable;
let typesTable = settings.typeTable;
let childTable = settings.childTable;
let templateTable = settings.templateTable;

if ([typesTable, childTable, templateTable].indexOf(parentTable) != -1 ||
    [childTable, templateTable, parentTable].indexOf(typesTable) != -1 ||
    [templateTable, parentTable, typesTable].indexOf(childTable) != -1) {
    throw new Error("Parent Table, Type Table, Template Table, and Child Table should all be different tables.")
}

// Define fields from script settings
let childFieldInTemplate = settings.childFieldInTemplate;
let childNameInChild = settings.childNameInChild;
let childOrder = settings.childOrder;
let templateOrder = settings.templateOrder;
let templateType = settings.templateType;
let parentFieldInChild = settings.parentFieldInChild;

// Select parent record to create child records & select type
let selectedEvent = await input.recordAsync('Pick a record from your "Parent Table" to generate child records for.', parentTable);
let parentTypes = selectedEvent.getCellValue(settings.parentType);

// Throw a meaningful error if parentTypes is null
if (parentTypes == null) {
    throw new Error("Project record provided doesn't have any assigned 'types' - you must assign a particular 'type' to the Parent record so that this script can create the correct child records.")
}

// Look up template records
let typesQuery = await templateTable.selectRecordsAsync();
let typesRecords = typesQuery.records;

// Filter the template records to match the selected type & add the parent ID to the map
let types = typesRecords.map(c => ({
    'child': [c],
    'childName': c.getCellValue(childFieldInTemplate),
    'templateTypes': c.getCellValue(templateType)?.map(x => x.id),
    'templateOrder': c.getCellValue(templateOrder)
    // Add additional template fields here and in section below using format below. 
    // Field names within c.getCellValue parentheticals should match field names in template table
    //  'templatePhase':c.getCellValue('Phase'),
    //  'templateDays': c.getCellValue('Days')
})).filter(x => {
    // Filter down the set of child records only to ones that match a "type"
    // that's listed in the Parent record "Types" field.
    let childTypeInParentTypes = false;

    // If the child record doesn't have any assigned template types, ignore it.
    if (!x.templateTypes) return false;
    
    parentTypes.forEach((parentType) => {
        if (x.templateTypes.includes(parentType.id)) {
            childTypeInParentTypes = true;
        }
    })
    return childTypeInParentTypes;
})

// Create the child records and sort them so that they are in order
let createRecords = types.map(c => ({
    fields: {
        [childNameInChild.name]: c.childName,
        [parentFieldInChild.name]: [selectedEvent],
        [childOrder.name]: c.templateOrder
        // Add additional template fields here and in section above using format below. 
        // Field names on the left should match field names in child table. 
        // Field names on the right following c. should match names created in section above that starts at line 72.
        //  'Phase':c.templatePhase,
        //  'Days': c.templateDays
    }
})).sort((a, b) => {
    return a.fields[childOrder] - b.fields[childOrder];
});

// output.inspect(createRecords.map((c)=>{return c.fields[childOrder]}));
if (selectedEvent) {
    // create records in batches of 50
    while (createRecords.length > 0) {
        await childTable.createRecordsAsync(createRecords.slice(0, 50));
        createRecords = createRecords.slice(50);
    }
}

output.text('Done!');

The bulk of the changes here are in the filter function starting on line 102 in this new script. Basically, instead of just looking at the first parentType, it loops through all of the linked records in parentType and sees if the task matches one of them.

Regarding the error you’re seeing - is it possible that one of the tasks in your “Template Table” doesn’t have an associated “Type”? I also included some better error checking code in this new script to hopefully fix this issue.

Hey Taylor! Thanks for the updated script.

I am getting the follow error after configuring and linking up the appropriate tables/fields etc. I’ve double checked it multiple times. I haven’t updated this new script at all. Had no problem configuring the original script. What’s going on here?

SyntaxError: Unexpected token .
on line 1
at a on line 1
at Generator._invoke on line 1
at Generator.F.forEach.u.(anonymous function) [as next] on line 1
at u on line 1
at o on line 1
on line 1
on line 1

Apologies for the back-and-forth here Susan, we’re working on getting a canonical version of this script published to the Marketplace.

In the meantime - here’s an even more-updated version of the script to try:

// Create settings
let settings = input.config({
    title: 'Create Records for Multiple Templates',
    description: `
## Instructions

This script will create records in a child table (e.g. "Tasks") that link back to a record in a parent table (e.g. "Projects") and are based on the template records that correspond to the parent record type.

For example, imagine your base has four tables:
\n
**Projects** - this is where you keep track of all your top-level projects. You'll add new projects here, and this script will generate the correct set of tasks depending on the type of project you specify. This table should have at least two fields:
* The name of the project
* A Linked Record field, that links to the **Project Types** table.
\n
**Project Types** - this is a reference table where you list out the different types of projects you'll have templates for. For example, if you're using this for software project management, this might include a "Product Launch" and a "Beta Release" project type. This table only needs one field:
* The name of the type of project.
\n
**Task Templates** - this is the table where you keep templates for the all the different tasks that should be associated with the various types of projects. For example, if one of your project types is "Pull off a heist," some task templates in this table might include "Assemble the crew" and "Scan the blueprints." This table needs the following fields:
* The name of the task.
* A Linked Record field, that links to the **Project Type** table for the type of project this task falls under.
* A number field, that represents where in the order tasks of this particular task should fall in the overall project.
\n
**Tasks** - this is the table where the actual tasks will be added, when you create a new project. Records from the "Task Templates" table will be copied to here. This table needs the following fields:
* The name of the task (which will be copied from **Task Templates** records).
* A Linked Record field, that links to the **Projects** table. This will specify which project this tasks was created for.
* A number field, that represents where in the order of tasks this particular task should fall in the overall project. (which will be copied from the **Task Templates** records).
\n
Using the interface below, link up which particular tables and fields in your base correspond to the tables and fields specified above. You can also have as many additional tables and fields as needed.
\n 
When you Run the script, it will ask you which record from your **Projects** table you'd like to generate tasks for. It will then copy the correct tasks from the **Task Templates** table based on the value of the Project record's associated **Project Type**.
\n
This script is also designed to be easily extended! If you'd like to customize it - for example, if you'd like to copy over additional fields from the **Task Templates** table, like "Duration" or "Assignee" - you're encouraged to look at and edit the underlying script. It's more approachable than it might look at first glance! 
\n
## Configuration
    `,
    items: [
        input.config.table('projectsTable', {
            label: 'Projects Table',
            description: 'The table where you keep track of actual projects, and where you\'ll be adding new projects that need associated tasks. (E.g. "Projects," "Campaigns")'
        }),
        input.config.table('projectTypesTable', {
            label: 'Project Types Table',
            description: 'A reference table where you list out the different potential types of projects. )'
        }),
        input.config.table('taskTemplatesTable', {
            label: 'Task Templates Table',
            description: 'Reference table with records for each task and a linked record to the Project Type Table (ex: Task templates; Activity templates)'
        }),
        input.config.table('tasksTable', {
            label: 'Tasks Table',
            description: 'Table in which you need to create records based on the Project Type linked record (ex: Tasks; Activities)'
        }),
        input.config.field('projectTypeField', {
            parentTable: 'projectsTable',
            label: 'Field in Projects linking to the Project Type',
            description: 'Linked record in Projects Table to Type Table (ex: Project type; Campaign size)'
        }),
        input.config.field('taskTemplateProjectTypeField', {
            parentTable: 'taskTemplatesTable',
            label: 'Field in Task Templates linking to the Project Type',
            description: 'Linked record in Template Table to Type Table (ex: Project type; Campaign size)'
        }),
        input.config.field('taskTemplateNameField', {
            parentTable: 'taskTemplatesTable',
            label: 'Field in Task Templates for the task name',
            description: 'Text field in Template Table to indicate name of record (ex: Task name; Activity name)'
        }),
        input.config.field('taskTemplateOrderField', {
            parentTable: 'taskTemplatesTable',
            label: 'Field in Task Templates representing the task order',
            description: 'Number field in Template Table to indicate order in which records should be executed (ex: Task order; Activity order)'
        }),
        input.config.field('taskNameField', {
            parentTable: 'tasksTable',
            label: 'Field in Tasks representing the task name',
            description: 'Text field in Child Table to indicate name of record (ex: Task Name; Activity Name)'
        }),
        input.config.field('taskOrderField', {
            parentTable: 'tasksTable',
            label: 'Field in Tasks representing the task order',
            description: 'Number field in Child Table to indicate order in which records should be executed (ex: Task order; Activity order)'
        }), 
        input.config.field('taskProjectField', {
            parentTable: 'tasksTable',
            label: 'Field in Tasks linking to the corresponding Project',
            description: 'Linked record in Tasks Table to indicate related record in the Projects table (ex: Project; Campaign)'
        })
    ],
})
// Define tables from script settings
let projectsTable = settings.projectsTable;
let projectTypesTable = settings.projectTypesTable;
let tasksTable = settings.tasksTable;
let taskTemplatesTable = settings.taskTemplatesTable;

if ([projectTypesTable, tasksTable, taskTemplatesTable].indexOf(projectsTable) != -1 ||
    [tasksTable, taskTemplatesTable, projectsTable].indexOf(projectTypesTable) != -1 ||
    [taskTemplatesTable, projectsTable, projectTypesTable].indexOf(tasksTable) != -1) {
    throw new Error("Projects Table, Project Types Table, Task Templates Table, and Tasks Table should all be different tables.")
}

// Define fields from script settings
// Define fields from script settings
let taskTemplateNameField = settings.taskTemplateNameField;
let taskNameField = settings.taskNameField;
let childOrder = settings.taskOrderField;
let taskTemplateOrderField = settings.taskTemplateOrderField;
let taskTemplateProjectTypeField = settings.taskTemplateProjectTypeField;
let taskProjectField = settings.taskProjectField;

// Select Project record to create Task records & select the Project Type
let selectedEvent = await input.recordAsync('Choose Record', projectsTable);
if (!selectedEvent) {
    throw new Error("It looks like you haven't selected a Project record to create tasks for. Is your Projects table empty?")
}
let projectType = selectedEvent.getCellValue(settings.projectTypeField);

// Look up template records
let typesQuery = await taskTemplatesTable.selectRecordsAsync();
let typesRecords = typesQuery.records;

if (projectType == null) {
    throw new Error("The selected record is missing a template type. Maybe you forgot to add a linked record to the type of project this should be?")
}

// Filter the template records to match the selected type & add the parent ID to the map
let types = typesRecords.map(c => ({
    'child': [c],
    'childName': c.getCellValue(taskTemplateNameField),
    'templateTypes': c.getCellValue(taskTemplateProjectTypeField)?.map(x => x.id),
    'templateOrder': c.getCellValue(taskTemplateOrderField)
    // Add additional template fields here and in section below using format below. 
    // Field names within c.getCellValue parentheticals should match field names in template table
    //  'templatePhase':c.getCellValue('Phase'),
    //  'templateDays': c.getCellValue('Days')
})).filter(x => {
    // Filter down the set of child records only to ones that match a "type"
    // that's listed in the Parent record "Types" field.

    // If the child record doesn't have any assigned template types, ignore it.
    if (!x.templateTypes) return false;

    for (const parentType of projectType) {
        if (x.templateTypes.includes(parentType.id)) {
            return true;
        }
    }

    return false;
})

// Create the child records and sort them so that they are in order
let createRecords = types.map(c => ({
    fields: {
        [taskNameField.name]: c.childName,
        [taskProjectField.name]: [selectedEvent],
        [childOrder.name]: c.templateOrder
        // Add additional template fields here and in section above using format below. 
        // Field names on the left should match field names in child table. 
        // Field names on the right following c. should match names created in section above that starts at line 72.
        //  'Phase':c.templatePhase,
        //  'Days': c.templateDays
    }
})).sort((a, b) => {
    return a.fields[childOrder] - b.fields[childOrder];
});

// output.inspect(createRecords.map((c)=>{return c.fields[childOrder]}));
if (selectedEvent) {
    // create records in batches of 50
    while (createRecords.length > 0) {
        await tasksTable.createRecordsAsync(createRecords.slice(0, 50));
        createRecords = createRecords.slice(50);
    }
}

output.text('Done!');

Thanks Taylor. Much appreciated.

It appears I am still getting the same error. I have it set up exactly the same as the original script that worked fine. Maybe scripts aren’t for me.

Jarvis
7 - App Architect
7 - App Architect

Hello @Taylor_Savage , will it be possible to use this script like this?

I have a “Projects” table with 2 additional fields - “Videos” and “Images”. As an example, what this says to me is that Project X will have a certain number of video tasks and a certain number of image tasks, and I want to create those in a new table. Here’s a screenshot of the projects table:

image

Here’s the result I want to get. I’ve added a single select field to show which type of task each one is, and then I’ve also added a “status” field which for my use case will be in “Open” for all new tasks created in this script.

image

If this is possible to create new records automatically with a script, it would save me a lot of mistakes/headaches as we end up making 700+ contents every month, so manually creating and managing these with our small team is a hassle.

Certainly! If I understand it correctly, this case is actually quite a bit more straightforward. Here’s a script for you:

// Get a hold of your projects table.
const projectsTable = base.getTable("Projects");

// Let the user pick a record from the Projects table to generate tasks
let projectRecord = await input.recordAsync("Select a Project to generate Tasks", projectsTable)

// Get the values from the Videos and Images fields for that record
const numVideos = projectRecord.getCellValue("Videos")
const numImages = projectRecord.getCellValue("Images")

if (numVideos == null || numImages == null) {
    throw new Error("Make sure you input a number for the number of Images and Videos required!")
}

// Get a hold of your tasks table.
const tasksTable = base.getTable("Tasks")

// Create all of the video tasks in the tasks table
for (let i = 1; i <= numVideos; i++) {
    await tasksTable.createRecordAsync({
        "Task Name": `Videos ${i}`, // This will generate names like "Videos 1", "Videos 2", etc.
        "Task Type": {name: "Videos"}, // Since this is a single select field, you set it by passing an object with "Name" equal to the tag you want
        "Project": [{id: projectRecord.id}], // Since we're setting a linked record field here, we need to pass an array of objets, with the "id" key set to the id of the record we want to link to.
        "Status": {name: "Open"}
    })
}

// Create all of the Images tasks in the tasks table
for (let i = 1; i <= numImages; i++) {
    await tasksTable.createRecordAsync({
        "Task Name": `Images ${i}`, // This will generate names like "Images 1", "Images 2", etc.
        "Task Type": {name: "Images"}, // Since this is a single select field, you set it by passing an object with "Name" equal to the tag you want
        "Project": [{id: projectRecord.id}], // Since we're setting a linked record field here, we need to pass an array of objets, with the "id" key set to the id of the record we want to link to.
        "Status": {name: "Open"}
    })
}

There are more efficient ways to write this script, but I tried to make it as clear to understand what’s going on as possible.

Hello Taylor, wow thank you so much! I’ll try tinkering with this. Like many others here I don’t know how to code (and try to work on examples already given), so I’m really grateful for this :grinning_face_with_big_eyes:

No problem! Tinkering/working with examples is coding! I don’t know a single professional software engineer who doesn’t copy/paste/tweak :slightly_smiling_face:

Let me know if you have any questions.

Hi @Taylor_Savage - Saw your reference to the team there working on getting a canonical version of the script published to the marketplace. Did that end up happening or is this still a work-in-progress? Thanks for your help!


Edit: just to expand on this, and to include an example… I see the script in the marketplace published in December last year “Create records for multiple templates” and what I am really hoping to accomplish here is to create sets of tasks per project that is based on the type of project it is. However, there is a common set of tasks that each project pulls from. For example “Email guests” is a template task listed once, but is assigned to multiple types of projects, and so for every project that has a type associated with it that includes the single task “Email guests”, it will create one new task specific to that project.

Example Projects (Types)…

  • New Project 1 - Homecoming Dance (Type of project: All-student event)
  • New Project 2 - Prom Dance (Type of project: Upper-classmen event)
  • New Project 3 - Alumni Reunion (Type of project: Alumni event)

Example Tasks (Types)…

  • Template Task 1 - Secure the off-campus event location (For types: Alumni event)
  • Template Task 2 - Determine Chaperones (For types: All-student event and Upper-classmen event)
  • Template Task 3 - Hire 3rd party photographer (For types: Upper-classmen event and Alumni event)
  • Template Task 4 - Email guests (For types: All-student event and Upper-classmen event and Alumni event)
  • Template Task 5 - Solicit charitable contributions (For types: Alumni event)

And the resulting output would be

  • Output Task 1: Homecoming Dance - Determine Chaperones
  • Output Task 2: Homecoming Dance - Email guests
  • Output Task 3: Prom Dance - Determine Chaperones
  • Output Task 4: Prom Dance - Hire 3rd party photographer
  • Output Task 5: Prom Dance - Email guests
  • Output Task 6: Alumni Reunion - Secure the off-campus event location
  • Output Task 7: Alumni Reunion - Hire 3rd party photographer
  • Output Task 8: Alumni Reunion - Email guests
  • Output Task 9: Alumni Reunion - Solicit charitable contributions

Thanks for your help!


Edit #2:
I originally was using this script that is in the marketplace which looked a little different than the Vimeo video tutorial above (small cosmetic changes to the script field drop-down menus). I tried updating the field type for the task template to be a multiple record-capable link to another record, and it didn’t work. But then I tried @Taylor_Savage 's script example from his Feb 19th post above, and it worked like a charm with the multiple types per task template. So, this may be what I needed, but I am going to start to really build this out now… Up until this point I was using a very simple base format setup that mostly mirrored the Vimeo example. Next, I’ll see whether the real data will work once I start populating more columns that would need to be used with each template task.
Question: Is there any plan to update the marketplace version of this script with the version that is in the thread above from Feb 19th? Or possibly any other even more updated version? :slightly_smiling_face: Thanks so much for the help.

Hello @Taylor_Savage , I’ve set everything up and it works flawlessly! Can’t thank you enough for your support.

I have another question regarding scripts - I noticed (as shown in this YouTube video) that we can trigger scripts to run automatically through automations. I followed the video, but got stuck at the last stage, where I want the automation to run the script for all Projects automatically (I’ve set the trigger to run every 24 hours as shown in the screenshot below).

image

I got confused when I realized that the original script is set up in such a way that it prompts me on each run to select the “Project”. The result I want to achieve is to create all Tasks for all Projects (or maybe for Projects meeting certain conditions, such as having “Green” status from a single-select field, + “Ongoing” status from a linked field, or for all projects in a certain view) automatically, without me having to select which Project, or without me having to click anything. Is this doable with the current automation?

Hello @Taylor_Savage . Following up on my latest message, here’s a screenshot of the error I’m getting when I’m trying to copy/paste the script into an automation. I’m guessing I’ll have to modify a few lines, and I tried to do it manually using the docs and by seeing some other messages in this board, but I’m still scratching my head :frowning:

image

Update: seeing the example on getting records (in the Examples section in the screenshot below), I managed to edit the error at line 5. Now I’m getting this error at line 8

image

Hey @BriShny - glad you got it to work! Yes, we’re planning on updating the version of this script in the Marketplace to an updated version.

Hey @Jarvis - here’s an adapted version of the above script for your Videos and Images example, that will run in an automation.

// Get a hold of your projects table.
const projectsTable = base.getTable("Simple Projects");

// Get a hold of your tasks table.
const tasksTable = base.getTable("Simple Tasks")

const projectRecordsQuery = await projectsTable.selectRecordsAsync();
const allProjectRecords = projectRecordsQuery.records;

// Iterate over all project records in the base.
Promise.all(allProjectRecords.map(async (projectRecord) => {
    // Skip to the next project record if this one already has Tasks associated with it.
    if (projectRecord.getCellValue("Simple Tasks") && projectRecord.getCellValue("Simple Tasks").length > 0) {
        return Promise.resolve();
    } 

    // Get the values from the Videos and Images fields for that record
    const numVideos = projectRecord.getCellValue("# Videos")
    const numImages = projectRecord.getCellValue("# Images")

    if (numVideos == null || numImages == null) {
        throw new Error("Make sure you input a number for the number of Images and Videos required!")
    }

    // Create all of the video tasks in the tasks table
    for (let i = 1; i <= numVideos; i++) {
        await tasksTable.createRecordAsync({
            "Task Name": `Videos ${i}`, // This will generate names like "Videos 1", "Videos 2", etc.
            "Task Type": {name: "Videos"}, // Since this is a single select field, you set it by passing an object with "Name" equal to the tag you want
            "Project": [{id: projectRecord.id}], // Since we're setting a linked record field here, we need to pass an array of objets, with the "id" key set to the id of the record we want to link to.
            "Status": {name: "Open"}
        })
    }

    // Create all of the Images tasks in the tasks table
    for (let i = 1; i <= numImages; i++) {
        await tasksTable.createRecordAsync({
            "Task Name": `Images ${i}`, // This will generate names like "Images 1", "Images 2", etc.
            "Task Type": {name: "Images"}, // Since this is a single select field, you set it by passing an object with "Name" equal to the tag you want
            "Project": [{id: projectRecord.id}], // Since we're setting a linked record field here, we need to pass an array of objets, with the "id" key set to the id of the record we want to link to.
            "Status": {name: "Open"}
        })
    }
}))

You’re almost there with the second example you posted.

The next step (that’s reflected in the above script) is to actually iterate over all of the projectRecords, check if they don’t yet have tasks assigned, and if not run the original code on them.