Help Creating a Script to Populate a Junction Table with Linked Records

Hello.

I’m trying to write a script that loops through all of the records in the table MyTable1, and for each record in MyTable1 loops through all of the records in table MyTable2, and at each iteration checks to see if table MyTable3 contains a record where Field1 is a link to the current iteration’s record in MyTable1 and where Field2 is a link to the current iteration’s record in MyTable2.

I’m essentially trying to automate populating a junction table where MyTable3 contains every possible combonation of linked records from MyTable1 and MyTable2.

There’s a great example of how to something similar in another thread (Automatically Populate Junction Tables!), but it wasn’t quite what I was looking for.

The script I’ve written so far is below. I wrote as comments the steps of the script that I’m unsure which functions to use to accomplish that step, and was hoping that someone might be able to help me out with this.

Thanks in advance!

let tableMyTable1 = base.getTable("MyTable1")
let queryMyTable1 = await tableMyTable1.selectRecordsAsync();
let recordsMyTable1 = queryMyTable1.records

let tableMyTable2 = base.getTable("MyTable2");
let queryMyTable2 = await tableMyTable2.selectRecordsAsync();
let recordsMyTable2 = queryMyTable2.records

let tableMyTable3 = base.getTable("MyTable3");
let queryMyTable3 = await tableMyTable3.selectRecordsAsync();
let recordsMyTable3 = queryMyTable3.records

for (let recordMyTable1 of recordsMyTable1) {
    for (let recordMyTable2 of recordsMyTable2) {
        
	// If recordsMyTable3 contains record where Field1 = recordMyTable1 and Field2 = recordMyTable2
	// Then nothing
	// Else create record in recordsMyTable3 where Field1 = recordMyTable1 and Field2 = recordMyTable2

    }    
}

for (let recordMyTable3 of recordsMyTable3) {

	// If recordMyTable3 Field1 = null or Field2 = null
	// Then delete recordMyTable3
	// Else nothing

}

Hi @StevenB!

Here’s an attempt at the script you described:

Click to expand
/**
 * Copyright 2020 Bocoup
 *
 * Permission is hereby granted, free of charge, to any person obtaining a copy
 * of this software and associated documentation files (the "Software"), to
 * deal in the Software without restriction, including without limitation the
 * rights to use, copy, modify, merge, publish, distribute, sublicense, and/or
 * sell copies of the Software, and to permit persons to whom the Software is
 * furnished to do so, subject to the following conditions:
 *
 * The above copyright notice and this permission notice shall be included in
 * all copies or substantial portions of the Software.
 *
 * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
 * IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
 * FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
 * AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
 * LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING
 * FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS
 * IN THE SOFTWARE.
 */

/**
 * Normalize junction table script
 *
 * Given a table serving to join two "foreign" tables, ensure that the first
 * table has exactly one record for every combination of records in the foreign
 * tables (and zero additional records).
 *
 * **Notes on adapting this script.**
 *
 * The script prompts for input every time it is run. For some users, one or
 * more of these values may be the same with every execution. To streamline
 * their workflow, these users may modify this script by defining the constant
 * values in the first few lines. The values should be expressed as JavaScript
 * strings in the object named `hardCoded`.
 */
'use strict';

/**
 * Users may provide values for any of the properties in the following object
 * to streamline the script's startup.
 */
const hardCoded = {
    firstForeignTableName: '',
    secondForeignTableName: '',
    junctionTableName: '',
    firstJunctionFieldName: '',
    secondJunctionFieldName: ''
};

/**
 * Do not edit any code following this message.
 */

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

const table1 = hardCoded.firstForeignTableName
    ? base.getTable(hardCoded.firstForeignTableName)
    : await input.tableAsync('First table name');
const table2 = hardCoded.secondForeignTableName
    ? base.getTable(hardCoded.secondForeignTableName)
    : await input.tableAsync('Second table name');
const table3 = hardCoded.junctionTableName
    ? base.getTable(hardCoded.junctionTableName)
    : await input.tableAsync('Junction table name');
const firstJunctionField = hardCoded.firstJunctionFieldName
    ? table3.getField(hardCoded.firstJunctionFieldName)
    : await input.fieldAsync('First junction field name', table3);
const secondJunctionField = hardCoded.secondJunctionFieldName
    ? table3.getField(hardCoded.secondJunctionFieldName)
    : await input.fieldAsync('Second junction field name', table3);

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

const query3 = await table3.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);
    }
}

const query1 = await table1.selectRecordsAsync();
const 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.`);

const 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 table3.deleteRecordsAsync(toDelete.slice(0, maxRecordsPerCall));
        toDelete = toDelete.slice(maxRecordsPerCall);
    }

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

    output.text('Done');
}

It deviates from your sample code a bit because of some limitations of Airtable’s Scripting Block API. We can’t perform complex queries about the existence of specific records in a given table; mostly, we can only retrieve a complete list of all records.

This script builds its own data structure to keep track of which “junction” records are present. It does this with JavaScript object whose keys are the combination of each “foreign” record’s ID.

I’ve included some comments to explain each part of the algorithm in context, and I’m happy to answer any questions you have here :slight_smile:

1 Like