Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Mar 10, 2020 04:06 PM
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);
}
Oct 01, 2021 11:08 AM
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
Oct 10, 2021 01:15 PM
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.
Oct 11, 2021 02:19 PM
yes, how do I get the junction table to auto update without manually running the script?
Oct 14, 2021 03:08 PM
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.
Oct 14, 2021 05:08 PM
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.
Dec 02, 2021 11:14 AM
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?
Dec 02, 2021 11:21 AM
It isn’t possible to trigger an app using an automation.
Dec 02, 2021 11:59 AM
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).
Dec 02, 2021 12:14 PM
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
Dec 02, 2021 12:51 PM
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.