Hello,
I am new here. I’m a French person, so please pardon in advance if my English is not as it should.
I have been in 3 days and not managing to get to a solution.
I use the template Script Create records for multiple templates from Airtable templates.
It used to work super fine.
I have a table called TypesMission that list all the sorts of Missions I have to manage (like Projects).
Then there is a template table for the Missions, called ModelesdeMission from which the data come and generate records in the Taches (tasks) table.
The script runs from a button field in the Missions table (junction table linking the clients to the tasks).
I am trying to upgrade the script to enable me to copy by program 2 fields in the child table : one is a singleselect, the other a link field. This would allow me to further automatize my workflow.
In singleselect I store the ‘Responsible person by default’, I would like to have it copied for each task created
in the link field I store the Dependency field link (to be able to manage begin/end dates of my tasks automatically though automation). Please note that the first line of a block of task has no dependency because it is the number 1 task. This empty value seems to be an issue when copying values.
Here is the script. It is mostly derived from this template script provided by Airtable
Here are the settings made
Thanks for helping me understand and solve what I do not see !
Christophe
// 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: t
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)'
}),
input.config.field('templateAssigneeField', {
parentTable: 'templateTable',
label: 'Template Record Assignee Field',
description: 'Template field that gives the responsible operator name of Task record'
}),
input.config.field('assigneeFieldInChild', {
parentTable: 'childTable',
label: 'Assignee field in child table',
description: 'Assignee in child table to indicate who is in charge'
})
input.config.field('TemplateDependenceField', {
parentTable: 'templateTable',
label: 'Template Record Dependency Field',
description: 'Template field that gives the name of previous Task record'
}),
input.config.field('DependencyFieldInChild', {
parentTable: 'childTable',
label: 'Dependency field in child table',
description: 'Linked record in child table to record related previous 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;
let templateAssigneeField =settings.templateAssigneeField;
let assigneeFieldInChild = settings.assigneeFieldInChild;
let TemplateDependenceField = settings.TemplateDependenceField;
let DependencyFieldInChild = settings.DependencyFieldInChild;
// guard clauses in case settings are not valid
if (assigneeFieldInChild.type != "singleSelect") {
output.markdown(`Check settings: ${assigneeFieldInChild.name} is not a single-select field.`)
output.markdown(`# Record NOT updated.`)
return
}
else if (!(assigneeFieldInChild.options?.choices.map(choice => choice.name).includes(templateAssigneeField.choices))) {
output.markdown(`Check settings: ${assigneeFieldInChild.name} does not include ${templateAssigneeField} as an option.`)
output.markdown(`# Record NOT updated.`)
return
}
}
// Select parent record to create child records & select type
let selectedEvent = await input.recordAsync('Choose record', parentTable);
while (!selectedEvent) {
output.text('You must select a record.');
selectedEvent = await input.recordAsync('Choose record', parentTable);
}
let parentType = selectedEvent.getCellValue(settings.parentType);
// 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': ic],
'childName': c.getCellValue(childFieldInTemplate),
'templateTypes': c.getCellValue(templateType).map(x => x.id),
'templateOrder': c.getCellValue(templateOrder),
'TemplateDependenceField': c.getCellValue(TemplateDependenceField)
// 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 => x.templateTypes.includes(parentTypen0].id))
// 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]: aselectedEvent],
childOrder.name]: c.templateOrder,
DependencyFieldInChild.name]: c.TemplateDependenceField
// 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.fieldsfchildOrder] - b.fieldsfchildOrder];
});
if (selectedEvent) {
// create records in batches of 50
while (createRecords.length > 0) {
await childTable.createRecordsAsync(createRecords.slice(0, 50));
createRecords = createRecords.slice(50);
//update Assignee filed inchild table
// submit the request to update the record
await childTable.updateRecordAsync(record, {
assigneeFieldInChild.id]: {name: templateAssigneeField},
});
output.markdown(`# Updated ${assigneeFieldInChild.name} of ${record.name} to be ${templateAssigneeField}`);
}
}
output.text('Done!');