Trigged Junction Script

I use this script (extension “Create junction table”), my configuration is
_ table1 = gen_emp
_ table2 = gen_date
_ junctionTable = generator
_ firstJunctionField = emp_id (in table1)
_ secondJunctionField = date_id (in table2)

let settings = input.config({
title: “Create junction table”,
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.table(“table2”, { label: “Second table name” }),
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: “Linked record to second table”,
}),
],
});

async function createJunction() {
let {
table1,
table2,
junctionTable,
firstJunctionField,
secondJunctionField,
} = settings;

if (table1 === junctionTable) {
    output.text("First table can't be the same as the junction table.");
    return;
}
if (table2 === junctionTable) {
    output.text("Second 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.")
}
if (
    firstJunctionField.type !== "multipleRecordLinks" ||
    secondJunctionField.type !== "multipleRecordLinks"
) {
    output.text(
        "First and second 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();
let query2 = await table2.selectRecordsAsync();

for (let recordId1 of query1.recordIds) {
    for (let recordId2 of query2.recordIds) {
        let key = `${recordId1}${recordId2}`;

        // 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]: [{ id: recordId2 }],
                },
            });

            // 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();

if (table1 === junctionTable) {
    output.text("First table can't be the same as the junction table.");
    return;
}
if (table2 === junctionTable) {
    output.text("Second 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.")
}
if (
    firstJunctionField.type !== "multipleRecordLinks" ||
    secondJunctionField.type !== "multipleRecordLinks"
) {
    output.text(
        "First and second 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();
let query2 = await table2.selectRecordsAsync();

for (let recordId1 of query1.recordIds) {
    for (let recordId2 of query2.recordIds) {
        let key = `${recordId1}${recordId2}`;

        // 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]: [{ id: recordId2 }],
                },
            });

            // 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");
}

Hey @Teddy_Krzastel ,

Welcome to Airtable Community !

Scripts run manually. Triggers can only be used in automations (for example when a new record enters a view) which you can use to run a script.

Thank you @Mohamed_Swellam

I’m surprised because I can choose “Run a script” (like an other action) in the Automation builder.

I think, my English is not very great.
I want to build an automation with
→ Trigger : When a field is updated
→ Action : Run a script (junction).

Maybe it’s clearer ?

Thank’s for your help.
Teddy

Yes, you can trigger a script from an Automation, this is fairly new to the platform and may have just been a miscommunication.

Scripts in automations can work a little differently than just the Scripting App.

When building your Automation script, you should see a panel on the left for input variables. Have you set those up?

Hello @Vivid-Squid

Thank you for your reply.

Yes, I have a panel for input variables.
I tried this morning but my script didn’t work. I have syntax error :confused:
It is difficult for me to understand this errors.

I have a script (like my previous message) :thumbs_up: , a panel for input variables :thumbs_up: , but it’s a maze for me :grinning_face_with_big_eyes:

Customizing a script is sometimes a very simple matter, and sometimes it requires many complex changes.

In this case the script was not designed to be run as an automation script. It could be converted to run as an automation script by replacing the script settings section with hardcoded tables and fields and stripping out all interaction with the user.

However, I do not recommend this. This script examines all records in three tables when creating junction records. It also performs several other calculations that don’t make sense in the context of an automation script triggered by a single record. The script would be extremely inefficient.

The idea of using this particular script to create junction records when a field is updated also doesn’t make sense. This script creates records for all combinations of records in each table, so the exact value of any field in any of the three tables should not affect which records exist in the junction table.

There are also a few other things I don’t like about this script. For example, the way it deletes junction records could lead to unintentional data loss. The “invalid” records that the script identifies should probably be manually examined by a human to determine how they should be handled.

If you are interested in learning how to write scripts yourself, I suggest you try starting from scratch, rather than reworking this script. You would have to make so many changes to make this script fit your use case that I don’t think it is worth it to adapt the script. There are many helpful people on this forum who assist new coders who put in the effort to teach themselves, but still have occasional targeted questions.

On the other hand, if you are not interested in learning to write scripts, you may want to either (1) keep looking for a different script more suited to your use case, if such a script exists, or (2) hire someone to write the script for you.

Hello @kuovonne

Thank you for your reply.
It’s very clear and I like your position.

The scripting language is new to me and I’m not sure I can develop just once yet.

How long do you think it takes to learn to code a junction script for a beginner ?
Is it a big challenge ?

Hello @kuovonne

Are you able to build a junction script ?
do you do this kind of work ?

Best regards,
Teddy

It varies a lot from person to person. It depends on you prior coding experience, your personal aptitude, and your perseverance when things don’t work. Someone already familiar with JavaScript and Airtable architecture could learn the Airtable vocabulary in a weekend. On the other hand some people spend months trying to learn JavaScript and eventually give up because it is not right for them.

I do not recommend starting to learn scripting from scratch by attempting to write a junction script. Start with something simpler. Learn to get cell values. Learn how to create a record. Gradually build up to the junction script piece by piece. One of the skills in coding is knowing how to break a task down into manageable pieces.