Help

Re: Running a script with settings in Automations

Solved
Jump to Solution
2923 0
cancel
Showing results for 
Search instead for 
Did you mean: 
David_Ohanessia
6 - Interface Innovator
6 - Interface Innovator

Hello everyone,

I’m very new to Airtable and also a non-coder. I’m trying to launch a script as an automation’s action, which is pretty straightforward.

However, the script I’m trying to launch needs settings to be populated before each launch, ask for a confirmation before launch and therefore the script needs to be run manually.

However, those settings are now fixed in my base so i would amend the script code directly to reflect those. I don’t know how to proceed. This script is part of this function :

https://www.airtable.com/universe/expK9hEwh5d1IGkH3/splitting-multiple-selectlinks-into-individual-r...

Here is the code :

let settings = input.config({
    title: "Create junction table (keep multiselect)",
    description: `Running this script will populate a junction table that contains every possible combination of linked
 records from two tables. Both tables must contain a linked field to the junction table being populated.`,
    items: [
        input.config.table("table1", { label: "First table name" }),
        input.config.field("multipleSelect", { parentTable: 'table1', label: "Multiselect field name", description: 'The multiple select field we want to split into multiple records' }),
        input.config.table("junctionTable", {
            label: "Junction table name",
        }),
        input.config.field("firstJunctionField", {
            parentTable: "junctionTable",
            label: "First junction field name",
            description: "Linked record to first table",
        }),
        input.config.field("secondJunctionField", {
            parentTable: "junctionTable",
            label: "Second junction field name",
            description: "Text field to hold second join value",
        }),
        input.config.field("secondJunctionFieldId",{
            parentTable: 'junctionTable',
            label: 'Second junction ID field name',
            description: 'The field in the junction table that holds the multiselect ID'
        }),
        input.config.select('joinType', {
            label: 'Join Type',
            description: 'Determines how to join records together.  Inner will provide every combination between both tables.  Left will only create records determined by the Link from Table 1 to Table 2 field',
            options: [
                {label: 'Left', value: 'left'},
                {label: 'Inner', value: 'inner'}
            ]
        })
    ],
});

async function createJunction() {
    let {
        table1 = base.getTable("Bailleurs"),
        multipleSelect =,
        junctionTable,
        firstJunctionField,
        secondJunctionField,
        secondJunctionFieldId
    } = settings;

    if (table1 === junctionTable) {
        output.text("First table can't be the same as the junction table.");
        return;
    }
    
    if (firstJunctionField === secondJunctionField) {
        output.text("First junction field can't be the same as the second junction field.")
        return;
    }
    if (firstJunctionField.type !== "multipleRecordLinks" ) {
        output.text(
            "First junction field should be of linked record type."
        );
        return;
    }


    let existing = Object.create(null);
    let toCreate = [];
    let toDelete = [];
    // Airtable limits batch operations to 50 records or fewer.
    let maxRecordsPerCall = 50;

    // Part 1: determine the necessary operations.
    //
    // We don't modify the table contents in this Part in the interest of
    // efficiency. This script may trigger a large number of database
    // modifications, and it's much faster to request that they be done in batches.
    // When we identify a record that should be created or deleted, we add it to
    // the appropriate array so we can batch the operations in Part 2 of the
    // script.

    let query3 = await junctionTable.selectRecordsAsync({
        fields: [firstJunctionField, secondJunctionField],
    });

    for (let record3 of query3.records) {
        let records1 = record3.getCellValue(firstJunctionField);
        let records2 = record3.getCellValue(secondJunctionField);

        // Either field in the junction table may have zero records. That's not
        // expected, so junction records like that should be removed.
        if (!records1 || !records2) {
            toDelete.push(record3);
            continue;
        }

        // Either field in the junction table may reference multiple records.
        // That's not expected, either, so junction records like that should be
        // removed.
        if (records1.length > 1 || records2.length > 1) {
            toDelete.push(record3);
            continue;
        }

        let key = `${records1[0].id}${records2[0].id}`;

        // Keep track of each record in the junction table that describes a unique
        // pair of foreign records. We'll use this to determine whether new records
        // need to be created.
        if (!(key in existing)) {
            existing[key] = record3;

            // If we've already seen a record in the junction table for two foreign
            // records, then the current record is a duplicate, so we should plan
            // to remove it.
        } else {
            toDelete.push(record3);
        }
    }

    let query1 = await table1.selectRecordsAsync();
    var links = [];
    if(settings.joinType === 'inner') {
        let query2 = table1.getField(multipleSelect.name).options;
        links = query2;
    }
    
    for (let recordId1 of query1.recordIds) {
        if(settings.joinType === 'left') {
            let record = query1.getRecord(recordId1);
            links = record.getCellValue(multipleSelect);
            links = links === null ? [] : links;
        }
        for(let recordId2 of links){
            let key = `${recordId1}${recordId2.id}`;

            // If we didn't see this combination of foreign records when we
            // traversed the junction table, we should plan to create a new record.
            if (!(key in existing)) {
                toCreate.push({
                    fields: {
                        [firstJunctionField.name]: [{ id: recordId1 }],
                        [secondJunctionField.name]: recordId2.name,
                        [secondJunctionFieldId.name]: recordId2.id
                    },
                });

                // If we *did* see this combination of foreign records, then we'll
                // remove the corresponding junction record from our data
                // structure. That way, once this loop is complete, the only
                // records that remain in the data structure will be the ones that
                // describe non-existent foreign records.
            } else {
                delete existing[key];
            }
        }
    }

    // If `existing` still has any entries, they are junction records which include
    // non-existent foreign records. We should delete those, too.
    toDelete.push(...Object.values(existing));

    // Part 2: Verify
    //
    // Inform the script's user of the changes to be made and await their
    // confirmation.
    output.markdown(
        `Identified **${toCreate.length}** records in need of creation.`
    );
    output.markdown(
        `Identified **${toDelete.length}** records in need of deletion.`
    );

    let decision = await input.buttonsAsync("Proceed?", ["Yes", "No"]);

    // Part 3: Execute the necessary operations

    if (decision === "No") {
        output.text("Operation cancelled.");
    } else {
        output.text("Applying changes...");

        while (toDelete.length > 0) {
            await junctionTable.deleteRecordsAsync(
                toDelete.slice(0, maxRecordsPerCall)
            );
            toDelete = toDelete.slice(maxRecordsPerCall);
        }

        while (toCreate.length > 0) {
            await junctionTable.createRecordsAsync(
                toCreate.slice(0, maxRecordsPerCall)
            );
            toCreate = toCreate.slice(maxRecordsPerCall);
        }

        output.text("Done");
    }
}

await createJunction();

As you can see there are 7 parameters and one question. I’d like to place static info for the 7 fields and avoid the question.

Does anyone know if this would be possible ?

Thank you very much for your help.
Best,
D

1 Solution

Accepted Solutions
kuovonne
18 - Pluto
18 - Pluto

Welcome to the Airtable community!

What is your interest level in learning to code?

Scripts designed to be run from Scripting App and scripts designed to be run as automations handle input and output very differently. Converting this particular script to work as an automation requires changing many lines, basically every line that has either the word input or output would need changing.

Being interested in coding or interested in learning to code would help given the number of changes and how picky code can be about having the exact right syntax.


If you are interested in learning to code, I recommend you start with converting lines with output.text or output.markdown to console.log and checking to make sure the script still runs.

If you felt comfortable making those changes, delete this line and see if you can make the other necessary changes so that the script still run.

let decision = await input.buttonsAsync("Proceed?", ["Yes", "No"]);

If you felt comfortable figuring out those changes, delete these lines and see if you can make the other necessary changes so that the script will still run. This time the changes are very far apart.

      input.config.select('joinType', {
            label: 'Join Type',
            description: 'Determines how to join records together.  Inner will provide every combination between both tables.  Left will only create records determined by the Link from Table 1 to Table 2 field',
            options: [
                {label: 'Left', value: 'left'},
                {label: 'Inner', value: 'inner'}
            ]
        })

If you accomplish those steps and find your self enjoying the process of learning to code, please come back to report your progress and get more hints on what else to do.

See Solution in Thread

2 Replies 2
kuovonne
18 - Pluto
18 - Pluto

Welcome to the Airtable community!

What is your interest level in learning to code?

Scripts designed to be run from Scripting App and scripts designed to be run as automations handle input and output very differently. Converting this particular script to work as an automation requires changing many lines, basically every line that has either the word input or output would need changing.

Being interested in coding or interested in learning to code would help given the number of changes and how picky code can be about having the exact right syntax.


If you are interested in learning to code, I recommend you start with converting lines with output.text or output.markdown to console.log and checking to make sure the script still runs.

If you felt comfortable making those changes, delete this line and see if you can make the other necessary changes so that the script still run.

let decision = await input.buttonsAsync("Proceed?", ["Yes", "No"]);

If you felt comfortable figuring out those changes, delete these lines and see if you can make the other necessary changes so that the script will still run. This time the changes are very far apart.

      input.config.select('joinType', {
            label: 'Join Type',
            description: 'Determines how to join records together.  Inner will provide every combination between both tables.  Left will only create records determined by the Link from Table 1 to Table 2 field',
            options: [
                {label: 'Left', value: 'left'},
                {label: 'Inner', value: 'inner'}
            ]
        })

If you accomplish those steps and find your self enjoying the process of learning to code, please come back to report your progress and get more hints on what else to do.

David_Ohanessia
6 - Interface Innovator
6 - Interface Innovator

Hi Kuovonne,

Thank you very much for this helpful response.

I’ll give a try to handle your hints and get back to the topic if I could manage it :winking_face:

Have a nice day !
Best,
D