Help

Script: Conditional Parent-child Record Creation

1051 0
cancel
Showing results for 
Search instead for 
Did you mean: 
JLindem
6 - Interface Innovator
6 - Interface Innovator

I've created a script that creates child records in a child table linked to parent records in a parent table through a linked field. It creates a set number of child records per parent record, if a set field in the parent record meets the conditions, depending on the number of records in a "count table" with a non-empty value in a designated "count field". It also supports populating a designated "category" field in the child table with the value of the "count field" in the "count table".

I originally created this for the translation of texts. The parent table hosts the English text, and when the workflow (designated by a single select field) moves to "Translate", the script checks a table listing the languages to be translated into, and creates a number of child records equal to the number of languages, and categorises the child-records based on the language.

It can be used for other systems where a set number of linked records need to be created automatically in a separate table based on criteria.

Here is the code:

//Defining settings of the script
const config = input.config({
        title: `Conditional Parent-child Record Creation`,
        description: `For records in a parent table with a matching value of a designated field, this script will create a
        number of "child" records in another table, where each "child" references the "parent" through a Linked Record field.
        \n The script creates a certain number of child-records per parent-record based on the number of records in the Count Table with
        non-empty values in the Count Field. The value of Count field is added to the records in the Child Table in the Category Field.`,
        items: [
            input.config.table('parentTable', {
                label: 'Parent Table',
                description: 'Select the parent table',
            }),
             input.config.field('fieldToCheck', {
                label: 'Field to check',
                description: 'Select the field containing the value to check',
                 parentTable: 'parentTable',
            }),
            input.config.text('valueToMatch', {
                label: 'Value to Match',
                description: 'Input the value to match records in "Field to check" against as a string',
            }),
            input.config.table('childTable', {
                label: 'Child Table',
                description: 'Select the child table where the linked record field to be populated is located',
            }),
            input.config.field('linkField', {
                label: 'Linked Field',
                description: 'Select the linked field within the child table where records are to be populated',
                parentTable: 'childTable',
            }),
            input.config.field('categoryField', {
                label: 'Category Field',
                description: 'Select the field where the category of each child-record should be populated. The category is fetched from the Count Field.',
                parentTable: 'childTable',
            }),
            input.config.table('countTable', {
                label: 'Count Table',
                description: `Select the table with the overview of the different linked child-records to create. For example, 
                if this table contains 10 records, the script will create 10 linked child-records per parent-record.`,
            }),
            input.config.field('countField', {
                label: 'Count Field',
                description: `Select the field to count records with non-empty values. 
                The values of this field are also used to categorise the linked child-records in the category-field.`,
                parentTable: 'countTable',
            }),
        ],
}); 

//Defining variables of the script by calling variables created from settings definition
const parentTable = config.parentTable;
const fieldToCheck = config.fieldToCheck;
const valueToMatch = config.valueToMatch;
const childTable = config.childTable;
const linkField = config.linkField;
const categoryField = config.categoryField;
const countTable = config.countTable;
const countField = config.countField;

//Log the valueToMatch to ensure correct input
console.log(valueToMatch);

//Function to count the number of records with a non-empty value in the countField
async function countRecordsWithNonEmptyValue(countTable, countField) {
    let query = await countTable.selectRecordsAsync();
    return query.records.filter(record => {
        let cellValue = record.getCellValue(countField);
        return cellValue !== null && cellValue !== undefined && cellValue.length > 0;
    }).length;
}

//Function to fetch the value from the count field to populate the category field
async function fetchValueOfCountField(countTable, countField) {
    let query = await countTable.selectRecordsAsync();
    return query.records.map(record => record.getCellValueAsString(countField));
}

//Function that creates the linked child-records
async function createChildrenLinkedRecords() {
    try {
        console.log('parentTable:', parentTable.name);
        console.log('fieldToCheck:', fieldToCheck.name);
        console.log('valueToMatch:', valueToMatch);
        console.log('childTable:', childTable.name);
        console.log('linkField:', linkField.name);

        if (
            linkField.type !== 'multipleRecordLinks' ||
            linkField.options?.linkedTableId !== parentTable.id
        ) {
            output.text ('Linked record field must be of type linked record to parent table.');
            return;
        }
        //Airtable limits batch operations to 50 records or fewer
        let maxRecordsPerCall = 50;

        //Using parentTable variable to get the field
        let field = parentTable.getField(fieldToCheck.name);
        let query = await parentTable.selectRecordsAsync();

        //Filter records based on the specified field value
        let parentRecords = query.records.filter(record => {
            let cellValueObject = record.getCellValue(field);
            let cellValueName = cellValueObject ? cellValueObject.name : null;
            return cellValueName === valueToMatch;
        });

        //Use the count of records with non-empty value from function countRecordsWithNonEmptyValue
        let newRecordCount = await countRecordsWithNonEmptyValue(countTable, countField);
        console.log('Count of records with non-empty values:', newRecordCount);

        //Get the values from the count field
        let countFieldValues = await fetchValueOfCountField(countTable, countField);
        console.log('Field values separated with commas:', countFieldValues.join(', '));

        //Iterate through the filtered parent records
        for (let parentRecord of parentRecords) {
            console.log ('Creating child records for parent:', parentRecord.id);

            //Part 1: Prepare new records
            let newRecords = countFieldValues.map(value=> {
                //Check whether the count field is empty or not
                if (value) {
                return {
                    fields: {
                        [linkField.id]: [{ id: parentRecord.id }],
                        [categoryField.id]: value,
                    },
                };
                }
                return null;
            }).filter(record => record !== null);

            //Part 2: Perform the new record operation in batches
            while (newRecords.length > 0) {
                        await childTable.createRecordsAsync(newRecords.slice(0, maxRecordsPerCall));
                        newRecords = newRecords.slice(maxRecordsPerCall);
                    }
                }
                output.text('Done');
    } catch (error) {
        //log the error message if an error occurs
        console.log('Error', error.message);
    }
}
await createChildrenLinkedRecords();

Hope you find it useful!

0 Replies 0