Hello,
I just made my first Script app writing my own custom code :tada: and wanted to share :blush:
Context: I am working with a base that has gone through several iterations of development and am trying to resolve some contradictory data sets. Specifically, for a directory of people who get assigned a “level.” There are five different fields to compare and resolve so as to make a single source of truth for each person. My fiirst step is to identify where there are mismatches across the five fields (a rollup, two lookups, and two single-select fields).
My first thought was to use a nested IF() ƒormula, but I found it difficult to construct the matching logic for the fields values and for when any them are without values (i.e. blank).
My next thought was to use an array formula to make an ARRAYUNIQUE() out of an ARRAYCOMPACT() values (i.e. remove the blank values, then look for unique values). To my surprise, the Array functions are only applicable in Rollups or when the field invoked in a ƒormula is a lookup - never crossed that nridge before with Airtable ƒormulas :astonished: Per the Formula reference page: “Note that array functions can only be used in rollup fields or when the input field is a lookup.”
So, maybe there’s a simpler ƒormulaic way, but not finding a solution with my first couple of ideas, I decided to give a Script app a go at finding mismatches (and then adding a :white_check_mark: to a Checkbox field).
This was also my first time using the options with selectRecordsAsync (to avoid the future deprecation). Specifying the fields definiitely makes a big difference in performance!
Per the comments, I have a lot of questions. Specifically regarding:
-
selectRecordsAsyncdefault sorting, am I seeing things right? I couldn’t find the default behavior documented, so it made me wonder. - Also wondering if the
selectRecordsAsync'sfields: []can be accessed by the field ID, or just the name :thinking: -
getCellValuebehavior when encounteringnullvalues - is there a better way? - Better solution suggestions for dealing with
null- e.g. a different method, or a better way to approach the ternary operator? - Suggestioins for determiining objects vs arrays to handle the different ways of invoking the key:value elements and indexes?
- Solutions for handling tier limits of under 50
updateRecordsAsyncat a time?
If you have any answers or suggestions on how I could improve my script, I would love to hear from you.
:nerd_face:
Thanks!
// Read in the records from the Members table
let table = base.getTable("Members");
let query = await table.selectRecordsAsync({
sorts: [
// // just noting for syntax reference
// {field: "Name", direction: 'desc'}
// // w/o a 'directiion' the sort defaults to
// // the 'asc' option for an ascending sort
// {field: "Name"}
// I am pretty sure {field: '{CREATED_TIME()}', direction 'asc'}
// is the default sorting field and does not require declaring a
// sort option at all, but I put it in below anyways
{field: "When their record was created"}
],
// declaring the specific fields reduces the Script's Run time significantly!
fields: [
"Level 1",
"Level 2",
"Level 3",
"Level 4",
"Level 5"
]
});
// I noticed below when using getCellValue that
// the script would stop iterating over the table
// where a null value was encourntered.
// Noting here for future investigation & learning,
// so I added a variable to count and confirm the
// total number of records iterated over
let totalCount = 0;
// to count the number of mismatches and confirm
// the accuracy of the checkbox update
let countMismatch = 0;
// helper function which will remove the null values from an array
let removeNull = (arr) => {
let noNull = []
for (let i = 0; i < arr.length; i++) {
if (arr[i] != null) {
noNull.push(arr[i]);
}
}
return noNull;
}
// helper function to reduce an array to unique values
// and check if there is more than one unique values
// i.e. proof-positive of a mismatch
let mismatch = (arr) => {
return [... new Set(arr)].length > 1;
}
// iterate over all the records on the Members table
// and make an array out of the values retrieved
// from each of the five "Level" fields
for (let q of query.records) {
totalCount += 1;
let arr = [];
// Airscript seems to handle null values in a weird way -
// it balks when it finds a null and stops iterating over the set of records
// so I used a ternary to set a null value if getCellValue finds one and balks.
// I am probably not using the getCellValue method correctly, or there's something more apropos?
arr.push(q.getCellValue("Level 1") == null ? null : q.getCellValue("Level 1")[0]); // Roll-up (array)
arr.push(q.getCellValue("Level 2") == null ? null : q.getCellValue("Level 2").name); // Single-Select (object)
arr.push(q.getCellValue("Level 3") == null ? null : q.getCellValue("Level 3")[0]); // Lookup (array)
arr.push(q.getCellValue("Level 4") == null ? null : q.getCellValue("Level 4")[0]); // Lookup (array)
arr.push(q.getCellValue("Level 5") == null ? null : q.getCellValue("Level 5").name); // Single-Select (object)
// IMHO the above should be broken out to make the array,
// and the bottom it's own function for writing to a checkbox field on Members
// e.g. a "getLevels" function which iterates over a "fields" array
// containing the field names/identifiers (requires navigating the
// object/array differences?) For the future . . .
// condition: pass the array of all five "Level" fields to the "removeNull"
// function, then use the "mismatch" function to evaluate the resulting array
// of level names to see if there are any mismatches, and if so, then add a check
if (mismatch(removeNull(arr)) == true) {
countMismatch += 1;
// to review ea. person's resulting array when mismatches are found
console.log(`${totalCount} ${q.name}`);
console.log(removeNull(arr));
// add the check when the condition is met
await table.updateRecordAsync(q, {
"Level Mismatch": true,
});
}
}
// Summary
console.log(`${countMismatch} mismatches marked in "Level Mismatch" from ${totalCount} records on ${table.name} table`);
// Final thoughts: add a while loop to reduce the writes to records to under 50 at a time?
