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

• Identifies unique members in the “Worklist” table.
• Tracks whether a member has active tasks.
• Ensures only one instance of each member is flagged as “Unique”.
• Updates the worklist dynamically to maintain correct tracking of active members.

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

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.

The script operates as follows:

Step 1: Initialization

• A Set (uniqueSet) is created to track members who have active tasks.
• The script loads the Worklist Table and the “Open Tasks” view.

Step 2: Retrieving Records

• The script queries all records from the Worklist table.
• It extracts the Full Name and Status of each record.
• The Full Name is formatted correctly to handle different data structures (arrays or strings).

Step 3: Processing the Worklist

• If a member has a Status of “To Do” or “In Progress”, the script checks if they have been encountered before:
  • If first occurrence, mark the record as “Unique” and add to `uniqueSet`.
  • If already encountered, ensure all other instances are not marked as Unique.

Step 4: Updating Records

• Updates to the “Unique” field are processed in batches of 50 for efficiency.
• Ensures database accuracy by limiting each unique member to only one flagged instance.

Step 5: Completion

• Once all records have been processed and updated, the script completes execution.
• The result is a clean and organized worklist where each member is tracked accurately.

// 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.");