Simple filtering with date range

[julienallard1]

Hi friends, I’m trying to do a relatively simple filtering on a repeating group, but can’t seem to figure out the right expression.

In the database, I have Tasks (thing). On my page there’s a repeating group listing those tasks. Above, I have a date picker and a dropdown with option (7, 15 or 30 days before).

What I’m trying to achieve is to show tasks that match the picked date or 7/15/30 days before that date. What I’ve tried:

• Put a constraint in the search of the repeating group with:
  Date =< then datepicker and
  Date => then datepicker(+days with the value of the dropdown which is a negative date range)

• Put a constraint in the search of the repeating group with:
  Date is in …<- range ->… but never got to make a valid expression

Just to mention, all tasks hour/min/s are set to 0.

Any help would be appreciated!

[julienallard1]

Anyone?

[keith]

You have some date. I’m going to call it “Task’s Date”. (You might actually be referring to Tasks’s Created Date or Task’s Modified Date or Task’s Completion Date or any number of other things.)

You have some other date, picked from a date picker. I’m going to call this “Picked Date”. (Such a date could come from anywhere. This could be any date.)

Let’s now look at a few simple things:

1. Is Task’s Date before Picked Date? Task's Date < Picked Date will be Yes (true) if so. Alternatively, Picked Date > Tasks's Date will be Yes (true) if so.

2. Is Task’s Date after Picked Date? Task's Date > Picked Date will be Yes (true) if so. Alternatively Picked Date < Task's Date will be Yes (true) if so.

It’s helpful to realize that we can ask these questions (and get a yes answer) in two different ways, as shown above. This becomes important very soon.

Let us now ask, what is the point in time 7 days before Picked Date? (What’s the point in time any number of days before Picked Date, where X is any number of days?):

1. 7 days before Picked Date is: Picked Date+(days)-7
2. X days before Picked Date is: Picked Date+(days)-X

OK cool. Can we actually construct such expressions in Bubble? Hmm. Well, in an expression field, we can construct the expression:

Picked Date+(days)-7

But only because we can type the minus-sign seven part. Here’s an example:

Run mode:

You might have noticed, however, that it is impossible to literally construct the expression:

Picked Date+(days)-X where X is some other dynamic value.

Here’s an example… Here is custom state X:

Now let’s try and build Calendar Grid A’s Date Clicked +(days)-X… I’d have to post a video to show, but you can’t do that (there’s a reason). The closest we can get is:

That is: We can only ADD X. If X contains a negative value then we are subtracting days. But we would have to put that negative value into X.

You already understand this, but I just wanted to point this out for others who might stumble across this.

But I think you might now see what you are doing wrong. You say you tried:

@julienallard1

Put a constraint in the search of the repeating group with:
Date =< then datepicker and
Date => then datepicker(+days with the value of the dropdown which is a negative date range)

This would obviously not work as you don’t need a negative DATE RANGE. You need a negative NUMBER. Your numbers should be -7, -15 and -30. There’s no such thing as a negative date range. (Date range is a data type that describes a time period from dateX to dateY. It does not resolve to a number. Additionally, dateX (a date) minus dateY (another date) is ALSO not a number. The expression dateX-dateY resolves to a date interval (which is another data type altogether that is ACTUALLY A UNIT OF TIME). There are operators on date intervals that can turn them into numbers, however.)

The +(days) operator (and all of the +(interval) operators) takes a NUMBER as its argument. So your dropdown values need to be NUMERIC, not ranges, not dates, not date intervals.)

Now, back to the general problem: Let’s pretend for a minute that we could write -X:

Is Task’s Date after Picked Date+(days)-X? That could be expressed as either:

Task's Date > Picked Date+(days)-X will be Yes (true) if so. Alternatively Picked Date+(days)-X < Task's Date will be Yes (true) if so.

Both of these kind of suck, for different reasons. The first one has -X on the right hand side (where we can’t always do date math). The second one has date math on the left hand side (good), but still has “-X” in it (bad because we can’t write negative X).

BUT… A-ha! Look at that first expression again: Task's Date > Picked Date+(days)-X

That could also be written as: Task's Date+(days) X > Picked Date

See? We could add POSITIVE X days to both sides of the expression and (1) get rid of the negative sign and (2) move the +(days) operator to the left hand side of the expression (where we can always do date math).

(We are just asking the question a slightly different way. Instead of asking, “Is the task date after X days before the picked date?” we ask, “Is X days after the task date after the picked date?” These questions have the same answer!)

So, you could use that as your constraint:

Task’s Date+(days) X > Picked Date

And now, you could express your options as POSITIVE numbers, 7, 15, and 30. This makes more sense interface-wise, even if the constraint is a little hard to understand.

[julienallard1]

Hi @keith, first I want to say I’m impressed by the time you took to answer me. You could have just shipped me a solution, but instead you took the time to teach me. This inspires me to also help other members on the forum whenever I can. Thank you vey much.

I did manage to make it work on my side. I changed the values from the dropdown from date interval to number that correspond to the number of days I want to look back.

However, Bubble takes very well negative values. I’ve tested it in an expression inside a text element and also in a constraint in a do a search.

Everything that’s after +(days): is the pointer to the dropdown’s value where I can select the days back. That value is -7 or -15 or -30.

Again thank you so much for your help!

[keith]

Glad that helped, @julienallard1! Of course, properties, states and variables can have negative values. And you had already thought of and anticipated this.

The issue I’m pointing out is that, if you have some property, state or variable on which you need to invert the sign, you often cannot do that in the context of a single expression (due to the way the expression builder evaluates). This often gets people “stuck”.

What the second part of my answer to you explains is that (at least in this particular case) the conditional expression that asks, “is date1 after (date2 MINUS some_time_interval)?” can be rewritten such that (1) the date math is on the left side of the expression and (2) we need not invert the sign on the time interval.

(We can ask: “is date1 PLUS some_time_interval after date 2?” This is, in fact, the same question as before, but this expression can always be built, regardless of whether some_time_interval is a constant that we want to type in, the value of a Bubble property, or a value held in a custom state.)