Script to compare two views by any field and modify a checkbox on match

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,});
    }
}
1 Like

Welcome to the community, @Nikita_Miltiadou! :smiley: Thanks for sharing your script! I moved the post to the “Show and Tell” category, as that’s a more appropriate place for sharing solutions with the community.