Hello everyone,
I’m very new to Airtable and also a non-coder. I’m trying to launch a script as an automation’s action, which is pretty straightforward.
However, the script I’m trying to launch needs settings to be populated before each launch, ask for a confirmation before launch and therefore the script needs to be run manually.
However, those settings are now fixed in my base so i would amend the script code directly to reflect those. I don’t know how to proceed. This script is part of this function :
https://www.airtable.com/universe/expK9hEwh5d1IGkH3/splitting-multiple-selectlinks-into-individual-r...
Here is the code :
let settings = input.config({
title: "Create junction table (keep multiselect)",
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.field("multipleSelect", { parentTable: 'table1', label: "Multiselect field name", description: 'The multiple select field we want to split into multiple records' }),
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: "Text field to hold second join value",
}),
input.config.field("secondJunctionFieldId",{
parentTable: 'junctionTable',
label: 'Second junction ID field name',
description: 'The field in the junction table that holds the multiselect ID'
}),
input.config.select('joinType', {
label: 'Join Type',
description: 'Determines how to join records together. Inner will provide every combination between both tables. Left will only create records determined by the Link from Table 1 to Table 2 field',
options: [
{label: 'Left', value: 'left'},
{label: 'Inner', value: 'inner'}
]
})
],
});
async function createJunction() {
let {
table1 = base.getTable("Bailleurs"),
multipleSelect =,
junctionTable,
firstJunctionField,
secondJunctionField,
secondJunctionFieldId
} = settings;
if (table1 === junctionTable) {
output.text("First 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" ) {
output.text(
"First 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(settings.joinType === 'inner') {
let query2 = table1.getField(multipleSelect.name).options;
links = query2;
}
for (let recordId1 of query1.recordIds) {
if(settings.joinType === 'left') {
let record = query1.getRecord(recordId1);
links = record.getCellValue(multipleSelect);
links = links === null ? [] : links;
}
for(let recordId2 of links){
let key = `${recordId1}${recordId2.id}`;
// 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]: recordId2.name,
[secondJunctionFieldId.name]: recordId2.id
},
});
// 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();
As you can see there are 7 parameters and one question. I’d like to place static info for the 7 fields and avoid the question.
Does anyone know if this would be possible ?
Thank you very much for your help.
Best,
D
