Help

Re: Run "Create Records from Multiple Select" Script across many rows at once

962 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Scott_Bergman
4 - Data Explorer
4 - Data Explorer

I am using the “Create records from multiple select” script.

I have a table named “Notes” with 1500 rows. There are two relevant columns. One column is a text field named “Description”. The second column is a multi-select field named “Categories.”

I want to create a new table which has a unique row for each Category-Description combination. I effectively got your script to work for one record at a time by pressing “Pick a record.”

Rather than using “Pick a record” to run the script on each of the 1500 rows separately, is there a way to run the script across all 1500 rows at once, to create thousands of Category-Description rows?

I am open to modifying the code, but was curious if there was a known solution before attempting that task.

Thank you!

1 Reply 1
Scott_Bergman
4 - Data Explorer
4 - Data Explorer

For anyone looking to do the same, I just wrote the code myself and submitted it to the script community. It’s waiting on approval, but in the mean time…

// Script settings
let s = input.config({
    title: '〽️ Create records from multiple 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('delivField', {
            parentTable: 'tableSource',
            label: '🍂 Multiple select field with names of records to create',
        }),
        // Source view: View to limit records to
        input.config.view('viewSource', {
            parentTable: 'tableSource',
            label: '🍂 Specify a view to limit records to',
        }),
        // 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)',
        }),
    ]
});


// query for all the records in a view
let table = s.tableSource;
let view = table.getView(s.viewSource.name);
let queryResult = await view.selectRecordsAsync({fields: [s.delivField]});
// print ID & "Description" from each record:
console.log('Records to parse through : ' + queryResult.records.length);


for (let record of queryResult.records) {
    
    //If you want to stop it short to test something
    // if (record.id === "rec8d9R5Mok1tMGA3") {
    //     break;
    // } 

    // Gets the desired # of records to create, and deliverable names, based on the fields chosen in the script settings
    let recToCreate = s.delivField.options?.choices.length;
    let deliverables = record.getCellValue(s.delivField.name);

    // Variables to store the applicable deliverable names and length (total records to create)
    let delivNames = [];
    let length = 0;

    if (deliverables) {
        // Creates record names from 'deliverables' multiple select field, if any
        for (let item of deliverables) {
            delivNames.push({
                'Name': item.name
            })
        }
        length = delivNames.length

        // Preview records to create, prompt user to confirm creation
        output.markdown('Create ' + length + ' records for **' + record.name + '**?');

        //To see the table of each record created
        output.table(delivNames);
        
        //To look at one record at a time
        //await input.buttonsAsync('', [{ label: 'Create records', value: 'Create records', variant: 'primary' }]);

        // 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: record.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 ✅
        // `);
    } else {
        output.markdown(`**No deliverables chosen for this record. Make selections in the ${s.delivField.name} field first.**`)
    };

//     console.log(`
// **${record.id}**
// ${record.getCellValueAsString(s.delivField)}
// `);
}