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

}