Filter to display DISTINCT (or Unique) rows based on specified columns

Feature Requests
1

In SQL you can use a DISTINCT to select only unique column values from rows that contain values that are repeated. There is also a GROUP BY in SQL to essentially do the same thing, but has unique benefits that allow you to do things similar to using rollups in Glide.

The problem comes up quite often where people want to display unique rows in a collection that contains several duplicates. I think it would be beneficial if we had a Distinct/Unique option in the collection configuration where we can specify any number of columns to be considered as a unique key.

For example let’s say we have a table like this:

Name Category Amount
Jeff Type 1 50
Jeff Type 1 51
Jeff Type 2 150
Jeff Type 2 151
Jeff Type 3 250
Peter Type 1 50
Peter Type 1 51
Peter Type 2 150
Peter Type 2 151
Peter Type 3 250

Ultimately I want the collection to only display Distinct/Unique rows like this:

Name Category Amount
Jeff Type 1 50
Jeff Type 2 150
Jeff Type 3 250
Peter Type 1 50
Peter Type 2 150
Peter Type 3 250

What I’m thinking is to have a Distinct/Unique configuration option in the collection settings where I could specify both Name and Category (and any other number of columns) as the columns to be used to define a row as unique.

Now I’m sure some of you might already see a problem…if we are essentially filtering out duplicates, then which Amount do we display? I think 99% of the time, that wouldn’t be an issue, but someone will run into it eventually. I don’t have a good answer for that, but I’m thinking we would always display the first distinct row and ignore the rest. The collection sorting could control which row is considered the first one in a unique grouping, or maybe we could have something like how a Single Value column works where you can specify first or last row. I’m open to any thoughts to improve the idea.

3 Likes
2

Ya…completely agree that it would be much more intuitive if we had something native. In Google Sheets, you can create a Unique formula.

Until then…

Here’s one way to achieve this using the Miracle Method and a helper table:

Without a helper table, you’d need to use a self relation like this:

2 Likes
3

I typically use or recommend the following method:

  • Make sure there is a Row ID column.
  • Create a single Relation or Query to link similar rows to themselves based on similar values.
  • Create a Lookup or Single Value column to retrieve the first matching Row ID from the Relation/Query.
  • Finally create an IF column comparing the row’s Row ID to the Lookup/Single Value column and return true if there is a match. Only the first occurrence will be a match.
4 Likes
4

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.

6

@Jeff_Hager any idea if this will work with big tables? Or how would you approach that when data is in a big table.

I need to create a list of unique values from a column

7

I’m not sure. I’ve never used big tables. As long as the Relation/Query can look at the entire table, then I would think everything else would work. I know @Darren_Murphy is pretty familiar with the ins and outs of big tables. Maybe he could say for sure.

1 Like
8

The relation/single value/if-then-else can be used with Big Tables, BUT… the result cannot be used as a Collection filter. Similarly, if you tried to take a joined list of the unique values, you’d be limited to the first 100 rows in the table.

I’m not sure if there is an effective workaround to this one, it’s something I’d need to experiment with. I’m away on vacation at the moment, so that’s not something I can do right now.

4 Likes