Hello,
I am trying to create an automation that will create junction records when a record for one table ("students") is created. Basically, I need a series of new record created in the junction table (called "student.weeks") linking the new student record to records in the other table (the "weeks" table).
The "students" table has each a record for each student with their course start and end dates.
The "weeks" table has all our school's starting dates (which are basically every week through the calendar year).
I assume I could use Airtable's "Create Junction Table" script. But, that would just create records for every starting date (in the "weeks" table), without regard for the student's enrolment dates.
I am hoping someone can give me some help with adjusting the code (below), so that I can use it in an automation that triggers when a new student record is added.
Here's the code from Airtable's script:
let settings = input.config({
title: "Create junction table",
description: `Running this script will populate a junction table that contains every possible combination of linked
records from two tables. Both tables must contain a linked field to the junction table being populated.`,
items: [
input.config.table("table1", { label: "First table name" }),
input.config.table("table2", { label: "Second table name" }),
input.config.table("junctionTable", {
label: "Junction table name",
}),
input.config.field("firstJunctionField", {
parentTable: "junctionTable",
label: "First junction field name",
description: "Linked record to first table",
}),
input.config.field("secondJunctionField", {
parentTable: "junctionTable",
label: "Second junction field name",
description: "Linked record to second table",
}),
],
});
async function createJunction() {
let {
table1,
table2,
junctionTable,
firstJunctionField,
secondJunctionField,
} = settings;
if (table1 === junctionTable) {
output.text("First table can't be the same as the junction table.");
return;
}
if (table2 === junctionTable) {
output.text("Second table can't be the same as the junction table.");
return;
}
if (firstJunctionField === secondJunctionField) {
output.text("First junction field can't be the same as the second junction field.")
}
if (
firstJunctionField.type !== "multipleRecordLinks" ||
secondJunctionField.type !== "multipleRecordLinks"
) {
output.text(
"First and second junction field should be of linked record type."
);
return;
}
let existing = Object.create(null);
let toCreate = [];
let toDelete = [];
// Airtable limits batch operations to 50 records or fewer.
let maxRecordsPerCall = 50;
// Part 1: determine the necessary operations.
//
// We don't modify the table contents in this Part in the interest of
// efficiency. This script may trigger a large number of database
// modifications, and it's much faster to request that they be done in batches.
// When we identify a record that should be created or deleted, we add it to
// the appropriate array so we can batch the operations in Part 2 of the
// script.
let query3 = await junctionTable.selectRecordsAsync({
fields: [firstJunctionField, secondJunctionField],
});
for (let record3 of query3.records) {
let records1 = record3.getCellValue(firstJunctionField);
let records2 = record3.getCellValue(secondJunctionField);
// Either field in the junction table may have zero records. That's not
// expected, so junction records like that should be removed.
if (!records1 || !records2) {
toDelete.push(record3);
continue;
}
// Either field in the junction table may reference multiple records.
// That's not expected, either, so junction records like that should be
// removed.
if (records1.length > 1 || records2.length > 1) {
toDelete.push(record3);
continue;
}
let key = `${records1[0].id}${records2[0].id}`;
// Keep track of each record in the junction table that describes a unique
// pair of foreign records. We'll use this to determine whether new records
// need to be created.
if (!(key in existing)) {
existing[key] = record3;
// If we've already seen a record in the junction table for two foreign
// records, then the current record is a duplicate, so we should plan
// to remove it.
} else {
toDelete.push(record3);
}
}
let query1 = await table1.selectRecordsAsync();
let query2 = await table2.selectRecordsAsync();
for (let recordId1 of query1.recordIds) {
for (let recordId2 of query2.recordIds) {
let key = `${recordId1}${recordId2}`;
// If we didn't see this combination of foreign records when we
// traversed the junction table, we should plan to create a new record.
if (!(key in existing)) {
toCreate.push({
fields: {
[firstJunctionField.name]: [{ id: recordId1 }],
[secondJunctionField.name]: [{ id: recordId2 }],
},
});
// If we *did* see this combination of foreign records, then we'll
// remove the corresponding junction record from our data
// structure. That way, once this loop is complete, the only
// records that remain in the data structure will be the ones that
// describe non-existent foreign records.
} else {
delete existing[key];
}
}
}
// If `existing` still has any entries, they are junction records which include
// non-existent foreign records. We should delete those, too.
toDelete.push(...Object.values(existing));
// Part 2: Verify
//
// Inform the script's user of the changes to be made and await their
// confirmation.
output.markdown(
`Identified **${toCreate.length}** records in need of creation.`
);
output.markdown(
`Identified **${toDelete.length}** records in need of deletion.`
);
let decision = await input.buttonsAsync("Proceed?", ["Yes", "No"]);
// Part 3: Execute the necessary operations
if (decision === "No") {
output.text("Operation cancelled.");
} else {
output.text("Applying changes...");
while (toDelete.length > 0) {
await junctionTable.deleteRecordsAsync(
toDelete.slice(0, maxRecordsPerCall)
);
toDelete = toDelete.slice(maxRecordsPerCall);
}
while (toCreate.length > 0) {
await junctionTable.createRecordsAsync(
toCreate.slice(0, maxRecordsPerCall)
);
toCreate = toCreate.slice(maxRecordsPerCall);
}
output.text("Done");
}
}
await createJunction();
