Trying to Set Up an Office Reservation System

%3CLINGO-SUB%20id%3D%22lingo-sub-2553815%22%20slang%3D%22en-US%22%3ETrying%20to%20Set%20Up%20an%20Office%20Reservation%20System%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2553815%22%20slang%3D%22en-US%22%3E%3CP%3EWith%20my%20company%20starting%20to%20return%20to%20the%20office%20they%20are%20looking%20for%20some%20kind%20of%20way%20to%20see%20how%20many%20people%20are%20going%20to%20be%20in%20the%20office%20on%20a%20particular%20day.%20I%20was%20thinking%20of%20using%20a%20Sharepoint%2FLists%20calendar%20that%20people%20can%20enter%20when%20they%20are%20going%20to%20be%20in%20the%20office.%20I%20was%20thinking%20individual%20departments%20could%20have%20their%20own%20calendar%20where%20they%20can%20see%20names%20and%20those%20would%20feed%20to%20a%20company%20wide%20calendar%20that%20would%20show%20the%20number%20of%20people%20from%20each%20department.%20I%20can't%20wrap%20my%20head%20around%20how%20I%20would%20be%20able%20to%20calculate%20how%20many%20people%20would%20be%20in%20the%20office%20on%20particular%20days.%20I'm%20sure%20there's%20a%20way%20but%20I%20am%20a%20total%20noobie%20with%20this%20stuff.%20HELLLPP!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2553815%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EMicrosoft%20Lists%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ESharePoint%20Online%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2564956%22%20slang%3D%22en-US%22%3ERe%3A%20Trying%20to%20Set%20Up%20an%20Office%20Reservation%20System%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2564956%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F785814%22%20target%3D%22_blank%22%3E%40jackrum%3C%2FA%3E%26nbsp%3Bwe're%20in%20the%20same%20situation%20across%20multiple%20offices%20so%20I%20have%20built%20a%20system%20very%20similar%20to%20what%20you%20want%20to%20do%20and%20our%20staff%20book%20a%20desk%20for%20a%20day%20at%20the%20office%20they%20are%20going%20to%20be%20in.%20It%20is%20based%20on%20the%20videos%20by%20April%20Dunnam%20shown%20at%20the%20bottom%20of%20this%20post.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20also%20used%20Power%20Apps%20as%20the%20front%20end%20and%20the%20backend%20in%20SharePoint%20has%202%20lists%2C%201%20for%20desks%20and%201%20for%20the%20reservations.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20desks%20list%20has%20the%20location%2C%20title%20of%20each%20desk%20and%20whether%20it%20is%20active%20or%20not%3A%3CBR%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%220-SP-DesksList.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F297015iDCD3486F32BC0F62%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%220-SP-DesksList.png%22%20alt%3D%220-SP-DesksList.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20columns%20I've%20got%20in%20Reservations%20list%20are%20as%20follows%3A%3CBR%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%220-SP-ReservationsList-Columns.png%22%20style%3D%22width%3A%20426px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F297021iCB4DDE5D419BD998%2Fimage-dimensions%2F426x257%3Fv%3Dv2%22%20width%3D%22426%22%20height%3D%22257%22%20role%3D%22button%22%20title%3D%220-SP-ReservationsList-Columns.png%22%20alt%3D%220-SP-ReservationsList-Columns.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20app%20in%20Power%20Apps%20looks%20like%20this.%20The%20user%20doesn't%20have%20to%20enter%20their%20name%20or%20email%20address%20as%20that%20is%20picked%20up%20automatically%20from%20their%20Office%20365%20details%3A%20the%20On%20Start%20function%20of%20the%20app%20sets%20the%20Current%20User%20variable%20using%26nbsp%3BSet(currentUser%2CUser())%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%221-App-Dashboard.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F297027iB3164EEE7333865D%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%221-App-Dashboard.png%22%20alt%3D%221-App-Dashboard.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%222-App-Calendar.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F297028i51A65F3EFEA2DB47%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%222-App-Calendar.png%22%20alt%3D%222-App-Calendar.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EAfter%20the%20first%20600%2B%20bookings%20were%20made%2C%20and%20after%20feedback%20from%20users%2C%20we%20simplified%20it%20from%20being%20able%20to%20book%20any%20slots%20between%208%20and%205%20of%20any%20duration%20to%20inititally%20just%208am%20to%201pm%20then%201pm%20to%206pm%2C%20but%20have%20just%20simplified%20it%20again%20yesterday%20to%20just%20booking%20for%20a%20day%2C%20however%20much%20of%20the%20day%20the%20user%20will%20actually%20use%20it.%20This%20makes%20the%20syntax%20for%20checking%20available%20desks%20easier%20and%20more%20reliable.%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%223-App-AvailableDesks.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F297030iCE434551A7F14CDE%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%223-App-AvailableDesks.png%22%20alt%3D%223-App-AvailableDesks.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EThat%20syntax%20is%20now%20quite%20straightforward%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-json%22%3E%3CCODE%3EFilter(Desks%2CNot(Title%20in%20Filter(%0A'Desk%20Reservations'%2C%0ACheckOutFrom%20%3D%20startTime).DeskText)%0A%26amp%3B%26amp%3B%20Active%20%3D%201%0A%26amp%3B%26amp%3B%20Location%20%3D%20selectedLocation)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%224-App-Confirm.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F297026iFA8E63436D405139%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%224-App-Confirm.png%22%20alt%3D%224-App-Confirm.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EOnce%20the%20user%20confirms%20the%20booking%20the%20data%20is%20patched%20to%20the%20desk%20reservations%20list%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-json%22%3E%3CCODE%3EForAll(selectedDesks%2C%0A%0APatch(%0A'Desk%20Reservations'%2C%0ADefaults('Desk%20Reservations')%2C%0A%7B%0ATitle%3A%20currentUser.FullName%20%26amp%3B%20%22%20%22%20%26amp%3B%20ThisRecord.Title%2C%0ALocation%3A%20selectedLocation%2C%0ADeskText%3A%20ThisRecord.Title%2C%0ACheckOutFrom%3A%20startTime%2C%0ACheckOutTo%3A%20endTime%2C%0AOnBehalfOf%3A%20txtOnBehalfOf.Text%2C%0AReservedBy%3A%20%7B%0A'%40odata.type'%3A%20%22%23Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser%22%2C%0AClaims%3A%20%22i%3A0%23.f%7Cmembership%7C%22%20%26amp%3B%20currentUser.Email%2C%0ADepartment%3A%20%22%22%2C%0ADisplayName%3A%20currentUser.FullName%2C%0AEmail%3A%20currentUser.Email%2C%0AJobTitle%3A%20%22%22%2C%0APicture%3A%20%22%22%0A%7D%0A%7D%0A))%3B%0A%0ANavigate(Success%2C%20ScreenTransition.CoverRight)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAdding%20the%20new%20item%20to%20the%20list%20triggers%20a%20flow%20in%20Power%20Automate%20to%20send%20the%20user%20an%20email%20confirming%20their%20booking%3A%3CBR%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%225-Email.png%22%20style%3D%22width%3A%20289px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F297031i7566C1C1D5AF527D%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%225-Email.png%22%20alt%3D%225-Email.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThere%20are%20other%20things%20that%20are%20going%20on%2C%20for%20example%20one%20screen%20in%20the%20middle%20is%20for%20the%20user%20to%20confirm%20they%20accept%20the%20risk%20assessment%20for%20the%20office.%20And%20they%20can%20also%20view%20a%20PDF%20of%20the%20layout%20of%20the%20office.%20Also%20anyone%20can%20print%20out%20the%20bookings%20for%20today%20at%20a%20particular%20office%20from%20the%20icon%20non%20the%20first%20screen.%20Our%20Directors%20like%20the%20app%20and%20have%20made%20it%20mandatory%20for%20bookings%20so%20I%20think%20it%20will%20be%20in%20use%20for%20the%20next%20year%20or%20so.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20hope%20that%20quick%20canter%20through%20the%20system%20has%20given%20you%20some%20ideas.%20Come%20back%20with%20any%20questions%20or%20if%20you%20need%20to%20know%20in%20detail%20how%20something%20has%20been%20done.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERob%3CBR%20%2F%3ELos%20Gallardos%3CBR%20%2F%3E%3CFONT%20size%3D%222%22%20color%3D%22%23006400%22%3EIntranet%2C%20SharePoint%2C%20Website%20and%20Power%20Platform%20Manager%20(and%20classic%201967%20Morris%20Traveller%20driver)%3C%2FFONT%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

With my company starting to return to the office they are looking for some kind of way to see how many people are going to be in the office on a particular day. I was thinking of using a Sharepoint/Lists calendar that people can enter when they are going to be in the office. I was thinking individual departments could have their own calendar where they can see names and those would feed to a company wide calendar that would show the number of people from each department. I can't wrap my head around how I would be able to calculate how many people would be in the office on particular days. I'm sure there's a way but I am a total noobie with this stuff. HELLLPP!

2 Replies

@jackrum we're in the same situation across multiple offices so I have built a system very similar to what you want to do and our staff book a desk for a day at the office they are going to be in. It is based on the videos by April Dunnam shown at the bottom of this post.

 

I've also used Power Apps as the front end and the backend in SharePoint has 2 lists, 1 for desks and 1 for the reservations.

 

The desks list has the location, title of each desk and whether it is active or not:
0-SP-DesksList.png

 

The columns I've got in Reservations list are as follows:
0-SP-ReservationsList-Columns.png

 

The app in Power Apps looks like this. The user doesn't have to enter their name or email address as that is picked up automatically from their Office 365 details: the On Start function of the app sets the Current User variable using Set(currentUser,User())

 

1-App-Dashboard.png

 

2-App-Calendar.png

After the first 600+ bookings were made, and after feedback from users, we simplified it from being able to book any slots between 8 and 5 of any duration to inititally just 8am to 1pm then 1pm to 6pm, but have just simplified it again yesterday to just booking for a day, however much of the day the user will actually use it. This makes the syntax for checking available desks easier and more reliable. 

3-App-AvailableDesks.png

That syntax is now quite straightforward:

 

Filter(Desks,Not(Title in Filter(
'Desk Reservations',
CheckOutFrom = startTime).DeskText)
&& Active = 1
&& Location = selectedLocation)

 

 

4-App-Confirm.png

Once the user confirms the booking the data is patched to the desk reservations list:

 

ForAll(selectedDesks,

Patch(
'Desk Reservations',
Defaults('Desk Reservations'),
{
Title: currentUser.FullName & " " & ThisRecord.Title,
Location: selectedLocation,
DeskText: ThisRecord.Title,
CheckOutFrom: startTime,
CheckOutTo: endTime,
OnBehalfOf: txtOnBehalfOf.Text,
ReservedBy: {
'@odata.type': "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",
Claims: "i:0#.f|membership|" & currentUser.Email,
Department: "",
DisplayName: currentUser.FullName,
Email: currentUser.Email,
JobTitle: "",
Picture: ""
}
}
));

Navigate(Success, ScreenTransition.CoverRight)

 

 

Adding the new item to the list triggers a flow in Power Automate to send the user an email confirming their booking:
5-Email.png

 

There are other things that are going on, for example one screen in the middle is for the user to confirm they accept the risk assessment for the office. And they can also view a PDF of the layout of the office. Also anyone can print out the bookings for today at a particular office from the icon non the first screen. Our Directors like the app and have made it mandatory for bookings so I think it will be in use for the next year or so.

 

I hope that quick canter through the system has given you some ideas. Come back with any questions or if you need to know in detail how something has been done.

 

Rob
Los Gallardos
Intranet, SharePoint, Website and Power Platform Manager (and classic 1967 Morris Traveller driver)

PowerApps #DeskReservationApp #TemplateTuesdayIn this video I'm addressing some of your questions and feature requests for the Desk Reservation Power App Te...
PowerApps #PowerAppsTemplates #TemplateTuesdayI showcase my Desk Reservation system template built in Power Apps. This is a mobile based app that lets you ...

@RobElliott 

Thanks for the reply. I knew of the April Dunham videos but was avoiding them since I don't know Power Apps yet. I'm just starting to wrap my head around Power Automate and Lists/Sharepoint so I was hoping I could just use them. It's starting to look Like all roads lead to those videos. I'm not sure I'll have it in me to learn power apps well enough to be able to pull something off by Sept. We'll see.