Solved

Modify "Create multiple records" script so it can be included in an automation

2 years ago
March 28, 2023
6 replies
12 views

Ann_Yeom
Known Participant
17 replies

Hi,

I've been trying to modify the Airtable script (Create records from multiple select) pasted below into an automation as an action. I was thinking removing the input commands and replacing the table/field location to my specific fields would work but having more trouble than I thought.

Any one able to help me out? I'm looking to modify the script below so I can include it in an automation.

The tables and fields would stay constant.

// 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',
        }),
        // 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.delivField.options?.choices.length;
let deliverables = r.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 **' + r.name + '**?');

    output.table(delivNames);
    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: r.id }]
            }
        })

    };

    // Batches the creation
    while (dToCreate.length > 0) {
        await s.tableDest.createRecordsAsync(dToCreate.slice(0, 50));
        dToCreate = dToCreate.slice(50);

Best answer by Alexey_Gusev

Ann_Yeom wrote:

Hi @Alexey_Gusev ,

Sure. The automation trigger, is when a record enters a view and the action is to run a script.

The script I was trying to modify... looks at the source table's multi-select field, its values for each row and creates a record in another table for each multi-select option selected, and links the new records back to the source record.

Here is the input fields and description of the extension I was trying to modify

So, I'm trying to accomplish this...
When an initiative enters this view

Trigger a script that will create a record in Table 2 for each "Project" selected for an "Initiative" in Table 1 and link back to Table 1. Note the primary field in Table 2 is a formula field.

Hope that is clearer. Thanks!

So, that's exactly what I thought. Except formula primary field in table 2, but that doesn't matter. I did test runs with checkbox

Input variables

result looks similar, just double check that table2 and field names in code match existing names

View original

Did this topic help you find an answer to your question?

+23

Alexey_Gusev
Brainy
1124 replies
2 years ago
March 28, 2023

Hi,

Could you please describe what this script doing and how it suppose to be triggered in your case?
From description at the beginning of script it does the same thing, as if you copy-paste the value of a multiselect field to the linked field of the same record.

Anyway, suppose you need to create records in 'table2' from value of multiselect field (list of options), one record for each in field 'Name' and link all them to 'parent' record.

Pass value as 'list', and record id as ID.
this script will create the needful

const {ID,list} = input.config();
const crt=list.map(x=>({fields:{'Name':x,'Link':[{'id':ID}]}}))
await base.getTable('table2').createRecordsAsync(crt.splice(0,50))

+20

Ben_Young1
Brainy
520 replies
2 years ago
March 28, 2023

Huh... that's an interesting script. I've always seen it, but I've never actually used it or taken a look at the code itself.

It's not worth your time to refactor that script, and it appears more complicated than it actually is.
Here's a quick look at how I would think of approaching this.

Here's the multi-select field I'm starting with, as well as the field options:

Here's my "target" table where I want to create my new records:

Super simple. Just a single field.
And here's the script itself:

// The table that contains the existing multi-select field.
const sourceTable = base.getTable("tableId");
// The table where we are writing the new records to.
const targetTable = base.getTable("tableId");

// Returns an array of each multiple select option from the specified field.
const fieldOptions = sourceTable
    .getField("fieldId")
    .options
    .choices
    .map(choice => choice.name.trim())

// Returns an array of record objects to create.
let recordsToCreate = fieldOptions.map(option => ({
    fields: {
        // Map the fields/values for the records you want to create here.
        "fieldId": option
    }
}));


if (recordsToCreate) {
    while (recordsToCreate.length > 50) {
        await targetTable.createRecordsAsync(recordsToCreate.slice(0, 50));
        recordsToCreate = recordsToCreate.slice(50);
    }
    await targetTable.createRecordsAsync(recordsToCreate);
}

Here's the result:

Another interesting little feature we can write in is a filter that will make sure that we only create records for each option that doesn't already exist in the table.

So if I add five new options to the field:

I can change the script to something like this:

// The table that contains the existing multi-select field.
const sourceTable = base.getTable("tableId");
// The table where we are writing the new records to.
const targetTable = base.getTable("tableId");

// Returns an array of each multiple select option from the specified field.
const fieldOptions = sourceTable
    .getField("fieldId")
    .options
    .choices
    .map(choice => choice.name.trim())

const uniqueOptions = fieldOptions
// Insert the id of the field in your target table that contains the name of the existing options.
    .filter(option => existingRecords.findIndex(record => record.getCellValueAsString("fieldId") === option) === -1)


// Returns an array of record objects to create.
let recordsToCreate = fieldOptions.map(option => ({
    fields: {
        // Map the fields/values for the records you want to create here.
        "fieldId": option
    }
}));


if (recordsToCreate) {
    while (recordsToCreate.length > 50) {
        await targetTable.createRecordsAsync(recordsToCreate.slice(0, 50));
        recordsToCreate = recordsToCreate.slice(50);
    }
    await targetTable.createRecordsAsync(recordsToCreate);
}

This was fun. Let me know if you're having trouble with something or if you want to add additional functionality or complexity to this.

Ann_Yeom
Author
Known Participant
17 replies
2 years ago
March 28, 2023

Alexey_Gusev wrote:

Pass value as 'list', and record id as ID.
this script will create the needful

const {ID,list} = input.config();
const crt=list.map(x=>({fields:{'Name':x,'Link':[{'id':ID}]}}))
await base.getTable('table2').createRecordsAsync(crt.splice(0,50))

Hi @Alexey_Gusev ,

Sure. The automation trigger, is when a record enters a view and the action is to run a script.

Here is the input fields and description of the extension I was trying to modify

So, I'm trying to accomplish this...
When an initiative enters this view

Trigger a script that will create a record in Table 2 for each "Project" selected for an "Initiative" in Table 1 and link back to Table 1. Note the primary field in Table 2 is a formula field.

Hope that is clearer. Thanks!

Ann_Yeom
Author
Known Participant
17 replies
2 years ago
March 28, 2023

Ben_Young1 wrote:

Huh... that's an interesting script. I've always seen it, but I've never actually used it or taken a look at the code itself.

It's not worth your time to refactor that script, and it appears more complicated than it actually is.
Here's a quick look at how I would think of approaching this.

Here's the multi-select field I'm starting with, as well as the field options:

Here's my "target" table where I want to create my new records:

Super simple. Just a single field.
And here's the script itself:

// The table that contains the existing multi-select field.
const sourceTable = base.getTable("tableId");
// The table where we are writing the new records to.
const targetTable = base.getTable("tableId");

// Returns an array of each multiple select option from the specified field.
const fieldOptions = sourceTable
    .getField("fieldId")
    .options
    .choices
    .map(choice => choice.name.trim())

// Returns an array of record objects to create.
let recordsToCreate = fieldOptions.map(option => ({
    fields: {
        // Map the fields/values for the records you want to create here.
        "fieldId": option
    }
}));


if (recordsToCreate) {
    while (recordsToCreate.length > 50) {
        await targetTable.createRecordsAsync(recordsToCreate.slice(0, 50));
        recordsToCreate = recordsToCreate.slice(50);
    }
    await targetTable.createRecordsAsync(recordsToCreate);
}

Here's the result:

Another interesting little feature we can write in is a filter that will make sure that we only create records for each option that doesn't already exist in the table.

So if I add five new options to the field:

I can change the script to something like this:

// The table that contains the existing multi-select field.
const sourceTable = base.getTable("tableId");
// The table where we are writing the new records to.
const targetTable = base.getTable("tableId");

// Returns an array of each multiple select option from the specified field.
const fieldOptions = sourceTable
    .getField("fieldId")
    .options
    .choices
    .map(choice => choice.name.trim())

const uniqueOptions = fieldOptions
// Insert the id of the field in your target table that contains the name of the existing options.
    .filter(option => existingRecords.findIndex(record => record.getCellValueAsString("fieldId") === option) === -1)


// Returns an array of record objects to create.
let recordsToCreate = fieldOptions.map(option => ({
    fields: {
        // Map the fields/values for the records you want to create here.
        "fieldId": option
    }
}));


if (recordsToCreate) {
    while (recordsToCreate.length > 50) {
        await targetTable.createRecordsAsync(recordsToCreate.slice(0, 50));
        recordsToCreate = recordsToCreate.slice(50);
    }
    await targetTable.createRecordsAsync(recordsToCreate);
}

This was fun. Let me know if you're having trouble with something or if you want to add additional functionality or complexity to this.

Thanks Ben!!
One question, how do we add to the script to link the new records created in the "Target table" back to the "Source table"?

+23

Alexey_Gusev
Brainy
1124 replies
Answer
2 years ago
March 28, 2023

Ann_Yeom wrote:

Hi @Alexey_Gusev ,

Sure. The automation trigger, is when a record enters a view and the action is to run a script.

Here is the input fields and description of the extension I was trying to modify

So, I'm trying to accomplish this...
When an initiative enters this view

Trigger a script that will create a record in Table 2 for each "Project" selected for an "Initiative" in Table 1 and link back to Table 1. Note the primary field in Table 2 is a formula field.

Hope that is clearer. Thanks!

So, that's exactly what I thought. Except formula primary field in table 2, but that doesn't matter. I did test runs with checkbox

Input variables

result looks similar, just double check that table2 and field names in code match existing names

Ann_Yeom
Author
Known Participant
17 replies
2 years ago
March 28, 2023

Alexey_Gusev wrote:

So, that's exactly what I thought. Except formula primary field in table 2, but that doesn't matter. I did test runs with checkbox

Input variables

result looks similar, just double check that table2 and field names in code match existing names

Works great! Thank you @Alexey_Gusev 😊

Thank you @Ben_Young1 as well!

Modify "Create multiple records" script so it can be included in an automation

6 replies

Reply

Most helpful members this week

Reply

Related topics

Easy way to import .reg file?icon

Basic Dell Command Update Workleticon

Automated Way to Group Devices

About Vulnerability Sync

Top Community Worklets

Most helpful members this week

Sign up

Login with SSO

Login to the community

Login with SSO

Scanning file for viruses.

This file cannot be downloaded