How We Modernized a Legacy App using Power Platform

In May, AIS held an internal hackathon for Microsoft Power Platform to expose our team to the platform, concepts, approaches through hands-on experience and to demonstrate the role Power Platform plays in modernizing legacy applications in the cloud.

The Microsoft Power Platform Hackathon was an opportunity for our enthusiastic team to modernize a legacy e-commerce (E-shop) application using Microsoft Power Platform. The legacy application, the deployment of eShopOnWeb, helped users find a product of interest by browsing and filtering. Users could also add products to their cart and checkout. The app also provided an interface for administrators to add, update, or delete products from the catalog.

A new system was developed to replace the legacy e-commerce application with the complete feature parity. The solution included a Power Apps Portal with the same “look and feel” functionality. We used Dataverse as the persistent layer instead of SQL server and integrated it with new communication methods such as sending emails and text messages to users. Additionally, we used a Web API to communicate with Legacy Reporting Systems using Power Platform Connectors, providing secure access to the new system using Azure Active Directory B2C.

In addition, the team explored ways to backup and source control the solution and automate the deployment from one environment to another. The diagram below represents the architecture of our final solution.

Final Power Platform Blog

It is named ‘Work Less, Do More,’ Power Platform replaces the work that might take many days or months to a few hours. So let’s dive in and learn how we arranged all these pieces and modernized the legacy application using Microsoft Power Platform.

JOIN OUR GROWING TEAM
AIS provides employees with opportunities to learn and grow in their careers. Won't you join us?

Technical Approach

The application was divided into different components, which were developed by individual teams. All the components were then pulled together to provide the complete Power app solution. In this section, we are going to highlight these components and how they were implemented in our solution:

The front-end team focused on building the Power Apps Portal for the end-users and a model-driven app for the administrators. The Portal allowed the users to browse through the product catalog, add an item to the cart, place an order, view their past orders, and manage their profile. The model-driven app allowed administrators to manage the product catalog just like the legacy application. The team used Portals Web API to fetch data from Dataverse and used Liquid templates for web pages.

The data team focused mainly on using Microsoft Power Platform Dataverse as the persistent layer for both the Portal and the admin app. They also migrated schema and data from legacy datastore to Dataverse by exploring various techniques, including Dataflows, CSV imports, and custom code.

The integration team focussed on leveraging existing Power Platform connectors to add new functionality to the system. For example, the system sends the order confirmation email to the user using the Office Outlook connector in the Power Automate Flow. Similarly, it sends text messages to users through the Twilio Connector. The team also leveraged SQL Server Connector for data sync so that the legacy reporting systems remained unaffected.

The DevOps team automated the portal deployment process using Power DevOps Tools and deployed the solution across three environments (dev, test, prod). Since Microsoft Power Platform does not support source control and versioning, the team used Azure DevOps as the solution repository and version control.

The identity team focused on providing secure access to the Portal to a different set of users. The team used Azure AD B2C to decouple identity and access management from the Portal application.

Stay tuned! We will be publishing a blog for each team for a deeper dive into their individual focuses for this hackathon.

Lessons Learned

Ultimately this hackathon proved that Power Platform is a great app modernization solution for the following reasons.

  • We can use Portal as a modern low code alternative to create websites and interact with data in Dataverse.
  • Model-driven apps provide a rich no-code design environment to create applications and share quickly.
  • We can quickly build secure apps using connectors.
  • Innovate and improve business, as these connectors are easily customizable, and end-users can easily change or create the content for Email or SMS Templates.
  • With the help of Power Platform build tools, we can quickly deploy the solution into various environments. Increase the release frequency.

MEETING NEEDS QUICKLY WITH POWER PLATFORM
AIS architected, developed, and deployed a secure global health solutions management application and digital marketplace built on Power Platform.

Recommended Reads

Prototyping in Axure vs. Power Apps

As I described in part one of this blog series, prototyping was an integral step of our user-centered design/discovery process. When we first started on the project, the UX Architect and I used Axure, a well-known prototyping tool familiar to us. However, as the project progressed, we moved into prototyping in Power Apps directly.

Several reasons led to such change:

  • Our client’s security policy required that we keep the prototypes in their tenant.
  • Axure is a third-party tool.
  • When developers built the new solution in Power Apps, they could not reuse any Axure elements, such as the interaction that we built with Axure’s dynamic panels. While they could inspect the Axure prototype about spacing, color, or padding, they had to re-create all design elements in Power Apps.
  • Some Axure controls or design elements were difficult to replicate in Power Apps due to formatting and accessibility limitations, which created inconsistencies between the prototype that stakeholders saw and the final solution.
  • Using Axure also incurred an additional licensing cost.

The table below lists some pros and cons between Axure and Power Apps.

Axure vs. Power Platform

As shown in the table above, prototyping in Power Apps presented significant advantages over Axure. Not only were we able to keep all content within the same tenant, but also our Developers could reuse some prototype code and design elements when they started development, such as directly copying form controls and functions from the prototype.

In addition, as the time of writing this blog, the project team has been developing a Power Apps Component Library, with the consistent design of form elements, which will further streamline code and element reuse in the future. In addition, the project team has been mentoring client employees from several business units in their InfoPath form from the modernization process. The Component Library will serve as a great tool to help these people, who may not have any UX or user interface design knowledge, follow UX design best practices.

Prototyping in Power Apps did pose some challenges because it always takes time to learn something new. However, I was very motivated and started to prototype in Power Apps as soon as I had the opportunity. I saw how our Developers quickly added, removed, or modified form elements when we worked together on some app requirements. I also had a glimpse of the Power Platform capabilities when I attended a workshop two years ago and knew it was citizen developer-friendly. (See my previous blog: Microsoft Business Applications: A UX Researcher’s Perspective).

My prototyping experience in Power Apps proved to be very positive, and I felt empowered by the Power Platform. Here’s why:

  • I received a lot of help from my Developer colleagues, especially Stephanie Zaloga (LinkedIn). She created a template for me based on a canvas app she developed, which included many frequently used form elements and controls, pre-formatted with the appropriate fill, border color, hover, and font variations. I could easily reuse them in my prototype without having to go through the tedious formatting process. (The Component Library in development will further help.)
  • I focused on improving form instructions, labels, and controls, based on form design and plain language writing best practices, which I was familiar and comfortable with.
  • I was able to prototype cascading data fields and simple interactions, usually crucial to meet client’s business needs, with just a few essential functions in Power Apps, such as Switch or If. During development, our Developers could copy and paste these correctly formatted controls, such as dropdown fields, text inputs, and HTML text/instructions, directly to the final solution, which sped up their overall app development time.
  • I did not create any back-end data for my prototype, which would require more Developer skills.
  • I found Microsoft’s online resources and support from the Power Apps community extremely helpful. It was comforting to start a search and see results highlighting a particular problem solved.

Extensive and Helpful Microsoft Resources

Form Design and Content Development Best Practices

When I prototyped the new solution based on the existing InfoPath forms, I always considered form design best practices and tried to improve form usability whenever possible. When we met with stakeholders to get their approval of the prototype and requirements, we would point out such improvements in the prototype, making sure that they were aware and comfortable with our recommendations.

Some of the form design best practices that we implemented included:

  • Follow digital content development best practices for instructions and labels
    • Rewrite content to be more usable and accessible, including removing all those Click Here or Here standalone links
    • Shorten or eliminate form instructions whenever possible
    • Use numbered lists for sequential steps and bulleted lists to support user scan, instead of long paragraphs
    • Spell out acronyms when they are used the very first time
    • Consistently use words and phrases and eliminate inconsistencies, such as up-grade vs. upgrade
  • Create new form sections with clear section labels, if needed, and logically group similar questions together
  • Ensure a clear visual distinction between primary and secondary action buttons, such as Save and Go Back, and align primary actions with input fields/user flow
  • Never use colors alone to convey information to meet accessibility guidelines

Dropdown lists were common form components. For app consistency, at the beginning of the project, our team agreed that:

  • Higher numbers or more favorable options should be placed at the top of all options instead of the bottom. This is to support natural conceptual mapping. For example: With dropdown options showing risk, we would display the options as:
    • 5 – High Risk (on the top)
    • 4 – Medium to High Risk
    • 3 – Medium Risk
    • 2 – Low Risk
    • 1 – No Risk (at the bottom)
  • Options should be displayed in a certain order: alphabetically or by priority/frequency.
  • Radio buttons should be used for two to three options instead of a dropdown.
    • Radio buttons should always have a default value when used.
    • The default value will be displayed first.

Project Updates and More Information

Our project that lasted from August 2020 to this February was a success, which led to two additional projects starting within the same insurance company. Since February, I have been working in a project team that requires Microsoft Dataverse and model-driven apps to modernize InfoPath forms related to an important product. In this project, I function as a BA, UX Researcher, and Organizational Change Manager. This has presented exciting new learning opportunities, as well as challenges. I may write another blog post depicting my experience afterward.

To learn more:

This video demo and blog provide a step-by-step walkthrough of adding related subgrids to a Power Apps portal for a Trip Planner Application.

You may consider adding a related subgrid to your portal if you have tables associated with each other. For example, you have a Trip table that is your main table and a Traveler table. You want your traveler table to be directly related to your trip table so that any data you add to your traveler table for a specific trip will be added to that trip and not all.

The steps to achieving this include creating the relationship between tables, modifying the forms and views, adding the related subgrid to the main Trip form, and configuring the Portal Management App and portal designer.

I have created a Trip Planner application where Trip is the main table and Traveler is the related table in this walkthrough. By the end, we will have one Trip to many travelers and ensure the travelers for one Trip don’t get added to a different trip.

Traveler Subgrid Example

This example image is what the Traveler subgrid will look like inside the beach trip.

Steps:

  1. In your solution, create a Trip table with custom columns
  2. Create a Lodging Table
  3. Create a 1:M relationship between Trip and Travelers
    One to Many from Related table
  4. Customize the view for both Trip and Traveler
  5. Add a new Main form inside the Trip table
    1. Add custom columns by dragging or clicking it from the left panel
    2. Add a related subgrid in the same tab
      Component LayoutNew Trip Layout
    3. Save and Publish the form, Click Back
  6. Navigate to the Portal Management App
    1. Create 2 new Entity Forms
    2. One for “New Traveler”
    3. One for “Update Traveler”
      New Traveler UpdateUpdate Traveler Image Edit and Record Source Type
  7. Create a new entity form called “Update Main Form”
    Select New Entity Step

    1. Navigate to Entity Form Metadata tab, click New Entity Form Metadata
  8. Select Subgrid from Type
  9. Select the correct subgrid from the drop-down
  10. Select the New Traveler form for Create
  11. Select Update Traveler form for all other actions
    Update Traveler and Create Entity
  12. Create an Entity form called “New Main Form” and follow the same steps for the subgrid configuration but ensure the Mode is Insert in the General tab
  13. Your Entity Forms should look like this
    Active Entity Forms
  14. Navigate to Entity Lists inside the Portal Management App and create a new list called “Main List”
    Main Entity List
  15. Under the Options tab, Add Main Form to Grid ConfigurationAdd Main List Entity to Configuration
  16. Navigate to the Portal Designer editor
    Navigate Trip Planner portal
  17. Add a List from Components
    Add list from components
  18. Select Use Existing, since we already created an Entity List in the Portal Management app
    1. Select Main List
      Update existing list
  19. Click Sync Configuration, then Browse Website
  20. You’ll see your Trip view and you’ll be able to Create New or Update an existing Trip
  21. Notice your related Traveler subgrid is added to both forms for editing
    Related Traveler Subgrid for editing 1
    Viewing Trip Details

As you can see, adding a related subgrid to your Power Apps Portal can be done out of the box with no code needed! The purpose of this is to be able to link multiple tables to each other and have them displayed in Power Apps Portals. Feel free to follow along using my instructional video for more detailed instructions.

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.

Leveraging the Power Platform and Microsoft Azure to connect housing agencies and intermediaries with the Department of Housing and Urban Development (HUD)

Intro

The US Department of Housing and Urban Development runs a program known as the Housing Counseling Program that aids housing agencies around the nation through things like grants and training. These agencies provide tremendous benefit to Americans in many forms including credit counseling, foreclosure prevention, predatory lending awareness, homelessness, and more. One of the requirements for approval into the program is that the agency uses a software system that is also approved by HUD and interfaces with HUD’s Agency Reporting Module SOAP web service.

Original System

The original system is built on Dynamics 365 Online using custom entities, web resources, and plug-ins. Complex batching functionality was implemented to deal with the two-minute timeout period that plug-ins have, web resource files (JavaScript, HTML, and CSS) were stuck in a solution with no real structure, and application lifecycle management (ALM) was extremely painful. The original system design also doesn’t account for the intermediary-to-agency relationship, specifically the roll-up reporting and auditing that intermediaries desperately need for providing their compiled agency data to HUD, as well as their own internal reporting. Finally, because the solution was built using Dynamics 365 Online Customer Engagement, licensing costs were more than double what we knew they could be with Microsoft’s new Power Apps licenses and Azure pay-as-you-go subscriptions.

The Original Diagram

Figure 1 – current CMS system build on Dynamics 365 Online

Definition

Intermediaries – organizations that operate a network of housing counseling agencies, providing critical support services to said agencies including training, pass-through funding, and technical assistance.

Modern System

Whereas the data schema for the solution remains mostly unchanged, the architecture of the system has changed profoundly. Let’s check out some of the bigger changes.

Power Apps Component Framework (PCF)

The Power Apps Component Framework enables developers to create code components that can run on model-driven and canvas apps and can be used on forms, dashboards, and views. Unlike traditional web resources, PCF controls are rendered in the same context and at the same time as any other component on a form. A major draw for developers is that PCF controls are created using TypeScript and tools like Visual Studio, and Visual Studio Code. In the modern solution, web resources are replaced with PCF controls that make calls directly to the Power Apps Web API.

Azure Resources (API Management, Function Apps, Logic Apps, and a Custom Connector)

A Custom Connector and Azure API Management are used to manage and secure various APIs that are used in the system. The connector can be used in any Azure Logic App to make connecting to an API much easier than having to configure HTTP actions. All of the business logic that once lived in plug-ins has been replaced with a combination of Azure Logic Apps and Azure Function Apps. This has provided incredible gains where development and testing are concerned, but it also provides a more secure and scalable model to support these agencies as they grow. Lastly, it removes the burden experienced with the two-minute time-out limitation that plug-ins have.

ALM with Azure DevOps Services and Power Apps Build Tools

Azure DevOps Services, coupled with the Power Apps Build Tools, are being used to ease the pain we experienced with ALM prior to these tools being available. Now we can easily move our Power App solutions across environments (e.g. dev, test, production) and ensure our latest solution code and configurations are always captured in source control.

ALM with Azure

Figure 2 – modern solution using Power Apps and Azure resources for extreme scalability and maintainability

Next Steps

I hope by demonstrating this use case you’re inspired to contact your Microsoft partner, or better yet contact us and let us work with you to identify your organization’s workloads and technology modernization opportunities.

Ready to dig a little deeper into the technologies used in this blog post? Below we have some hands-on labs for you. Stay tuned for more updates!

  1. Power Apps Component Framework Controls
    As a developer, PCF Controls has been one of the most exciting things to grace the Power Platform landscape. In this video, I’ll show you how to go from zero to a simple PCF control that calls the Power Platform Web API. I also provide a Pro Tip or two on how I organize my PCF controls in my development environment.

  2. Using an Azure Logic App to Query Data in an On-Premises SQL Server Database
    Companies are embracing modernization efforts across the organization, quickly replacing legacy apps with Power Apps and Azure resources. This can’t all happen at once though, and often times companies either can’t or won’t move certain data to the cloud. In this hands-on lab, I’ll walk you through using an Azure Logic App and an On-Premises Data Gateway to connect to a local SQL Server 2017 database to query customer data using an HTTP request and response.
  3. Azure API Management and Custom Connectors
    API’s give you the ability to better secure and manage your application interfaces. Couple an API with a Custom Connector and you not only better secure and manage, but also make it super easy for other app developers across the organization to connect to your back-end resources through the API, without having to worry about all of the API configurations.
  4. Power Apps DevOps Using Best Practices
    There’s more to DevOps than just backing up your Power App solutions in source control and automating your build and release pipeline. Good solution management and best practices around publishers, entity metadata, components, and subcomponents are also key to manageability and scalability with your ALM.
This blog post is for all developers of all levels that are looking for ways to improve efficiency and time-saving ideas. It begins by providing some background on me and how my experience with Microsoft Excel has evolved and aided me as a developer. Next, we cover a scenario where Excel can be leveraged to save time. Finally, we go over a step-by-step example using Excel to solve the problem.

Background

As a teenager growing up in the 80s, I was fortunate enough to have access to a computer. One of my favorite applications to use as a kid was Microsoft Excel. With Excel, I was able to create a budget and a paycheck calculator to determine my meager earnings from my fast food job. As my career grew into software development, leveraging all of the tools at my disposal as a solution against repetitive and mundane tasks made me more efficient. Over the years, colleagues have seen solutions I have used and have asked me to share how I came up with and implemented them. In this two-part blog post, I will share the techniques that I have used to generate C#, XML, JSON, and more. I will use data-loading in Microsoft Power Apps and Dynamics as a real-word example; however, we will need to start with the basics.

The Basics

Before going into the data-loading example, I wanted to provide a very simple example. Keep in mind that there may be more effective solutions to this specific example that do not use Excel; however, I am using it to illustrate this simple example. Let’s say you had a data model and a contact model that, for the most part, were the same with the exception of some property names, and you needed to write methods to map them. You know the drill:

var contact = new Contact();
contact.FirstName = datamodel.firstName;
contact.LastName = datamodel.lastName;
contact.PhoneNumber = datamodel.phoneNumber;
contact.CellPhone = datamodel.mobileNumber;

Not a big deal, right? Now let’s say you have a hundred of these to do and each model may possibly have 50+ properties! This would very quickly turn into a time consuming and mundane task; not to mention you would likely make a typo along the way that another developer would be sure to let you know about in the next code review. Let us see how Excel could help in this situation.

In this scenario, the first thing you will need is the row data for the contact and data models. One way would be using the properties. Consider the classes below:

Use Properties to Identify Classes

  1. Create 3 Excel worksheets called Primary, Secondary, and Generator
  2. Copy/paste the property statements from Contact into Primary worksheet and ContactDataModel into a Secondary worksheet.
  3. Select Column A in the Primary worksheet
    Create three Excel Worksheets
  4. In Excel, select the Data tab and then Text to Columns
  5. Choose Delimited, then Next
    Choose Delimited
  6. Uncheck all boxes and then check the Space checkbox, then Finish
    Uncheck All Boxes
  7. Your worksheet should look like the following:
    Sample of Worksheet
  8. Repeat 3-7 with the Secondary worksheet
  9. Select cell A1 and then press the = key
  10. Select the Primary worksheet and then cell D1
  11. Press the Enter key, you should return to the Generator worksheet and the text “FirstName” should be in cell A1
  12. Select cell B1 and then press the = key
  13. Select the Secondary worksheet and then cell D1
  14. Press the Enter key, you should return to the Generator worksheet and the text “firstName” should be in cell A1
  15. Drag and select A1:B1. Click the little square in the lower-right corner of your selection and drag it down to row 25 or so. (Note: you would need to keep dragging these cells down is you added more classes.)
    You will notice that by dragging the cells down, it incremented the rows in the formula.
    Incremented Rows in the Formula
    Press CTRL+~ to switch back to values.
  16. Select cell C1 and enter the following formula:
    =IF(A1=0,””,A1 & “=” &B1&”;”)
    As a developer, you probably already understand this, but the if statement is checking to see if A1 has a value of 0 and simply returns an empty string if so. Otherwise, string concatenation is built.
  17. Similar to an earlier step, select cell C1 and drag the formula down to row 25. Your worksheet should look like:
    Select and Drag Formula
  18. You can now copy/paste the values in column C into the code:
    Copy and Paste Values into Column C

As you continue on, Excel keeps track of the most recent Text to Columns settings used; so, if you pasted another set into the Primary and Secondary worksheets, you should be able to skip steps 1-5 for remaining classes. In the sample class file and workbook, I have included Address models as an illustration.

Next Steps

This example has covered the basic concepts of code generation with Microsoft Excel: extracting your data and writing the formulas that generate the necessary code. Depending on what you are trying to accomplish, these requirements may grow in complexity. Be sure to consider the time investment and payoff of using code generation and use where it makes sense. One such investment that has paid off for me is data loading in Microsoft Power Apps which we will cover in the next post: Code Generation with Microsoft Excel: A data-loading exercise in Microsoft Power Apps.

Download Example Workbook

Download Address Models

While personnel management is a sub-category of Human Resources (HR) that only focuses on administration, the tasks and responsibilities can outstretch the duties of an HR manager. Personnel managers hold an important role by focusing on hiring and developing employees to become more valuable to the company.  

A few of these areas of interest include: 

  • Job analyses
  • Strategic personnel planning
  • Performance appraisals
  • Benefit coordination
  • Recruitment
  • Screening
  • New employee orientation
  • Training
  • Wages
  • Dispute resolution
  • Other record-keeping duties

Power Apps and Personnel Management 

Now I bet you’re thinking how this could tie in with Power Apps. With the various areas that a personnel manager can be involved in, doesn’t it make sense to have one application where everything exists? So that this busy personnel manager can easily navigate and participate in day-to-day duties with ease, get the job done more efficiently, and have it readily available for other team members to view and analyze.

How bizarre would it be if I told you we could build this application with little to no code and have it ready to be used in less than half the time it would take for a developer to code it from scratch? Not bizarre and very doable. With Power Apps, one can quickly build custom business applications that connect to your business data stored either in the data platform, Common Data Service for Apps, or in various online and on-premise data sources like Azure, SharePoint, Excel, Office 365, Dynamics, SQL Server, and so on.  

Why Power Apps?

Apps that are built using Power Apps transform your manual business processes to digital, automated processes. Even more good news – these apps will have a responsive design and can run on any browser or your mobile device. Power Apps will potentially alleviate the need to hire expensive custom developers and this will give you the power and tools necessary to move your business forward.

Let’s Take a Closer Look

If a personnel manager is doing the following, this is how Power Apps can be integrated:

Personnel Management Duty: Posting job ads, reviewing resumes, conducting interviews and making a final decision with management.

Power Apps Solution: This can be done through the Business Process Flow. As you can see with the example below, you will be able to ensure that users enter data consistently and are taken through the same steps every time they work through this type of process.

Stages in Business Process Flow

Personnel Management Duty: Analyze salary data and reports to determine competitive compensation rates.

Power Apps Solution: Power BI, a modern data visualization tool that can spot trends in real-time and make better, more informed decisions based on your specified dataset. This example below depicts the various ways to display data using custom visualizations. Imagine the possibilities!

Sales Dashboard

Personnel Management Duty: Develop and maintain a human resources system that meets the company’s information needs.

Power Apps Solution: Using Dynamics 365, an app within Power Apps. Through the unified interface, your organization will have an application that is easy to use with the flexibility to grow.

 Personnel Management Duty

Personnel Management Duty: Continually monitor changing laws, legislation movements, arbitration decisions and collective bargaining contracts.

Power Apps Solution: Dashboard management that Dynamics 365 offers can easily check for recent changes within your system.

Sales Activity Dashboard

Personnel Management Duty: Continually deliver presentations to management and executives regarding current and future human resources policies and practices.

Power Apps Solution: Use the Power Apps Unified Interface to present detailed reports, dashboards, and forms. You’ll be able to demonstrate the versatility of the application on various devices.

 Customizing Applications

Power Apps not only gives you the capability to drive your business growth but it also eases your mind on the ability to change, update, delete, and customize your application as you see fit. Personnel management is not a simple feat but, using Power Apps can make your mission more manageable while also keeping everything in one place.

How to use galleries to create dynamic entries in a data source in Power Apps

In this article, we will see how we can use galleries in Power Apps to create multiple rows for adding records to a data source. We will create dynamic entries in a gallery that looks like a form and adds/deletes a line/row with the press of a button.

Scenario: XYZ Inc. is a sales company that deals in sales of hardware components from manufacturers to retailers. User A is an on-field sales agent of XYZ Inc. and uses a static application to enter the order details from a customer. This application is further connected to a SharePoint list and creates a new item on the list whenever User A enters the detail and hits the submit button. The application provides the ability to enter only one order detail at a time and User A ends up putting more effort and time in entering those details.

We designed a customized Power App for XYZ Inc. where User A authenticates and lands on the Order Details page. User A can view all their previous entries, search for an order by entering the name of the customer, vendor, invoice number, etc. Functionality to add details is provided within the app. User A clicks the add new orders button and a form gallery is displayed. User A can add multiple records by creating new lines with the press of a button in the form gallery. A local collection with all the entries on the form is created in Power Apps. Once User A hits the “Finish & Save” button, an item for each entry is created on the SharePoint List and the Order Details gallery is updated with these newly added records.

Let’s look at the component-wise description of the controls in the app. The schema for data on the SharePoint List is:

S.No Column Name Column Data Type
1 Title (Order Number) Single Line of Text (255 Chars)
2 Customer Single Line of Text (255 Chars)
3 Shipping Address Single Line of Text (255 Chars)
4 Billing Address Single Line of Text (255 Chars)

On the App -> OnStart option, the expression used is:

ClearCollect(DynamicGallery,{Value:1}); Clear(OrderCollection); Collect(OrderCollection,Filter(OrderDets,StartsWith(Title,"Order1"))); Collect(OrderCollection,Filter(OrderDets,StartsWith(Title,"Order2"))); Collect(OrderCollection,Filter(OrderDets,StartsWith(Title,"Order3"))); Collect(OrderCollection,Filter(OrderDets,StartsWith(Title,"Order4"))); Collect(OrderCollection,Filter(OrderDets,StartsWith(Title,"Order5"))); Collect(OrderCollection,Filter(OrderDets,StartsWith(Title,"Order6"))); Collect(OrderCollection,Filter(OrderDets,StartsWith(Title,"Order7"))); Collect(OrderCollection,Filter(OrderDets,StartsWith(Title,"Order8"))); Collect(OrderCollection,Filter(OrderDets,StartsWith(Title,"Order9")))

Explanation: Here, I am creating a collection “Dynamic Gallery” and this is the number of rows corresponding to the gallery control for creating the new orders. I am creating another collection “OrderCollection” and this collection contains all the Order Details from the SharePoint List named “OrderDets”.

Note: The “StartsWith” function is not delegable if a variable is passed as the second argument which is the reason why I am using multiple “Collect” statements to iterate over all possible values.

Galleries to create dynamic entries in a Data Source in PowerApps1

  1. This icon is the Home Page icon and clicking on this navigates the user to the home screen
  2. This icon is the Order Details Screen icon and clicking on this navigates the user to the Order Details Screen
  3. This icon is the Edit an Item icon and clicking on this allows the user to edit a particular item
  4. This icon is the Refresh Icon and clicking on this refreshes the data source, the expression used here is:

Refresh(OrderDets);Clear(OrderCollection); Collect(OrderCollection,Filter(OrderDets,StartsWith(Title,"Order1"))); Collect(OrderCollection,Filter(OrderDets,StartsWith(Title,"Order2"))); Collect(OrderCollection,Filter(OrderDets,StartsWith(Title,"Order3"))); Collect(OrderCollection,Filter(OrderDets,StartsWith(Title,"Order4"))); Collect(OrderCollection,Filter(OrderDets,StartsWith(Title,"Order5"))); Collect(OrderCollection,Filter(OrderDets,StartsWith(Title,"Order6"))); Collect(OrderCollection,Filter(OrderDets,StartsWith(Title,"Order7"))); Collect(OrderCollection,Filter(OrderDets,StartsWith(Title,"Order8"))); Collect(OrderCollection,Filter(OrderDets,StartsWith(Title,"Order9")))

Explanation: This refreshes the data source (“OrderDets” SharePoint List). It also clears the existing data from the “OrderCollection” collection and refills it with the new data.

  1. This is a gallery control that populates all the items of the SharePoint list
    • The expression used in the “Text” property of the “Order Details” label is:

"Order Details Total Orders Count:"&CountRows(OrderCollection)

Explanation: This expression concatenates the simple text (wrapped in “”) with the integer returned as a result of the “CountRows” function applied on the “OrderCollection” collection.

    • The expression used in the “Items” property of the Gallery is:

Sort(Filter(OrderCollection,If(!IsBlank(TextInput3.Text),StartsWith(Title,TextInput3.Tex t) ||
StartsWith(Customer,TextInput3.Text),true)),Value(Last(Split(Title,"r")).Result),Descen ding)

  1. This is a stack of Text labels used to show the information when an item is selected in the “Order Details” gallery. Expressions used on the labels:

Customer: Gallery5.Selected.Customer, Shipping Address: Gallery5.Selected.'Shipping Address', Billing Address: Gallery5.Selected.'Billing Address'

Explanation: Each line is an individual expression that fetched the attributes of the item selected in the “OrderDetails” gallery (Gallery5).

  1. This is a button control that enables the gallery control for the user to create dynamic lines and enter the order details. Expression used on this button:

ClearCollect(DynamicGallery,{Value:1});Set(NewOrder,true);Set(ResetGallery,false);Set(ResetGallery,true)

Explanation: Here I am recreating the “DynamicGallery” collection to accommodate just one value that corresponds to one row of the newly visible dynamic control gallery. I am setting up two new variables “NewOrder” and “ResetGallery” that control the visibility/reset of this dynamic gallery control, “Total Number of New Orders” and the “Finish and Save Button” controls.

Use Galleries to create dynamic entries in a Data Source in PowerApps 2

  1. This is the dynamic gallery control that I customized for user inputs. This gallery control has four text input controls to get the values for each of the attributes of the SharePoint List. The user can create multiple lines (one at a time) to add multiple records in one go. Data from each line is directly patched to the data source to create a new item. The user can remove the line by clicking the “X” icon. Configuration of the elements of the gallery control:

Gallery Properties:
Items: DynamicGallery, Visible: NewOrder,

Explanation: “DynamicGallery” is the collection that holds count of the orders to be added. “NewOrder” is a variable used to set the visibility of the controls.

  1. This icon is to remove the current row from the dynamic gallery. The expression used on this is:

Icon Properties:
OnSelect: Remove(DynamicGallery,ThisItem), Visible: If(ThisItem.Value <>
Last(Sort(DynamicGallery,Value,Ascending)).Value,true,false)

Explanation: We are removing the current item from the gallery by pressing this button (“OnSelect”) property. This icon’s visibility is set in a way that it shows up only if the current row is not the last item of the dynamic gallery.

  1. This icon is to add a new row/ line to the dynamic gallery. The expression used on this is:

Icon Properties:
OnSelect: Collect(DynamicGallery,{Value: ThisItem.Value + 1}), Visible: If(ThisItem.Value =
Last(Sort(DynamicGallery,Value,Ascending)).Value,true,false)

Explanation: We are adding a row/line by adding an item to the dynamic gallery collection by pressing this button (“OnSelect”) property. This icon’s visibility is set such that it shows up only on the last item of the dynamic gallery.

  1. This button is to perform the patch action on the “OrderDets” SharePoint List and it patches all the entries made by the User A in the dynamic gallery. The expression used in this control is:

ForAll(
Gallery3_1.AllItems,
Concurrent(
Patch(OrderDets,Defaults(OrderDets),{Title:TextInput2_6.Text,Customer:TextInput2 _7.Text,'Shipping Address':TextInput2_4.Text,'Billing Address':TextInput2_5.Text}), Patch(OrderCollection,Defaults(OrderCollection),{Title:TextInput2_6.Text,Customer: TextInput2_7.Text,'Shipping Address':TextInput2_4.Text,'Billing
Address':TextInput2_5.Text})));
ClearCollect(DynamicGallery,{Value:1});
Set(NewOrder,false);
Refresh(OrderDets)

Explanation: In this control, the concurrent function executes two patch commands, one in the data source (“OrderDets” SharePoint List) and the other on the local collection (“OrderCollection”) based on the inputs by the User A in each of the line/ row of the dynamic gallery. The “DynamicGallery” collection is being reset to hold a single value. The variable “NewOrder” is set to “false” to toggle the visibility of the dynamic gallery and then we finally refresh the data source.

Note: We are doing a concurrent patch instead of refreshing and recollecting the data in the collection “OrderCollection” from the data source to optimize the operations in the app.

  1. This is the text label control that displays the total number of current lines/rows User A has created. The expression used here is:

"Total Number of New Orders: "& CountRows(Gallery3_1.AllItems)

Explanation: Here the text “Total number of New Orders” is being concatenated with the number of rows of the dynamic gallery.

Use Galleries to create dynamic entries in a Data Source in PowerApps 3

  1. This is the text input control of the dynamic gallery. Here I am validating the text input and checking through the “OrderCollection” if the entered Order Number already exists. If it exists, the user will get an error notification. The expression used in the “OnChange” property of this control is:

If(TextInput2_6.Text in OrderCollection.Title,Notify("Order Number already exists!",NotificationType.Error))

Explanation: Here the if condition checks if the text of the text input control exists in the “Title” column of the “OrderCollection” collection and pops an error message if the condition is met.

  1. This is the error notification generated when the user enters an existing order number in the text input.

Use Galleries to create dynamic entries in a Data Source in PowerApps 4

  1. This is the App settings page of the app where we are increasing the soft limit of the data row limit on of non-delegable queries from 500 to 2000.

In this article, I have shown a basic implementation of the dynamic galleries concept and the multiple items/ records patch function for a SharePoint data source. This can be replicated with minor changes in the expressions for other data sources such as CDS, excel, SQL, etc.

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

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!

PowerApps logoI’m working on a project with a straightforward requirement that’s typically solved with out-of-the-box Power Apps platform features. The customer needs to capture employment application data as part of their online hiring process. In addition to entering standard employment data, applicants enter a typical month’s worth of budgeted expenses to paint a clear financial picture which serves as one factor in their suitability for employment.

The Requirements

The solution consists of an Application entity and a related Expense entity to track multiple expenses per Application. The Application entity needs to capture aggregate expense values based on expense categories. The Expense entity stores the institution or business receiving the payment, the Expense Type (lookup), the monthly payment in dollars, and the balance owed in dollars. Expense Types are associated with “Expense Categories.” An Expense Category is associated with one or more Expense Types. The Application entity tracks 15-20 aggregate expense values that need to be calculated from the individual expense entries.

For example, the expense types “Life Insurance” and “Auto Insurance” are both associated with the expense category “Insurance.” The Application entity has an aggregate field called “Total Insurance Expenses” to capture the sum of all monthly insurance-related expenses.

The basic entity model is shown below:

Diagram of entity model

To summarize all that detail (which is probably hard to follow in paragraph form), the essential requirements are:

  • Capture standard employment data for applicants
  • Capture monthly budgeted expenses associated with an expense type and category
  • Calculate 15-20 aggregate expenses based on expense category
  • The solution must accommodate the addition of new expense categories

The aggregate fields on the Application entity fall into one of four categories: 1) a monthly payment total by expense category, 2) an outstanding balance total by expense category, 3) a monthly payment total across all categories, and 4) a total outstanding balance across all categories.

The breakdown for each of the expense categories and their associated aggregations on the Application entity can be depicted as such:

Expense Category Category Payment Rollup Category Balance Rollup All Payments Rollup All Balances Rollup
Automobile Total Automobile Expenses Total Auto Balances Owed Total Monthly Expenses Total Outstanding Debt
Credit Card Total Credit Card Expenses Total Credit Card Balances Owed Total Monthly Expenses Total Outstanding Debt
Food/Clothing Total Food/Clothing Expenses Total Monthly Expenses
Housing Total Housing Expenses Total Housing Balances Owed Total Monthly Expenses Total Outstanding Debt
Insurance Total Insurance Expenses Total Monthly Expenses
Medical/Dental Total Medical Expenses Total Monthly Expenses
Other Debt Total Other Debt Expenses Total Other Debt Balances Owed Total Monthly Expenses Total Outstanding Debt
Utilities Total Utility Expenses Total Monthly Expenses

The table shows that some expense categories require a total of four aggregate calculations, whereas others only require two aggregate calculations. The calculations should occur when an Expense is created, the values for a monthly payment or balance owed change, an Expense is deactivated/activated (state change), or when an Expense is deleted. “Total Monthly Expenses” is calculated for all expense entries. Only four categories require the category balance and total outstanding balance calculations.

Platform Limitations

Maximum Number of Rollup Fields

Dynamics 365 only allows for a maximum of 10 rollup fields on any given entity — these are fields that take advantage of the “Rollup” field type customized in the solution, the values for which are automatically calculated by the platform on a predetermined interval, e.g., every 12 hours.

One option to overcome this limitation — only available in on-premises Dynamics 365 implementations — is to modify the maximum number of rollup fields per entity in the MSCRM_CONFIG database. There are rare circumstances wherein modifying table values in this database are beneficial. However, given the possibility of a disaster recovery situation in which Dynamics 365 needs to be reinstalled and/or recovered, any modifications made to the MSCRM_CONFIG database could be lost. Even if an organization has well-documented disaster recovery plans that account for these modifications, there’s always a chance the documented procedures will not be followed, or steps possibly skipped.

Another consideration is the potential to move to the cloud. If the customer intends to move their Dynamics 365 application to the cloud, they’ll want to ensure their solution remains on a supported path, and eliminate the need to re-engineer the solution if that day comes.

Rollup Calculation Filters

Rollup fields in Dynamics 365 are indeed a powerful feature, but they do come with limitations that prevent their use in complex circumstances. Rollup fields only support simple filters when defining the rollup field aggregation criteria.

To keep this in the context of our requirements, note above that the Expense Type and Expense Category are lookup values in our solution. If we need to calculate the sum of all credit card expenses entered by an applicant, this is not possible given our current design, because the Expense Type is a lookup value on the expense entry. You’ll notice that when I try to use the Expense Type field in the filter criteria for the rollup field, I’m only given the choices “Does not contain data” and “Contains data.” Not only can’t I use actual values of the Expense Type, but I can’t drill down to the related Expense Category to include it in my aggregation filter.

Screenshot of test rollup screen

Alternatives

The limitations above could be overcome by redesigning our solution, for example, by choosing to configure both the Expense Type and Expense Category fields as Option Sets instead of lookups, along with some sophisticated Business Rules that appropriately set the Expense Category based on the selected Expense Type. That’s one option worth considering, depending on the business requirements with which you’re dealing. We could also choose to develop a code-heavy solution by writing plugin code to do all these calculations, thus side-stepping the limitation on rollup fields and accommodating the entity model I’ve described.

The Solution

Ultimately, however, the customer wants a solution that allows them to update their expense tracking requirements without needing developers to get the job done. For example, the organization may decide they no longer want to track a certain expense category or may want to add a new one. Choosing to create entities to store the necessary lookup values will afford them that kind of flexibility. However, that still leaves us the challenge of calculating the aggregate expense values on the Application entity.

The final solution may still require the involvement of their IT department for some of the configuration steps but ideally will not require code modifications.

Lookup Configuration

The first step toward our solution is to add four additional fields to the Expense Category entity. These four fields represent the four aggregation categories described above: 1) Category Payment Rollup, 2) Category Balance Rollup, 3) Payments Rollup (for all categories), and 4) Balances Rollup (for all categories).

These fields will allow users to define how aggregate values are calculated for each Expense Category, i.e., by identifying the target fields on the Application entity for each aggregation.

Screenshot of Expense Category in Dynmaics 365

Custom Workflow Activity

The next step is to write a custom workflow activity to perform the aggregate calculations described above. Custom workflow activities present several benefits to the customer, primarily centered on the ease of configuration within the Dynamics 365 UI itself  (run asynchronously, run on-demand, and/or when specific events occur on the target record type, e.g., create, update, delete, or state change). Custom workflow activities can accept user-defined parameters configured in the workflow definition.

This means that — as you might have guessed — the custom workflow activity can be written in such a way to allow users to add new Expense Categories so that the aggregate calculations “just work” without requiring code modifications or changes to the workflow configuration in the solution.

Here’s the custom workflow activity class that runs the calculations followed by the workflow definition. As you can see below, I’ve included the Application and Expense Category fields as required input parameters for the workflow activity. (I’ll likely refactor this solution to accept the four fields as inputs, but for now, this gets the job done. Thanks to my good friend, Matt Noel, for that suggestion.) Further down, you’ll notice that for each aggregate field, we run a custom fetch query configured appropriately to perform the required calculation.

using Microsoft.Xrm.Sdk;
using Microsoft.Xrm.Sdk.Client;
using Microsoft.Xrm.Sdk.Query;
using Microsoft.Xrm.Sdk.Workflow;
using System;
using System.Activities;

namespace Project.Workflow.Activities
{
    public class CalculateExpenses : CodeActivity
    {
        #region Input Parameters

        [RequiredArgument]
        [Input("Application")]
        [ReferenceTarget("new_application")]
        public InArgument<EntityReference> Application { get; set; }

        [RequiredArgument]
        [Input("Expense Category")]
        [ReferenceTarget("new_expensecategory")]
        public InArgument<EntityReference> ExpenseCategory { get; set; }

        private readonly string _categoryPaymentRollupField = "new_categorypaymentrollup";
        private readonly string _allPaymentsRollupField = "new_paymentsrollup";
        private readonly string _categoryBalanceRollupField = "new_categorybalancerollup";
        private readonly string _allBalancesRollupField = "new_balancesrollup";

        #endregion

        protected override void Execute(CodeActivityContext executionContext)
        {
            var tracer = executionContext.GetExtension<ITracingService>();
            var context = executionContext.GetExtension<IWorkflowContext>();
            var serviceFactory = executionContext.GetExtension<IOrganizationServiceFactory>();
            var orgService = serviceFactory.CreateOrganizationService(null);
            var orgContext = new OrganizationServiceContext(orgService);

            string[] _rollupFields =
            {
                _categoryPaymentRollupField,
                _allPaymentsRollupField,
                _categoryBalanceRollupField,
                _allBalancesRollupField
            };

            var expenseCategory = GetEntity(orgService, ExpenseCategory.Get(executionContext), _rollupFields);
            var applicationRef = Application.Get(executionContext);
            var application = new Entity("new_application")
            {
                Id = applicationRef.Id
            };

            // Set the Category Payment Rollup
            if (expenseCategory.GetAttributeValue<string>(_categoryPaymentRollupField) != null)
            {
                var paymentRollup = expenseCategory.GetAttributeValue<string>(_categoryPaymentRollupField);
                application[paymentRollup] = new Money(GetExpenseAggregate(orgService, application.Id, expenseCategory.Id, false, true));
            }

            // Set the rollup for all Monthly Payments
            if (expenseCategory.GetAttributeValue<string>(_allPaymentsRollupField) != null)
            {
                var allPaymentsRollup = expenseCategory.GetAttributeValue<string>(_allPaymentsRollupField);
                application[allPaymentsRollup] = new Money(GetExpenseAggregate(orgService, application.Id, expenseCategory.Id, false, false));
            }

            // Set the rollup for Category Balances
            if (expenseCategory.GetAttributeValue<string>(_categoryBalanceRollupField) != null)
            {
                var categoryBalanceRollup = expenseCategory.GetAttributeValue<string>(_categoryBalanceRollupField);
                application[categoryBalanceRollup] = new Money(GetExpenseAggregate(orgService, application.Id, expenseCategory.Id, true, true));
            }

            // Set the rollup for all Category Balances 
            if (expenseCategory.GetAttributeValue<string>(_allBalancesRollupField) != null)
            {
                var allBalancesRollup = expenseCategory.GetAttributeValue<string>(_allBalancesRollupField);
                application[allBalancesRollup] = new Money(GetExpenseAggregate(orgService, application.Id, expenseCategory.Id, true, false));
            }

            // Execute the update on the Application rollup fields 
            try
            {
                //trace 
                orgService.Update(application);
            }
            catch (Exception e)
            {
                //trace
                throw new InvalidPluginExecutionException("Error updating Application: " + e.Message);
            }
        }

        private static Entity GetEntity(IOrganizationService service, EntityReference e, params String[] fields)
        {
            return service.Retrieve(e.LogicalName, e.Id, new ColumnSet(fields));
        }

        private decimal GetExpenseAggregate(IOrganizationService service, Guid applicationId, Guid expenseCategoryId, bool balanceRollup, bool includeCategory)
        {
            var sum = 0m;

            var aggregateField = balanceRollup ? "new_balanceowed" : "new_monthlypayment";

            var fetchXML = @"<fetch distinct='false' mapping='logical' aggregate='true' >" +
                  "<entity name='new_expense' >" +
                    "<attribute name='" + aggregateField + "' aggregate='sum' alias='sum' />" +
                    "<filter type='and' >" +
                      "<condition attribute='statecode' operator='eq' value='0' />" +
                      "<condition attribute='new_applicationid' operator='eq' value='" + applicationId + "' />" +
                    "</filter>";

            var endFetch = "</entity></fetch>";

            if (includeCategory)
            {
                var categoryFetch = @"<link-entity name='new_expensetype' from='new_expensetypeid' to='new_expensetypeid' link-type='inner' alias='ExpenseType' >" +
                      "<link-entity name='new_expensecategory' from='new_expensecategoryid' to='new_categoryid' link-type='inner' alias='ExpenseCategory' >" +
                        "<filter type='and' >" +
                          "<condition attribute='new_expensecategoryid' operator='eq' value='" + expenseCategoryId + "' />" +
                        "</filter>" +
                      "</link-entity>" +
                    "</link-entity>";

                fetchXML += categoryFetch + endFetch;
            }
            else
            {
                fetchXML += endFetch;
            }

            FetchExpression fetch = new FetchExpression(fetchXML);

            try
            {
                EntityCollection aggregate = service.RetrieveMultiple(fetch);

                foreach (var c in aggregate.Entities)
                {
                    if (((AliasedValue)c["sum"]).Value is Money)
                    {
                        sum = ((Money)((AliasedValue)c["sum"]).Value).Value;
                        //tracer.Trace("Sum of payments is: {0}", sum);
                    }
                }
            }
            catch (Exception e)
            {
                //tracer.Trace(e.Message);
                throw new InvalidPluginExecutionException("Error returning aggregate value for " + aggregateField + ": " + e.Message);
            }

            return sum;
        }
    }
}

Workflow definition:

Powerapps workflow screenshot

Configuration of custom inputs:

Configuring custom inputs

Testing

After building and registering my workflow assembly, I created expense entries for all expense types ensuring that all expense categories were represented. The following images depict the successful aggregation of payments and balances:

Screenshot of expense output

Screenshot of debt output

Conclusion

Custom workflow activities are a powerful tool that balances the need for a highly maintainable solution after deployment, with complex requirements that need a coded solution. The design gives end-users the flexibility to adapt their data collection needs over time as their requirements change, and they can do so with little or no involvement from IT.

As I mentioned above, an alternative approach to this requirement could involve writing a plugin to perform the calculations. This approach would still require some entity-based configurations for flexibility but would suffer from limited end-user configuration needed if or when requirements change. I can also update the custom workflow activity to accept the four aggregate fields as optional arguments to the workflow. Doing so would enable users to run separate workflow processes for each expense type/category, giving them additional configuration control over these automated calculations.