Search for data with filtering or maybe grouping

Hi I am really hoping someone can point me in the right direction with this issue I am having.

I have a list with tasks. Tasks has a field “complete” which is “yes” or “no”. Then I have another field “completed on” and it is a date field. There is also an order field.

So for this list I want to show 5 tasks in the order of importance. I don’t want to show the completed ones UNLESS they are completed on that day.

So basically I need to query for completed_on = current date AND complete = no and sorted by order. But I can’t figure out how to actually do this. When I get the first part right, it constrains it to only the ones complete_on = current date.

I have tried many things relating to constraining, :filitered, :group_by but I just can’t seem to get the correct way to pull this in. Any ideas?

Thanks in advance.

Can you share a screenshot of how you have this set up?

Thank you so much for looking at this… maybe I am going about it all wrong. I have tried a variety of things and I understand why this doesn’t actually work. I am not just looking to sort by “completed_on”, I want to filter by that and sort by Order. It is the filtering with AND that seems to be tripping me up.

Hey @lauren1

You are trying to get the following list:

The list of Tasks that are:

  1. The Task is NOT complete. (Field Complete is “no”)
  2. OR the Task may be complete (field Complete is “yes”, but only if it has been completed “today” (whatever that means… we will get to that)

Once we have that list, you just want to show the first 5 as they would appear when sorted by “Order” (which indicates their importance).


Let’s examine these criteria and see how we can model them in Bubble… and see if we can do this with a constraint that we could put on the Search, so that we download as little extraneous data as possible to the browser (rather than having to filter a big list in the browser):

  1. Note: When we do a Search in Bubble, we can apply multiple constraints, but these constraints are logically ANDed together. We have no way of ORing constraints in the Search’s constraint dialog.

  2. When we examine the idea above, we see that we want to return Tasks whose status may be Complete or not Complete. So it seems that we would not attempt to constrain on “Complete”. (Complete being a boolean, any basic constraint we do here will leave us with ONLY Tasks that are Complete OR Tasks that are not Complete. But not both.) But we want both (for certain cases).

  3. So the question is: Is there any other difference between a Complete Task and a not Complete Task. Yes, yes there is: a Complete Task will have a non-empty Completed_On date. A not Complete Task will have an empty Completed_On date. This is good. Let’s remember this.

Let’s turn to the idea of Complete Tasks. It is OK for our list to contain them if and only if they were completed “today”. How do we determine this? There are two ways:

  1. Date-wise comparison: A task must have been completed “today” if its Completed_On date is greater than the starting point of today. The starting point of today is this:

Current date/time change hours to 0 change minutes to 0 change seconds to 0

So, if Task's Completed_On > Current date/time change hours to 0 change minutes to 0 change seconds to 0, the Task was completed today. (Note that time only moves in one direction – forward – so we do not need to check if the task was completed before the end of today. If such a Task exists, that task is from the future and we should be very very spooked right now.)

Similarly, if Task's Completed_On < Current date/time change hours to 0 change minutes to 0 change seconds to 0 the Task was completed before today. (Again, there is no such thing as a Task completed after today. Such a task would be from the future.)

  1. Text-wise comparison: If we want to know whether two date objects (which are of course date/time objects) happened on the same day, we can compare just the “calendar date” parts as text and see if they are identical. Like:

Task's Completed_On:formatted as mm/dd/yyyy is Current date/time:formatted as mm/dd/yyyy
^^^^ Note: Both :formatted as operators must be configured identically.


I did some testing here and while we can evaluate an empty date against a non-empty date, this seems not to be the case in a Search constraint.

For example: Suppose that Some_Task’s Completed_On is empty (it is a null date). This expression will evaluate to “yes” in a text element:

Some_Tasks's Completed_On < Current date/time (just as an example)

… and conversely the > condition will evaluate to no.

It seems like we could use this fact to construct a constraint that passes what we want regardless of whether Completed_On is empty or not, but it seems we cannot. (Oh foo!) If we could, we might be able to do this in just one slick Search.

When we use that as a Search constraint – regardless of whether we “ignore empty constraints”, no Task with an empty Completed_On date will pass such a constraint. It’s not entirely clear to me why, but this approach is a non-starter!

Example: A constraint set up this way…

… only returns Tasks that have a non-empty Completed_On date. This seems like a bug given what we know about the behavior of a similar expression in a text element. This example, BTW, yields the following in run mode:

Even though I have Tasks in my sample app that have empty Completed_On dates, they simply will not appear here, regardless of the “Ignore empty constraints” setting in the constraints dialog.

But we CAN get the Completed tasks that happened today using this technique. So that’s good. And of course not Completed tasks are just Do a Search for… Tasks where Complete is “no”.


  1. Well, we can’t do the textwise date comparison in the constraint as we can’t turn Completed_On into text there. So not helpful. (But aren’t you glad to know about this method of determining if two dates/times occur on the same day?)


So, unless we want to download ALL Tasks to the browser and process them there (we don’t really want to do that if we can avoid it), we need to combine two lists. Two lists of the same type can be combined using the :merged with operator.

So, we know a way to get Completed tasks that happened today. That Search is like this:

In my sample app this yields just the Tasks that are complete but where completion happened today:

Now let’s get the Tasks that are NOT complete:

This yields:

Now let’s combine those:

^^^ the first search has the “Completed_On > start of today” condition. The second Search has the “Complete = no” condition.

This yields a list of Tasks that got completed today combined with a list of Tasks that are not completed, as we expect:

NOW: YOUR app has a concept of “order”. So, in YOUR app, you would (1) not sort either of these searches in the constraint dialogs – cuz that doesn’t matter. (2) append a :sorted operator to this combined search that sorts these the combined list by order. I don’t know what your order represents so that’s up to you how that works. But in my example, I’m just going to sort them by Created Date descending so you can see how the expression looks (where mine says “Created Date” yours would say Order):

This yields:

Now you want to truncate that list just to the first five entries. There are two ways of doing that:

If you are displaying this list in an RG, you could just force the RG to be a fixed number of cells set to 5.

However, if you want to truncate the underlying list (so that, for example, your RG could be any style), you add the :items until operator like this:

This yields:

And there you go.

Now, a caveat: You seem to be SEARCHING FOR ALL TASKS. This of course is fine as you’re developing your app. But note that in a multi-user app, obviously, you want a constraint everywhere in this example of something like “Created by = Current user” right? Because of course you don’t wanna mix up MY Tasks with YOUR Tasks. I’m assuming you get this, but FYI.

(Aside: such a constraint is not necessary if you have a privacy rule set up that allows only the Task’s creator to see their tasks, but for clarity of your programming I would still have that. But that’s just me.)

The sample app I futzed around with and grabbed snips from, is this page in my Timezoner demo app. (The editor is viewable by all.)

Here’s the run mode:

And here’s edit mode for this page:

Since I believe that this is actually the most performant and scalable way to do this particular search, I’m not going to show how you would do this with Do a Search for… Tasks (unconstrained):filtered (and then with Advanced set in the dialog and an expression), but you could explore that on your own now that you grok some new concepts. (The bad thing about :filtered – if you do it client side / in the browser as we would be doing here – is that ALL tasks will get downloaded to the browser and then the filter operation will be performed by the browser. This is wasteful.)


Hi Keith, I am going to dig into this a bit later, but I just want to say thank you so much for taking the time to explain all of this to me. I can’t wait to sit down and learn!

1 Like

Hey @lauren, it’s a little bit of a ramble as I THOUGHT I knew what the answer would be and then discovered that curveball (that, in this case, Search constraints DO NOT behave in the same way as any other evaluation of an empty date vs a non-empty date would).

The tl;dr version of this is:

The best way to do this is to :merge (1) Search for Tasks that were completed today (I explain how to do that) with (2) Search for Tasks where Complete = “no” (no explanation for this is needed, I should think).

You can kill both birds with one stone using :filtered with the Advanced condition (in which you can do OR type constraints) but this means downloading all Tasks to the browser first and this is not advisable. (So I do not give that answer.)

@keith I just want to follow up and tell you how helpful this was. Not only did it resolve my issue, but also introduced me to new concepts in Bubble. I appreciate your time so much. Thanks.

1 Like