Help

The Community will be undergoing maintenance from Friday February 21 - Friday, February 28 and will be "read only" during this time. To learn more, check out our Announcements blog post.

SCRIPTING Extension Help

Topic Labels: Extensions
225 3
cancel
Showing results for 
Search instead for 
Did you mean: 
cguckert
4 - Data Explorer
4 - Data Explorer

Hi All,

I've been working on customizing a script for an air table base I'm building, and I've hit a wall with fixing the section about copying over dependency (linked record field) from the template tasks table into the active tasks table. I need the newly created tasks in the "Active Tasks" table to have dependencies mapped to each other as instructed by the "Template Tasks" table. 

Here is how my base is set up: 

Newsrooms Table

  • "Revenue Services Link" → Links to "Template Revenue Services" Table
  • "Create Template Tasks" → Button Field
  • "Link to Active Tasks" → Links to "Active Tasks" Table
  • "Onsite Ad Ops Tasks Created" → Checkbox/Boolean
  • "Newsletter Ad Ops Tasks Created" → Checkbox/Boolean
  • "Strategy and Execution Tasks Created" → Checkbox/Boolean
  • "Branded Content Tasks Created" → Checkbox/Boolean

Template Revenue Services Table

  • "Template Task Links" → Links to "Template Tasks" Table

Template Tasks Table

  • "Task Name" Single Line Text Field
  • "Dependency" Linked Records in "Template Tasks" Table
  • "Description" Long Text Field
  • "Resources" Long Text Field
  • "Responsible" Multi-Select Field
  • "Estimated Days" Duration Field
  • “Task Group” Single-Select Field

Active Tasks Table

  • "Newsrooms" → Links to "Newsrooms" Table
  • "Template Task Link" → Links to "Template Tasks" Table
  • "Partner Revenue Service Link" → Links to "Template Revenue Services" Table
  • "Task Name" Single Line Text Field
  • "Dependency" Linked Records in "Active Tasks" Table
  • "Description" Long Text Field
  • "Resources" Long Text Field
  • "Responsible" Multi-Select Field
  • "Estimated Days" Duration Field
  • “Task Group” Single-Select Field

Any advice is greatly greatly appreciated!! Help me get past this wall.

 

 

 

 

// Define table names and field mappings for project and task data
const project_table_name = 'Newsrooms';
const project_template_link_field_name = 'Revenue Services Link';

const task_table_name = 'Active Tasks';
const task_project_link_field_name = 'Newsrooms';
const task_dependency_field_name = 'Dependency';
const task_primary_field_name = 'Task Name';
const task_partner_revenue_service_link = 'Partner Revenue Service Link';
const task_to_template_link_field_name = 'Template Task Link';

const project_template_table_name = 'Template Revenue Services';
const task_template_table_name = 'Template Tasks';
const proj_temp_task_temp_link_field_name = 'Template Task Links';
const task_temp_dependency_field_name = 'Dependency';
const task_temp_primary_field_name = 'Task Name';
const task_temp_description_field_name = 'Description';
const task_temp_resources_field_name = 'Resources';
const task_temp_responsible_field_name = 'Responsible';
const task_temp_estimated_days_field_name = 'Estimated Days';
const task_temp_group_field_name = 'Task Group';

// Fields to update when tasks are created
const task_creation_fields = {
    "1_Technical Support_Onsite Ad Ops": "Onsite Ad Ops Tasks Created",
    "2_Technical Support_Newsletter Ad Ops": "Newsletter Ad Ops Tasks Created",
    "3_Consultive Support_Strategy and Execution": "Strategy and Execution Tasks Created",
    "4_Production Support_Branded Content Creation": "Branded Content Tasks Created"
};

// Fetch the record that triggered the button click
let record = await input.recordAsync('Select a project', base.getTable(project_table_name));
if (!record) {
    output.markdown("No record selected. Exiting.");
    return;
}

output.markdown('# Creating tasks for new assets and assigning dependencies');

// Fetch necessary tables
const project_table = base.getTable(project_table_name);
const task_table = base.getTable(task_table_name);
const project_temp_table = base.getTable(project_template_table_name);
const task_temp_table = base.getTable(task_template_table_name);

// Retrieve linked project templates
let linkedTemplates = record.getCellValue(project_template_link_field_name);
if (!linkedTemplates || linkedTemplates.length === 0) {
    output.markdown(`No project template found for ${record.name}. Exiting.`);
    return;
}

// Fetch all project and task templates
const project_temp_results = await project_temp_table.selectRecordsAsync();
const task_temp_results = await task_temp_table.selectRecordsAsync();

// Map project templates to their respective task templates
let project_task_temp_map = {};
for (let r of project_temp_results.records) {
    let temp_tasks = r.getCellValue(proj_temp_task_temp_link_field_name) || [];
    project_task_temp_map[r.id] = temp_tasks.map(t => t.id);
}

// Map task templates to their dependencies
let task_temp_dependency_map = {};
for (let r of task_temp_results.records) {
    let next_task = r.getCellValue(task_temp_dependency_field_name);
    task_temp_dependency_map[r.id] = next_task ? next_task[0].id : null;
}

// Track newly created task mappings for dependencies
let newTaskIdMap = {};

for (let p of linkedTemplates) {
    let p_id = p.id;

    // Check if tasks were already created for this template in Newsrooms
    let project_record = await project_table.selectRecordAsync(record.id);
    if (project_record) {
        let task_created_field = task_creation_fields[p.name];
        if (project_record.getCellValue(task_created_field)) {
            output.markdown(`Tasks already created for ${p_id} and ${record.name}. Skipping...`);
            continue;
        }
    }

    // Retrieve task templates for the selected project template
    if (!project_task_temp_map[p_id]) {
        output.markdown(`No tasks found for template ${p_id}. Skipping...`);
        continue;
    }
    
    let task_temp_ids = project_task_temp_map[p_id];
    let task_temps = task_temp_ids.map(id => task_temp_results.getRecord(id));

    // Create task records for the selected project and template
    let payloads = [];
    let tempToNewTaskMap = {}; // Store new task IDs mapped to their templates

    for (let t of task_temps) {
        let newTask = {
            fields: {
                [task_project_link_field_name]: [{ id: record.id }],
                [task_primary_field_name]: t.getCellValueAsString(task_temp_primary_field_name),
                [task_temp_description_field_name]: t.getCellValueAsString(task_temp_description_field_name),
                [task_temp_resources_field_name]: t.getCellValueAsString(task_temp_resources_field_name),
                [task_temp_responsible_field_name]: t.getCellValue(task_temp_responsible_field_name),
                [task_temp_estimated_days_field_name]: t.getCellValue(task_temp_estimated_days_field_name),
                [task_to_template_link_field_name]: [{ id: t.id }],
                [task_temp_group_field_name]: t.getCellValue(task_temp_group_field_name),
                [task_partner_revenue_service_link]: [{ id: p_id }]
            }
        };
        payloads.push(newTask);
    }

    // Create tasks in Airtable
    let createdTasks = await task_table.createRecordsAsync(payloads);
    createdTasks.forEach((task, index) => {
        tempToNewTaskMap[task_temp_ids[index]] = task.id;
    });

    // Store task mappings for dependency updates
    Object.assign(newTaskIdMap, tempToNewTaskMap);

    // Update Newsrooms table to mark task creation as complete
    let updateField = task_creation_fields[p.name];
    if (updateField) {
        await project_table.updateRecordAsync(record.id, { [updateField]: true });
    }
}

// Update dependencies for the newly created tasks
for (let [oldTaskId, newTaskId] of Object.entries(newTaskIdMap)) {
    let dependency = task_temp_dependency_map[oldTaskId];
    if (dependency && newTaskIdMap[dependency]) {
        await task_table.updateRecordAsync(newTaskId, {
            [task_dependency_field_name]: [{ id: newTaskIdMap[dependency] }]
        });
    }
}

output.markdown(`### Done creating tasks and updating dependencies`);

 

 

 

 

3 Replies 3

Any chance you could provide access to a testing table where all the fields and the script are set up?  Would make it a lot easier to help you troubleshoot your code!

Hm, I just tried this out and it errors out at line 81 as some of the variables are undefined.  Could I confirm that this script works and you need to add functionality to it?  If so, could you provide the working script that is able to create tasks but cannot add the dependencies? 

If you don't have much development experience, you may want to look into using a Record Template instead as it'd do what you need as well I think