Need simple modifications to Airtable’s Junction Table extension

[Regi]

Hi guys,

I’m working with a base that I have created for our team with 3 tabs: Talent, Client and Projects
Since this is a many to many relationship between Talent and Projects, I created a Junction table to log and manage information specific to various Talent and Project relationships. We now have over 40 projects and 800 + talent records that become 30,000 + records in the junction table. I need some help with modifying this junction table script to avoid unnecessary records being created.
I only want this for selected records in the Project tab i.e. live projects instead of complete ones or checked projects in the Project table.

I’m a little new to Airtable and I don’t know how to script unfortunately, so i’d any input/guidance towards this issue.

[Andrey_Kovalev]

@Regi How does your script look like? If you use your junction table for input then you shouldn’t have so many records for every possible Talen/Project combination.

[Regi]

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

@Andrey_Kovalev, thank you so much for taking the time to respond :slightly_smiling_face: really appreciate it.
This is the code I’m using from the Junction table extension. In my Project table I log all of our projects but only few of them are actually live and need records created in the junction table for every Talent/Live Project combination.

[Andrey_Kovalev]

@Regi Your script seems to be working as expected, but the outcome is unclear to me. You’ve got everything linked, but no information on active Project/Talent links. If I correctly understand you in your first post you would like to eliminate all non-active links, right? Then you should probably use an additional field that marks the link as active.

[Regi]

@Andrey_Kovalev, Yes this script that I am using is from the marketplace (Airtable). It is designed to populate a junction table that contains every possible combination of linked records from two tables. Unfortunately I am not a coder, so I’m stuck at this point and don’t know how to make the modifications necessary to suit my work.

In my Projects table I have a field that marks the status of each project as shown in the screenshot below.

I’d like the script to read this information and update the junction table accordingly.

Once the project status in the Projects table is updated from ‘live’ to ‘complete’ and I run the script again, I want the script to retain any information specific to previous ‘live’ projects so that I can keep track of notes I made specific to an old project.

[Andrey_Kovalev]

@Regi I came up with the following structure which uses a couple of automation scripts for linking. If you are interested I will provide more info. Here is a base https://airtable.com/shrZqlKnKBUhCpYqJ.

First script creates job records and is activated when Push checkbox is set to “checked”.

//collect input data
let rec = input.config()

//access required tables
let tProductions = base.getTable("Productions")
let tJobs = base.getTable("Jobs")
let tLogs = base.getTable("Projects Log")
let selProject = await tProductions.selectRecordAsync(rec.recID)
let selJobs = await tJobs.selectRecordsAsync()
let selLogs = await tLogs.selectRecordsAsync()
const Link = "Productions"

//create and fill in Roles array
let Roles = selProject.getCellValue("Roles")

//create new records in Jobs table according to changes in Productions table
for (let j of Roles) {
    if (!selJobs.records.find(r => r.name === rec.prodID + " - " + j.name)) {
        let JobRecID = await tJobs.createRecordAsync({
            [Link]: [{id: selProject.id}],
            "Role": j.name,
            "Status": { name: "New" },
        })
        console.log(JobRecID)

        //register new job in Projects Log table
        await tLogs.createRecordAsync({
            "Project": rec.prodID + " - " + j.name,
            "Stage": "New"
        })
    }
}

It uses two input variables

Second script manages log records and is activated either Status or Archive field update.

const rec = input.config().recID

//access required tables
let tJobs = base.getTable("Jobs")
let tLogs = base.getTable("Projects Log")
let selJob = await tJobs.selectRecordAsync(rec)
let selLogs = await tLogs.selectRecordsAsync()

//check if the record already exists
if (undefined === selLogs.records.find ( r => 
    r.getCellValueAsString("Project") === selJob.name && 
    r.getCellValueAsString("Stage") === selJob.getCellValueAsString("Status") &&
    r.getCellValue("Archived") === selJob.getCellValue("Archive")))
    {
    //create new record in Projects Log table according to changes in Jobs table
    await tLogs.createRecordAsync({
        "Project": selJob.name,
        "Stage": selJob.getCellValueAsString("Status"),
        "Archived": selJob.getCellValue("Archive")
    })

    //check if Archived checkbox is true and update all Project occurencies
    if (selJob.getCellValue("Archive") === true) {
        for (let r of selLogs.records) {
            if (r.getCellValueAsString("Project") === selJob.name) {
                await tLogs.updateRecordAsync(r, {
                    "Archived": true
                })
            }
        }
    }
}    

It uses one input variable

[Regi]

Sorry about the very late response, been a busy week. Thank you for taking the time to respond.

Your second script will definitely be useful for my case and I can see how the first script might be relevant to some extent but it’s not quite fitting for my layout I think, if it is then I’m struggling to see it :grinning_face_with_sweat: .

Here’s an example base of my original setup. This might clarify. Example Base - Airtable