Book A Room Question

[BenjaminMartin]

How do I modify the code below to only include rooms that start with "TSI" and not exclude "FTC"

ClearCollect(
AvailableRoomEmailName,
Filter( AddColumns(RenameColumns(AvailableRoomEmails, "Result", "Email"), "Name",
LookUp(AllRooms, Email = Address).Name),
Name <> "FTC"));

[v-yutliu-msft]

Hi @BenjaminMartin ,

Do you want to filter the collection based on rooms that start with "TSI" and are equal to "FTC"?

If so, try this formula:

ClearCollect(AvailableRoomEmailName,
             Filter( AddColumns(RenameColumns(AvailableRoomEmails, "Result", "Email"), 
                                "Name",
                                 LookUp(AllRooms, Email = Address).Name),
                     Name <> "FTC",
                     StartsWith(Name,"TSI"),
                     Name="FTC"
                    )
             )

Best regards,

[BenjaminMartin]

@v-yutliu-msft 

I only want the rooms that start with TSI, sorry if my question was confusing. I had <> "FTC" before but just wanted it to be StartsWith "TSI" because other rooms are being added.

I put this code under the RoomSelectScreen but it only returns 1 blank option. 

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

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

ClearCollect(AvailableRoomsSorted, SortByColumns(First(AvailableRooms).MeetingTimeSuggestions, "Confidence", Descending));

ClearCollect(AvailableRoomsConcat, Concat(Filter(First(AvailableRoomsSorted).AttendeeAvailability, Availability = "Free"),
Attendee.EmailAddress.Address, ","));

ClearCollect(AvailableRoomEmails, Split(First(AvailableRoomsConcat).Value, ","));
ClearCollect(AvailableRoomEmailName,
Filter( AddColumns(RenameColumns(AvailableRoomEmails, "Result", "Email"),
"Name",
LookUp(AllRooms, Email = Address).Name),

StartsWith(Name,"TSI")
);
UpdateContext({ShowLoading: false});
Set(RoomsLeft, CountRows(AllRooms) - 20 * AvailableRoomsCounter)
))

[v-yutliu-msft]

Hi @BenjaminMartin ,

Do you mean that no available room display in the room list?

Actually this is regardless of your new added Filter() function. This is correct.

I've researched this template, the problem is the template itself.

Please delete this part of the RoomSelectScreen's OnVisible:

If(DateTimeRoomChange,...)

You just need to set  RoomSelectScreen's OnVisible:

Set(CalendarFromSelectMeeting, false);

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

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

    ClearCollect(AvailableRoomsSorted, SortByColumns(First(AvailableRooms).MeetingTimeSuggestions, "Confidence", Descending));

    ClearCollect(AvailableRoomsConcat, Concat(Filter(First(AvailableRoomsSorted).AttendeeAvailability, Availability = "Free"), 
        Attendee.EmailAddress.Address, ","));

    ClearCollect(AvailableRoomEmails, Split(First(AvailableRoomsConcat).Value, ","));

    ClearCollect(AvailableRoomEmailName,
Filter( AddColumns(RenameColumns(AvailableRoomEmails, "Result", "Email"),
"Name",
LookUp(AllRooms, Email = Address).Name),
StartsWith(Name,"TSI")
);

    UpdateContext({ShowLoading: false});
    Set(RoomsLeft, CountRows(AllRooms) - 20 * AvailableRoomsCounter)

Best regards,

[BenjaminMartin]

I tried the code you put above and I still get no result (attached). The More Rooms button on the RoomSelectScreen works so it's not that big of a deal. The user just has to click More Rooms to get results.

[v-yutliu-msft]

Hi @BenjaminMartin ,

That's strange...

On my side, this formula works good.

Actually, it there' s no room in AvailableRoomEmailName, it will not show like the pic that you attached.

It will look like this:

So I think maybe the problem is your gallery's setting.

Have you set your gallery like this?

 If the problem still exists, could you show me the formula in  More Rooms button?

I don't know why you could save the problem by clicking the button.

Best regards,

[BenjaminMartin]

@v-yutliu-msft 

That looks right. Here is the code I'm using for More Rooms that works

If(RoomsLeft >= 20,
Set(AvailableRoomsCounter, AvailableRoomsCounter+1);
Set(AllRoomsConnector, Concat(LastN(FirstN(AllRooms, 20*AvailableRoomsCounter), 20), Address & ";"));
Set(RoomsLeft, CountRows(AllRooms) - 20*AvailableRoomsCounter),

RoomsLeft < 20 && RoomsLeft > 0,
Set(AllRoomsConnector, Concat(LastN(AllRooms, RoomsLeft), Address & ";"));
Set(RoomsLeft, 0));

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

ClearCollect(AvailableRoomsSorted, SortByColumns(First(AvailableRooms).MeetingTimeSuggestions, "Confidence", Descending));

ClearCollect(AvailableRoomsConcat, Concat(Filter(First(AvailableRoomsSorted).AttendeeAvailability,
Availability = "Free"), Attendee.EmailAddress.Address, ","));

ClearCollect(AvailableRoomEmails, Split(First(AvailableRoomsConcat).Value, ","));

ClearCollect(AvailableRoomEmailName,
Filter( AddColumns(RenameColumns(AvailableRoomEmails, "Result", "Email"),
"Name",
LookUp(AllRooms, Email = Address).Name),
StartsWith(Name,"TSI");));
UpdateContext({ShowLoading: false});
Set(RoomsLeft, CountRows(AllRooms) - 25 * AvailableRoomsCounter)
;