On 2018-09-18, I gave a presentation about the business solution “Investment requests” I created with: SharePoint Online (Modern team site), PnP PowerShell, Flow and PowerApps. The 45 minutes were over before I knew it. Several topics were not, or in lesser detail, presented. I therefor decided to start a blog post series specially for this.
The blog post series has the following topics:
- Introduction
- Executing Flow actions with service account permissions
- Permissions
- PowerApps
- And something more
Introduction
The amount of PowerApping was limited for this solution. An one screen app was created containing overview information. Also the task list has been updated with a custom form so employees (management) who are involved in the approval process have a good looking screen. Some basic logic was added too.
App
The app was created with the workflow account and shared with application management and the process owner. The app is added as a web part on a page which is only visible by application management and the process owner.
Below is the property OnVisible of the start screen.
UpdateContext({startScreenVisible: true});
UpdateContext({preloaderScreenVisible: false});
UpdateContext({count_pmplanta: 0});
UpdateContext({count_bcplanta: 0});
UpdateContext({count_pmplantb: 0});
UpdateContext({count_bcplantb: 0});
UpdateContext({count_pmplantc: 0});
UpdateContext({count_bcplantc: 0});
UpdateContext({count_fm: 0});
UpdateContext({count_do: 0});
UpdateContext({count_fd: 0});
UpdateContext({count_ceo: 0});
UpdateContext({count_cfo: 0})
The variable startScreenVisible is used to show/hide a group containing an rectangle icon spreading the whole screen and the button “Get information”. This way, one has to click on the button to show the overview information. The related page in SharePoint is shown faster and the employee can make the decision if (s)he indeed wants to get the overview information.
When the button is clicked, all processing is executed. While processing, a preloader screen is shown.
All data comes from the document library “Requests”. The maximum amount of records (2000) is not an issue for many years. With a maximum of 200 requests per year, the solution should work for at least 10 years. The no-/low-code of the property OnSelect of the button is shown below. First, all records are read from the document library. Transformations etc. are done in the app.
UpdateContext({preloaderScreenVisible: true});
UpdateContext({startScreenVisible: false});
ClearCollect(colMaster, Requests);
ClearCollect(colPMAssigned, Filter(colMaster, fafp1jtpm="Assigned"));
ClearCollect(colBCAssigned, Filter(colMaster, fafp1jtbc="Assigned"));
ClearCollect(colFMAssigned, Filter(colMaster, fafp1jtfm="Assigned"));
ClearCollect(colDOAssigned, Filter(colMaster, fafp1jtdo="Assigned"));
ClearCollect(colFDAssigned, Filter(colMaster, fafp1jtfd="Assigned"));
ClearCollect(colCEOAssigned, Filter(colMaster, fafp1jtceo="Assigned"));
ClearCollect(colCFOAssigned, Filter(colMaster, fafp1jtcfo="Assigned"));
ClearCollect(colPMPlantAAssigned, Filter(colPMAssigned, fafp1entity.Value="Plant ""A"""));
ClearCollect(colPMPlantBAssigned, Filter(colPMAssigned, fafp1entity.Value="Plant ""B"""));
ClearCollect(colPMPlantCAssigned, Filter(colPMAssigned, fafp1entity.Value="Plant ""C"""));
ClearCollect(colBCPlantAAssigned, Filter(colBCAssigned, fafp1entity.Value="Plant ""A"""));
ClearCollect(colBCPlantBAssigned, Filter(colBCAssigned, fafp1entity.Value="Plant ""B"""));
ClearCollect(colBCPlantCAssigned, Filter(colBCAssigned, fafp1entity.Value="Plant ""C"""));
UpdateContext({numberOfRequests: CountRows(colMaster)});
UpdateContext({count_pmplanta: CountRows(colPMPlantAAssigned)});
UpdateContext({count_bcplanta: CountRows(colBCPlantAAssigned)});
UpdateContext({count_pmplantb: CountRows(colPMPlantBAssigned)});
UpdateContext({count_bcplantb: CountRows(colBCPlantBAssigned)});
UpdateContext({count_pmplantc: CountRows(colPMPlantCAssigned)});
UpdateContext({count_bcplantc: CountRows(colBCPlantCAssigned)});
UpdateContext({count_fm: CountRows(colFMAssigned)});
UpdateContext({count_do: CountRows(colDOAssigned)});
UpdateContext({count_fd: CountRows(colFDAssigned)});
UpdateContext({count_ceo: CountRows(colCEOAssigned)});
UpdateContext({count_cfo: CountRows(colCFOAssigned)});
UpdateContext({numberOfRequests2017: CountRows(Filter(colMaster, Created >= Date(2017,1,1), Created < Date(2018,1,1)))});
UpdateContext({numberOfRequests2018: CountRows(Filter(colMaster, Created >= Date(2018,1,1), Created < Date(2019,1,1)))});
UpdateContext({numberOfRequests2019: CountRows(Filter(colMaster, Created >= Date(2019,1,1), Created < Date(2020,1,1)))});
UpdateContext({numberOfRequests2020: CountRows(Filter(colMaster, Created >= Date(2020,1,1), Created < Date(2021,1,1)))});
Clear(columnChart);
Collect(columnChart, {Year:2017, Value:numberOfRequests2017});
Collect(columnChart, {Year:2018, Value:numberOfRequests2018});
Collect(columnChart, {Year:2019, Value:numberOfRequests2019});
Collect(columnChart, {Year:2020, Value:numberOfRequests2020});
Clear(columnChart2);
Collect(columnChart2, {Entity:"Corp", Value:CountRows(Filter(colMaster, Created >= Date(2018,1,1), Created < Date(2019,1,1), fafp1entity.Value="Corporate"))});
Collect(columnChart2, {Entity:"A", Value:CountRows(Filter(colMaster, Created >= Date(2018,1,1), Created < Date(2019,1,1), fafp1entity.Value="Plant ""A"""))});
Collect(columnChart2, {Entity:"B", Value:CountRows(Filter(colMaster, Created >= Date(2018,1,1), Created < Date(2019,1,1), fafp1entity.Value="Plant ""B"""))});
Collect(columnChart2, {Entity:"C", Value:CountRows(Filter(colMaster, Created >= Date(2018,1,1), Created < Date(2019,1,1), fafp1entity.Value="Plant ""C"""))});
Clear(columnChart3);
Collect(columnChart3, {Entity:"Corp", Value:CountRows(Filter(colMaster, Created >= Date(2018,1,1), Created < Date(2019,1,1), fafp1sourcelibrary="corporate/"))});
Collect(columnChart3, {Entity:"A", Value:CountRows(Filter(colMaster, Created >= Date(2018,1,1), Created < Date(2019,1,1), fafp1sourcelibrary="planta/"))});
Collect(columnChart3, {Entity:"B", Value:CountRows(Filter(colMaster, Created >= Date(2018,1,1), Created < Date(2019,1,1), fafp1sourcelibrary="plantb/"))});
Collect(columnChart3, {Entity:"C", Value:CountRows(Filter(colMaster, Created >= Date(2018,1,1), Created < Date(2019,1,1), fafp1sourcelibrary="plantc/"))});
Collect(columnChart3, {Entity:"Proj", Value:CountRows(Filter(colMaster, Created >= Date(2018,1,1), Created < Date(2019,1,1), fafp1sourcelibrary="projects/"))});
UpdateContext({preloaderScreenVisible: false})
Several collection are generated. When all processing is done, the preloader screen is hidden and the screen with the overview information becomes visible.
On the overview page, the following information is shown:
- The active requests for all roles.
- A graph showing the number of requests per year.
- The number of requests per entity for the year 2018.
- The number of requests per team for the year 2018.
Task list form
Below, a screenshot of the form is shown.
The form has been optimized so only useful fields are shown. The first three fields were set to read-only. The background was changed so it fits with the theme of the site. Also, when “Decline” is chosen, the field “Comment” becomes required.
The task list form is shown in edit mode when an approver clicks on the related link in the email (s)he got when a task was assigned to him/her.