Creating a view of distinct users.

[JeffJohnVol]

I have a table of worklist items that are assigned to users. I want to create an automation that once triggered will execute the "find records" and would go through the distinct list of users that have open tasks.  Open tasks I can do with a filter, but how do I work it out such that if Joe has 3 tasks, the action only executes once for Joe?

I tried the group but it didn't seem to do the trick (unless I'm doing it incorrectly). I could just do it all in a script, but I'd rather use the no-code tools to drive the automation.

Thanks.

[JeffJohnVol]

FWIW, here is what ChatGPT said:

Me:

in airtable, is there a way to create a view of distinct values in a column, so if there was a column called "name" and it had "Jeff", "Jeff", "Bob", "Joe", "Jeff", "Joe", it would return 3 rows of "Jeff", "Bob" and "Joe"?

ChatGPT said:

ChatGPT:

Airtable does not have a built-in feature to automatically create views with distinct values for a particular field. However, there are a few ways you can achieve this:

### Option 1: Use a "Linked Record" to Another Table
1. **Create a new table** for the distinct values (e.g., a new "Names" table).
2. In the original table (where the names column exists), convert the "Name" field to a **"Link to another record"** pointing to the new "Names" table.
3. This will ensure that each name appears only once in the "Names" table, and you can use that table to see the distinct values.

### Option 2: Use the "Group by" Feature
1. In your original table view, click on the **"Group"** button (in the toolbar above your records).
2. Select the column ("Name") you want to group by.
3. Airtable will then group all records by the distinct values in that column, and you can collapse the grouped rows. While this does not create a new view with just distinct values, it helps visualize unique values.

### Option 3: Use a Script in Airtable's Scripting Block
You can write a custom script using Airtable's scripting block that extracts distinct values from a column and displays them or writes them to another table.

Here’s a simple example of how you can script this:

```javascript
let table = base.getTable("Original Table"); // Replace with your table name
let query = await table.selectRecordsAsync();
let distinctValues = new Set();

// Loop through records and add unique values to the Set
for (let record of query.records) {
let name = record.getCellValue("Name"); // Replace with your field name
if (name) {
distinctValues.add(name);
}
}

// Output the distinct values
for (let value of distinctValues) {
console.log(value);
}
```

This script will output the distinct values from the "Name" field in the console.

### Option 4: Use Airtable Automations (with Scripting)
You could also build an automation that runs on specific triggers (e.g., record created) and updates or checks distinct values in another table.

While there is no direct option for a distinct-view, these methods allow you to accomplish similar functionality in Airtable.

[TheTimeSavingCo]

Hmm I think you'd need a table where each record represented a single user to handle this I'm afraid, and you'd make the automation use that table as a repeating group input, thus running once per user

[JeffJohnVol · Accepted solution]

I ended up adding a new column "Unique" ("Yes"/"No") and wrote a script to update first occurrence of each entry to Yes. Then I created a new view with a filter on Unique.  That will do what I need. Code listed below:

// Load the "Worklist" table
let worklistTable = base.getTable("Worklist");

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

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

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

    // Ensure fullName is a string if it's returned as an array (from a lookup, etc.)
    if (Array.isArray(fullName)) {
        fullName = fullName.join(", ");
    }
    
    // Check if status is either "To Do" or "In Progress" in the single select
    if (status.name === "To Do" || status.name === "In Progress") {
        if (fullName) {
            // Check if this "Full Name" has been encountered before
            if (!encounteredNames.has(fullName)) {
                // If not encountered before, mark as "Yes" and add to the set
                await worklistTable.updateRecordAsync(record.id, {
                    "Unique": { name: "Yes" }
                });
                encounteredNames.add(fullName); // Add the name to the set
            } else {
                // If already encountered, mark as "No"
                await worklistTable.updateRecordAsync(record.id, {
                    "Unique": { name: "No" }
                });
            }
        }
    }
}