This blog is a step-by-step walkthrough of building a fully functioning leave request app for any organization with a vacation, sick leave, military leave, bereavement leave, holidays, Jury Duty, and so on. This app will include a solution in the Common Data Service, a Model-Driven App, a Canvas App, and Power Automate. The Canvas App is for employees to submit a request. The Model-Driven App will be a back-office system used by admin or supervisors to check everyone’s requests. The Power Automate flow will trigger an approval email to the employee’s supervisor and an automated email to the employee once the supervisor has either approved or rejected the leave request.

The app’s flow will start with the employee using the Canvas App to submit a leave request form. Once that form is submitted, an email is sent to the supervisor, and, simultaneously, a new row is added to the Model-Driven App with the new leave request entry. Once the supervisor has approved or rejected the request, the employee will receive an email with the decision.

Pre-requisites

  1. A Microsoft PowerApps Trial Plan 2, this can be a Free Trial
  2. Ensure you are using an environment with a database

Common Data Service (CDS)

  1. Navigate to https://make.powerapps.com/
  2. Create a New Solution called “Leave Request Solution”
    1. Create a New Publisher called “Leave Request App”Create a New Publisher
  3. Select New > Entity name it “Leave Request Entity” and Enable Attachments
    Enable Attachments CDS
  4. Select Add Fields
    Add Fields DSC
  • Add Field: First name
    • Data Type: Single Line Text
    • Required
  • Add Field: Last name
    • Data Type: Single Line Text
    • Required
  • Add Field: Email
    • Data Type: Email
    • Required
  • Add Field: Supervisor Email
    • Data Type: Email
    • Required
  • Add Field: Start date
    • Data Type: Date Only
    • Required
  • Add Field: End date
    • Data Type: Date Only
    • Required
  • Add Field: Request Type
    • Data Type: Option Set
      • Create New: “Request Type”
        Request New Option
    • New Option: Vacation Leave
    • New Option: Sick Leave
    • New Option: Military Leave
    • New Option: Bereavement Leave
    • New Option: Jury Duty
  • Add Field: Work Items
    • Data Type: Multiline Text
    • Required: Recommended
  1. SAVE ENTITY
  2. Navigate to the Views tab
  3. Select Active Leave Request Entities
    Leave Request Entity
  • Right-click on Created On Column > Remove
  • Add Last Name by clicking the field on the left
  • Add Email by dragging the field to the editor
  • Add Start Date
  • Add End Date
    Add Start and End Date
  1. Once your View looks like the above, click Save, Publish, then Back
  2. Navigate to the Forms tab
    1. Click on the row with the Form type “Main”
      Form type “Main”
    2. Add all custom fields by clicking them directly on the left
    3. Select the section so that it is outlined in purple
    4. Click Formatting on the right, select 2 columns
      Formatting
  3. Once your Form looks like the above, select Save, Publish, then Back

Model-Driven App

  1. Navigate to the “Leave Request Solution”
  2. Select New > App > Model-Driven App
  3. Name it “Leave Request Back Office”
  4. Check “Use the existing solution to create the App”
  5. Select Next
  6. Select Solution: * “Leave Request Solution”
  7. Select Site Map to configure it
    Select Site Map to Configure

    1. Select the pencil next to New Subarea
    2. Select the “Leave Request Entity” from the dropdown on the right
      Leave Request Entry from dropdown
  8. Publish, and Save and Close
  9. In the App Designer view, select Publish then Play
  10. Select New
    1. Add test data
    2. Select Save & Close

Canvas App

  1. Navigate back to the “Leave Request Solution”
  2. Select New > App > Canvas App > Tablet form factor
  3. Select the Insert tab > Forms > Edit
  4. A box will appear in the editor, select Connect to data
    1. Select “Leave Request Entities” on the left
    2. Under Data source on the right, select “Leave Request Entities”
    3. Select Edit Fields
    4. Add the remaining fields in the popup modal
    5. Set Default mode to New
    6. Change name to “Leave Form”
      Change Name to Leave Form
  5. Navigate to Insert tab > Button
    1. Select OnSelect from dropdown
    2. Type “SubmitForm(‘Leave Form’);Navigate(Screen2);” in the functions(fx) box
      Functions box

      1. Select Text from dropdown and type “Submit Request”
  6. Navigate to Insert tab > New Screen > Success
  7. Navigate to the File tab > Save > Publish > Play
    Save and Publish, then play
  8. Add test data, select Submit, navigate back to the Model-Driven App to see the new entry

Power Automate

  1. Navigate back to the “Leave Request Solution”
  2. Select New > Flow
  3. In the search box type “When a record is created” and select the Common Data Service option
    Common Data Service Trigger Box
  4. Fill in the trigger box:
  5. Select New Step
  6. Then the Choose an Action box, type “Start and wait for an approval” in the search box and select it
  7. Fill in the action box using the Dynamic Content box
    1. Click inside the input field and then select the correct dynamic field from the Dynamic Content popup box
      Dynamic Content Box
  8. Select New Step
  9. Select Condition
  10. Fill in the boxes:
    Fill in the Boxes for the Trigger
  11. Select Save >Test > Check “I’ll perform the trigger action” > Select Save & Test
  12. Navigate to your Canvas App, select Play, add test data, and Submit Form
  13. You should see an approval email in the inbox you sent the supervisor email to, select Approve
    Approve Email
  14. Another email will be sent to the employee’s email with the response
    Approved Request

Final Thoughts

Congratulations! You have completed the Leave Request app using the Common Data Service, a Model-Driven App, a Canvas App, and Power Automate. You have a fully functioning application that can be used right away! The next steps would be to implement a status field where the Approve or Reject decision will be updated in the Model-Driven App so supervisors can keep track of any pending requests. This app can be adjusted to your organization’s needs and requirements, but this app is a great starting point.

INFOPATH FORM MODERNIZATION WITH POWER PLATFORM
Discover how we helped a Fortune 100 Insurance Company modernize their InfoPath forms with Power Platform.

Passing just about anything from Power Apps to Flow with the newly released JSON function

In this article, I will show you how we can send data from a Canvas App using the freshly released JSON function. I will pass data from the data table (of a SharePoint List), microphone (audio recording), and camera control (photo) to an MS Flow. A condition logic is set up in Flow to check the file type and create those accordingly in a dedicated SharePoint Library.

This article focuses on a canvas app and a flow. We will look at the component-wise structuring of both the app and the flow to achieve the objective.

Canvas App

Let’s look at the control-wise screens and functions used in the Canvas App.

  1. Data from a SharePoint list is displayed on a Gallery control in the app. A user can export this data to a PDF file and save it to SharePoint Document Library, and download it in the browser window.

Gallery Control

Here, we have a Gallery (‘Gallery2’) control that is populated with the data from a SharePoint List. The data is filtered to show only the first 10 records. The expression used on the ‘Items’ property of the Gallery control is:

FirstN(ShowColumns(OrderDets,"Title","Customer","ShippingAddress","BillingAddress"),10)

Explanation: Get the first 10 items from the ‘OrderDets’ SharePoint list and get the columns as specified.

The ‘Create PDF’ button creates a local collection and then triggers an MS Flow and passes the collection as an argument along with the desired file name using the JSON function. Finally, once the PDF is created and the Flow is executed successfully, the PDF file is opened in a new tab of the browser. The expression used on this button is:

ClearCollect(PDFCollection,Name:Concatenate("Test123",Text(Today()),".pdf"),Url:JSON(ShowColumns(Gallery2.AllItems,"Title","Customer","ShippingAddress","BillingAddress"))});Launch(CreateFilesSharePoint.Run(JSON(PDFCollection,IncludeBinaryData)).responsereturned)

Explanation: The ‘ClearCollect’ function creates a collection named ‘PDFCollection’ and this stores the data in the gallery control and the name of the PDF file. The name of the PDF file is a concatenated string with the naming convention of ‘Test123-today’s date.pdf’. The ‘URL’ key inside the ‘PDFCollection’ stores string type value for the table formatted Gallery items, using the JSON function. This value is later parsed as JSON while sending as an argument to the Flow. The ‘Launch’ function opens a new browser window to launch the newly created PDF file’s URL received as a response from the ‘CreateFilesSharePoint’ flow.

  1. The Microphone control on the app is used to record audio. Multiple recordings can be created and played/viewed on the gallery control.

Microphone Gallery Control

Here, we have a Microphone control ‘Microphone1’ to record the audio inputs and store that into a local collection ‘AudioCollection’. The Expression used on the ‘OnStop’ property of the Microphone control is:

Collect(AudioCollection{Name:Concatenate("Audio",Text(Today()),Text(CountRows(AudioCollection)),".mp3"),Url:Microphone1.Audio})

Explanation: The ‘Collect’ function updates a collection ‘AudioCollection’ to store the audio recordings with the unique file name. The filename is a concatenated string of ‘Audio-Today’s date-index of the audio file.mp3’.

The ‘Submit’ button triggers the Flow and creates all the audio recordings as separate files on the SharePoint document library. The Expression used on this button is:

CreateFilesSharePoint.Run(JSON(AudioCollection,JSONFormat.IncludeBinaryData))

Explanation: Here the JSON function converts the audio file URL to binary data and sends the ‘AudioCollection’ data to the ‘CreateFilesSharePoint’ flow.

The ‘Clear’ button clears data from the ‘AudioCollection’.

  1. The camera control is used to click photos in the canvas app. Multiple pictures can be captured and viewed on the Gallery control.

Camera Gallery Control

Here, we have a camera control ‘Camera1’ to capture a picture and store it into a local collection ‘ImageCollection’. The Expression used on the ‘OnSelect’ property of the Camera control is:

Collect(ImageCollection,{Name:Concatenate("Image",Text(Today()),"-",Text(CountRows(ImageCollection)),".jpg"),Url:Camera1.Photo})

Explanation: Collect function updates the ‘ImageCollection’ collection with the unique file name and the URL of the photo taken from the camera control. The name of the file is a concatenated string of ‘Image-Today’s Date-Index of the photo in the gallery control.jpg’.

The ‘Submit’ button triggers the Flow and creates all the images as separate files on the SharePoint document library. The Expression used on this button is:

CreateFilesSharePoint.Run(JSON(ImageCollection,JSONFormat.IncludeBinaryData))

Explanation: Here, the JSON function converts the image file URL to binary data and sends the ‘ImageCollection’ data to the ‘CreateFilesSharePoint’ flow.

The ‘Clear’ button clears data from the ‘ImageCollection’.

MS Flow

Coming to the ‘CreateFilesSharePoint’ flow: This flow is triggered by the button controls on the different screens in the Canvas App.

Action 1: Initialise a variable -> accommodates the input coming from the canvas app.

Action 2: Initialise a variable (2) -> To get the string to send a response back to the canvas app.

Action 3: Parse JSON: Get the dynamic data by parsing the data received from the canvas app according to the schema where we have an array that contains objects with the attributes: ‘Name – Filename’, ‘URL – Filecontent’.

Flow 1

Action 4: Apply to Each control: Iterate over each file item from the body output of the Parse JSON function.

Action 5: Condition control within the Apply to each Control: Split the file name and check if the extension is a PDF file.

If No,

Action 6: Create File 2 in SharePoint: to create a file for the image/ audio type in the defined library. If Yes,

Action 7: Parse JSON 2: The data content passed from the Power Apps as the URL key is now being parsed as individual elements to create an HTML table and then finally create a PDF file out of it.

Action 8: Create HTML Table: Creates an HTML table with the column names as headers and gets the data from the Parse JSON 2 action.

HTML Table from Parson JSON

Action 9: Create File in OneDrive: To create a temporary HTML file from the HTML table generated in the previous step and store it in the ‘Hello’ folder on the OneDrive.

Action 10: Convert File in OneDrive: To convert the previously created HTML file to a PDF document.

Action 11: Create File 2 in SharePoint: To create the PDF file from the converted file from the previous action. The file is stored in the specified document library on SharePoint.

Action 12: Delete File from OneDrive: To delete the temporary HTML file that was created in Action 9.

Action 13: Get file Properties SharePoint: To get the URL of the PDF file created in SharePoint.

Action 14: Set Variable: Set the URL to the file as a string value.

Create and Transform Files

Action 15: Respond to Power Apps: Send the URL of the file created on SharePoint to Power Apps. (Outside of the apply to each control)

Respond to PowerApps

In this blog, we have seen how we can use the JSON function to pass data from Power Apps to Flow. We were able to successfully send binary data (image files, audio recordings) and a gallery data table. We can also send collections, data directly from data sources with appropriate filters, etc. The attributes that can be sent via the JSON function does not support sending attachments, nested arrays/objects.

I hope you found this interesting and this helped you. Thank you for reading!