Cannot update Singleselect and link field from template table

[Christophe_RENA]

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: [
        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': [c],
    '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(parentType[0].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]: [selectedEvent],
        [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.fields[childOrder] - b.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);
    //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!');

[Alexey_Gusev]

Hi,

You forget the most important part - what’s the problem?
I mean - what happens now, when you press button?

if the error message not helpful, insert this:
output.inspect(createRecords) // create records in batches of 50

in such way:

if (selectedEvent) {
 output.inspect(createRecords)    // create records in batches of 50
    while (createRecords.length > 0) {

(optionally) temporary change sign to ‘less than zero’ < 0 to prevent write
you will see an object createRecords, expand it and expand one fields object

reply with screenshot

singleselect must be updated with object {name: 'option_name'}
(option_name should be already present in available options)
link should be updated by array of {id: 'rec_id'} objects or by empty array to clear
with your screenshoot we can see what’s wrong

[Christophe_RENA]

Hello Aleksey
thanks for helping.
I found a nice lesson in this video and I solved my problem !
https://youtu.be/zHQjljEov30

It works fine. The small change would be to write the code for inputting the value of the active field instead of prompting the user if I want to use a Button field.

I post my code below for sharing with the community.
The only bit is that I do not find the right logic to copy the dependency link.
This field is a reference to the previous task in an order. I use it in an automation to update the start date of the forthcoming task when the previous one is complete. The automation copies the End Date into the Start Date of the forthcoming task.
I would like to also populate this link field from the template, but have not found yet how to code this.

Thanks a lot. Regards.
Christophe

//Comments in French followed by English
//sélectionner une mission
// l'utilisateur choisit la Mission qui a besoin de voir ses tâches remplies
//Select a mission
//The user selects the mission for which tasks must be created
// Table "Missions" keeps all missions and clients (like Projects)
// The view Missions without tasks filters out the populated missions.

    let missions = base.getTable("Missions");
    let missionsview = missions.getView("Missions sans tâche");

//the line below will be replaced with a input from the active record in the "Missions" table

    let Mission = await input.recordAsync("Choisir la mission à développer",missionsview);
    //console.log(Mission);

//Trouver les tâches en modèle correspondant à la mission sélectionnée
// En premier il faut charger tous les enregistrements de la table ModelesdeMission
// Find the tasks in the Template that match the selected type of mission
// As a first step, all records pertaining to the Template table are loaded
// Table ModelesdeMission keeps the templates for every type of missions existing

    let tableModelesdeMission = base.getTable("ModelesdeMission");
    let AllModelesdeMission = await tableModelesdeMission.selectRecordsAsync();
    //console.log(touslesModelesdeMission);

// En deuxième, il faut filtrer pour ne conserver que le type de mission sélectionnée
// As a second step, we filter out the non relevant records and keep only the ones matching the Mission selected
//Table TypesMission lists all type of Missions existing

    let TachesdeMissions = AllModelesdeMission.records.filter( Modeledemission => {
        return Modeledemission.getCellValueAsString("TypesMission") === mission.getCellValueAsString("TypeMission")
    });
    //console.log(TachesdeMissions);

// En troisièmme, il faut dupliquer les enregistrements dans la table des tâches actives
// In a 3rd step, we create an array with the corresponding format to the Child Table (here the Task Table)
// and we bring back the values from the Template table (ModelesdeMission)

    let TachesUtiles = TachesdeMissions.map(Modeledemission => {
        return {
            fields:{
                "NomdeTache" : Modeledemission.getCellValue("NomTache"),
                "Responsable":{
                    name: Modeledemission.getCellValue("Responsable par défaut").name
                            },
                "Mission (lien)": [Mission],
                }
                }});
    //console.log(TachesUtiles);

// Copier les données dans la table des Taches
// Copy this array TachesUtiles in the Taches table

    let tableTaches = base.getTable("Taches") ;
    await tableTaches.createRecordsAsync(TachesUtiles);

[Alexey_Gusev]

Hi,

return {
            fields:{
                "NomdeTache" : Modeledemission.getCellValue("NomTache"),
                "Responsable":{
                    name: Modeledemission.getCellValue("Responsable par défaut").name
                            },
                "Mission (lien)": [Mission],
                }
                }});

You should set it here, together with your fields in such format:

"LinkFieldName": [ {'id': record_id_to_link } ]

[Christophe_RENA]

Thanks a lot Alexey. Appreciate. Keep well.