Forum Documentation Showcase Pricing Learn more

Date Range Issue - Filtering Range in a Range


#1

Not%20Working

Can anyone see why this is not working? It is driving me insane. I have been working on this too long and can’t figure it out.

I am trying to filter out technicians that are not available during the specific date range of an event.

Each technician can have a list of dates that they are unavailable so i don’t want them showing up in the dropdown. For some reason they keep showing up anyways. What is wrong with my filter?

Thanks in advance.


#2

Hey @J805,

Not positive here, but I don’t think your first expression is evaluating to a valid date range. When I use a valid date range, I don’t see the “doesn’t contain” option. It also looks like that first expression is a List that needs to be narrowed down to one Event Range. Am I seeing this wrong?

Also, you might want to be looking at an overlap instead of contains for your use case. Using contains means the second range has to be completely contained within the first range, while overlaps allows for them to be partially overlapping.

That being said, I don’t think there is a “doesn’t overlap” or “doesn’t contain” option for date ranges, so to filter out those with no overlap, you might have to use “is before” OR “is after” to include only those folks without an overlap.



Looking to accelerate your app development?

Let me turn
:thinking: :tired_face: :confounded:

into
:grinning: :sunglasses: :woman_student:

Development through Coaching at https://uniqueideas.com or schedule a free intro session :gift:

Ken Truesdale
LinkedIn


#3

Yes, you are correct, there is a LIST of dates that it needs to check. I will check out the “is before” OR “is after” to see if that works. Thanks for the suggestion.


#4

I do this sort of thing quite often… the issue is, it is hard to describe what you need to do.

You have a list of Technicians, each of whom has a list of “booked dates” on them. You have a user-defined range of times that the user would like to have a Technician come and “handle their business” (as it were).

You need to iterate over the list of Technicians, but also iterate over each technician’s list of booked dates.

Your dropdown is a list of “available technicians”.

An individual Technician is AVAILABLE if:

  1. There are no individual date/times for which the Technician is UNavailable within the user-selected range. In Bubble terms, this is:

Technician's Dates Unavailable:filtered (Advanced: User's range contains point This Date) is empty

You are constructing your user’s date range on-the-fly, so this becomes:

Technician's Dates Unavailable: filtered (Advanced: Parent group's schedule/event/appointment's Event End<-- range -->Parent group's schedule/event/appointment's Event Start contains point This Date) is empty

(And, at this point, we are re-thinking our field labels! But that’s cool.)

  1. But we do not have just one Technician. We have a list of Technicians. The list of ALL Technicians in the universe is:

Do a Search for… Technicians

  1. So, what we must do is :filter the list of all Technicians in the universe by the criteria #1. In Bubble terms this is:

Do a Search for… Technicians:filtered

  1. What’s the :filtered condition? The filter described in #1. So this becomes:

Do a Search for... Technicians:filtered (Advanced: This Technician's Dates Unavailable:filtered (Advanced: User's range contains point This Date) is empty)

Building this will pop up multiple Advanced filter dialogs. The way to build it is to create:

Do a Search for... Technicians:filtered

… first. THEN, click on that :filtered. Create the Advanced condition there.

… then, in the dialog for that Advanced condition, build:

This Technician's Dates Unavailable:filtered

… then drilldown on THAT :filtered. Create the second Advanced condition there:

Parent group's schedule/event/appointment's Event End<-- range -->Parent group's schedule/event/appointment's Event Start contains point This Date is empty

It’s entirely possible I’ve noted something wrong here as I’m just doing this off the top of my head. However, I think you see now how to think about this and construct it. You were just thinking “one level down”… you need to “pop up” a level and start with the list of all Technicians (Do a Search for… Technicians) and filter that.


#5

Ok. I see. I guess that is probably where it looked like it was working but I was getting stuck. I try to avoid searches if at all possible but maybe in this case it is something I need to do. I guess one search function is not too bad.

Thanks for the detailed explanation. I’m just glad I understood it. Thanks. I will try it out tomorrow and let you know what happens. :slight_smile:


#6

The Search function is a necessary evil. Note however that you could constrain that search by orthogonal criteria. (Is the technician in this service area? for example).

But don’t be afraid to snag the list of all Technicians. Bubble is way more performant than you think it is. It downloads data like a mother… grabber.

Would you think twice about grabbing the list of all Technicians for your dropdown? Probably not. Any subsequent filtering you do only serves to slow things down.

You could also move all of this to the server by building an API endpoint in your app and then calling the endpoint from your app. (See my video about the Amazon product search API for a discussion of that strange-sounding idea and how you set it up.)

Caveat: Bubble’s server is probably not faster than the user’s Browser, unless you are paying for a bunch of extra capacity, or the Technician object is very large.

If you REALLY think that you’re going to have zillions of Technicians and their data object is very large, rethink the organization of your Technician object. Make it smaller. Or just make a TechnicianAvailabilty object that’s a proxy for the larger Technician object.

But all of this is putting carts before horses. Just make it work first. Optimize after that.


#7

Haha. Ya. I have learned a lot since my first app. How to do less searches is one thing I have learned. So I guess doing a search once in a while is ok. But ya, gotta get this working first. :slight_smile:


#8

This is super helpful @keith - thanks for the pointers. I’m working on something with one other added element of complexity - a user can book any number of different services with varying appointment lengths. I’ve set this up to show a repeating group of available appointment times for that appointment rather than a drop-down, but the logic here feels similar and I’m also failing (hard) at getting the multiple advanced filter option to work.

It seems like what’s really missing here is a “doesn’t overlap with”, function that can check a list of dates/times that are booked and make sure that doesn’t overlap with the range of an appointment.

I built a test case that used a group of potential start times as a repeating group (RepeatingGroup StartTime Data) that filters a user’s search by dates. When a booking is made, a Reservation is dropped into that StartTime to show it’s busy. That Reservation has a Busy Range.

My first attempt at this used one repeating group with the full list of Start Times within the day a client wanted to book, then filtered that in another repeating group by “Reservation is empty”.

That gave me a list of Start Times without Bookings, but what I really needed was a list of Start Times that were not only empty, but wouldn’t conflict with other bookings once you add on the dynamic variable of Service Length.

To do that, I sent the Service Length as a state to the second repeating group and added a collapsable group in each cell of the RepeatingGroup that hid that cell whenever the Busy Range of the first repeating group overlapped with the range of Start Time + X minutes Start Time of that cell.

I knew/know there has to be a more elegant way of building this, but it largely accomplishes the task of saying “Just show me stuff that doesn’t overlap”.

In testing this solution, the issue I uncovered is that Bubble’s repeating groups - at least in Vertical Scroll - seem to populate initially based on what a cell’s height should be rather than what it is. This meant that when I filtered by a more restrictive service lengths, I was only loading a couple available Start Times when there were multiples of that available.

What I See First After Selecting a Date:

And What’s Actually Available…After Forcing the RepeatingGroup to Scroll Down

Getting to that point had been pretty simple and showing potential clients a list of available spaces that was 10% of what was really available didn’t seem like a great option, so I started trying to figure out how to filter the list based on the same logic and have run into the same issue that seems to have started this conversation.

Any combination of filter variables I use either zero’s out the whole list or come’s back red as a “should be a yes/no, but is empty” error. I’ve now spent around 5x as much time trying to get this filter to work as I did building the rest of the app and I keep coming back to the idea that the element here that would make this both possible and logical is having the option to filter by a date range NOT overlapping with another date range. Contain/Doesn’t Contain doesn’t seem to do the trick because an appointment doesn’t need to sit entirely within anything to be a problem.

The test version of the app lives here if anyone’s interested in having a click around.

Just wondering if anyone’s solved a similar issue and how they cracked it. Happy to chat here or DM and also happy to pay for some coaching on this as it’s obviously a bit beyond my current skill set and understanding and I’m looking forward to learning a little more.


#9

Well, building this sort of thing isn’t exactly easy, as you know.

One tip I can give here: You say:

You have to think about this a little differently.

You can do all of this with ranges, of course. Do you save your reservations with starts and ends (and, when doing so, do you construct a range and save that? It’s a helpful thing to do.)

I haven’t closely read your entire (very detailed) message yet. However…

It sounds to me like you think about availability as finding the open slots. That’s not how you do it.

You find conflicts. If there is any conflict, the time is not available. If there are no conflicts (the list of conflicts we either search or filter for is empty), our desired time range must be available.

So what we do is a listwise check of “overlaps with”.

I could say more about this I suppose, but really I’d need to see your editor to understand how you are trying to do it now. I don’t quite see how you’re generating the timeslots that feed the Available Times widget.

The point being that somewhere you need to have a list representing all possible slots in the day. Bubble cannot create such a list, so you must either prototype that in the dB or generate it via scripting.

You’re on the right track here, but what you do is FIND THE CONFLICTS and then subtract those from the list of all available slots. The remaining slots are what you would display in your “Available Times” widget.

(The widget might INSTEAD be a list of all slots in the day and you’d simply design it such that slots collapse if they intersect with an existing reservation.)

Maybe the above is helpful to you?

I’m sort of interested in seeing your editor as a way of understanding what it is you’re doing, but I rarely look at people’s projects. This one though… kind of interesting.