Automatically Populate Junction Tables!

[VictoriaPlummer]

If you have a many-to-many relationship, chances are you have a junction table. And chances are, setting up and maintaining that junction table is probably…not your typical idea of a fun time.

Welp, now it can be! Thanks to some serious help from @Stephen_Suen, we figured out how a script could help us automate this process. In the below example I have a list of projects, and for each project I’ve assigned multiple companies to it. I also have another table named “Tracking” where I track all the details related to a specific project-company pair.

Previously to create that junction, I had to do a lot of finagling, which took a fair bit of time. However, with a script, I can automatically create the records I need. Below I’m going to assign companies to Project 5, and then use a script to create the junction records.

Ahh, the joy of automation. :massage_woman:t5: Thank you @Stephen_Suen! Script below. Example base here.

let projectsTable = base.getTable('Projects');
let projectQuery = await projectsTable.selectRecordsAsync();
let projectRecords = projectQuery.records;

let companyTable = base.getTable('Companies');
let companyQuery = await companyTable.selectRecordsAsync();

let joinTable = base.getTable('Tracking');
let joinQuery = await joinTable.selectRecordsAsync();

// Go through all the records in the projects table
for (let projectRecord of projectRecords) {
    output.markdown('#### Checking project: ' + projectRecord.getCellValueAsString('Name') + '...');
    // For each project, get the linked company records
    let companyRecordsLinkedToProject = projectRecord.getCellValue('Companies');

    // For each project, get the linked join records
    let joinRecordsLinkedToProject = projectRecord.getCellValue('Tracking');
    if (joinRecordsLinkedToProject === null) {
        // If there are no linked join records, use an empty array instead
        joinRecordsLinkedToProject = [];
    }

    // Create a Set of company record ids that are linked to the project.
    // A Set is like an array, but more efficient.
    let companyRecordIds = new Set();
    for (let linkedCompanyRecord of companyRecordsLinkedToProject) {
        companyRecordIds.add(linkedCompanyRecord.id);
    }

    // Loop over all join records linked to the project.
    for (let linkedJoinRecord of joinRecordsLinkedToProject) {
        // Get the full join record, not just name and id
        let joinRecord = joinQuery.getRecord(linkedJoinRecord.id);
        let companyRecordLinkedToJoin = joinRecord.getCellValue('Company')[0];
        let companyRecordId = companyRecordLinkedToJoin.id;

        // Remove this company record id from the Set.
        output.text('Join record already exists for company: ' + companyRecordLinkedToJoin.name);

        companyRecordIds.delete(companyRecordId);
    }

    // Now we have a Set of company record ids that don't have join records.
    for (let companyRecordIdToJoin of Array.from(companyRecordIds)) {
        // Create the join record linking this project and this company.
        let companyRecord = companyQuery.getRecord(companyRecordIdToJoin);
        let companyName = companyRecord.getCellValueAsString('Company Name');
        output.text('Creating join record for company: ' + companyName);
        await joinTable.createRecordAsync({
            'Project': [{id: projectRecord.id}],
            'Company': [{id: companyRecordIdToJoin}],
        });
    }
    
    output.text('\n');

    output.inspect(companyRecordIds);
}

[Allen_Moldovan]

Question: How do you make the script disregard a record if no company is assigned to the project within the projects table. I’ll venmo 20 bucks to the person who solves it. Thanks

[Dominik_Bosnjak]

I don’t remember the specifics of this script, but generally speaking, the syntax for the conditional you’re looking for could go

if(record.getCellValue(YourCompanyLinkFieldName) === null)

or

if(!record.getCellValue(YourCompanyLinkFieldName))

If you were iterating over your records using a flatMap, you could attach return [] to this conditional to filter out company-less records:

query.records.flatMap(record=>{

if(!record.getCellValue(YourCompanyLinkFieldName)) return []

else return record
})

Shorthand using a ternary:

query.records.flatMap(r => r.getCellValue(YourCompanyLinkFieldName) ? r : [])

Pretty much any Array.prototype method allows you to apply this sort of logic to your queries, though.

Another example with filter, assuming query is your recordsQueryResult object:

query.records.filter(rec => rec.getCellValue(YourCompanyLinkFieldName))

Or just stick the conditional into a loop, of course.

If this helped, donate that 20 to a worthy cause, cheers.

[Jeff_Gennusa]

yes, how do I get the junction table to auto update without manually running the script?

[Charles_111696]

Hello, this script is awesome. I’m new to scripting and essentially changed my table and field names to match the examples you provided. My first run, I received this error:

TypeError: companyRecordsLinkedToProject is not iterable
at main on line 27

Any help regarding this would be awesome.

[Justin_Barrett]

Welcome to the community, @Charles_111696! :grinning_face_with_big_eyes: The assumption is that the {Companies} link field (name taken from the sample script and table) in the [Projects] table is linked to one or more records from the [Companies] table. While the script does do some error checking, it doesn’t account for project records where no such links exist, and my gut says that some of your records have no links in that field.

The purpose of the script is to link projects to companies using a junction table, so it’s important that there be at least one company link for each project. If your use case is such that there may be instances with no such link, a small modification to the script should take care of this. After this line:

    let companyRecordsLinkedToProject = projectRecord.getCellValue('Companies');

Add the following:

    if (!companyRecordsLinkedToProject) {
        continue;
    }

This will skip the creation of junction table records for any project record with no company links.

[Zion_Brock]

Have you guys seen the new “junction table” app by Airtable? It works extremely well. However, i can seem to figure out how to trigger it using an automation.

Anyone know how?

[kuovonne]

It isn’t possible to trigger an app using an automation.

[Kamille_Parks]

Are you by chance referring to Junction Assistant? If so, that app was developed by myself and not by Airtable. Just clarifying so that if anyone had questions about its use or function they know to contact me directly (support@kamillionaireapps.com) as opposed to Airtable’s support.

Kuovonne is correct in that the App can’t be triggered by an Automation, but you could repurpose the script in this thread to run via an Automation (if you have a Pro plan).

[Zion_Brock]

Thanks, I was just referring to the Airtable one. But I did see yours in there. What’s the advantage of using the one you made?

And I’m curious how you would adapt the automation into a script. I tried to do it, but to know avail. I’m no coder I guess.

Thanks

[Kamille_Parks]

I don’t recall a junction table-focused app developed by Airtable (but they did release junction-focused scripts in the marketplace). I’d give a comparison of the apps’ features if you can point me to where it is.