Filtering available time slots

Hi Everyone,

I have been spending two full days trying to resolve an apparently easy task with no luck. I am trying to build a simple booking engine to book a call.

The DB consists of two tables:

  1. Appointments: the actual booking. This table contains a booking date field and hour field that references an Option Set (Hours)
  2. Time Slot Exceptions: a record that keeps track of eventual time I want to block bookings due to unavailability. This table contains a From Date and To Date fields (date) and From Hour and To Hour (Option Set Hours).

The problem I have is to dynamically show available slots by showing All Hours (option set) filtered by no appointments available in the same time slot, but also filtering Time Slot Exceptions. I tried by combining the two constraints with no luck. I also tried to build to different filtered lists and merge them and no luck.

As you can see from screenshot below, the list is whitening all free slots on 21st (that is wrong) and showing the 2 slots on 22nd (which is right).

Does anyone know how can i achieve this result?

Thanks in advance.

Screenshot 2024-03-20 171656

Screenshot 2024-03-20 171556
Screenshot 2024-03-20 171525


I have been building something similar, was wondering if we could connect and help each other on the same.