Sharepoint Saturday Charlotte

1. Implementing a Panel-based Approval Application in Flow and PowerApps Lori ValoneTools & Technology Architect /TeradataDoggone Good Consulting Sharepoint Saturday Charlotte

2. Diamond Platinum Gold Silver

3. Speaker Survey and Raffle • This year we are replacing the paper version of Speaker Survey with Microsoft Forms. • Scan the QR code for each session to access and submit your survey. • QR codes can be found in the program guide or on the room sign located by the door. • You will receive ONE raffle ticket for each session survey you complete. • The raffle ticket volunteer will validate your First and Last name before providing your ticket(s) • Drawing will be held this afternoon 4:30-5:00 pm. • Must be present to win. You can download and use the QR Reader app available for both iOS and Android

4. Introduction • Tools & Technology Architect for Teradata’s Consulting Program Office • Pack Leader at Doggone Good Consulting • Develop solutions using PowerApps, Flow, Power BI and Teams • Microsoft PowerApps Community SuperUserand just nominated for Microsoft MVP • Live and work on a mountaintop in Western North Carolina www.DoggoneGoodConsulting.com PowerUsers Community: LRVinNC Twitter: lvalone

5. Agenda • Business Process • Architecture • Business • Information • Application • Components • Outlook • Flow • PowerApps • Teams • Takeaways & Questions

6. Business Process

7. Business Process • Internal Consulting teams submit proposals to compete for project funding • An investment governance process manages the funding approval process • Multi-member leadership panel reviews and votes on each proposal • Monthly go/no-go meeting reviews active and recently finalized submissions

8. Architecture

9. Business Architecture Consulting leadership considers project proposals Proposal submitted for funding review Team develops project proposal Go / no-go decision rendered on each proposal

10. Information Architecture Team drafts project proposal Approval request sentto panel Proposal emailed toshared mailbox Proposal logged to data store Outcome logged to data store Email sent to proposal originator Proposal data provided made available for review

11. Application Architecture Desktop application Word Flow /Sharepoint Flow /Sharepoint Flow /Outlook Outlook Flow /Outlook Team drafts project proposal Approval request sentto panel Proposal emailed toshared mailbox Proposal logged to data store Outcome logged to data store Email sent to proposal originator PowerApps Proposal data made available for review

12. Components

13. Outlook 1 Shared mailbox gathers proposals Set up shared mailbox to gather proposals which are to be submitted in a standard format and with a standard subject Set up folder within the shared mailbox to hold the proposals – this is the folder that Flow will look at Set up a rule to move submissions to this folder upon arrival 2 3

14. Flow 3 flows provide core processing PRIMARY

15. Flow Initialize variables Primary flow Log and store proposal Investment Governance – Primary Flow • Flow manages the primary approval mechanism • Assigns a proposal ID • Validates a proposal document in Word format was submitted • Sends an acknowledgement of receipt to the submitter • Sends approval requests to each member of the approver panel • Records the accept/reject response as well as any comments provided by the approver • Sends a reminder after 5 days to any approver who has not already responded Sendnotifications Overview Request approvals Record approval responses

16. Flow Initialize Flow • Trigger is receipt of a new email in the designated folder in shared mailbox • Retrieve last proposal key value used, increment by 1 and update, resolving save conflict if necessary • Initialize all necessary variables including the proposal name, approve and reject counters, etc. Investment Governance – Primary Flow

17. Flow Log and Store Proposal Investment Governance – Primary Flow • Process the attachments to the email, looking specifically for a Word document • If no Word document is found, send the user an email advising them to resubmit according to the correct procedure LEARNINGS • Users directed to submit a single proposal per email as a Word document but we can’t count on that occurring • Email signatures sometimes contain graphics which get treated as attachments and therefore must be accounted for

18. Flow • If the document name does not include “Proposal Document”, just save a copy to Sharepoint • If the user submits more than one proposal document, accept the first but send them an email notifying them they will need to resubmit the additional proposal(s) • Store a copy of the proposal in the Sharepoint library • Log the submission to the Sharepoint list • Save the ID and proposal name for use later in the flow Log and Store Proposal – Word Document Investment Governance – Primary Flow @or(contains(items('Process_each_attachment')?['Name'], 'Proposal Document'),contains(items('Process_each_attachment')?['Name'], 'Proposal_Document'))

19. Flow Send Notifications • If a Proposal Document was included, send email to user letting them know their submission has been received and provide them the proposal ID and name • If no Proposal Document was found, notify the user they will need to resubmit using the correct submission protocol Investment Governance – Primary Flow

20. Flow Request Approvals • Identical parallel streams started for each panel members • Entry added to a Sharepoint list to log current approval status so it can be monitored by PowerApps • Approvers are “Everyone must approve” because we want to receive feedback from all panelists • Markdown used in approval message for formatting • Approval includes link to the Sharepoint library as well as a direct link to the proposal Types of approvals Investment Governance – Primary Flow

21. Flow Request Approvals – 5 day reminder • Also included as a parallel stream is a reminder stream on a 5 day timer • Once timer expires, Approval Status of each of the panel members is checked • If the response is still pending, a reminder email with a link is sent to the panel member. Investment Governance – Primary Flow

22. Flow Record approval responses • Update Approval Status SP list to indicate the approval is complete for this panel member • Get a current copy of the approval record to ensure the latest approve/reject counts are available • Load the current approve count into the ApproveCtr variable, then increment • Update approval record with count, and if this is the first approval to go out, also change the submission status from “Submitted” to “In Review” • Log approval comments for the panel member • Reject logic mirrors the process for an approval Investment Governance – Primary Flow

23. Flow Reporting flows Daily Report of Funded CAD Proposals Daily Report of Active CAD Proposals Scheduled daily reports via email

24. Flow Email report on one or more proposals PowerApps-invoked flows • Report flows format and print data for 1 or more proposals selected in PowerApps • ClearPendingApprovals updates the status on all remaining unapproved records to reflect a decision already made for a proposal

25. PowerApps Visibility to complete proposal catalog Displays gallery of all proposals with switch to include archived proposals, if desired Selecting a proposal displays details including Submission Status, current approve/reject count and reviewer comments Edit the selected proposal to update Funding status, flag to request additional information, and log when it will go to steering team review – invokes ClearPendingApprovals flow when saved

26. PowerApps Generate emailed reports Ability to request reports on a single proposal, all proposals or based on criteria (shown at right) Invokes one of three flows to carry out the request and send results to email

27. Teams Provides ease of access to application • Added as a tab in the relevant channel • Be sure to grant access to all channel users who should have access to the app

28. In Closing…

29. #SPSCLT19 Speaker Survey Session 1

30. Takeaways and Questions • Use Flow-based Approvals to record votes and feedback for review panels • Use PowerApps to provide visibility to process status during and after the review process • Use a shared mailbox to provide both inbound and outbound communications related to proposals • Teams can provide ready access to a community of users Questions?

31. Thank You! PowerUsers Community: LRVinNC Twitter: lvaloneWebsite: www.DoggoneGoodConsulting.com