Help

Create Junction records based on field date values

Topic Labels: Automations
720 3
cancel
Showing results for 
Search instead for 
Did you mean: 
Ian_Gillanders
4 - Data Explorer
4 - Data Explorer

Hello,

I am trying to create an automation that will create junction records when a record for one table ("students") is created.  Basically, I need a series of new record created in the junction table (called "student.weeks") linking the new student record to records in the other table (the "weeks" table). 

The "students" table has each a record for each student with their course start and end dates.
The "weeks" table has all our school's starting dates (which are basically every week through the calendar year).

I assume I could use Airtable's "Create Junction Table" script. But, that would just create records for every starting date (in the "weeks" table), without regard for the student's enrolment dates.  

I am hoping someone can give me some help with adjusting the code (below), so that I can use it in an automation that triggers when a new student record is added.

Here's the code from Airtable's script:

 

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

 

 

3 Replies 3

You might want to provide screenshots of your tables and the relevant fields as that information would be needed to help you with the script

You could also try approaching this without scripting by creating a system that would link all of your weeks to a single student, having lookup fields that would pull over the student's start and end dates to the "Weeks" table, and having a formula field that would check if that "Weeks" record was within the start and end dates

You could then use a conditional rollup in the "Students" table to display all the weeks that are within the start and end dates, and then create a comma separated list of unique values to paste into a linked field to the table where you want these new records created, e.g. `[Student 1 Name] - [Week 1], [Student 1 Name] - [Week 2]`, etc.  This would create one record in the linked table per unique comma separated value that didn't exist in the linked table

Hi Adam,

Here are screen shots of the relevant tables.  Still not sure if your suggestion of lookup fields would work.  I believe I need to have a dedicated record for each each week a given student is at the school in order to preserve the data we need to keep.

Weeks tableWeeks tableStudents tableStudents tableStudent-weeks tableStudent-weeks table

From what I can tell from your screenshots, the non-scripting option I suggested should work fine

Unfortunately I'm pretty busy with work and so won't be able to help customize your script for you, sorry!  With luck someone else will be able to help with this now that the field names and types are known!