cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
BenjaminMartin
Helper I
Helper I

Book A Room Question

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"));

6 REPLIES 6
v-yutliu-msft
Community Support
Community Support

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,

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@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)
))

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,

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.

 

 

 
 

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:

3136.PNG

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

Have you set your gallery like this?

3137.PNG

 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,

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@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)
;

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are Your Ready?

Test your skills now with the Cloud Skill Challenge.

Top Solution Authors
Top Kudoed Authors
Users online (47,616)