How do I filter the table to get only unique rows?

Hello, guys.

Help me please. I have a table with emails repetitions. I need to filter it to get only unique ones. I use .unique() in filter formula but it doesn`t work. What am I doing wrong?

Here is the screenshot

Here`s the link to the doc

Hi there @Alexey_Demin :blush: !

I think I would simply use an interactive filter instead of a formula to filter your table by the test email adresses :blush: .

Thanks for your reply @Pch . But this solution bring me to the opposite result I`m looking for. Interactive filter in your example shows the emails that repeat itself. I need the table with unique emails only without any repetitions.

Sorry @Alexey_Demin , I misunderstood your problem :blush: .

If I understand correctly this time, you want to filter your table table by emails which are not duplicates.

This is not done as easily as using a formula :blush: but it can be done :wink:

You first need to indicate clearly, in your table, which emails are duplicates from the others and once you’ve determined that, then you can filter your table by using a formula (or still an interactive filter)

In my sample, in the Example 1, I’ve added a Row ID field (which is a Property type of field) and a field Count().

The field Count() counts cumulatively how many times Email Test X appears in the table.

So to get to your desired result, after that, you just need to filter your table using the Count() field instead of the emails with Count() = 1

The Example 2 works following the same principle but with a Checkbox field, which you can also use to filter your table :blush:.

There might be other ways to do that too :blush:

I hope this helps :wink:

1 Like

Yep. But seems like this will sort out from the table all emails that are mentioned more then once. I need unique emails only in the table.

If I get you right, you want to display the same table but filter out the rows where the same email has appeared before?

Then the task is split into two steps:

  1. Figure out if this email appeared before
  2. Filter out those rows :slight_smile:

To figure out if this email appeared before, you first need to let Coda know what’s “before”. You can do that by e.g. indexing your rows using

thisTable.Find(thisRow)

or any other way to give them some order (e.g. by simply calculating thisRow.RowId())

Then to see if an email appeared in any of the previous rows, you’ll add a formula:

thisTable.Filter(
  Index < thisRow.Index
  AND email = thisRow.email
).IsNotBlank()

(or .IsBlank() to get true for rows where the email appears for the first time but not the subsequent ones)

All that’s left is to add a filter to only show rows where this checkbox is not set (i.e. no prior row with the same email found).

Implemented in the original doc that you shared:

As for the Unique() function — that’s a function that takes in a list of values and reduces it down to only unique values. That’s when you want to use that value in a formula. It’s useless in a filter like the one you needed here.

1 Like

@Paul_Danyliuk Got it. I had to crack it by myself. Thank you so much.

@Paul_Danyliuk, I am impressed by the beauty of this concise formula , it is so elegant.

I tried to make it work via a button with a FormulaMap but could not differentiate between the complete column and the “ThisRow variation”. Is there a way to do so or does this only work inside a table?

thisTable.Filter(
  Index < ThisRow.Index
  AND email = ThisRow.email
).IsNotBlank()

Thanks!