Skip to main content

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 = P];


    }





    // 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': P{id: projectRecord.id}],


            'Company': C{id: companyRecordIdToJoin}],


        });


    }


    


    output.text('\n');





    output.inspect(companyRecordIds);


}

This is an interesting example. Could you clarify some things?



It looks like there is already a many-to-many relationship between the [Projects] and [Companies] through the record links, without the junction table. The junction table provides a very helpful place to store additional information about each relationship by breaking the many-to-many relationships down into two sets of one-to-many relationships.



However, doesn’t this new junction table replace the previous many-to-many relationship?



Do you envision this script as part of a one-time transition to a better base design? I could see this as a helpful tool, with the existing fields directly linking gProjects] and dCompanies] being turned into lookups/rollups.



Or do you envision it as part of an on-going usage while maintaining two versions of the many-to-many relationships? Perhaps there is a legacy system that allows adding new tables & fields, but prevents the changing the existing fields? In this situation, there would be many difficulties in keeping the two methods of managing the relationships in sync that the script does not currently handle.

This is an interesting example. Could you clarify some things?



It looks like there is already a many-to-many relationship between the [Projects] and [Companies] through the record links, without the junction table. The junction table provides a very helpful place to store additional information about each relationship by breaking the many-to-many relationships down into two sets of one-to-many relationships.



However, doesn’t this new junction table replace the previous many-to-many relationship?



Do you envision this script as part of a one-time transition to a better base design? I could see this as a helpful tool, with the existing fields directly linking gProjects] and dCompanies] being turned into lookups/rollups.



Or do you envision it as part of an on-going usage while maintaining two versions of the many-to-many relationships? Perhaps there is a legacy system that allows adding new tables & fields, but prevents the changing the existing fields? In this situation, there would be many difficulties in keeping the two methods of managing the relationships in sync that the script does not currently handle.


Great question! For me it mostly has to do with usability. In this example, one person - say a project manager, is outlining all the high-level information about the project including key dates, and which companies should be involved. I envision the project manager using the script to create those junction records where another contributor to the team can easily find where they should be inputting data.



So yes while you are maintaining two many-to-many relationships with the script, it’s mainly about when/how you’re inputting the information. Would love your thoughts on this as well.

Great question! For me it mostly has to do with usability. In this example, one person - say a project manager, is outlining all the high-level information about the project including key dates, and which companies should be involved. I envision the project manager using the script to create those junction records where another contributor to the team can easily find where they should be inputting data.



So yes while you are maintaining two many-to-many relationships with the script, it’s mainly about when/how you’re inputting the information. Would love your thoughts on this as well.


I understand that different ways of viewing and interacting with the data is important for different users of the base. However, I really think that keeping two sets of many-to-many relationships in sync is problematic. Not only are you duplicating information (which should not happen in a normalized database), the script currently does not keep the two sets of relationships in sync and does not recognize certain circumstances when they are out of sync.



If you are starting from scratch or can redesign the base to have a completely different design, I recommend replacing the existing fields linking directly to Projects] and aCompanies] with lookups/rollups to the junction table. When a project manager wants to edit the high-level information, do it all in a script (which would be slightly different from the current one). While this is a slightly different workflow, it doesn’t really add much work to any of the users, as the manger has to click the “run” button for a script anyway. It just shifts the order of actions around.



This is also were it would be nice to have a new “run script” field type, like some of Airtable’s competitors.

I’ve just come across this, so pleased to see someone has contributed a script. Many thanks @Stephen_Suen!



I’m not a coder unfortunately… is there any guidance as to how I can adapt this script to auto-fill my own junction table to save on a lot of data entry time?



I tried replacing the tables names in your script with my own ones, but quickly realised it’s more complicated than that.



A huge thanks in advance!

I understand that different ways of viewing and interacting with the data is important for different users of the base. However, I really think that keeping two sets of many-to-many relationships in sync is problematic. Not only are you duplicating information (which should not happen in a normalized database), the script currently does not keep the two sets of relationships in sync and does not recognize certain circumstances when they are out of sync.



If you are starting from scratch or can redesign the base to have a completely different design, I recommend replacing the existing fields linking directly to [Projects] and [Companies] with lookups/rollups to the junction table. When a project manager wants to edit the high-level information, do it all in a script (which would be slightly different from the current one). While this is a slightly different workflow, it doesn’t really add much work to any of the users, as the manger has to click the “run” button for a script anyway. It just shifts the order of actions around.



This is also were it would be nice to have a new “run script” field type, like some of Airtable’s competitors.




Yes, thank you for the suggestion @kuovonne , it would be very much welcomed! @VictoriaPlummer @Stephen_Suen



Best,



oLπ

Code worked perfectly. I can save hours in my week. Thank you @VictoriaPlummer

I guess this is too old to edit so I’ll just add here:



I’ve updated the base and script to check for changes in the Projects table. So if you remove someone from a group in the Projects table it’s reflected in the Junction Table. Just add this bit to the end of the script.



// Get Join Records Now that the Script has Run





let joinQueryTwo = await joinTable.selectRecordsAsync();





let joinRecordsTwo = joinQueryTwo.records;





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





// Delete Irrelevant Records Now that the Script has Run





await joinTable.deleteRecordsAsync(nonMatches)

Victoria, this is amazing. First of all, thank you for sharing this!



I’m wondering if there’s a way in which you could modify this script so that the user would not have to select the company in the Projects tab first.



In other words, is there a way in which this could work where if the user added a new record in the Projects tab, the junction (Tracking) table automatically added the project for every customer?



I can’t seem to figure this out on my own for some reason.


Thanks!

Thank you so much for this script!


It is exactly the thing I was looking for, works perfectly! @VictoriaPlummer

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

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


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 e] to this conditional to filter out company-less records:



query.records.flatMap(record=>{





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





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.

Victoria, this is amazing. First of all, thank you for sharing this!



I’m wondering if there’s a way in which you could modify this script so that the user would not have to select the company in the Projects tab first.



In other words, is there a way in which this could work where if the user added a new record in the Projects tab, the junction (Tracking) table automatically added the project for every customer?



I can’t seem to figure this out on my own for some reason.


Thanks!


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

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.

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.


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 eProjects] table is linked to one or more records from the eCompanies] 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.

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?

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?


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

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?


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

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


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

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


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.

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.


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

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


It doesn’t appear for me:




Do you have the URL?

It doesn’t appear for me:




Do you have the URL?


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

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 guess this is too old to edit so I’ll just add here:



I’ve updated the base and script to check for changes in the Projects table. So if you remove someone from a group in the Projects table it’s reflected in the Junction Table. Just add this bit to the end of the script.



// Get Join Records Now that the Script has Run





let joinQueryTwo = await joinTable.selectRecordsAsync();





let joinRecordsTwo = joinQueryTwo.records;





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





// Delete Irrelevant Records Now that the Script has Run





await joinTable.deleteRecordsAsync(nonMatches)

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

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. 

Reply


Terms & Conditions