Dynamically add number in query

I am trying to add a number dynamically from the database in a search. So far my clicking has not resulted in any substantial progress. Is this possible?

This is how it looks, and I want o replace 50 with something more dynamic:

First, your expression doesn’t mean what you think it does.

Somedate<- range ->Someearlierdate:end

Is not a range. It’s the endpoint of the range, which you already know is Somedate, as you just defined the range.

So this resolves to Somedate. And then you are subtracting 50 hours. So this expression resolves to:

Current Date Time +hours -50

A date. Which is why the > comparison is not throwing an error. FYI.

I see what you’re trying to do here, and you’re smart to try and do a range-wise comparison, but you actually can’t do that in this particular context.

Which actually sucks, but it’s a thing. Here’s why:

To check if Date1 (a date) is inside of a DateRange (a date range), we write the expression:

DateRange **contains point** Date1

Unfortunately, there is no converse expression:

Date1 is a point contained by DateRange

^^^ this is not a thing we can do.

“Is contained by” is only available for ranges. We can write:

Range1 is contained by Range2

… which evaluates to true if Range1 is fully enclosed by Range2.

So, you’re on the right track here, but since Created Date is a date and dates don’t have an “is a point contained by” comparator, we have to do it the old fashioned way. Just have two conditions inside your search dialog.

The first one is:

Created Date > Current Date Time +(hours) -50

The second one is:

Created Date < Current Date Time

[^^^ EDIT: This is, of course, a silly constraint: No object can have a Created Date that is AFTER Current Date/Time as such an object would be from the future, but I wrote this on mobile and was trying to get across the more generic comparison of some date being between two other dates.]

Both of these you will be able to construct. (Note: In case it’s not obvious, multiple constraints in a search dialog are logically ANDed together… so this will give you the result you desire.)

As for why you could not add additional dynamic data to your malformed expression: as I noted, your expression as pictured evaluates to a date. The only thing you can do after that would be any additional date operations. (Like +(days)1 or something.

Now, you were NOT really running into the following limitation, but since you are familiar with date ranges, you will surely want to know this:

When we dynamically construct a range using the <- range -> constructor, we can only do math operations on the LEFT HAND side of the expression. That is, you can do math on only one point of the range.

So if we DID want to construct the range of “50 hours ago until now”, we can do that like this:

Current Date Time +(hours) -50 <- range -> Current Date Time

(Aside, the left hand side IS NOT automatically the start of the range. It can be the end. Start and end are determined not by which side of the range constructor they are on, but by which one is greater than the other. We are thankful for this!)

But note that we cannot construct the range “50 hours ago until 10 hours from now” in a single step. Because that would require us to do math on both points of the range — both sides of the constructor.

You might be starting to see the reason for this (and it’s kind of a bullshit reason), but it is what it is: It’s like this…

As you start building this expression, you start with a date. You plop down a date and then the expression editor says, “OK, now you can add any date operators.” So you can do +hours. And then you can do <- range ->. But now we are in range-construction mode. We plop down an object of date type to specify the second point of the range. The expression editor says, “Great! That’s a range!” And now all we can do is access DATE RANGE operators.

And we are going, “Wait wait, Expression editor! We are not done building our date!”

And the expression editor is like, “Nope.”

And we are like, “OK smartass, well, we will use parentheses to let you know that we are still constructing our date!”

And the expression editor says, “Ha ha, Bubble doesn’t have parentheses. Suck it, user.”

And we are like, “ @Bubble, please add parens for situations like this.”

Thanks for the explanation. Actually this works as intended for me, which is to only show notes 50 hours back in time. But what I want to do now is to get the number from a database field like this: Created Date > Current date/time <- range -> Current date/time:end +(hours): - NumberFromDatabase

NumberFromDatabase could be for example 50.

@g111 as @keith explained in much detail, bubble doesn’t allow you to do date operations (e.g. adding/subtracting hours) on the right-hand side of the date range operator.
So it doesn’t matter whether you’re getting the number from the database or even entering a static number, bubble just doesn’t support that.
So you could move the computation to the left side of the range as Keith explained.

1 Like

Yes, anyhow I need to replace a hardcoded value with a dynamic variable from the database if it’s possible,

Right. When you do it in a way that Bubble allows, you will be able to insert dynamic data. You are having trouble inserting dynamic data because of HOW you are trying to do this. The math part goes to the left of the <- range -> constructor.

Here’s a range you can build:

Current date/time +(hours) - some_dynamic_numeric_value <- range -> Current date/time

This will resolve to a date range. Now you could build on that:

Current date/time +(hours) - some_dynamic_numeric_value <- range -> Current date/time contains point some_date

This will resolve to a Boolean (a yes/no as Bubble calls it). It will be yes/true if some_date is inside of the range. It will be no/false otherwise.

But, again, YOU CANNOT BUILD:

Current date/time <- range -> Current date/time +(hours) some_dynamic_numeric_value

That’s not a thing, for the reasons I explained at the end of my post. We should be able to write:

(Any_date_object:any-damn-date-operations-we-choose) <- range -> (Any_other_date_object:any-damn-date-operations-we-choose)

… however, parens are not a thing in Bubble.

(Thanks, @seanhoots for the assist on this!)

Thanks for the long explanation. Somehow my expression works and an expression which returns a yes no answer does not work. I have thoroughly tested that it works and to make it a bit dynamic I moved it to a workflow that use different date ranges based on conditions.