Filtering List by Count of Unique Field Items

I’m hoping the Bubble community can help me out with an operator or way of structuring a series of “Do a Search For”'s.

Background on issue:
Every item in my table has a date associated with it. Date’s are not unique and there are likely multiple records per date.

Here are the steps that I would like to perform as I diagram out the sequence:

  1. Retrieve the a list of records from the table.
  2. Count how many times each unique date is used from the list of records.
  3. Return a list of unique dates with a count of the number of times each is used where count > 1.
  4. Filter the original list of records from the table to only include those with a date in the list returned in step 4.
  5. Add list from step 4 to a custom state.

By no means do I necessarily need to do anything with the list in step 3, so if you can think of a way to achieve the same result without returning the list, have at. I just cannot figure out, using “Do a Search For”, how to achieve this. I can picture the SQL, but I’m having trouble with the Bubble UI.

Any help is appreciated - thanks.

Hi there, @silkymitts… if I understand your post correctly, you want a list of records where the record’s date appears more than once in the table, right? If that is correct, I have a suggestion that probably isn’t what you expected, but it would make the search super simple, and I would likely consider going this route if I was you.

After playing around with this one for a while, I couldn’t come up with the necessary search(es) either. Now, that doesn’t mean a search-related solution doesn’t exist, of course (I could definitely have been overthinking it), and I’m guessing the List Shifter plugin might even do the trick here. That being said, if you are open to the idea of adding a field to the table and doing some processing (which could be done on the backend) as records are added to the table, the whole thing could be simplified quite a bit.

To get to the suggestion, you could add a field called count to the table. When a record is added, store a number in that field that is a count of the number of records that have the same date as the newly-added record. Then, if the number in the field of the newly-added record is greater than 1, make changes to the list of records that have the same date, and update their count to the same number. With that setup in place, you would simply need to do a search for records where the count field is greater than 1, and I believe you would end up with the desired list of records.

Anyway, that’s what I’ve got, and I hope this helps, even if it only serves as the worst idea you’ve ever heard and something you know you definitely don’t want to do. :slight_smile:


Thanks Mike.

I went down the same road and actually did something pretty similar with my temporary solution. I don’t want to, but maybe I’ll have to investigate the List Shifter plugin and see if that would help me out.

This table is updated from a bunch of different places (changing whether the records would be counted from the original step 1 search) so it would be great if I could figure out how to return this list as needed.

Really appreciate you playing around with it. It’s a tricky one.

1 Like

I kept playing around with it, and I got something that looks like it produces the desired list… and yes, in my example, I did call the data type silkymitt. :slight_smile:

Here is the main search…

There are no constraints on the search after the is in operator. Here is the Group By dialog (you can set the starting date to whatever you want, of course)…

Here is the advanced filter…

A tricky one, indeed, and now I can stop thinking about it forever. :slight_smile:

1 Like

@mikeloc BRILLIANT!

That exactly solved my issues. For anyone reading later, I was a little confused on how to get the “filtered’s date” value (to eliminate the error that exists up to that point. But you just have to choose “each item’s _____ [field name]____” and you’ll be set.

Mike, you’re a peach. Thanks so much for the help.

1 Like

This topic was automatically closed after 70 days. New replies are no longer allowed.