Help

Re: Automatically Populate Junction Tables!

1638 0
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);
}
27 Replies 27

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. 

I know this was forever ago, I apologize. I'm trying to use this script, but the example base no longer gives access (or maybe I was supposed to request access manually?) Either way, my main question is about this add-on to the bottom of the script.

in this line:
let nonMatches = joinRecordsTwo.filter(c => !c.getCellValueAsString('Project Lookup').split(', ').includes(c.getCellValueAsString('Company')))

It looks like "Project Lookup" is a column added to the junction table. How do I do that so that it works properly? What am I looking up that allows the script to check for changes? 

cgreaves
6 - Interface Innovator
6 - Interface Innovator

Answering my own question if anyone gets this far. This was the only part of the code that was missing some comments to help beginners understand.

The addition to the script is to provide a one-way sync update to your new junction table from one of the original tables. If, in your Company table, you delete a Project linking record in your Projects field, this piece of the script would delete the corresponding record of that Company/Project pair. It doesn't do the reverse, so if you ran this script again on the same tables, it would just recreate it.

In this case, (watch how many times I can say lookup in one sentence) you would create a lookup field in your junction table called "Project Lookup" that looks up the Company link record field from your Project table. This is where I got confused - I would have called this "Company Lookup", since the values would be Company links, not Project links - and the default name in Airtable would be "Companies (from Projects)". So this filter will compare the list of all Companies linked to this record's Project, and the Company that is explicetly linked in this record by the previous script. If the Company is missing, you must have deleted that link in the Companies or Projects table, so it will delete the record here.