New Script for multiple templates

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.

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!');
1 Like

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