Help

Re: Automatically Populate Junction Tables!

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

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 [Projects] and [Companies] 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.

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.

Airtabling
5 - Automation Enthusiast
5 - Automation Enthusiast

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!

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

Best,

oLπ

Neil_Baptista
5 - Automation Enthusiast
5 - Automation Enthusiast

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

VictoriaPlummer
7 - App Architect
7 - App Architect

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)
Zion_Brock
6 - Interface Innovator
6 - Interface Innovator

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!

Victor_S
4 - Data Explorer
4 - Data Explorer

Thank you so much for this script!
It is exactly the thing I was looking for, works perfectly! @VictoriaPlummer

Allen_Moldovan
7 - App Architect
7 - App Architect

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 [] 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.

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

Charles_111696
5 - Automation Enthusiast
5 - Automation Enthusiast

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
image

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 [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
6 - Interface Innovator
6 - Interface Innovator

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.

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

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.