Help

Need simple modifications to Airtable’s Junction Table extension

Topic Labels: Scripting extentions
2598 6
cancel
Showing results for 
Search instead for 
Did you mean: 
Regi
4 - Data Explorer
4 - Data Explorer

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.

6 Replies 6
Andrey_Kovalev
8 - Airtable Astronomer
8 - Airtable Astronomer

@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
4 - Data Explorer
4 - Data Explorer
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.

@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.

@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.

Screenshot 2022-11-17 at 15.55.48
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
8 - Airtable Astronomer
8 - Airtable Astronomer

@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
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
variable

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