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.
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
Step 2: Retrieving Records
Step 3: Processing the Worklist
Step 4: Updating Records
Step 5: Completion
// 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.");