I made this script which allows you to compare two lists based off a field value, then modify a checkbox when matches are found.
My use case example:
- I create a custom list of contacts to email about a product by selecting a checkbox next to who I want to send to.
- I save this list of contacts as a view in Airtable by filtering where contacts have a ticked checkbox.
- I make another list of contacts to email about yet another product, however, I want to make sure that I’m not sending this to the previous contacts and their companies I sent to previously.
In this case the script works as such:
- Table is my database
- Reference view is my first contact list view
- View to Modify is the second list I want to compare to the first
- Match field is the ‘Company’ field (string in airtable) - this could be anything
- Field to modify is the checkbox which is ticked for my second view
- Set cell as ‘unticked’ to remove the companies I have already contacted
You can use this script to do anything however, essentially it compares two views, and where there is matches modifies a checkbox to be ticked or unticked.
CODE BELOW
//Header
output.markdown("### Compare Lists and Set Value");
// get the table name
output.markdown("### Select the Table");
let sourceTable = await input.tableAsync("Pick the table:");
let sourceTableName = sourceTable.name;
// prompt the user to pick a view:
output.markdown("### View to Reference");
let sublistView = await input.viewAsync("Select the view to reference", sourceTable);
let sublistViewName = sublistView.name;
let sublist = sourceTable.getView(sublistViewName);
let sublistRec = await sublist.selectRecordsAsync();
// prompt the user to pick a view:
output.markdown("### View to Modify");
let sourceView = await input.viewAsync("Pick a view to modify", sourceTable);
let sourceViewName = sourceView.name;
let sourcelist = sourceTable.getView(sourceViewName);
let sourcelistRec = await sourcelist.selectRecordsAsync();
// get the source field name
output.markdown("### Match Field");
let sourceField = await input.fieldAsync("Pick which field to reference:", sourceTable.id);
let sourceFieldName = sourceField.name;
//choose checkbox to modify
output.markdown("### Field to Modify");
let modifyField = await input.fieldAsync("Pick which field to modify:", sourceTable.id);
let modifyFieldName = modifyField.name;
//choose cell value
output.markdown("### Set Cell Value");
let setcell = await input.buttonsAsync(
'Set the cell value',
[
{label: '✅ ', value: true},
{label: ' ', value: false}
],
);
//build the sublist array to match sourcelist against
let subArray = [];
for (let record of sublistRec.records) {
subArray.push(record.getCellValueAsString(sourceFieldName));
}
//compare sublist to sourcelist and preform action on sourcelist
for (let record of sourcelistRec.records) {
if (subArray.includes(record.getCellValueAsString(sourceFieldName))) {
await sourceTable.updateRecordAsync(record, {
[modifyFieldName]: setcell,});
}
}
