Filter according to values inside a list of Date Ranges

Hi,
I’m trying to let users search for other users using date/time range filters.

  • Each user can set their own available time slots, which will be stored in the user DB as a list of date ranges.
  • Users can filter other users by selecting a date range, and users who have available time slots within this selected time range will be displayed in the result.

For example, user A added 25, 27, and 29 Jul 4 - 6 pm as available time slots.
And another user - User B, is searching for all users who have available time slots in 24 to 26 Jul.
So user A will appear in the search result.

In my User collection db, I’ve added a list of date ranges - “timerange”.
User collection db - list of date ranges

I am able to show user A in the filter result, when I specify in the advance filter the item number (first item/ item#) in the list of date ranges. i.e. timerange:item#1 overlaps with AirDate/Time Picker A’s value <-range-> AirDate/Time Picker B’s value
Screenshot 2022-08-03 at 6.45.00 PM

But how can I check all the items in the list of date ranges instead of only checking one item in the list?

Do I need to do a loop? or should I create another collection in db that stores all users and stores all their available time slots as “a date range” instead of “a list of date ranges”?

I’m not from a technical background, and I’ve been stuck on this problem for a very long time! Hope someone can help! Thank you very much!

Hello -

I would probably set this up using an additional data type called “availability” which stores a single date range for a single user.

Screen Shot 2022-08-03 at 11.15.07 AM

When you go to filter by a user’s range selection, you can do a search for availabilities where user=desired user and date range overlaps with user’s selected date range.

Let me know if you have any questions!

1 Like

Thank you for your reply Sam!

However, in my case, our users might have different numbers of available slots (date range). Some may have 3, some may have 5, and some even more. And some available slots could be within the same day.

So I need to check all the available slots for each user, and return users who have at least one available slot that overlaps with the selected date range (by the other user)

So does your mentioned solution still works for my case? Thank you very much for your answer!

Best,
Hubert

Hi Hubert! Yes - the solution I presented should work for your use case.

The key here is the search we are running to filter out availabilities that overlap is actually not a search for users, but rather a search for availabilities. From there, we can access the list of availabilities:each item's user:unique elements to return a list of users who have at least one availability record overlapping the user’s range input. Since each individual availability for a user is a different availability record, this will include users who have ANY availabilities overlapping and won’t require them to have ALL.

I hope that makes sense!

Hello Sam,

Thank you again for your help! I tried to implement your approach.
I created an extra data type called “AvailableTime”, and it stores single date range for a single user.

When I try to print out the items in “AvailableTime”, I see a single user has 7 time ranges like this


The data source is the list of “Available Time” that I stored in a state in the page

I then tried to follow your expression to return a list of unique users but it returns error

Could you please advise how I should implement this properly?
Thank you very much!!!

Hello!

It looks like the reason that your data expression is triggering an issue and turning is red is because the type of content returned by the dynamic expression does not match the type of content of your repeating group.

If you switch the type of content to “Users,” this should be a valid expression and data source.

Let me know if that helps resolve things!

Thank you for your reply. But now I have two data types, one is “Users”, and another one “Available Time”.

As you suggested in your previous post, I’m trying to display the list of “Available Time” so that’s why I’m using “Available Time” as the type of content.

So I wonder if I should use “User” as the repeating group data source, or should I use “Available Time” as the data source.
Could you clarify please? Thank you very much!

@sam.morgan To give you more info, this is what my DB looks like

Hello!

It really depends on the specific interface you’re trying to display. The data is set up in the right way, so these questions are more a matter of how to display things on the front end.

You mentioned two different goals for displaying data in two different responses:

So I need to check all the available slots for each user, and return users who have at least one available slot that overlaps with the selected date range (by the other user)

In the above case, you should set your type of content to “User” since the list you are trying to return is a list of users who have at least one slot that overlaps.

As you suggested in your previous post, I’m trying to display the list of “Available Time”

In this case, you should set the type of content to “AvailableTime” since that is the type of content you are trying to return.

Features like this will often require multiple repeating groups - sometimes, certain repeating groups may even be hidden and used just as a reference to a different list. Here’s an example of how I might structure a very basic UI for this feature:

  1. Set up a repeating group with type of content “User” and data source “do a search for availabilities where range overlaps with users selected range:each item's user”. This repeating group will display a list of all users that have availability at the selected time.
  2. Create a custom state on your page called “selected user” and set up a workflow so that if a user clicks a user from the repeating group, the state is set to the current cell’s user.
  3. Set up a second repeating group with type of content “AvailableTime” and data source “do a search for availabilities where user = custom state user and date range overlaps with user's selected date range

The result of this setup will be an interface where someone can select a date range, then choose from a list of users that are available during that range, and then view specific overlapping availabilities for the specific user and selected date range.

There are a ton of different ways this interface could take shape, each with their own unique setup and data source, but the concepts are all closely related, and the data structure is the same.

If you’re still having trouble and want us to take a look at your specific app, please reach out to support@bubble.io. This will allow us to dive into your app editor and tailor our advice specific to your use case. We’re a bit limited in what we can do on the forum, since it wouldn’t be appropriate to post details about your specific app in this public environment.

I hope this helps!

I see thank you for your detailed reply and I will reach out to support@bubble.io

1 Like

Hello @sam.morgan

Further to your answer, I actually tried to “do a search for availabilities where range overlaps with users selected range:each item’s user”

But it returns error

The custom state UserList2 is a list of “Users”, so with the “:filtered” function, I tried to use both the advanced filter and also filter for Available Time to do a search for the “Available Time” but both returned error

The search for Available Times look like this
Do a search for

Could you please point out how to do this correctly? Thank you very much!

Best,
Hubert

Hi Hubert,

It would be difficult for me to tell you how to input the correct data sources here without diving into your application and gathering more context on your UI and element setup.

These types of questions require a lot more context on your specific set up and application, and I can see that you’ve already reached out to support and Jonathan is looking into this behavior for you. He’ll follow up as soon as he has some concrete answers to provide.

The advice I’ve provided in this thread is meant to be general advice about overlapping date ranges and the data structure of an availability calendar. But it’s important to understand that each individual implementation of this functionality will have nuances that call for slight adjustments and alternate data sources.

Take the advice I’ve given here as general concepts rather than specific guidance on your app - Jonathan will be able to get into the specifics with you via email :slight_smile:

Let me know if you have any questions!

This topic was automatically closed after 70 days. New replies are no longer allowed.