Power Apps is Microsoft PaaS for creating various application which can make your and yours colleagues life much easier. When our company bought Office 365 subscription I started to explore it and very quickly found use case.

Use case/history

In our team we had problem to find-out who is where. Someone works from home, someone is on training, sick leave, vacation etc… Yes, few of us sends an e-mail when out of office, but for some of us even this is much work to do. So I decided to create Power App which could solve this. First version is very basic, just form where you can select absence type, start, end and add description. Form is connected to Azure SQL table and on this table is hooked Logic Apps flow which generates e-mail with all data and sends it to the team. Second part is calendar where everyone can check who was, is and will be (thanks to help from my colleague). Nice bonus is mobile app for even more easy absence adding. It works in our team until now. As the app is very useful and works nicely it still has some limitations. No approvals, no balance for absence types, it does not take care of vacations and weekends. So I decided to create more mature app which would cover all mentioned features.

Architecture decisions

First step was to find-out If there is some existing solution for managing absences in Power Apps. I found Microsoft template.., great problem solved.. Well, after some investigation I realized it is not that good. Every user has own Sharepoint list with data, approvals were made inside app, no approval notifications or e-mails. So in the end I decided to make my own version from scratch using only design inspiration. Another change I’ve made was to move out from SQL server. Reason was clear. Microsoft put SQL connector to Premium tier so our Office 365 subscription would not cover it anymore. As replacement I chose Sharepoint lists which on one hand are very easy to use, on the other has limitations I will mention later.

Easy to manage

Easily add/remove absences types as they are stored in Sharepoint List.

How does it work?

When you start app, first screen you see is “New leave request”. There you can choose what type of absence you want, From and To time and add description. On the right side you will see number of requested days and future balance. On lower right side there is information about approval. Some absence types can be submitted directly, some of them must be approved first by users manager.

“My Leave Requests” screen shows your absence records, those not approved yet, with “Recall” button. So once you create request which has to be approved, Microsoft Power Automate flow is initiated and generate Approval request to your manager (based on Office 365 configuration). He receives interactive e-mail where he can approve/decline your absence. Before he decides, you can Recall you request. If request is for Absence Type which does not need approval, your absence is added immediately.

Built-in Approvals

Manager receive interactive approval e-mail.

Absence calendar

Full visibility about absences, thanks to embedded Power BI calendar.

Another interesting screen is “Home” where is Power BI calendar which shows who is where. Absence types are grouped and showed in different colors. (e.g. when you are unreachable during absence the color is red, green when you are working but not from office, etc.). Here I must point to one inconvenience. Sharepoint lists datasource in Power BI does not support direct query. So only way to get actual data is to schedule report refresh which is possible only 10 times a day (or adjust app back to Azure SQL where direct query is supported).

edit: I’ve found a way to refresh report when navigate to it’s screen, hooray who needs SQL anymore 🙂

Last two screens are “Public Holiday” table, where you can see which days will be excluded when counting taken days and “About” screen, where boring stuff about version, support etc. is.

Some technical stuff

App is based on few Sharepoint lists which act like tables. There is list with absence records, list with users (these can be automatically imported from Office365/AAD) list with Absence types, etc.. . Thanks to Lists and Power App design, app admins can add/remove absence types including their behavior by editing relevant Sharepoint list. Also design (colors, texts, graphics, etc..) can be easily adjusted to fit any need. Power Automate flow is hooked on Sharepoint list and it is used to initiate Approval (if needed). Approvals are made by standard Office 365 app.

Included in Office 365 E3

No need for additional subscription, all parts are covered in O365 E3.

Last but not least

For running this app you only need Office 365 subscription which include Power Apps and Power Automate (e.g. E3) and that’s it. It is also easily configurable as Power Apps are low code platform. While working on this app I’ve learnt lot about platform including it’s downsides. It is perfect when you need simple, ideally one-purpose apps connected to Microsoft ecosystem. On the other hand sometimes it is pain to do some more mature logic in it, especially lack of creating own functions was frustrating. (now it is being partly solved by reusable components). Another downside is code clarity. As there is no, one, simple code overview you need to go through all objects one by one if you want to change for example variable name, or part of logic common for more objects. If you want to know more about app just leave comment here or contact me directly.