Table of Contents

Set Member Worklist Flags Script

1. Purpose

This script is designed to manage and update worklist records in an Airtable base. Specifically, it:

By automating this process, the script improves record accuracy, prevents duplication issues, and helps maintain an organized task management system.

2. Flowchart Representation

Below is a structured flowchart representation of how the script processes data:

  1. Initialize an empty **Set (uniqueSet)** to track unique names.
  2. Load the **"Worklist"** table.
  3. Load the **"Open Tasks"** view from the **"Worklist"** table.
  4. Query all records from the **"Open Tasks"** view.
  5. Query all records from the **"Worklist"** table, retrieving:
     - "Full Name"
     - "Unique"
     - "Status"
  6. Loop through each record:
     - Extract **Full Name** and **Status**.
     - Convert **Full Name** to a consistent string format.
     - If **Status** is **"To Do"** or **"In Progress"**, proceed:
       - If the **Full Name** has not been encountered before:
         - Mark the record as **"Unique"**.
         - Add **Full Name** to `uniqueSet`.
       - Otherwise, ensure **"Unique"** is set to `false`.
  7. Update the **"Unique"** field for the applicable records.
  8. Exit the script.

3. Description of Operation

The script operates as follows:

Step 1: Initialization

Step 2: Retrieving Records

Step 3: Processing the Worklist

Step 4: Updating Records

Step 5: Completion

4. Program Code

// Initialize a Set to track unique names
let uniqueSet = new Set();

// Load the "Worklist" table
let worklistTable = base.getTable("Worklist");
// Get the "Open Tasks" view
let openView = worklistTable.getView("Open Tasks");

// Query the records from the "Open Tasks" view
let query = await openView.selectRecordsAsync();

// Query all records in the "Worklist" table
let worklistQuery = await worklistTable.selectRecordsAsync({
    fields: ["Full Name", "Unique", "Status"] // Include necessary fields
});

// Create a set to track encountered "Full Name" values
let encounteredNames = new Set();

// Loop through all records in the "Worklist" table
for (let record of worklistQuery.records) {
    let fullName = record.getCellValue("Full Name");
    let status = record.getCellValue("Status");

    // Ensure fullName is processed correctly (array or string)
    if (Array.isArray(fullName)) {
        fullName = fullName.join(", ");
    }

    // Check if status is "To Do" or "In Progress"
    if (status === "To Do" || status === "In Progress") {
        if (!encounteredNames.has(fullName)) {
            encounteredNames.add(fullName);
            uniqueSet.add(record.id);
        }
    }
}

// Prepare batch updates
let updates = worklistQuery.records.map(record => {
    return {
        id: record.id,
        fields: {
            "Unique": uniqueSet.has(record.id)
        }
    };
});

// Batch update records (Airtable allows 50 updates per batch)
while (updates.length > 0) {
    await worklistTable.updateRecordsAsync(updates.splice(0, 50));
}

console.log("Updated 'Unique' flags for applicable worklist members.");