Automatically Populate Junction Tables!

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);
}
6 Likes

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.

1 Like

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.

1 Like

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!

1 Like

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

Best,

oLπ

1 Like

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

1 Like

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)
3 Likes