Date field setup for Create records for multiple templates script

Hi,

insert 4 lines

output.inspect(createRecords)
output.text(’=========’)
output.inspect(typesRecords)
let debug=await input.buttonsAsync(‘press to’,[‘continue’])

if (selectedEvent) {
// >>>HERE <<<
while (createRecords.length > 0) {

if there are too many records, you can use “output.inspect(createRecords).slice(0,5)” or whatever.
expand any record and try to detect what’s wrong with format.
I never used this script, and usually skip or convert Data, but if you won’t be able to fix, put a piece of screenshot here, with expanded record(s)

btw,

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.”)
}

JFYI, Set data type, storing unique set of values, is very useful for such operations:

let check=new Set([typesTable, childTable, templateTable,parentTable]);
if check.size<4 throw new Error…

Thanks for your advice @Alexey_Gusev.

As a non-technical user I feel challenged by this. I inserted the four lines of code 1:1 into the respective section of the code but receive a new error:

ERROR
SyntaxError: Invalid or unexpected token
    on line 1
    at o on line 1
    at Generator._invoke on line 1
    at Generator.F.forEach.u.<computed> [as next] on line 1
    at t on line 1
    at a on line 1
    on line 1
    on line 1

The cause is probably as simple as a variable, bracket, comma or semicolon…

I’m copying the entire code below, it’s the original script plus four fields I added manually.

// 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('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),
    'templateDate':c.getCellValue('Date'),
    'templateDeparture':c.getCellValue('Departure'),
    'templateService':c.getCellValue('Service'),
    'templateDays':c.getCellValue('Days')
    // 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,
        'Date':c.templateDate,
        'Departure':c.templateDeparture,
        'Service':c.templateService,
        'Days':c.templateDays
        // 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
    output.inspect(createRecords)
    output.text(’=========’)
    output.inspect(typesRecords)
    let debug=await input.buttonsAsync(‘press to’,[‘continue’])
    while (createRecords.length > 0) {
        await childTable.createRecordsAsync(createRecords.slice(0, 50));
        createRecords = createRecords.slice(50);
    }
}

output.text('Done!');

The template’s table four manual fields, the date field throws the error.

Single expanded record’s relevant fields.

The child table where the records are created.

Thanks for helping out a JavaScript dummy. :winking_face:

ERROR
SyntaxError: Invalid or unexpected token
    on line 1
    at o on line 1
    at Generator._invoke on line 1
    at Generator.F.forEach.u.<computed> [as next] on line 1
    at t on line 1
    at a on line 1
    on line 1
    on line 1

// 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('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),
    'templateDate':c.getCellValue('Date'),
    'templateDeparture':c.getCellValue('Departure'),
    'templateService':c.getCellValue('Service'),
    'templateDays':c.getCellValue('Days')
    // 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,
        'Date':c.templateDate,
        'Departure':c.templateDeparture,
        'Service':c.templateService,
        'Days':c.templateDays
        // 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
    output.inspect(createRecords)
    output.text(’=========’)
    output.inspect(typesRecords)
    let debug=await input.buttonsAsync(‘press to’,[‘continue’])
    while (createRecords.length > 0) {
        await childTable.createRecordsAsync(createRecords.slice(0, 50));
        createRecords = createRecords.slice(50);
    }
}

output.text('Done!');

HI,

sorry, i didn’t realize you are not familiar with JS
and it’s my fault a bit, I forget that using Blockquote tag turns
“correct brackets”:

to “incorrect”

When copy-pasting, they turned to

And that’s weird because I suppose that Blockquote main purpose - to highligt source code, but not to corrupt it :grinning:
Well, maybe i missed or don’t know something here yet.

anyway, you can delete three lines, and leave just
output.inspect(createRecords),
others are not so important in that case
but maybe your info is enough already, I’m in hurry now, but will take a closer look within a hour…

UPD: Well, i’m out of ideas. i even can’t reproduce your error. Maybe somebody who used this script, may help.

Thanks for your help, unfortunately this didn’t solve the issue.

output.inspect(createRecords),

With a closing comma (,) it errors with:

ERROR
SyntaxError: Unexpected token 'while'
    on line 1
    at o on line 1
    at Generator._invoke on line 1
    at Generator.F.forEach.u.<computed> [as next] on line 1
    at t on line 1
    at a on line 1
    on line 1
    on line 1

When I replace the comma with a semicolon or delete it, the previous error returns.

ERROR
j: Can't create records: invalid cell value for field 'Date'.
Cell value has invalid format: <root>.0 must be a string, <root>.1 must be a string, <root>.2 must be an object
    at main on line 131

Script section:

if (selectedEvent) {

    // create records in batches of 50

    output.inspect(createRecords);

    while (createRecords.length > 0) {

        await childTable.createRecordsAsync(createRecords.slice(0, 50));

        createRecords = createRecords.slice(50);

    }

}

The inspect field outputs the following. When I use the four lines of code (instead of only inspect), I get to the continue field, a longer list of objects and then the same error as above.

(38) [Object, Object, Object, Object, Object, Object, Object, Object, Object, Object, …]
0: Object
1: Object
2: Object
3: Object
4: Object
5: Object
6: Object
7: Object
8: Object
9: Object
10: Object
11: Object
12: Object
13: Object
14: Object
15: Object
16: Object
17: Object
18: Object
19: Object
20: Object
21: Object
22: Object
23: Object
24: Object
25: Object
26: Object
27: Object
28: Object
29: Object
30: Object
31: Object
32: Object
33: Object
34: Object
35: Object
36: Object
37: Object

Anyone who can assist with the date field problem? I

output.inspect(createRecords),

ERROR
SyntaxError: Unexpected token 'while'
    on line 1
    at o on line 1
    at Generator._invoke on line 1
    at Generator.F.forEach.u.<computed> [as next] on line 1
    at t on line 1
    at a on line 1
    on line 1
    on line 1

ERROR
j: Can't create records: invalid cell value for field 'Date'.
Cell value has invalid format: <root>.0 must be a string, <root>.1 must be a string, <root>.2 must be an object
    at main on line 131

if (selectedEvent) {

    // create records in batches of 50

    output.inspect(createRecords);

    while (createRecords.length > 0) {

        await childTable.createRecordsAsync(createRecords.slice(0, 50));

        createRecords = createRecords.slice(50);

    }

}

(38) [Object, Object, Object, Object, Object, Object, Object, Object, Object, Object, …]
0: Object
1: Object
2: Object
3: Object
4: Object
5: Object
6: Object
7: Object
8: Object
9: Object
10: Object
11: Object
12: Object
13: Object
14: Object
15: Object
16: Object
17: Object
18: Object
19: Object
20: Object
21: Object
22: Object
23: Object
24: Object
25: Object
26: Object
27: Object
28: Object
29: Object
30: Object
31: Object
32: Object
33: Object
34: Object
35: Object
36: Object
37: Object

that’s better but not much ))
you may remove or comment other three lines

every object here is an update for 1 record
i just wanted to see it’s structure, could you pls ‘open’ it (just one of 38, any)?
like this for #2, but if there any nested structure inside fields, open it also

Thanks for being as patient!

Here’s the complete and expanded log of one object - the structure of all is homogenic.

(38) [Object, Object, Object, Object, Object, Object, Object, Object, Object, Object, …]
0: Object
fields: Object
NameChild: null
Departure: Array(1)
0: Object
id: "recjErK0GHSVhfMQ9"
name: "BAPBT2201"
Day: 1
Date: Array(1)
0: "2022-06-04"
Service: Array(1)
0: Object
id: "recBwXV1McNA9YCfv"
name: "Guide "
Days: 12

output.inspect(createRecords),

ERROR
SyntaxError: Unexpected token 'while'
    on line 1
    at o on line 1
    at Generator._invoke on line 1
    at Generator.F.forEach.u.<computed> [as next] on line 1
    at t on line 1
    at a on line 1
    on line 1
    on line 1

ERROR
j: Can't create records: invalid cell value for field 'Date'.
Cell value has invalid format: <root>.0 must be a string, <root>.1 must be a string, <root>.2 must be an object
    at main on line 131

if (selectedEvent) {

    // create records in batches of 50

    output.inspect(createRecords);

    while (createRecords.length > 0) {

        await childTable.createRecordsAsync(createRecords.slice(0, 50));

        createRecords = createRecords.slice(50);

    }

}

(38) [Object, Object, Object, Object, Object, Object, Object, Object, Object, Object, …]
0: Object
1: Object
2: Object
3: Object
4: Object
5: Object
6: Object
7: Object
8: Object
9: Object
10: Object
11: Object
12: Object
13: Object
14: Object
15: Object
16: Object
17: Object
18: Object
19: Object
20: Object
21: Object
22: Object
23: Object
24: Object
25: Object
26: Object
27: Object
28: Object
29: Object
30: Object
31: Object
32: Object
33: Object
34: Object
35: Object
36: Object
37: Object

Haven’t looked too much into this yet but isn’t this part here giving you an infinite loop an/or a maximum call stack exceeded error? slice !== splice.

Slice and splice go beyond my horizon. :upside_down_face:

In general the script works fine, but the date field somehow breaks it.