Within this eBook, we will look at how to make use of the Microsoft Power Platform and the Common Data Service (CDS) to create a fully functioning Leave Request App in under 30 minutes.
This eBook will cover the basics for creating a company PTO (Personal Time Off) leave request app. Where the employee will submit a form for leave, the supervisor will receive an email saying this person wants to take leave, and then the employee will gain a response back.
To create this, we’ll be adding entities to the Common Data Service (CDS), using the canvas apps and model-driven apps as well as Power Automate for email notifications.
The first part of this eBook contains an explanation about what each part does, then the second section contains step by step instructions to help you create a solution to use in your business organisation.
Microsoft Power Platform
The Microsoft Power Platform is a low code platform that spans Office 365, Azure, Dynamics 365, and stand-alone applications including Power BI, Power Apps, and Power Automate. These processes will allow you to automate your applications, anything from adding a Twitter feed, to sending an email to even connecting two instances in Azure.
There are over 300 data connectors that can be used in Power Automate to make your businesses come to life and automate those systems that you have within your business.
The Power Platform also includes Power Virtual Agents (PVAs), which allow you to have chatbots in your applications. The Common Data Service (CDS), which is the database for the Power Platform, can be used to create these agents so that they are automated through the database system. You don’t need to have a real person managing these
chatbots, you can make them speak to a customer through the database using AI and adding your own conversations through that.
Common Data Service
The Common Data Service contains standard entities. I like to think of the standard entities as the bigger box for the Common Data Service, so within the entities, you’ll be able to add forms, views, charts, business process flows etc.
You can also create a security model for your applications within the Common Data Service, down to field level, and determine who can view those fields. You can create security, for example, by restricting only HR to see employee salary details.
I like to think of model-driven apps as the back office of your organisation. A model-driven app will be where IT or Admin can find information about other applications.
The model-driven app allows you to display lists of things created in the CDS; an example can be seen below.
This app contains details about the primary contact, timeline, a business process flow, with additional tabs for a summary, product line items, quotes, and related documents, each of which has different kinds of items inside of them.
What’s also important about model-driven apps is that they are relational applications. If you want to have an account that is connected to a contact, you’ll be able to make that relationship within the model-driven app so that all contacts within that account are included and available to view.
Canvas apps are kind of like WYSIWYG (What you see is what you get). So think of canvas apps as Excel and word, but also incorporate a lot of design so you can actually drag and drop almost anything.
In our case for this leave request app, we’re going to be using the canvas app to submit a form so the employee will go into your canvas app and fill out the information for their leave. For example, with their name, their email, the start date of their leave, the end date, and the type of leave.
The employee won’t be able to see what other people have submitted because this is just a form, but once they submit it, the supervisor will get an email and their specific leave will get added to the model-driven app that we will create for supervisors/managers.
Power Automate, as the name suggests, automates the processes for your organisation.
A trigger needs to kick off the flow. This can be a manual process, set to run at a specific time schedule, or you can say; ‘hey every time this entity is updated…’ then trigger the flow. For our system, the trigger will be when someone submits a leave request.
The second part of Power Automate contains the actions that need to take place once the trigger has occurred. In our application, when a record is created, we need to send an email to the owner.
Power Apps Licensing
People are always curious about Power Apps licensing. The different plans and related costs are summarised below:
Getting started with Power Apps
Initially, go to http://make.powerapps.com, and once you’ve created a Power Apps tenant, on the Home page, you should see the following:
Finding your way around the Power Apps screen is fairly straightforward and similar to the other Microsoft365 technologies as you’d expect.
The waffle menu in the top left corner allows you to connect to different apps.
By clicking on “Environment” in the top menu, you can switch between environments.
The default environment is Contoso, however, this doesn’t have a database system, so I’ve created a new environment called Collab365.
You need to have admin access and admin rights to create a new environment & then can click on ‘Tools’, ‘Admin center’, then ‘+ New’ which will take you through the process of creating a new environment.
All of your environments will be displayed in the Admin Center:
Creating the Leave App
The Back Story:
You are an organisation that has employees who want to take leave, but the very basic system isn’t working. People aren’t sending emails, or there’s no good way for someone to request leave or track leave requests.
The steps in this solution are:
- create a form for your employees to provide the necessary information for their leave.
- That form will produce an email that is sent to the supervisor. It will also create a row inside of a model driven app so that the supervisors or managers can review all of the leave requests.
- When the supervisor approves (or rejects) the request, the employee will receive an email saying either; ‘your leave was approved’ or ‘your leave was rejected,’ and that will also update the model-driven app.
The Power Apps Interface
If this is your first time on the Power Apps user interface, this is what the home screen looks like:
There are four options for creating an App:
- Start from data
- Canvas app from blank,
- Model driven app from blank, or
- Portal from blank.
For our solution, we’re going to be creating data within the Common Data Service. Go into solutions (left-hand menu) and create a new solution:
Give the solution the Display Name ‘Leave request Solution.’ I always include the word ‘solution’ in the name, so it’s obvious that this is the solution. Similarly, when creating entities, it’s useful to add the suffix ‘entity’ so you can keep track of all elements of the application.
I want to create a new publisher to use for this app so it can be unique, instead of using the default CDS publisher, although it’s fine if you’d prefer to use existing publisher.
For the display name I’m going to use ‘LeaveRequestApp’ and the prefix I’m just going to abbreviate this to LRA (leave request app), and here you can see that it generates that prefix: LRA_entity. Everything else you can leave blank:
Save and close, go back to the New solution form and choose the new publisher:
Leave request app version set to one, and we can leave everything else as is, then click create, to create the new solution.
The Leave Request Solution has been added to our out of the box solutions:
Click on the Leave Request Solution, which, of course, will initially be empty.
Creating New Entities
We now need to start creating the Entities for our app. Click ‘+New’ then ‘Entity ‘.
Enter the Display name as ‘Leave Request Entity’.
In the name field, the prefix LRA_ is used because we created a publisher for our solution. This primary name field is required and will be populated automatically in our form and entities, so change this to ‘First Name.’
We’re going to leave enabling attachments blank and click ‘Done’ to create the entity. This takes a few moments, but you will eventually see the following:
The initial fields that are created are all out of the box fields that can be used in forms, views, and Power Automate.
The only field we’ve created so far is the primary name field called ‘First Name’. We now need to add more fields, so select ‘+ Add Field’
Our second field type is going to be ‘Last Name’, so we can collect the employees first and last names. We need this to be a required field and click done to create this field.
In the same way, create new required fields for:
- Email (change the data type to email)
- Supervisor email (again remember to change the data type to email)
- Start date (data type needs to be ‘date only’)
- Work items (data type needs to be multiline text and in the description field, add ‘outstanding work items’)
- Request type (data type needs to be Option Set)
We need to create a new option set for the Request Type field, as we want the reasons for requesting leave to be; Annual leave, sick leave, and holiday:
You can select a default value if you want, but I’m going to leave it blank. Make the field recommended, and click ‘Done’:
Now that we’ve created our fields, we’re going to go ahead and save the entity. You can see we have all of our new fields within our fields tab:
Building the View
Next, we need to build our view of the collected data so we can see all of the requests, line by line, the first name, last name, etc. Initially, when you click on the ‘View’ tab, you will see all of the ‘out of the box’ views like this.
Let’s start with the one at the top of the list, ‘Active Leave Request Entities’, which will take us into this designer view:
Initially, ‘First Name’ and ‘Created On’ are the only columns in this view. We want to remove ‘Created On,’ leave ‘First name’ and add the other ones that are important for us.
To remove columns that are unwanted (in this case, ‘created on’), click the down arrow to the right of the name, and select ‘Remove.’
Click on the fields in the left-hand menu to add the ones you want (‘End date,’ ‘Email,’ ‘Last Name,’ and ‘Start date’) or use the ‘+ Add Column’ option. You can change the order of these by clicking and dragging them, and also change the width of the columns by clicking on the edge of the column title and dragging to the desired width.
Once you’re happy with your format, save and publish your work.
Once we start adding data, you’ll be able to see all of this come to life. Now we need to create our form.
Creating the Form
This form will allow the supervisors or managers to review the information about that specific employee leave request within the model-driven app, but we’ll actually be using the same form for our canvas app. From the ‘Forms’ tab, select an out of the box form.
It’s often easier to start with an out of the box option and tweak if for your needs, rather than start from scratch using the ‘Add Form’ option.
The primary text field is always added to everything. Again we need to add the other fields we want to include in this form.
To add items, simply click on them in the left-hand menu, or drag and drop them into the form.
Add ‘Email’, ‘Last Name’, ‘Request Type’, ‘Start Date’, ‘End Date’, ‘Work Items’ and ‘Supervisors Email’.
Fields that are not needed (in our case, ‘Owner’) can be hidden:
Select 2 columns from the Formatting option in the General menu
Move ‘Start Date’ and ‘End Date’ into the new column and rearrange the fields in your form into the order you want them by clicking and dragging.
Name the entity; ‘Leave request,’ then save and publish again,
Go back to the solution, and you’ll see that the leave request entity and the option set we created are shown:
Creating the Model-Driven App
Now we’re going to create our model-driven app. From the Leave Request Solution. Go back to our leave request solution select ‘+ New,’ ‘App,’ then select ‘Model-driven app.’
This will take you to the ‘Create a New App’ screen, which is the interface for creating a model-driven app as shown below.
Call this app ‘Leave Request Back Office,’ and select ‘Use existing solution to create the
App’, then click ‘Next.’
Select ‘Leave Request Solution’ from the drop-down menu and select done.
This is going to bring us to the app designer for our model-driven app, which will initially show that we have some missing configuration for the site map.
The site map will be the navigation for our model-driven app. Click the pencil to the right of ‘Site Map’ and again by ‘New Subarea’ to edit this.
A long drop-down list will be presented in the entity field, which you can scroll through, or begin typing; ‘leave request’ to find and select this option more easily.
This will connect the entity created earlier in CDS to this model-driven app so that when the user starts adding data, it is all connected. Change the group name to ‘Requests.’
‘Save,’ ‘Publish,’ and ‘Save and Close’ which will return you to the App designer, where you will notice the ‘Configuration Missing’ message has gone.
Then ‘Save’ and ‘Publish’ here as well:
Then ‘Play’ it to view the back office model-driven app created for our leave requests.
This is our back office and will display the view created earlier, currently, with no data in it.
To add some test data, click ‘+ New.’
Then complete all of the required fields, then ‘Save and Close.’
As more requests are added over time, you can filter them by using any of the column names or export to Excel or add a flow.
Creating the Canvas App
Now we need to create our canvas app. Go back to http://make.powerapps.com and you’ll see the model-driven app and the site map that we’ve created.
Click ‘+ New’, ‘App, ‘Canvas App’, and ‘Tablet form factor’. This will take a few moments.
Then skip to the next screen & click ‘Get started’ if prompted.
From the ‘Insert’ tab, click the ‘Forms’ drop-down menu, then the ‘Edit’ option, which will allow users to edit existing fields and add new rows.
Click ‘Connect data’ (shown in very light grey in the middle of the screen).
Then select ‘Leave Request Entities’ from the entity list provided:
In the form menu on the right-hand side of the screen, select ‘Leave Request Entities’ from the ‘Data Source’ field drop-down.
Rename the form to ‘Leave Form’:
Next, click on ‘Edit Fields’ in the form menu:
We need to add all of the fields we created for the form as this will become the interface the employee will use to enter all of the necessary information. Select ‘Request Type’ and ‘Supervisor email’ and ‘Work items’ and then ‘Add.’
Insert a label to the top of the form so that the user knows what it is and align the boxes and text on the form as you wish.:
Change the default mode to ‘New’ so that when we go to test it, we can have an empty form to complete.
Also, insert a button so that the user can submit this form.
Change the button to say ‘Submit’ by selecting amending the Text property.
When the button is selected, we want the data in the form to be submitted. To do this, from the drop-down, select ‘OnSelect’ and in the formula field add: SubmitForm(‘Leave Form’);
Next, we’re going to add a success screen so that the user goes to a different page after they have submitted their data, so they know the process has worked properly.
From the ‘Insert’ menu, drop-down menu, select ‘New Screen,’ then ‘Success.’
Edit the text so that it will make sense for the user.
Next, edit the button formula to say; SubmitForm(‘Leave Form’);Navigate(Screen2);
Save, and then try it. Click the play button and fill the form out.
Upon submission it should take you to the different screen:
Creating the Flow
Finally, we need to create the flow in Power Automate to bring all of these different elements to life.
From the ‘+ New’ menu, select ‘Flow’.
This is the flow that’s going to bring it all to life and will begin when the employee fills in the Leave Request Form to create a record.
For the trigger, search for; ‘when a record is created’ in the search connectors and triggers field and select; ‘When a record is created, updated or deleted – Common Data Service (current environment)’ from the triggers options.
For the trigger condition, chose; ‘Create.’ For the entity name, select ‘Leave Request Entities’ and for the scope, select organisation. Then add a New Step.
The next step is to ‘start and wait for an approval,’ using the ‘First to respond’ option.
This approval process will send an email to the supervisor, many elements of which can be dynamically created using data we are utilising in this application. Use the ‘Add Dynamic content’ option to select the relevant data for each field.
Set the Title of the email to be dynamically created using the ‘First Name,’ space, ‘Last name’ and add the text ‘is requesting leave’. For the Assigned to field, add ‘Supervisor Email,’ again using dynamic content. The details will form the body of the email and again can be created using a mixture of dynamic content and text to make sense for the supervisor receiving it e.g.
Add another new step. For this Action, we need to add a condition.
If the outcome of the approval is equal to approve, we want to send an email to the person who has requested it stating ‘Your request has been approved.’ Similarly, if the outcome is that the Leave request is not approved, we need to trigger an appropriate response to the originator.
Create the ‘No’ flow in the same way, then Save the flow and then click test with you performing the trigger. Then and click ‘Save and Test.’
Once saved, go back to the Power App form and press play. Fill out the form using active email addresses and submit. Confirm the success page appears and go back to the model-driven app to verify the test data has been successfully added.
Check to see if the approval email has been received, select approve, and click the submit button.
Finally check that the employee email was received and conclude that everything is working as expected.
You should now have a fully functioning Leave Request App.