Hello Airtable Community,
I've been working on a script in Airtable's scripting block (a modified version of the junction table script extension) that is meant to populate and maintain a junction table with every possible combination of linked records from two specific tables. Here's what I'm looking to achieve:
Create records: For each combination of records between the "Talent" table and "Projects" table where the project has a status of "Proposal Sent" or "Job Live", I want to create a new record in my "Junction" table. This junction table has linked record fields pointing back to the corresponding records in the "Talent" and "Projects" tables.
Delete records: For records in the junction table, if a project has a status of "Complete & Unsuccessful", the corresponding junction record should be deleted. If the project is "Complete & Successful", the junction record should only be deleted if there is no data in the following fields: "Contact Status", "Application Status", "Project Notes", "Score", and "Talent Summary".
I've written a script to handle this (pasted below), but when I run it, no new records are created in the junction table and no records are deleted. I'm not receiving any error messages, so it's unclear why the script isn't working as expected.
I've confirmed that the table and field names in my script match those in my base. The linked record fields in my junction table are set to allow linking to multiple records. I've also verified that there are records in the "Talent" and "Projects" tables that should be triggering the creation and deletion of records in the junction table according to the criteria I've specified.
Any help in identifying what might be going wrong would be much appreciated. Thanks in advance!
Here's my current 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.`,
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.")
return;
}
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 = [];
let maxRecordsPerCall = 50;
let query3 = await junctionTable.selectRecordsAsync({
fields: [firstJunctionField, secondJunctionField],
});
for (let record3 of query3.records) {
let records1 = record3.getCellValue(firstJunctionField);
let records2 = record3.getCellValue(secondJunctionField);
if (!records1 || !records2) {
toDelete.push(record3);
continue;
}
if (records1.length > 1 || records2.length > 1) {
toDelete.push(record3);
continue;
}
let linkedProjectRecord = await table2.selectRecordsAsync({
recordIds: [records2[0].id],
});
let projectStatus = linkedProjectRecord.records[0].getCellValue("Status");
if (!(projectStatus === 'Job Live' || projectStatus === 'Proposal Sent')) {
continue;
}
let key = `${records1[0].id}${records2[0].id}`;
if (!(key in existing)) {
existing[key] = record3;
} else {
toDelete.push(record3);
}
}
let query1 = await table1.selectRecordsAsync();
let query2 = await table2.selectRecordsAsync();
let filteredRecordIds2 = query2.records
.filter(record => {
let status = record.getCellValue("Status");
return status === 'Job Live' || status === 'Proposal Sent';
})
.map(record => record.id);
for (let recordId1 of query1.recordIds) {
for (let recordId2 of filteredRecordIds2) {
let key = `${recordId1}${recordId2}`;
if (!(key in existing)) {
toCreate.push({
fields: {
[firstJunctionField.name]: [{ id: recordId1 }],
[secondJunctionField.name]: [{ id: recordId2 }],
},
});
} else {
delete existing[key];
}
}
}
let finalDeletionRecords = [];
for (let record of Object.values(existing)) {
let linkedProjectRecord = await table2.selectRecordsAsync({
recordIds: [record.getCellValue(secondJunctionField)[0].id],
});
let projectStatus = linkedProjectRecord.records[0].getCellValue("Status");
if (projectStatus === "Complete & Unsuccessful") {
finalDeletionRecords.push(record);
} else if (projectStatus === "Complete & Successful") {
let contactStatus = record.getCellValue("Contact Status");
let applicationStatus = record.getCellValue("Application Status");
let projectNotes = record.getCellValue("Project Notes");
let score = record.getCellValue("Score");
let talentSummary = record.getCellValue("Talent Summary");
if (!contactStatus && !applicationStatus && !projectNotes && !score && !talentSummary) {
finalDeletionRecords.push(record);
}
}
}
toDelete = finalDeletionRecords;
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"]);
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();
