Script in Automation - Running in Extension not in automation
Hey @Edward!
Suppose I have the following table structure:
I want to accomplish the following:
- When a record's Target Field in the table is empty, I want to conduct a full query of all the records.
- I want to take a value from one of the other records and use that value to fill data not just for the individual record that triggered the automation, but for every record that meets the same criteria in the table.
There's two ways I think about doing this. The big question is about how you want to determine the "last" record.
Do you want to use the last record with a value that was most recently created? Or do you want to sort the existing values and take the ending (or starting) value when their sorted?
First, we're going to focus on the first method.
To do this, I need to create a Created Time computed field in my schema. It'll look like this:
Now we can pop open our automation and get started.
We're going to query for all the records in our table. We'd like to return the Created Time and Target Field field objects along with our query.
Next, we're going to create two arrays. The first array will contain our empty records. These will be the records we're going to update.
The second will be all the records that return data in the Target Field.
Here's what we have so far:
const table = base.getTable("tblzFk0qc721m5ppw");
const records = await table.selectRecordsAsync({
fields: [
"fldPXi737F2FL24Tt", // Created Time
"fld0LecZ30NiBgbYj" // Target Field
]})
.then(query => query.records);
let emptyRecords = records
.filter(record => !record.getCellValueAsString("fld0LecZ30NiBgbYj"));
let filledRecords = records
.filter(record => record.getCellValueAsString("fld0LecZ30NiBgbYj"));
When we log those arrays to the console, we should see an array of two record objects that contains the records that are empty.
We should also see a second array with one record object since only one of our records had a value in the Target Field.
As expected:
If you're curious as to why the record.name property returned "Unnamed record", the name property is inherited from the record's primary field, but since we didn't query the primary field in our original table.selectRecordsAsync() call, it has defaulted to returning "Unnamed record."
Now that we have our respective arrays, we need to find the filled record that was most recently created.
We can do this by sorting the array elements, then using the Array.pop() method to grab the record we're looking for.
Here's the snippet that will sort the filled records and return the record object of the most recently created record:
let recentRecord = filledRecords
.sort((recordA, recordB) => {
let recordACreated = new Date(recordA.getCellValue("fldPXi737F2FL24Tt")).getTime();
let recordBCreated = new Date(recordB.getCellValue("fldPXi737F2FL24Tt")).getTime();
return recordACreated - recordBCreated;
})
.pop();
Now we need to update our empty records.
To do this, we need to pass an array of record objects with the record id and the field values mapped.
We can do two things here. The first option is to declare a new variable and then map the data from the emptyRecords array to the new variable in the desired format.
This is a bit redundant since we don't use the emptyRecords array for anything else.
An alternative second method that would optimize and streamline a little bit would be to move the emptyRecords array declaration to the point where we actually need it and then do all the formatting in a single operation.
Here's what the script would look like with the first method:
const table = base.getTable("tblzFk0qc721m5ppw");
const records = await table.selectRecordsAsync({
fields: [
"fldPXi737F2FL24Tt", // Created Time
"fld0LecZ30NiBgbYj" // Target Field
]})
.then(query => query.records);
let emptyRecords = records
.filter(record => !record.getCellValueAsString("fld0LecZ30NiBgbYj"));
let filledRecords = records
.filter(record => record.getCellValueAsString("fld0LecZ30NiBgbYj"));
let recentRecord = filledRecords
.sort((recordA, recordB) => {
let recordACreated = new Date(recordA.getCellValue("fldPXi737F2FL24Tt")).getTime();
let recordBCreated = new Date(recordB.getCellValue("fldPXi737F2FL24Tt")).getTime();
return recordACreated - recordBCreated;
})
.pop();
let recordUpdates = emptyRecords
.map(recordObj => ({
id: recordObj.id,
fields: {
"fld0LecZ30NiBgbYj": recentRecord?.getCellValueAsString("fld0LecZ30NiBgbYj") // Map update to Target Field.
}
}));
Here's what it looks like with the second method:
const table = base.getTable("tblzFk0qc721m5ppw");
const records = await table.selectRecordsAsync({
fields: [
"fldPXi737F2FL24Tt", // Created Time
"fld0LecZ30NiBgbYj" // Target Field
]})
.then(query => query.records);
let filledRecords = records
.filter(record => record.getCellValueAsString("fld0LecZ30NiBgbYj"));
let recentRecord = filledRecords
.sort((recordA, recordB) => {
let recordACreated = new Date(recordA.getCellValue("fldPXi737F2FL24Tt")).getTime();
let recordBCreated = new Date(recordB.getCellValue("fldPXi737F2FL24Tt")).getTime();
return recordACreated - recordBCreated;
})
.pop();
let recordUpdates = records
.filter(record => !record.getCellValueAsString("fld0LecZ30NiBgbYj"))
.map(recordObj => ({
id: recordObj.id,
fields: {
"fld0LecZ30NiBgbYj": recentRecord?.getCellValueAsString("fld0LecZ30NiBgbYj") // Map update to Target Field.
}
}));
From here, we need to handle the record updates.
The table.updateRecordsAsync() method is limited to only being able to handle fifty record updates at a time.
If we have more updates than the limit, then we need to batch the update operations.
Here's what the remainder of the script looks like:
const table = base.getTable("tblzFk0qc721m5ppw");
const records = await table.selectRecordsAsync({
fields: [
"fldPXi737F2FL24Tt", // Created Time
"fld0LecZ30NiBgbYj" // Target Field
]})
.then(query => query.records);
let filledRecords = records
.filter(record => record.getCellValueAsString("fld0LecZ30NiBgbYj"));
let recentRecord = filledRecords
.sort((recordA, recordB) => {
let recordACreated = new Date(recordA.getCellValue("fldPXi737F2FL24Tt")).getTime();
let recordBCreated = new Date(recordB.getCellValue("fldPXi737F2FL24Tt")).getTime();
return recordACreated - recordBCreated;
})
.pop();
let recordUpdates = records
.filter(record => !record.getCellValueAsString("fld0LecZ30NiBgbYj"))
.map(recordObj => ({
id: recordObj.id,
fields: {
"fld0LecZ30NiBgbYj": recentRecord?.getCellValueAsString("fld0LecZ30NiBgbYj") // Map update to Target Field.
}
}));
if (recordUpdates) {
while (recordUpdates.length > 50) {
await table.updateRecordsAsync(recordUpdates.splice(0, 50));
recordUpdates = recordUpdates.splice(50);
};
await table.updateRecordsAsync(recordUpdates);
}
Okay, let's test our work.
I'm going to create a new record in our table called Record 4 to give us a bit more data to work with. I'll also populate some additional data.
We can see that Record 4 is the most recently created record.
Let's delete the Target Field value for Record 3.
When we do this, we can expect that Record 4 Value will be written to Record 3.
Sweet! Now, let's delete all the value expect for one.
Sweet! That concludes the first method of doing this, which relies on the Created Time field comparison.
Now we're going to look at a method that relies on simply sorting the Target Field values and using the Target Field value at the start or end of the sorted array.
This can get a bit tricky if the Target Field could possibly contain strings that start with a number.
Suppose you have the record data shown above.
If we were to sort the records by the Target Field values, in ascending alphabetical order, the record at the end would return the "9020" value.
If you called the Array.reverse() or the Array.shift() method, it would return the "1234" value.
It would completely ignore the values that start with alphabetical characters and only focus on the numbers.
If you want to mimic the behavior of Airtable's sorting behavior, you can utilize the following configuration to handle the sorting:
let sortedRecord = filledRecords
.sort((recordA, recordB) => {
let valueA = recordA.getCellValueAsString("fld0LecZ30NiBgbYj");
let valueB = recordB.getCellValueAsString("fld0LecZ30NiBgbYj");
return valueA.localeCompare(valueB, "en", {
ignorePunctuation: true,
caseFirst: "false"
});
})
.pop();Logging the sortedRecord value to the console would return the "This is my test value" record because t sorts above h or any of the numbers.
If you want to flip the order, you would switch the Array.pop() method with the Array.shift()method like this:
let sortedRecord = filledRecords
.sort((recordA, recordB) => {
let valueA = recordA.getCellValueAsString("fld0LecZ30NiBgbYj");
let valueB = recordB.getCellValueAsString("fld0LecZ30NiBgbYj");
return valueA.localeCompare(valueB, "en", {
ignorePunctuation: true,
caseFirst: "false"
});
})
.shift();Here's the final script:
const table = base.getTable("tblzFk0qc721m5ppw");
const records = await table.selectRecordsAsync({
fields: [
"fld0LecZ30NiBgbYj" // Target Field
]})
.then(query => query.records);
let filledRecords = records
.filter(record => record.getCellValueAsString("fld0LecZ30NiBgbYj"));
let sortedRecord = filledRecords
.sort((recordA, recordB) => {
let valueA = recordA.getCellValueAsString("fld0LecZ30NiBgbYj");
let valueB = recordB.getCellValueAsString("fld0LecZ30NiBgbYj");
return valueA.localeCompare(valueB, "en", {
ignorePunctuation: true,
caseFirst: "false"
});
})
.pop();
let recordUpdates = records
.filter(record => !record.getCellValueAsString("fld0LecZ30NiBgbYj"))
.map(recordObj => ({
id: recordObj.id,
fields: {
"fld0LecZ30NiBgbYj": sortedRecord?.getCellValueAsString("fld0LecZ30NiBgbYj") // Map update to Target Field.
}
}));
if (recordUpdates) {
while (recordUpdates.length > 50) {
await table.updateRecordsAsync(recordUpdates.splice(0, 50));
recordUpdates = recordUpdates.splice(50);
};
await table.updateRecordsAsync(recordUpdates);
}Let's test it out!
Boom!
If you intend to try these scripts out in your base, you'll (naturally) want to confirm that the table id and all the field ids are correctly updated for your schema.
