Calendar 'Contains' Operator

[Ankur]

Continuing the discussion from How to use the range funtions - e.g of how to use it:

Hi @keith
Such a wonderful and detailed explanation here, in fact I had to recreate all the scenarios to learn it, which I did successfully. Could you tell a little more about the ‘Contains’ operator, like in the examples given in your thread there is a definite time interval (Of 15 Secs or 10 Days), how do we search for a condition when the date is to be fetched from the database?

In the scenario above both the date ranges (Date range and Other date range) are defined, date range is current date/ time, other date range is current date/ time + 10 days, but I want to check whether the other date range is already there in the database or not which I am not able to do.

I am working on a scheduler where people make reservations for using a meeting room and the time of each reservation should not overlap. There should be a pop-up instead which tells that the time slot is not available.

I have also put in place a time interval field which calculates difference between ‘from time’ to ‘to time’ in hours. But I am not able to get this working anyhow. Please help.

[Ankur]

Hey Guys,
It’s really frustrating, I have been trying to figure this out for the past 3 days but no success. Can somebody help me please?
I am just not able to search event ranges already in the DB, rest all is working fine. I have to compare the new booking request with the one’s already lying in the DB so that events do not overlap. Every time I try to enter some expression it asks for ‘more’ and remains in red.

[mikeloc]

Hi there, @Ankur… I don’t know if this will help you, but I do something in one of my apps that is very similar to what you have described (assuming I understand your post correctly).

When a user tries to enter a date range for a new occurrence in my app, I check the date range against the ranges that are already in the database, and if the new range overlaps with an existing range, I show an alert to the user and terminate the workflow. Does that sound like what you are trying to do? If so, here is the Only when condition that determines whether or not to display the alert…

I’m thinking you might be able to use something like that… what do you think?

Best…
Mike

[Ankur]

Hey @mikeloc,
Thanks for the prompt reply. Yes this is exactly what I want in my app, but I am not able to understand the ‘count>0’ in your algorithm. Where do you exactly update it?

I want to show you what exactly I have been able to achieve so far:
My events DB has these fields:

My workflow updates like this:

The pop-up which I want to show (But am failing to do) is:

Can you share more information please?

[mikeloc]

I’m happy to share more information, @Ankur.

The way my condition works is that I’m searching through the existing ranges to see if any of them overlap with the new range, and I am counting those overlaps. So, if the condition comes back with no overlaps (or a count of 0), then I know it’s okay to move forward with saving the new range. If I get a count of greater than 0, I know there is at least one overlap, and I can show the alert and terminate the workflow. Does that make sense? With regard to your exact question of where do I update that count, it is all contained within the condition itself (that is, I am not storing a count somewhere).

As to why your exact condition is not working, I would have to recreate your setup and test it out, so I can’t say for sure at this point.

[Ankur]

Thanks @mikeloc
I am sure going to give it a try and hope I join the right pieces.
If you don’t mind can I disturb you again if I fail?

[Ankur]

Hello again @mikeloc
I tried the condition of formatting as a number but slightly in a different way and seems to work for me as it shows the correct value when the app is run, it shows whether a condition is met or not (0 and 1). In the screenshot below 8th of March is already booked between 1500 and 1600 hrs hence the number showing below (Circled) is 1.

On the other hand When a different time is selected (1700 to 1800 hrs) which is not already reserved it is showing 0:

So far so good, but still the correct pop-up doesn’t show which is required for confirmation or rejection of a reservation. I tried to run it in debug mode and here is the result.

The Condition that I have put for pop-up to show is:

This is supposed to show ‘Not Available’ if overlapping’s value is 1, but it is still shows ‘Booking Successful’ although the overlapping’s value is 1.

I tested again by changing the overlapping’s value to 0 but same result:

What could be the problem?

Update: The condition is working absolutely fine in DB, when the dates are overlapping a new reservation is not getting created.

[mikeloc]

Hi, @Ankur… so the workflow stops the overlapping reservation from being created but doesn’t show the correct message to the user, right? Well, one out of two ain’t bad, eh?

As you mentioned, you went a different way than I suggested, so it’s hard for me to tell why it’s not showing the correct message. Can you share some additional screenshots of the workflow that displays the popup and maybe the popup itself?

[keith]

So, you’ll have an object (what Bubble calls a Thing) in your database that represents a Reservation. There are many ways to do this, but for the sake of example, let’s say it’s like this

A Reservation has a start and end time which you store as a date range. Further it would be associated with a Room, right?

So it would at a minimum look like this:

And then, for example, if you want to get all reservations that are on some certain day, that search is like:

In the above “Calendar Grid Pro’s Date Clicked” is just 12:00 AM on some given day.

[mikeloc]

The real answer is we just needed to wait for @keith to show up.

Hope I didn’t muddy the waters… I just have something similar to what Ankur described in one of my apps and thought it might help out, even if it’s just food for thought.

[keith]

The type of interface that I think the OP is trying to create is a pretty common one, but you hit some issues in trying to do this (and I’ve still not really picked up work on my “Time Grid” companion for Calendar Grid Pro, which would provide the type of interface that people really want).

But I’m working up a little example and a video to go along with it that covers how you would do this as it is interesting. It turns out that, to make it easy, I need to add a couple of new functions to List Shifter (and I’ve not quite got them debugged yet). But here’s a lil’ preview:

If you watch this, you’ll see the issue: Note that I book one reservation right on top of another. How would we go about filtering the list of available time slots so that a user can’t book a time that’s already blocked?

Bubble’s built-in list-oriented expressions do not enable the filtering we need to do (where we must iterate over a list of time slots and also over a list of blocked ranges). You might be able to work it out, but if you do, that processing will be very inefficient. But (with the addition of “contains” and “contains point” functions in List Shifter) we could do it really easily. So that’s the next little update to List Shifter and I’ll explain how we would use that in this context.

Also in the above video you can see that I’m giving my still-in-development “Reanimator” plugin a little workout.

[Ankur]

Thanks a lot @mikeloc and @keith for the reply,

First thing I want to understand from @keith is what video editor are you using, that’s gonna be great help without having to share the entire editor itself?

Second query is why have you used +1days in this expression? We are talking about any indefinite date.

And my most important question, if an entry is updating in database then why is it not showing up in pop-up when the expression is right? (As in my case where the DB is updating but pop-up not showing correct value)

If you allow I can share editor to my app so that you can have a deeper insight of what I am struggling with.

In fact the reservations that I mentioned are to be done by me when a guest calls and asks to book it. I manage a coworking space by the name of The Co-Workers and am letting out meeting rooms for people to book it, I have to keep a record of all bookings so that none of them overlaps. It’s difficult to do it manually or in excel sheets hence I am trying to address it via an application.

[keith]

You asked about how and where you might use “contained by” in a search. What I show above is an example that asks the following:

“Fetch me the list of Reservations whose start and end times are within some specific day?” (and do it using “contained by”…)

So, if we have a thing called a Reservation, whose start and end times are held in a date range field called Booked Range, we:

1. Search for Reservations
2. Set a constraint using field Booked Range
3. Use constraint “contained by”
4. Specify the date range for the constraint to be some day

Your next question is, “How do we represent ‘the entirety of some given calendar day’ as a date range?”

Well, the range that a given calendar day spans (remember that a date range is just a thing with two dates – the earlier specifying the beginning of the range and the later specifying the end of the range) is this:

The very first moment of the day … until… the very first moment of the next day

First we must construct (or grab) a date that is “the day we are interested in at 12:00 AM”. Once we have that (let’s call it “MyDay”), we can easily get the first moment of the next day as:

MyDay +(days) 1

And then our Bubble range can be expressed as:

MyDay +(days) 1 <- range -> MyDay

Why, you might ask, do we not write:

MyDay <- range -> MyDay +(days) 1

… and the answer is that you can’t construct that expression in Bubble (just try it and you’ll see).

So we do our date math on the LEFT side of the expression.

So anyway, in my example, the day we are interested in is held in Calendar Grid Pro B’s Date Clicked (this will be a date like March 4, 2020 at 12:00 AM). And so the range I build is just:

Calendar Grid Pro B's Date Clicked +(days) 1 <- range -> Calendar Grid Pro B's Date Clicked

[Ankur]

Hey @keith @mikeloc

I just couldn’t do it the way you guys guided me but it somehow happened, I am very bad in understanding states etc.
But as it is made successfully to a certain extent I thought this should be shared with the community so that if anybody else (again bad at states) encountered this problem then it could be of some help to them, hence sharing the editor for the community.
I know it’s not up to mark, but I haven’t finished it yes, so please don’t mind the UI.

Thanks a lot guys.

Bubble Editor - Meeting-room-scheduler