Thanks @Justin_Barrett , I finally manage to do it. You teach me how to fish :grinning_face_with_big_eyes:
// Script settings
let s = input.config({
title: 'Create records by concatenating two columns (single text and multi-select) field options',
description: 'Creates 1 record in another table for each option in a multiple select field in the source table, and links the new records back to the source record.',
items: [
// Source table select
input.config.table('tableSource', {
label: '🍂 Table with existing records'
}),
// Source table: Multiple select field with deliverables
input.config.field('tempField', {
parentTable: 'tableSource',
label: '🍂 Multiple select field with names of records to create',
}),
// Source table: Multiple select field with deliverables
input.config.field('monthField', {
parentTable: 'tableSource',
label: '🍂 Single text or field with month name to create',
}),
// Destination table select
input.config.table('tableDest', {
label: '🌱 Table to create new records in'
}),
// Destination table: Name or title field
input.config.field('destinationField', {
parentTable: 'tableDest',
label: '🌱 Deliverable name or title field in destination table',
}),
// Destination table: Linked record field (back to the Source table record)
input.config.field('projField', {
parentTable: 'tableDest',
label: '🌱 Linked record field (links to table with existing records)',
}),
]
});
// You can use a button field that points to this script to run it for specific records,
// or you can run the script directly, in which case the following prompts the user to pick a record
let r = await input.recordAsync('Pick a record', s.tableSource);
while (!r) {
output.text('You must select a record.');
r = await input.recordAsync('Pick a record', s.tableSource);
}
// Gets the desired # of records to create, and deliverable names, based on the fields chosen in the script settings
let recToCreate = s.tempField.options?.choices.length;
let templateName = r.getCellValue(s.tempField.name);
let monthName = r.getCellValue(s.monthField.name);
templateName.forEach(item => console.log(`${monthName}; ${item.name}`));
let combinedArray = templateName.map(item => `${monthName}; ${item.name}`);
console.log(combinedArray);
output.table(combinedArray);
await input.buttonsAsync('', [{ label: 'Create records', value: 'Create records', variant: 'primary' }]);
let length = 0;
length = combinedArray.length
let delivNames = [];
for (let item of combinedArray) {
delivNames.push({
'Name': item
})
}
length = delivNames.length
// Create records
let dToCreate = [];
for (let i = 0; i < length; i++) {
let name = delivNames[i].Name
dToCreate.push({
fields: {
[s.destinationField.id]: name,
[s.projField.id]: [{ id: r.id }]
}
})
};
// Batches the creation
while (dToCreate.length > 0) {
await s.tableDest.createRecordsAsync(dToCreate.slice(0, 50));
dToCreate = dToCreate.slice(50);
}
// Output confirmation for the user
output.markdown(`
${length} records created ✅
`);
