Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Oct 13, 2022 10:14 AM
Hello! I feel like I’m so close to getting my script to run- I keep getting the following error with my below code- any ideas on this? Does it have to do with my last line as an await?
let table1=base.getTable("August 1 2022 Issues- Onward");
let table2=base.getTable("Sprints-");
let junctionTable=base.getTable("August-1 Onward Sprint Junction");
let firstJunctionField=junctionTable.getField("August 1-Issues");
let secondJunctionField=junctionTable.getField("Sprints");
let joinType="left";
let tableLink=table1.getField("Sprints-");
console.log();
async function createJunction() {
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();
var links = [];
if(joinType === 'inner') {
let query2 = await table2.selectRecordsAsync();
links = query2.recordIds;
}
for (let recordId1 of query1.recordIds) {
if(joinType === 'left') {
let record = query1.getRecord(recordId1);
links = record.getCellValue(tableLink);
links = links === null ? [] : links.map((i)=>{return i.id});
}
for(let recordId2 of links){
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.`
);
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();
Oct 13, 2022 04:25 PM
Welcome to the community, @Allie_Hagmeyer! :grinning_face_with_big_eyes:
No, that’s not the problem. Thankfully I was able to see enough in your screenshot to tell that you’re running this script in an automation, and that makes the problem easy to determine.
The output
object when running a script in an automation has completely different behavior than the same named object when running a script in a Scripting extension. In a Scripting extension, it’s used to create output that you’d like the user to see when running the script. In an automation, it’s used to set data that can be used by later actions in the automation.
Because there’s no interactive output available when the automation runs, the only way to show anything to the user (after it has run) is by using the console. Replace output.text
with console.log
to generate a line in the console log for later review. (While testing, you can see this output directly in the testing sidebar; after activating your automation, the only way to see the log is in the automation run history).
For example:
output.text("Done")
…should become…
console.log("Done")
Oct 13, 2022 06:43 PM
The output.text won’t work in an automation script, but I suspect that there is at least one other issue. You have a syntax error, and output.text() should not cause that type of error.
See all those wavy red underlines? They indicate potential errors. Sometimes these wavy red underlines show up because the actual error is somewhere earlier in the script.
I find it most concerning that the final closing curly brace in your screen shot has a wavy red underline. But the problem is probably much earlier in the script.