Help

Automatically Populate Junction Tables!

Topic Labels: Scripting extentions
13282 25
cancel
Showing results for 
Search instead for 
Did you mean: 
VictoriaPlummer
7 - App Architect
7 - App Architect

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.

Screen Shot 2020-03-10 at 6.59.52 PM

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.

Screen Recording 2020-03-10 at 07.04 PM

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);
}
25 Replies 25

Just go into the Applications area, add new app, search for “junction”

It doesn’t appear for me:
image

Do you have the URL?

It’s the purple one right there titled “Create junction table”

That’s a script, not an app, and the script was published September 2020 which is why I was confused what the “new Airtable app” would be.

That script creates all possible Junction Table combinations between two tables but doesn’t allow you to select which records actually need junctions. It also only allows linking between two records.

My app gives you greater control which records get created, lets you see if you’re about to create duplicates, and permits any number of record connections.

Both the “Create junction table” script and my app are different from the script at the top of this post. If you need advice on how to convert that script into one compatible with automations you should make your own topic so this one can stay focused on the script up top.

I just noticed that if your Project name has commas in it, the junction record gets deleted when you add this last piece of code.
The corresponding junction record is added to the junction table with quotes, and then it gets deleted.

the solution to avoid this would be to remove or replace commas. could this be included in the code?

thanks

jjl-pdx
5 - Automation Enthusiast
5 - Automation Enthusiast

Just tried this out to solve my exact same problem - and it worked! Including the delete portion you added (which was like my first question - great update). Thanks so much!!! 

Though, it was a bit confusing as you have tables and fields with the same name, and fields with the  same name in multiple tables... took a bit of detective work to sort out what was what.

I can create an automation to run this when records enter a view (I guess only available on Pro plan and better... I have enterprise) 

FYI - it says the function selectRecrodsAsync is deprecated. I have no idea about anything JavaScript - I'm just a copy/paste/edit guy. It works, so I'm OK, I guess... but for whatever reason that function is crossed-out in the scripting edit window. I guess it doesn't matter? 

Just curious if anyone knows if there is an alternative way to do with Automations, and the "+Add Record" option - I tried that, but couldn't get it to work.