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!
