Another time issue with Book A Room App

[DBG_Wiehl]

Hello 
Yes, I am an absolute beginner in using PowerApps, so please don't blame me for my question - please 🙂

Here we go:
The Book A Room - App shows available time slots in a 30min grid. I changed that in 5min slots by adjusting the initial settings to:

[..]
ClearCollect(Times;{Text: "07:00";Minutes: 420};
{Text: "07:05";Minutes: 425};
[..]

I also changed the settings where the MOD is calculated to

Set(DateSelected; Today());;
Set(DateTimeRoomChange; true);;
If(
Mod(Minute(Now()); 5) <= 2;
Set(StartDateTime; DateAdd(Now(); -Mod(Minute(Now()); 5); Minutes));;
Set(EndDateTime; DateAdd(StartDateTime; 5; Minutes));
Mod(Minute(Now()); 5) >= 3;
Set(StartDateTime; DateAdd(Now(); -Mod(Minute(Now()); 5) + 5; Minutes));;
Set(EndDateTime; DateAdd(StartDateTime; 5; Minutes));
Set(StartDateTime; Now());;
Set(EndDateTime; DateAdd(Now(); -Mod(Minute(Now()); 5) + 5; Minutes))
);;

In my understanding this calculates times by checking if the MOD of 'Actual Minutes' / 5 is equal less 2 or equal more than 3. Afterwards adjusting the start time in parts of 5min. Works all well, as far as I can see.

But when it comes to select a start time, the query always sends a 'No rooms available' back, when the start time is unequal to a full hour or half hour (like 09:25). When the start time is equal to a full or half hour (like 09:00 or 09:30), available rooms are shown properly.

I really cannot find any other variables or settings, where I can adjust that 30min grid to a 5min grid.

Any help appreciated 🙂

[Anonymous]

Hi @DBG_Wiehl welcome to the PowerApps community and thanks for your question. I am going to bump this so the community can review and assist. 

@Anonymous 

[BBM1]

Is there any solution? Got the same problem.

[DBG_Wiehl]

Still no answer.. 😕
This is somewhat sad, because my school could use this app, indeed...

[DBG_Wiehl]

Ok, I'll post my adjusted script. Maybe someome can find any issues with that...

App on Start:

ClearCollect(Times; 
{Text: "00:00"; Minutes: 0};
{Text: "00:05"; Minutes: 5};
{Text: "00:10"; Minutes: 10};
{Text: "00:15"; Minutes: 15};
{Text: "00:20"; Minutes: 20};

..and so on

Landing-Screen (Button NoMeeting) - these are my adjustments to achieve a 5min timetable instead of the (working) 30mins:

Set(DateTimeRoomChange; true);;
If(Mod(Minute(Now()); 5) <= 2; 
    Set(StartDateTime; DateAdd(Now(); -Mod(Minute(Now()); 5); Minutes));;
    Set(EndDateTime; DateAdd(StartDateTime; 5; Minutes));
   Mod(Minute(Now()); 5) >= 3;
    Set(StartDateTime; DateAdd(Now(); -Mod(Minute(Now()); 5) + 5; Minutes));;
    Set(EndDateTime; DateAdd(StartDateTime; 5; Minutes));
   Set(StartDateTime; Now());;
   Set(EndDateTime; DateAdd(Now(); -Mod(Minute(Now()); 5) + 10; Minutes))
);;

Set(StartDateTimeUTC; Text(DateAdd(StartDateTime; TimeZoneOffset(); Minutes); "[$-en]yyyy-mm-ddThh:mm") & ":00.000Z");;
Set(EndDateTimeUTC; Text(DateAdd(EndDateTime; TimeZoneOffset(); Minutes); "[$-en]yyyy-mm-ddThh:mm") & ":00.000Z");;

Set(BookForMeeting; false);;

If(NoRoomsList;
    Navigate(RoomSelectScreen; Cover);
    
    Navigate(RoomListSelectScreen; Cover)
)

RoomSelect-Screen (OnVisible):

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 & ";"))
    );
    
    Office365.FindMeetingTimesV2({RequiredAttendees: AllRoomsConnector, IsOrganizerOptional: true, Start: Now(), End: DateAdd(Now(),1,Hours), MeetingDuration: 30, MinimumAttendeePercentage: "1", ActivityDomain: "Unrestricted"}));
    */
    
    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)

the problem must be located somewhere here; because if I set start times unequal to xx:00 or xx:30 no rooms are returned..

[DBG_Wiehl]

Ok. it seems I#'ve got a solution; finally 🙂

The problem seems to be somewhere in the date/time format that is created in the app.
For me (location Germany) the whole thing started to work at the moment I introduced a second DateTimeUTC; like:

Set(StartDateTimeUTC; Text(DateAdd(StartDateTime; TimeZoneOffset(); Minutes); "[$-en]dd-mm-yyyyThh:mm:") & "00.000Z");;
Set(EndDateTimeUTC; Text(DateAdd(EndDateTime; TimeZoneOffset(); Minutes); "[$-en]dd-mm-yyyyThh:mm:") & "00.000Z");;
Set(StartDateTimeUTC2; Text(DateAdd(StartDateTime; TimeZoneOffset(); Minutes); "[$-en]yyyy-mm-ddThh:mm:") & "00.000Z");;
Set(EndDateTimeUTC2; Text(DateAdd(EndDateTime; TimeZoneOffset(); Minutes); "[$-en]yyyy-mm-ddThh:mm:") & "00.000Z");;

As you can see StartDateTimeUTC follows German rules to show a date; which is like Day Month - Year

The second StartDateTimeUTC2 follows a rather logical rule, which is Year - Month - Day

So what have I done?
1. I exchanged every row in the BookARoom-App where these two variables are set or changed.

2. Next I changed the 'On Visible' in the ConfirmationScreen in that way, that every StartDateTimeUTC was changed into StartDateTimeUTC2
Now I see all rooms which are available in ticks of 5min; regardless of the starting time - working

Now I can book the room with the disered span of time (min 5min) - working

I would lie if I told you I understand the whole thing - I don't *g* but it works... Yeah 🙂

(This is just a short description what I did to make this app work for me. If you are interested in the details, please send me a notification)

[BBM1]

Hey kannst du vielleicht die ganze App hier anfügen? Ich denke es betrifft viele Stellen im Code oder? Danke und schön, dass du das Problem gelöst hast.

[DBG_Wiehl]

Ich habe hier meine Version der Raumbuchungs-App angefügt.
Zahlreiche Anpassung im optischen Design

Viele Codeschnipsel geändert; darunter insbesondere:
- Änderung der Zeit-Ticks von 30min auf 5min-Intervalle
- Hinzufügen von Variablen DateTimeStart/EndUTZ2, um Buchung durchzuführen
- Änderung des Timecodes, um Raumbuchungen/Outlook abzufragen; von yyyy-mm-ddThh:mm auf dd-mm-yyyyThh:mm

Bei Fragen bitte kurze Nachricht
Hinweis: Ich habe die Befürchtung, dass die Zeitabfrage immer noch nicht korrekt funktioniert