cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Victoria
Kudo Kingpin
Kudo Kingpin

Complex Gallery filtering

Hello. I have a desk reservation app which was bookable by the day. Now the users have asked for the ability to book am or pm or the whole day so trying to shoehorn that in. The booking screen is linked to 2 galleries, Gal1 displays desks and Gal2 displays timeslots. 

Untitled3.png

I have a bookings list (DeskRequests) with 3 available timeslots, am/pm/all day, for each desk per day. 
Untitled4.png

 

 

But I need a way to hide the other available slots in the app. ie if someone books the desk all day, I don't want the AM or PM slots to appear for that day. Also if someone books am/pm, I need to remove the All day item from the app. 

I don't want to switch the other 2 columns to booked (that would be a way to remove them) because I will also be sorting this list by the booked desks, in another screen. And I can't just delete the obsolete items because the user might cancel the desk and then I would need the other options.  

I'm already filtering Gal2 by a label that filters on the selected desk, the column status value "Available" and the date.
Filter(DeskRequests, Desks=LabelHiddenToFilterOnSelectedDesk.Text, Status.Value = "Available" && Text(Date,ShortDate) = Text(DatePicker1.SelectedDate,ShortDate))


Can I use labels and if statements somehow? 


Any help, will be gratefully received. Thanks

 

1 ACCEPTED SOLUTION

Accepted Solutions

Found a way to achieve this using a filter and count rows in the Items property..

Filter(
    DeskRequests,
    Text(
        Date,
        ShortDate
    ) = Text(
        DatePicker1.SelectedDate,
        ShortDate
    ),
    Desks = LabelFilterSelectedDesk.Text,
    Status.Value = "Available",
    If(
        Timeslots = "All day",
        CountRows(
            Filter(
                DeskRequests,
                Text(
                    Date,
                    ShortDate
                ) = Text(
                    DatePicker1.SelectedDate,
                    ShortDate
                ),
                Desks = LabelFilterSelectedDesk.Text,
                Status.Value = "Booked",
                Timeslots = "PM" || Timeslots = "AM"
            )
        ) = 0,
        CountRows(
            Filter(
                DeskRequests,
                Text(
                    Date,
                    ShortDate
                ) = Text(
                    DatePicker1.SelectedDate,
                    ShortDate
                ),
                Desks = LabelFilterSelectedDesk.Text,
                Status.Value = "Booked",
                Timeslots = "All day"
            )
        ) = 0
    )
)

 

In case it helps anyone else. 

View solution in original post

6 REPLIES 6
rubin_boer
Super User
Super User

hi @Victoria 

 

You can build conditions to determine what can be visible or not as follows. I will illustrate this with the second gallery but you could use the data source directly.

 

Here are the steps:

Step 1. set a variable to the state of all the checkboxes

The OnSelect of the Gallery is set to the formula below

Set(
    varAvailable,
    Concat(
        yourGalleryName.AllItems,
        Text(yourCheckBoxName.Value),
        ","
    )
)

This will give a string such as "false,false,false" based on the state of the checkbox

 

Step 2. determine which combination needs to hide and show controls

Condition 1: When all the checkboxes are false you want to have the user select any of the three choices

Condition 2: When "All Day" is selected the other two must be hidden

Condition 3: When "AM" or "PM" is selected the other must be available but "All Day" must be hidden.

 

Step 3. create the formula to reflect the conditions

Assuming your gallery rows are "All Days",  "AM", "PM" 

When all are available all checkboxes are false  available "fasle,false,false"

When all day is booked the checkboxes results into "true,false,false"

When AM or PM is booked the checkboxes results into "fase,true,false" or "fase,false,true" or "fase,true,true"

These are captured below. The Visible property of the checkboxes in the gallery is set to the formula below

Or(
    varAvailable = "true,false,false" && Self.Text = "All Day", //show all day only
    Or( //show AM/PM only
        varAvailable = "false,true,false", 
        varAvailable = "false,true,true",
        varAvailable = "false,false,true" 
    )  && Self.Text in "AM, PM", 
    varAvailable = "false,false,false" //when all are false every checkbos is visible
)

 

Result 

Peek 2022-06-18 18-30.gif

 

I hope it helps,

R

rubin_boer
Super User
Super User

@Victoria hi i see you have similar post than this one, can we close this one?

Victoria
Kudo Kingpin
Kudo Kingpin

Hi Rubin. Thanks for taking the time to explain. Sorry to not get back sooner, I've been away.
Tried this out and it's not quite working. Hope you don’t mind me coming back to you.

Have set Gallery OnSelect to...

Set(
    varAvailable,
    Concat(
        Gallery3_1.AllItems,
        Text(Checkbox1_1.Value),
        ","
    )
)

 

I've set the Visible property of Checkbox1_1 to ...

Or(
    varAvailable = "true,false,false" && Self.Text = "All Day", //show all day only
    Or( //show AM/PM only
        varAvailable = "false,true,false", 
        varAvailable = "false,true,true",
        varAvailable = "false,false,true" 
    )  && Self.Text in "AM, PM", 
    varAvailable = "false,false,false" //when all are false every checkbox is visible
)

 

But when I do this I've a couple of issues.
1. All the checkboxes disappear entirely, so I can select them.
2. The timeslots column show/hiding as expected either..
Here’s some examples from from the list..Untitled12.png

Desk 1 - booked all day - app is still showing am and pm
Desk 2 - booked am  - app is still showing All day and PM
Desk 3 - booked pm  - app is still showing All day and AM

Untitled13.png

 

Untitled14.png

 

I can see your example was working so I'm a bit stuck. 
Kind regards, Vicki

Victoria
Kudo Kingpin
Kudo Kingpin

1. All the checkboxes disappear entirely, so I can* select them.

*can't select them!

This is frustrating. I followed your instructions but I can't get it working. 

Presentation1.gif

Found a way to achieve this using a filter and count rows in the Items property..

Filter(
    DeskRequests,
    Text(
        Date,
        ShortDate
    ) = Text(
        DatePicker1.SelectedDate,
        ShortDate
    ),
    Desks = LabelFilterSelectedDesk.Text,
    Status.Value = "Available",
    If(
        Timeslots = "All day",
        CountRows(
            Filter(
                DeskRequests,
                Text(
                    Date,
                    ShortDate
                ) = Text(
                    DatePicker1.SelectedDate,
                    ShortDate
                ),
                Desks = LabelFilterSelectedDesk.Text,
                Status.Value = "Booked",
                Timeslots = "PM" || Timeslots = "AM"
            )
        ) = 0,
        CountRows(
            Filter(
                DeskRequests,
                Text(
                    Date,
                    ShortDate
                ) = Text(
                    DatePicker1.SelectedDate,
                    ShortDate
                ),
                Desks = LabelFilterSelectedDesk.Text,
                Status.Value = "Booked",
                Timeslots = "All day"
            )
        ) = 0
    )
)

 

In case it helps anyone else. 

Helpful resources

Announcements
Power Platform Call June 2022 768x460.png

Power Platform Community Call

Join us for the next call on August 17, 2022 at 8am PDT.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Users online (1,972)