How to all Resource Room's based on Multiple Datetime?

[Anonymous]

Hi everyone,

is there any way to get all Resource Room's based on multiple DateTime?
for example I have this Collection (DateTimeBookingColllectionUTC)

StartTimeUTC	EndTimeUTC
2020-07-03T13:00:00.000Z	2020-07-03T14:00:00.000Z
2020-07-05T13:00:00.000Z	2020-07-05T14:00:00.000Z

and Resource Room's(AbcRoom, XyzRoom).
How to get all available room to the new collection based on DateTime in DateTimeBookingColllectionUTC above?
But if the AbcRoom with the date 2020-07-03T13:00:00.000Z -  2020-07-03T14:00:00.000Z has already been booked before, then AbcRoom will not appear in the new collection.

ClearCollect(AvailableInResourceCollection,
    ForAll(DateTimeBookingCollectionUTC,
       Office365.FindMeetingTimes(
          {
              RequiredAttendees: AllRoomsConnector,
              IsOrganizerOptional:true,
              Start: StartTimesUTC,
              End: EndTimesUTC,
              MeetingDuration: DateDiff(
                  StartTimeBooking,
                  EndTimeBooking,
                  Minutes
              ),
              MinimumAttendeePercentage: "1",
              ActivityDomain:"Unrestricted"
          }
       )
    )
);

I've tried this code, but it didn't work, It always gets all the rooms even though a few rooms have already been booked.
Thanks

[v-xida-msft]

Hi @Anonymous ,

Based on the scenario that you mentioned, I think there is something logic issue with it. You mean that if AbcRoom with the date 2020-07-03T13:00:00.000Z -  2020-07-03T14:00:00.000Z has already been booked before, then AbcRoom will not appear in the new collection. But if the AbcRoom has not been booked with date 2020-07-05T13:00:00.000Z -  2020-07-05T14:00:00.000Z , how do you want to to with that? Not include it in the new Collection?

According to the ForAll formula that you mentioned, it would fetch available Rooms based on each StartTimeUTC and EndTimeUTC in your DateTimeBookingColllectionUTC collection. If the AbcRoom has been booked with the date 2020-07-03T13:00:00.000Z -  2020-07-03T14:00:00.000Z , it would not be fetched within the first loop of ForAll formula. But if AbcRoom has not been booked with date 2020-07-05T13:00:00.000Z -  2020-07-05T14:00:00.000Z , it would be fetched within the second loop of ForAll formula. In the final DateTimeBookingColllectionUTC collection, the AbcRoom would be shown up for the date 2020-07-05T13:00:00.000Z -  2020-07-05T14:00:00.000Z rather than  2020-07-03T13:00:00.000Z -  2020-07-03T14:00:00.000Z ,.

As an alternative solution, I think you could consider configure two Date time Picker controls in your canvas app -- StartTime & EndTime as below:

Then you could consider fetch the available rooms based on the Start Time and End Time that you choose in your app. Then you need to modify your formula as below (set the Items property of a Gallery to following😞

Office365.FindMeetingTimes(
          {
              RequiredAttendees: AllRoomsConnector,
              IsOrganizerOptional:true,
              Start: Text(StartDatePicker.SelectedDate + Time(Hour(Value(StartHour.Selected.Value)), Minute(Value(StartMinute.Selected.Value)),0), "[$-en-US]yyyy-mm-ddTHH:mm:ss.fffZ"),   // Modify formula here
              End: Text(EndDatePicker.SelectedDate + Time(Hour(Value(EndHour.Selected.Value)), Minute(Value(EndMinute.Selected.Value)),0), "[$-en-US]yyyy-mm-ddTHH:mm:ss.fffZ"),  // Modify formula here
              MeetingDuration: DateDiff(
                  StartTimeBooking,
                  EndTimeBooking,
                  Minutes
              ),
              MinimumAttendeePercentage: "1",
              ActivityDomain:"Unrestricted"
          }
 )

Please consider take a try with above solution, then check if the alternative solution could help in your scenario.

Best regards,

[Anonymous]

Hi @v-xida-msft 
that's exactly my problem
ya, it's not included in the new collection if the AbcRoom has been booked in the DateTime range above(DateTimeBookingColllectionUTC ).  According to the code you gave me, it will not multiple DateTime because in the DateTimeBookingColllectionUTC  there can be many DateTime (can be 3 or more rows) that must be loop to get available resource room

[v-xida-msft · Accepted solution]

Hi @Anonymous ,

If the AbcRoom with the date 2020-07-03T13:00:00.000Z -  2020-07-03T14:00:00.000Z has already been booked before, and you do not want it to be shown up in the new collection even though the  AbcRoom has not been blocked with date 2020-07-05T13:00:00.000Z -  2020-07-05T14:00:00.000Z ?

I think you have not understood my statement mentioned before. I mean, if the AbcRoom with the date 2020-07-03T13:00:00.000Z -  2020-07-03T14:00:00.000Z has already been booked, but the AbcRoom has not been booked with the date 2020-07-05T13:00:00.000Z -  2020-07-05T14:00:00.000Z ? What about this? Do you get it? How do you want to handle this condition?

For date 2020-07-03T13:00:00.000Z - 2020-07-03T14:00:00.000Z in your (DateTimeBookingColllectionUTC) collection, the Office365Outlook.FindMeetingTimes() function result would not include the AbcRoom , but the AbcRoom has not been booked with the date 2020-07-05T13:00:00.000Z -  2020-07-05T14:00:00.000Z , so for 2020-07-05T13:00:00.000Z -  2020-07-05T14:00:00.000Z  in your (DateTimeBookingColllectionUTC) collection, the Office365Outlook.FindMeetingTimes() function result would include the AbcRoom.

If you do not want the AbcRoom to be shown up even though the AbcRoom has not been blocked with date 2020-07-05T13:00:00.000Z -  2020-07-05T14:00:00.000Z , I have made a test on my side, please consider take a try with the following workaround:

Clear(AvailableInResourceCollection);
ForAll(
       DateTimeBookingCollectionUTC,
       If(
          IsEmpty(AvailableInResourceCollection),
          Colelct(
                  AvailableInResourceCollection,
                  AddColumns(
                             Office365.FindMeetingTimes(
                                                         {
                                                         RequiredAttendees: AllRoomsConnector,
                                                         IsOrganizerOptional:true,
                                                         Start: StartTimesUTC,
                                                         End: EndTimesUTC,
                                                         MeetingDuration: DateDiff(
                                                                             StartTimeBooking,
                                                                             EndTimeBooking,
                                                                             Minutes
                                                                           ),
                                                         MinimumAttendeePercentage: "1",
                                                         ActivityDomain:"Unrestricted"
                                                       }
                                  ),
                                  "Room Name", First(Locations).DisplayName)
                  )
          ),
          Colelct(
                  AvailableInResourceCollection,
                  Filter(
                      AddColumns(
                             Office365.FindMeetingTimes(
                                                         {
                                                         RequiredAttendees: AllRoomsConnector,
                                                         IsOrganizerOptional:true,
                                                         Start: StartTimesUTC,
                                                         End: EndTimesUTC,
                                                         MeetingDuration: DateDiff(
                                                                             StartTimeBooking,
                                                                             EndTimeBooking,
                                                                             Minutes
                                                                           ),
                                                         MinimumAttendeePercentage: "1",
                                                         ActivityDomain:"Unrestricted"
                                                       }
                                  ),
                                  "Room Name", First(Locations).DisplayName)
                  ),
                  'Room Name' in AvailableInResourceCollection.'Room Name'
              )
          )
       )
)

Best regards,