Gallery filtering with two collections

[Hazeyn]

• Hello everyone, I am trying to make an orientation program for a new employee. I'm having trouble with Orientation Time reservation. My sharepoint list has two columns called Orientation_start :" Start date,time" Orientation end:"End date,time"

• And I keep this list in a collection called Col_Ik_Oryanasyon_emri :

• I made a collection called Or_rev_time that combines the selected date and time with a calendar I made:

• What I want to do will be a gallery like the following. The times between start_date and end date will not be visible in " Col_Ik_Orientation_order " for example : start date :"10.03.2023 08:30" end date : " 10.03.2023 10:00 If the gallery is " 10.03.2023 08: 30, 10.03.2023 09:00, 10.03.2023 09:30 and 10.03.2023 10:00 " times will not be visible. Apart from this, times can be selected

• when the filter works, the gallery will appear as below and the user will not be able to select a pre-allocated time

• Thank you in advance for your valuable help.🙂

[LaurensM · Accepted solution]

Hi @Hazeyn,

I did some testing on my end and came up with the following 3 solutions. Within the solution, please replace all references to ORIENTATION_START and ORIENTATION_END to the correct names and double check the collection names used.

Option 1: Create an additional collection and use conditional formatting in the gallery. We will not be able to disable certain fields, but we can show the availability and prohibit the selection of that field. 

Option 2: The Gallery will use buttons instead of labels to display the data. This way we do not need an additional collection and we can disable the selection of certain fields. However, this will require more changes in your app - especially how gallery selection is handled.

Option 3: Filter out the unavailable time slots and only show the available ones. This will require the least changes.

Guide option 1

Directly after creating the Or_rev_time, we will create an additional collection that shows the time slots and their availability. This collection will also serve as the Items property of the time slot gallery:

ClearCollect(
    colTimeSlotOverview,
    AddColumns(
        OR_rev_time,
        "Available",
        With(
            {currentTimeSlot: DateTimeValue(Ortime)},
            //Check for booked time slots
            CountRows(
                Filter(
                    Col_Ik_Oryanasyon_emri,
                    currentTimeSlot >= DateTimeValue(ORIENTATION_START) && currentTimeSlot < DateTimeValue(ORIENTATION_END)
                )
            ) = 0 && CountRows(
                //Check for booked time slots between 2 bookings (e.g. 9:00 or 10:30)
                Filter(
                    Col_Ik_Oryanasyon_emri,
                    currentTimeSlot = DateTimeValue(ORIENTATION_END) || DateAdd(currentTimeSlot,30,TimeUnit.Minutes) = DateTimeValue(ORIENTATION_START)
                )
            ) < 2
        )
    )
)

The Items property of your time slot gallery will simply be colTimeSlotOverview

To show the availability you could write the following code in the gallery's TemplateFill property:

If(
    ThisItem.Available,
    Color.ForestGreen,
    Color.IndianRed
)

To prohibit the selection of unavailable time slots, we will need to change the Default and OnSelect of the gallery.

Default:

LookUp(colTimeSlotOverview, Available)

OnSelect:

If(
    //Change Gallery1 to the correct time slot gallery name
    Gallery1.Selected.Available <> true,
    //If not true, select the default record again and show an error
    Reset(Gallery1);
    Notify(
        "Please select an available time slot",
        NotificationType.Error
    )
)

Final result:

Guide option 2

Here you will have to replace your current label in the timeslot gallery with a button. The button will display the Ortime and be selectable if the timeslot is available. Next to changing the label to a button, you will also have to change any logic relating to Gallery.Selected.

Instead of referencing the selected gallery item as GalleryName.Selected, we will save the current gallery selection to a variable on the press of the button. We can disable the selection of the button, not the selection of that item in the gallery - Gallery.Selected (for the timeslot gallery) should thus be replaced. Any code that you might have (not refering to my code in option 1) on the OnSelect of that gallery, will also have to be placed in the OnSelect of the button.

No collection creation needed, you can use the following code as the Items property of the gallery:

AddColumns(
    OR_rev_time,
    "Available",
    With(
        {currentTimeSlot: DateTimeValue(Ortime)},
        CountRows(
            //Check for booked time slots
            Filter(
                Col_Ik_Oryanasyon_emri,
                currentTimeSlot >= DateTimeValue(ORIENTATION_START) && currentTimeSlot < DateTimeValue(ORIENTATION_END)
            )
        ) = 0 && CountRows(
            //Check for booked time slots between 2 bookings (e.g. 9:00 or 10:30)
            Filter(
                Col_Ik_Oryanasyon_emri,
                currentTimeSlot = DateTimeValue(ORIENTATION_END) || DateAdd(
                    currentTimeSlot,
                    30,
                    TimeUnit.Minutes
                ) = DateTimeValue(ORIENTATION_START)
            )
        ) < 2
    )
)

Button's DisplayMode:

If(
    ThisItem.Available,
    DisplayMode.Edit,
    DisplayMode.Disabled
)

Button's OnSelect:

Set(varSelectedSlot, ThisItem.Ortime)

Important: as a reminder, all previous references to TimeSlotGalleryName.Selected should be replaced with the varSelectedSlot. Any code previously written in the OnSelect of the gallery should be moved to the button's OnSelect.

Final Result:

Guide option 3

In this case, simply use the following code in the TimeSlot Gallery's Items property:

Filter(
    AddColumns(
        OR_rev_time,
        "Available",
        With(
            {currentTimeSlot: DateTimeValue(Ortime)},
            CountRows(
                Filter(
                    Col_Ik_Oryanasyon_emri,
                    currentTimeSlot >= DateTimeValue(ORIENTATION_START) && currentTimeSlot < DateTimeValue(ORIENTATION_END)
                )
            ) = 0 && CountRows(
                Filter(
                    Col_Ik_Oryanasyon_emri,
                    currentTimeSlot = DateTimeValue(ORIENTATION_END) || DateAdd(
                        currentTimeSlot,
                        30,
                        TimeUnit.Minutes
                    ) = DateTimeValue(ORIENTATION_START)
                )
            ) < 2
        )
    ),
    Available
)

Final Result:

If this solves your question, would you be so kind as to accept it as a solution.

Thanks!

[Hazeyn]

LaurensM thank you so much for everything. 3 solution ways were like a lesson for me.

🙂