How to count unique records in grouped view


#1

Hi! We’re using Airtable as client & case management for our local diaper pantry. I’m trying to figure out how many unique clients we saw within a time frame. We can filter by date, then group by client code. For instance, right now that view shows that we had 95 visits in the time frame we’re searching. We can group by client code, then count how many groups - but there’s gotta be an easier way to figure out how many unique visits, right? I feel like I’m missing something super obvious but this is all still pretty new to me. Thanks in advance for any help!


#2

I was having a very similar problem and someone just helped me solve it yesterday, so maybe it will work for you?

Here is what he said:

There is a workaround that will get you the number you want, but it’s not pretty…
Should only take about 5-10 minutes to set up.

  • Step 1:
    Create a new table called “Summary” or some-such
  • Step 2:
    Create a linked record field in your grouped table that links to a single record in “Summary”
  • Step 3:
    Link every single record in your grouped table to the same record in “Summary” (this can be achieved by linking the top record, then copying that field, selecting that field for all records [ ctrl / cmd + shift + down arrow ] and pasting)
  • Step 4:
    Create a rollup field in the “Summary” table that rolls up the same field you are grouping on from your other table, using this formula in the rollup field:
COUNTA(ARRAYUNIQUE(values))

This will count the unique values from the field rolled up, and that count will match the number of groups that exist in your grouped table, because a group is created for each unique value in that field.