Book a room app - add room list filter

[AnthonyRegnier]

Hello PowerGuys,

I have taken the Book a Room template app and added some filters (3 check boxes and 1 slider) that I would like to apply to the available room results when I have selected one of the room list (Please check screenshot attached).

Users will use the slider to select how many seats they want the room to have, and they will use the checkbox to select whether they need a room with audio only, or audio & video capacity. The third checkbox is to include special rooms reserved for special events such as Town halls, boardrooms etc...

Since we cannot query the RoomCapacity the description, I would like to use the Display Name of the room to filter them out.

Room-USANY-01 (8 Seats, Audio Only) -> This one has only a phone

Room-USALA-01 (16 Seats, Teams Ready) -> This one has Audio & Video

The idea is, unless there is a better idea and I invite to you let me know (building a separated Sharepoint List)?, 

Is it possible to amend the code so that it filters by doing like a select-string or If("string" in "VarResult", Return, NotReturn).

Sounds complex... What do you think?

Thanks

Anthony

[v-bofeng-msft]

Hi @AnthonyRegnier :

Do you want to filter the rooms by the keywords in the room name?

I think this function can be implemented with “Match” and “In”.

I’ve made a test for your reference:

Step1:add three check boxes controls(Special rooms/ Audio&video/ Audio only)

Step2:add a slider control(Slider1)

Step3:add a gallery and set it’s property to:

Filter(
    aa,                  /*aa is my data source*/
    If(
        'Special rooms'.Value,
        "Town halls" in Name || "boardrooms" in Name,
        false
    ) || If(                       /*|| Equivalent to or*/
        'Audio&video'.Value,
        "Teams Ready" in Name,
        false
    ) || If(
        'Audio only'.Value,
        "Audio Only" in Name,
        false
    ),
    Value(                      /*Convert string to number*/
        Match(
            Match(
                Name,
                "\(\d+"          /* Use "(" to locate the number of seats*/
            ).FullMatch,
            "\d+"               /* get the number of seats*/
        ).FullMatch
    ) > Slider1.Value             /*Compare with the value of slider 1*/
)

Now, the user can select one or more check boxes and the gallery will display the related rooms.

In addition, by moving the slider, we can filter out rooms with less than the specified number of seats.

Best Regards,

Bof

[AnthonyRegnier]

@v-bofeng-msft  that's awesome, thank you so much. I'm going to analyze that and learn!

I'm trying to look on how to implement it in the current code, do you have an idea?. Must it be inserted in this code below?

If I read it correctly, it creates a custom collection... Then the "items" property of the gallery, is "AvailableRoomEmailName"

Set(IsPrevRoomSelect, false);
If(DateTimeRoomChange,
    Set(AvailableRoomsCounter, 1);
    UpdateContext({ShowLoading: true});
    Set(RoomsLeft, Blank());
    If(!NoRoomsList,
        ClearCollect(AllRooms, Office365.GetRoomsInRoomList(RoomsListsGallery.Selected.Address).value)
    );
    Set(DateTimeRoomChange, false);
    /*
    If(CountRows(AllRooms) > 20,
        Set(AllRoomsConnector, Concat(FirstN(AllRooms, 20), Address & ";"));
        If(CountRows(AllRooms) > 40, 
            Set(AllRoomsConnector1, Concat(LastN(FirstN(AllRooms, 40), 20), Address & ";"))
        ),
        Set(AllRoomsConnector, Concat(AllRooms, Address & ";"))
    );
    */
    Set(AllRoomsConnector, Concat(FirstN(AllRooms, 20), Address & ";"));

    
    Concurrent(
        ClearCollect(AvailableRooms, Office365.FindMeetingTimes({RequiredAttendees: AllRoomsConnector, IsOrganizerOptional: true, 
            Start: StartDateTimeUTC, End: EndDateTimeUTC, MeetingDuration: DateDiff(StartDateTime, EndDateTime, Minutes),
            MinimumAttendeePercentage: "1", ActivityDomain: "Unrestricted"})),

        If(CountRows(AllRooms) > 20,
            If(CountRows(AllRooms) > 40,
                Set(AllRoomsConnector1, Concat(LastN(FirstN(AllRooms, 40), 20), Address & ";")),
                Set(AllRoomsConnector1, Concat(LastN(AllRooms, CountRows(AllRooms) - 20), Address & ";"))
            );
            ClearCollect(AvailableRooms1, Office365.FindMeetingTimes({RequiredAttendees: AllRoomsConnector1, IsOrganizerOptional: true, 
                Start: StartDateTimeUTC, End: EndDateTimeUTC, MeetingDuration: DateDiff(StartDateTime, EndDateTime, Minutes),
                MinimumAttendeePercentage: "1", ActivityDomain: "Unrestricted"})
            ),
        Clear(AvailableRooms1)
        )

    );
    If(CountRows(AllRooms) > 20,
        Collect(AvailableRooms, AvailableRooms1)
    );
    ClearCollect(AvailableRoomsSorted, SortByColumns(First(AvailableRooms).MeetingTimeSuggestions, "Confidence", Descending), If(CountRows(AllRooms) > 20, SortByColumns(Last(AvailableRooms).MeetingTimeSuggestions, "Confidence", Descending)));

    ClearCollect(AvailableRoomsConcat, Concat(Filter(First(AvailableRoomsSorted).AttendeeAvailability, Availability = "Free"), 
        Attendee.EmailAddress.Address, ","), If(CountRows(AllRooms) > 20, Concat(Filter(Last(AvailableRoomsSorted).AttendeeAvailability, Availability = "Free"), 
        Attendee.EmailAddress.Address, ",")));

    ClearCollect(AvailableRoomEmails, Split(First(AvailableRoomsConcat).Value, ","), If(CountRows(AllRooms) > 20, Split(Last(AvailableRoomsConcat).Value, ",")));

    ClearCollect(AvailableRoomEmailName, AddColumns(RenameColumns(AvailableRoomEmails, "Result", "Email"), "Name", 
        LookUp(AllRooms, Email = Address).Name));
    UpdateContext({ShowLoading: false});
    Set(RoomsLeft, CountRows(AllRooms) - 40 * AvailableRoomsCounter)
);
Clear(AvailableRooms);
Clear(AvailableRooms1)

[v-bofeng-msft]

HI @AnthonyRegnier :

Q1:I'm trying to look on how to implement it in the current code, do you have an idea?

I suggest you try this solution:

Adding this formula

ClearCollect(AvailableRoomEmailName,Filter(
    AvailableRoomEmailName,                  
    If(
        'Special rooms'.Value,
        "Town halls" in Name || "boardrooms" in Name,
        false
    ) || If(                       /*|| Equivalent to or*/
        'Audio&video'.Value,
        "Teams Ready" in Name,
        false
    ) || If(
        'Audio only'.Value,
        "Audio Only" in Name,
        false
    ),
    Value(                      /*Convert string to number*/
        Match(
            Match(
                Name,
                "\(\d+"          /* Use "(" to locate the number of seats*/
            ).FullMatch,
            "\d+"               /* get the number of seats*/
        ).FullMatch
    ) > Slider1.Value             /*Compare with the value of slider 1*/
)),

 below

ClearCollect(AvailableRoomEmailName, AddColumns(RenameColumns(AvailableRoomEmails, "Result", "Email"), "Name", 

 Q2:Must it be inserted in this code below?

 No.I think the easiest way is：

 Set the gallary's items property to

Filter(
    AvailableRoomEmailName,            
    If(
        'Special rooms'.Value,
        "Town halls" in Name || "boardrooms" in Name,
        false
    ) || If(                       /*|| Equivalent to or*/
        'Audio&video'.Value,
        "Teams Ready" in Name,
        false
    ) || If(
        'Audio only'.Value,
        "Audio Only" in Name,
        false
    ),
    Value(                      /*Convert string to number*/
        Match(
            Match(
                Name,
                "\(\d+"          /* Use "(" to locate the number of seats*/
            ).FullMatch,
            "\d+"               /* get the number of seats*/
        ).FullMatch
    ) > Slider1.Value             /*Compare with the value of slider 1*/
)

 Best Regards,

Bof

[AnthonyRegnier]

Hi @v-bofeng-msft 

Thank you very much! I am facing few problems, probably some details to adjust...

First, I have modified the code just to add my checkboxes and the proper values I wish to filter, please see below:

ClearCollect(AvailableRoomEmailName,Filter(
    AvailableRoomEmailName,                  
    If(
        ChkBox_Restricted.Value,
        "Restricted" in Name,
        false
    ) || !If(                       /*|| Equivalent to or*/
        ChkBox_AudioVideo.Value,
        "Audio Only" in Name,
        false
    ) || If(
        ChkBox_AudioOnly.Value,
        "Audio Only" in Name,
        false
    ),
    Value(                      /*Convert string to number*/
        Match(
            Match(
                Name,
                "\(\d+"          /* Use "(" to locate the number of seats*/
            ).FullMatch,
            "\d+"               /* get the number of seats*/
        ).FullMatch
    ) > Slider1.Value             /*Compare with the value of slider 1*/
)), 

I have tried your code, I'm facing the below problems:

Issue 1:

I used to have results but not since I added the IF NOT:  !If(ChkBox_AudioVideo.Value,"Audio Only" in Name(...).
I then removed completely to only keep the Restrictedand the Audio Only but still I'm not getting any results anymore, this is the code (the right extract):

    ClearCollect(AvailableRoomEmailName,Filter(
    AvailableRoomEmailName,                  
    If(
        ChkBox_Restricted.Value,
        "Restricted" in Name,
        false
    ) || If(
        ChkBox_AudioOnly.Value,
        "Audio Only" in Name,
        false
    ),
    Value(                      /*Convert string to number*/
        Match(
            Match(
                Name,
                "\(\d+"          /* Use "(" to locate the number of seats*/
            ).FullMatch,
            "\d+"               /* get the number of seats*/
        ).FullMatch
    ) > Slider1.Value             /*Compare with the value of slider 1*/
)), 
        LookUp(AllRooms, Email = Address).Name);

Issue 2: 

When I go back and want to select another room list, it keeps displaying the the previous rooms

Issue 3:

Which isnt really an issue but more a question - how to do display the seat number based on the selection? Do you display greater and equal value? Just equal?

Thank you again for your help!

[v-bofeng-msft]

Hi @AnthonyRegnier :

 About issue1/issue2:

 I want to recommend this solution to you.(It can make your app look more intuitive)

 Set the gallary's items property to

Filter(
    AvailableRoomEmailName,            
    If(
        'Special rooms'.Value,
        "Town halls" in Name || "boardrooms" in Name,
        false
    ) || If(                       /*|| Equivalent to or*/
        'Audio&video'.Value,
        "Teams Ready" in Name,
        false
    ) || If(
        'Audio only'.Value,
        "Audio Only" in Name,
        false
    ),
    Value(                      /*Convert string to number*/
        Match(
            Match(
                Name,
                "\(\d+"          /* Use "(" to locate the number of seats*/
            ).FullMatch,
            "\d+"               /* get the number of seats*/
        ).FullMatch
    ) > Slider1.Value             /*Compare with the value of slider 1*/
)

About issue3:

    Value(                      /*Convert string to number*/
        Match(
            Match(
                Name,
                "\(\d+"          /* Use "(" to locate the number of seats*/
            ).FullMatch,
            "\d+"               /* get the number of seats*/
        ).FullMatch
    ) > Slider1.Value             /*Compare with the value of slider 1*/

 The formula makes the gallery display a greater value.

Best Regards,

Bof

[AnthonyRegnier]

Hello mate,

Thanks again for your reply!

I have changed the code for the gallery items - but do I need to do anything with the onVisible property of the screen? It is currently back to default with no amended code.

Regarding the checkbox filters:

Is it possible to by default have all the rooms showed, with no filtering, and only after if I enable the checkbox "Teams only" it filters out the room that don't have it in their name, and same for the "Audio Only" ? Right now no rooms are showed if both checkbox are disabled. (Also it must not be case sensitive).

Also still regarding the checkbox filters: 

When I enable both "Audio Only", and "Teams Ready", both rooms are showing but I would like the match to be "and" , not "Or". I have replaced "||" by "&&" but none are showing. I guess it's because 0 will show if "Teams Ready" is disabled?

I have made a quick test by replacing "Teams Only" by "VC-" in order for them to all be displayed, and put "&&" instead of "||".  But when "Audio Only" is disabled it doesn't show all the other rooms...

For the slider:

When I move the number to a low figure (let's say 4) it shows all the rooms which is ok because I have a room with 100 Seats capacity but my slider only goes up to 25 seats. It then become pointless to give the ability to chose 4 seats if all the rooms are displayed randomly. I can only think about a solution to have the room sorted by seats numbers... Do you think it's possible to achieve? (Btw: I have changed the operator to ">=").

Thank you so much for your help!

Thank you so much for your help, really!

[v-bofeng-msft · Accepted solution]

Hi @AnthonyRegnier ：

Q1:Regarding the checkbox filters

Yes

Q2:Also still regarding the checkbox filters

According to your requirements, I think the correct logic is to use || instead of &&.

Q3:For the slider

Yes

Here is my code:

Sort(Filter(              /*sort by the number of seats*/
    AvailableRoomEmailName,
    If(
        'Special rooms'.Value,
        "Town halls" in Name || "boardrooms" in Name,
        false
    ) || If(
        'Audio&video'.Value,
        "Teams Ready" in Name,
        false
    ) || If(
        'Audio only'.Value,
        "Audio Only" in Name,
        false
    )||And(!'Special rooms'.Value,!'Audio&video'.Value,!'Audio only'.Value),  /*show all rooms by default*/
    Value(
        Match(
            Match(
                Name,
                "\(\d+"
            ).FullMatch,
            "\d+"
        ).FullMatch
    ) >= Slider1.Value
),Value(
        Match(
            Match(
                Name,
                "\(\d+"
            ).FullMatch,
            "\d+"
        ).FullMatch
    ),Ascending)

I think these links will help you a lot:

IsMatch, Match, and MatchAll functions in Power Apps：

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-ismatch

Sort and SortByColumns functions in Power Apps

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-sort

Operators and Identifiers in Power Apps

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/operators

Best Regards,

Bof

[AnthonyRegnier]

Awesome thank you so much! You're a real champ!