Using "create records using multiple templates" to auto-link child record to another child record

Forum|Forum|2 years ago
May 3, 2023
2 replies
115 views

+3

Teagan_Clark
New Participant

Hi there! I'm getting some templated tasks set up for my team using the "create records using multiple templates" extension. Very useful! I've gone into the code and begun adding some additional fields to auto-populate. The simple ones were easy, but I have something trickier I'd like to pull off, and I'm unsure if it's possible.

These tasks can be dependent on another task. I'd love to be able to auto-populate that dependent task into that column as a linked record. But code wise, I'm imagining that it would somehow need to reference the other created records, pull the record ID, and populate that into the "Dependent Task" column. Any idea if this might be possible? This puts me a bit out of my depth as a low-coder 😅 Thanks for your consideration!

Here's the current part of the code, for reference:

// 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),
    'templateStatus': {name: c.getCellValueAsString('Status')},
    'templateDueDays': c.getCellValue('numDaysBeforeDue'),
    'templateNotes': c.getCellValue('Notes')
    // 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,
        'Status': c.templateStatus,
        'Due Date Calculation Number': c.templateDueDays,
        'Notes': c.templateNotes
        // 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
    }

+31

TheTimeSavingCo
Brainy
Forum|Forum|2 years ago
May 4, 2023

Hmm, the only way I can think of making this work is if you can set up your table and script so that your tasks are linked to a specific project. If you can, then you could try:
1. Creating another field in the "Tasks" table that would contain the name of the task that this task would be linked to called "Child task name" or something, and updating your script to put the name of the child task in there
2. Having an automation that would trigger if this field was not empty AND the linked field for the child task was empty, and the automation would:
- Do a "Find Record" action to look for a task within the same project and has the correct name, e.g. Task 2 from Project A
- Update the triggering record by linking it to the found record

Without a unique project to tie the tasks to it would be difficult for you to identify the specific dependent task you need to link as there would be multiple records with the same task name you know what I mean?

Adam, Airtable Consultant - thetimesaving.company

Like

+3

Teagan_Clark
Author
New Participant
Forum|Forum|2 years ago
May 17, 2023

Hmm, the only way I can think of making this work is if you can set up your table and script so that your tasks are linked to a specific project. If you can, then you could try:
1. Creating another field in the "Tasks" table that would contain the name of the task that this task would be linked to called "Child task name" or something, and updating your script to put the name of the child task in there
2. Having an automation that would trigger if this field was not empty AND the linked field for the child task was empty, and the automation would:
- Do a "Find Record" action to look for a task within the same project and has the correct name, e.g. Task 2 from Project A
- Update the triggering record by linking it to the found record

Without a unique project to tie the tasks to it would be difficult for you to identify the specific dependent task you need to link as there would be multiple records with the same task name you know what I mean?

I like this idea! The script does tie all the tasks to a unique project, so I think an automation could be the answer here.

I am running into an issue where the script doesn't want to recognize the name of the task in the template to link in order to add it to the task tab. I assume it's because they are two different types of columns (template is a Look Up field, while the task tab has it as a single line text column) but I'm not sure what I can do.

Current working code:

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),
    'templateStatus': {name: c.getCellValueAsString('Status')},
    'templateDueDays': c.getCellValue('numDaysBeforeDue'),
    'templateNotes': c.getCellValue('Notes'),
    'templateDupe': {name: c.getCellValueAsString('Reoccurring Task?')}
    // 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,
        'Status': c.templateStatus,
        'Due Date Calculation Number': c.templateDueDays,
        'Notes': c.templateNotes,
        'Reoccurring Task?': c.templateDupe
        // 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);
    }
}

What I've tried adding:

//template record attempt # 1
'templateDependent': {name: c.getCellValueAsString('dependentLookUp')}

//template record attempt # 2
'templateDependent': c.getCellValue('dependentLookUp')

//task record
'dependentLookUp': c.templateDependent

The error:

ERROR

j: Can't create records: invalid cell value for field 'dependentLookUp'. Cell value has invalid format: <root> must be a string

I'm a very new javascripter, so any ideas are appreciated!

I've tried it with dependentLookUp being both a lookup field, and as it as a formula field that displays the lookup.

Like

ERROR

Sign up

Login to the community

Scanning file for viruses.

This file cannot be downloaded