Return to scripts

Automatically Populate Junction Tables

Creator: Victoria Plummer

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. This script can help automate this process by automatically creating the records needed for a junction table.

See the source for an example of how this script is used.

Source Code

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