Microsoft PowerApps – Sharing Learning

I heard about Microsoft “Power Platform” last year but due to professional life I never got a chance to read and explore about it until last month. I just started with Microsoft PowerApps about month ago and had lots of challenges to implement very common and small business module that is “Timesheet”. The requirement was to allow employees to just save & submit “Timesheet” using Microsoft PowerApps.

First most questions and comments I had were…

• Why PowerApps?
• Why not mobile native apps developed for iOS & Android?
• Why to duplicated business logic which is already written and working fine for Web Portal?
• How some of the basic calculation rules will be implemented?
• Why to allow direct database access?
• Why to query and update tables directly?
• PowerApps is primitive!
• Already someone failed or rejected to work on this so why to try again!?

Actually if we look at above points, mostly are NOT related to “PowerApps”.  But in fact, these were related to my lack of knowledge and willingness to not to accept this task. It is like you drive your effort towards something only if it is forced; having said that this time I was forced to play, explore & learn about “PowerApps” and develop this “Timesheet” module. So whatever I am sharing here is just my learning and I must say that I am not expert on PowerApps!

Below are the basic requirements / validations for “Timesheet” screen which I will be explaining on how to implement:

• Database: Azure SQL Server
• Important tables:
  • Users – Master table for employee login details
  • Employees – Master table for more details of employee
  • Projects – Master table for all the projects with start and end date along with other required fields
  • EmployeeProjects – Project assigned to each employee along with employee start and end dates
  • Timesheets – Master table for all the timesheet for a week for an employee along with “Overtime”, “Compensatory Off”, “Total” hours and “Status”
  • TimesheetDetails – Daily hours for a project along with “Status”
• Home/Dashboard screen should display employee name
• Start of the week is always Saturday
• Timesheet status is integer Enum in web portal
• Employee will have access to multiple projects
• Employee should be able to add comment for specific project
• Validations Horizontal/by Row (for a project):
  • Display total number of hours booked for a project in a week
  • Project: Week Off should be 16 hours
  • Employee should not be allowed to enter hours for future dates; except “Week Off” & “Vacation / Holiday” project 
  • If employee joined mid of the week, employee should not be allowed to enter hours for earlier dates; except “Week Off” project
  • If specific project end in mid of the week, employee should not be allowed to enter hours for later dates
• Validations Vertical/by Column (for a day):
  • Minimum – 8 hours 
  • Maximum – 24 hours
  • Employee should be either book hours in “Vacation / Holiday” project or any other project; both shouldn’t be allowed
• If employee entered more than 56 hours in projects, additional hours must be entered in “Overtime” and/or “Compensatory Off”.
• If hours are entered, input background colour should be changed to green; otherwise it should be white
• Any validations failed, input border should be changed to red & message but be displayed
• Background colour of alternate row should be be different

I know, it looks too much to implement but in next set of blogs we will see step-by-step how this was done.  To make things more friendly & easy to understand, I am going to split it into multiple blogs.

---

On top I am going to skip few of the basic starting points, like:

• Creating new PowerApps
• Creating Data -> Connections for SQL Server
• Adding Tables/Views to PowerApps as Data Source

You can refer Create a canvas app in PowerApps quick start on these points.