Author – Nick Doelman
Introduction
Excel sheets are easy to track business data, but there are pitfalls with this approach. The Power Platform allows a low-code, no-code method to build a robust business application. Power Platform business applications can be further expanded with other tools and technologies, such as business analysis tools and Artificial Intelligence.
The Spreadsheet
Up until the 1970s, computers were limited to universities, governments, and large enterprises. The invention of the desktop computer along with spreadsheet software was a revolutionary step for all small and medium businesses.
Almost 50 years later, there are many unique business data tracking and transaction requirements that are still implemented using tools like Microsoft Excel.
And why not? Microsoft Excel is fast and easy. You create a new worksheet and start entering data. If you are a bit more advanced, you can create some charts and macros to speed up your work or perform some analysis.
However, there are many pitfalls. Excel has an upper limit on the amount of data you can store. Usually, only one person can effectively enter and update data on a worksheet. While there are some locking and privacy features, for the most part, a user of an Excel worksheet has access to all the data.
Humans are humans. It can take a very slight keyboard slip and data can easily be changed or deleted even without the knowledge of the user.
I had a client a while back lose months’ worth of data because someone accidentally overwrote an important Excel file that was tracking key business data.
As a business, we no longer keep piles of cash in a box in our office, it is kept safe in a bank. Should we treat our valuable data the same?
Business Applications
There are hundreds of business applications on the market today for managing data for a variety of industries and needs. Accounting, project management, customer relationship, and sales tracking are a few examples. These systems have evolved from desktop client-server to cloud browser-based systems that can be accessed from anywhere on any device. Furthermore, many of these systems have very flexible personalization tools and application programming interfaces (APIs) that allow businesses to tailor these applications to their specific needs.
However, there are some instances where a business may have very unique requirements that are not necessarily served by available business applications and too complicated to manage using a spreadsheet. There are tremendous investments in resources and time to build a custom software application from the ground up.
Low-code, No-code Platforms
The last couple of years has seen the rise of Low-code development platforms. The idea is to provide tools to allow business users to build robust business applications without complex software coding.
The Microsoft Power Platform is an example of a low-code, no-code platform that is the assembly and evolution of several tools and technologies. Much of the platform traces its roots from Microsoft Dynamics CRM, a customer relationship management application that provided flexible tools to extend CRM applications. Many businesses benefited from Dynamics CRM as an “anything” relationship management platform (aka xRM) and this helped shaped many of the tools available on the Microsoft Power Platform today.
Build a Model-driven Power App to Replace a Business-critical spreadsheet
While there are a few different options to build solutions in the Power Platform (Canvas-apps, Power Automate, Portals) the next section will focus on taking an example Excel-based application and creating a corresponding model-driven Power App.
Model-driven Power Apps are designed around particular modeling of business data.
Business Requirement
In this example, the requirement is to track the allocation of subsidies for student employment placements. This is modeled after a real-world project I recently implemented, simplified quite a bit to highlight the main points in this post.
In this example, we have an Excel worksheet that is tracking each placement request. We have columns to contain the employer information, the employer contact, student information, and of course information on the subsidy itself.
While the Excel sheet is workable, there are all of the issues I mentioned above. We also see that there is some potential duplication (e.g. ABC company has multiple requests). Chances are that ABC Company also interacts with the organization for other projects and initiatives (more on that later).
If we want to build a management app, where can we start?
Step by Step
To try this out, if you have an existing Microsoft 365 (Office 365) subscription, you can start to build your app using the Power Platform Community plan. The community plan is not meant for production uses, but you can build your app and test it out. We will discuss later how to license Power Apps and deploy the app to production environments.
The process will provide a Power Platform developer environment for you to build apps. You will arrive at the Power Apps Maker Portal. While you might be tempted to click the “Model-driven app from blank” in the Make your own app section, we should lay a bit of groundwork and design first.
When creating Power Apps, it is a best practice to create the assets in the context of Solutions. This will allow us to package everything and move it to a test or production environment. There is also a full application lifecycle management (ALM) story to go along with this but let’s keep it to the basics. When you click on solutions you will be prompted to create a database.
You will need to pick the currency and you might want to install sample apps and data to take a look at some examples of model-driven apps.
This process will create a Dataverse. A Dataverse is a database but with many other features and options for building applications, automation, securing, and storing data. The core technology is Azure SQL but a Dataverse is much, much more.
When the process is complete, you will see a listing of existing solutions, both administrative and samples.
To build our app, we will create a new solution. We can give it a name, and we can also create a publisher that will further personalize the app. The solution will have a version that can help with the management of updates and new versions.
Designing the Data Model
Now that we have the foundation, a bit of pre-planning goes a long way to build an effective app. If we look at our spreadsheet app, we can begin to categorize our data and see we have employer info, employer contacts, student contacts, and placement data. If we think further, an employer’s contact info is the same as the student’s contact (name, phone, email). There also might be a situation (although rare) where the student contact is the same person as the employer contact. Whether it’s a white-board or Visio, we can begin to design our data model.
Ideally, we should establish any fields (columns) as well. Now that we have identified our data, we need to see how the parts are related to each other and “link them up”.
Other Design Considerations
Now that we have the data model, we should consider the design of other aspects of our app. These can be User Interface considerations (and you should understand the basic structure of how model-driven user interfaces are constructed) as well as security, automation, reporting, and integration. Understanding how these will work to meet the requirements will make the actual construction of the app smoother as well as better user acceptance.
Building the App
Understanding the Common Data Model
Before we go off and start adding new tables to our solution, we might want to understand what the Dataverse can give us before we build. Most of the applications I have built over the years need to track companies and contacts. It would be tedious to always have to build new tables with company name, address, email, phone, etc. The Common Data Model provides pre-built “common” tables with common functionality (e.g. email integration) so we don’t have to re-invent the wheel each time we create an app. Looking in the Tables section of the maker portal, we can find items like “Accounts” (which have the structure to track company information) as well as “Contacts” (which have the structure to track people information, and can integrate with Microsoft Outlook).
However, further searching we don’t have a “Placement” table in our Common Data Model (however, there are accelerators available that cover a variety of scenarios). For our app, we will create our own Placement table.
We will add the existing tables to our solution.
And then we will add a new table to the solution.
We will create our Placement table, and with the existing Account and Contact tables, we will have created the basis of our data model from our design.
For the tables we create, there will be several auto-generated fields (create on, modified on, etc) but we need to add the specific fields (columns) to track our specific information. There are a variety of field “types” (text, number, date, etc) that will help enforce formats and data accuracy. We can also create calculated or rollup fields to aggregate information.
Once the columns are created, we need to link the various tables together with lookup fields. This is done using “relationships”. To link to our Placement table, we will choose “Many to One” to link to the Account and Contact tables (Many Placements can be linked to One Account) or vice versa.
The next step is to create the user interface. Model-driven apps provide a basic framework of “Views” and “Forms” to display and allow users to interact with the data. This is one of the main differentiators from “canvas” Power Apps that provide a blank canvas where a user needs to align and build out various controls.
There are series of views pre-generated for a table and we can either modify these existing views or create our own. A view is very similar to an Excel worksheet as its shows data in a series of rows and columns.
We will have a lot of flexibility on column view width, placement, sorting, and filtering. A model-driven app can have many views as well as embed views in model-driven forms (next).
We can design forms to show the details of a particular record in our app. The form structure is made up of tabs and sections and generally displays fields but can be extended to show embedded views and forms from related records.
We can also modify our Account and Contact forms to better suit our application as well.
Once we have defined our tables, views, and forms, we can dive deeper and add charts, dashboards, and business process flows to further energize our apps. For now, we have enough for a basic app, and we can create the app as part of our solution.
I created the app with the solution as a base, this added the appropriate tables. From here we can define further what tables, forms, views, dashboards, etc. will be part of our app.
We can build more than one app, and have it reside in the same environment. We could have another department build an app for potentially a training program, but share data with our Placement app. This is the power of the Dataverse, centralizing core business information.
Within our app designer, we will need to define our site map, which is the main navigation framework for model-driven apps. Here we can break down the navigation to Areas, Groups, and Sub-Areas.
We can also place links to dashboards or even external browser-based applications.
Once we create our Sitemap, we can publish our app and try it out.
Now we have a working business application where we can track our Placements.
Notice that a no time did I add “code” or complex functionality. Most of the application development was point and click.
Here are some of the immediate benefits or ditching the spreadsheet and using a model-driven app;
- Data can be entered and updated in a consistent, easy to use interface
- The application is automatically multi-user
- The application can be accessed from almost anywhere
- Security roles can be created to allow certain users to only access parts of the data
- Data can be exported to Word or Excel Templates
- The app is responsive and will adapt to different devices
- Data is backed up on the Microsoft cloud
Expanding the App
The app can be further extended and expanded using tools from the Power Platform. Users could create Power BI reports analyzing the data tracked in the app. Users can also create AI Builder models to predict trends or perform other analysis. Users can automate many tedious or complex business processes using Power Automate. Information can be surfaced to external stakeholders using Power Apps portals. More specific, task-based apps can be created using canvas Power Apps to interact with the Dataverse information captured in the app.
As applications evolve, there will be requirements where we need to involve a professional developer. The Power Platform has published APIs where pro developers can further extend the functionality of the platform with plug-ins, custom actions, and user interface controls.
Deployment
The entire solution can be exported and imported into a production Dataverse environment. Many of the deployment steps can be automated using Azure Dev Ops.
Licensing
Microsoft licensing can be complex and Power Apps subscriptions are no exception. The core licensing for Power Apps is either “per user” that provides a user with an unlimited number of apps and a “per app” license that allows a user access to two apps + portal access. The subscriptions also allow for a certain amount of database storage and compute capacity. However, if you consider the return on investment for these apps, there is tremendous value over trusting the data to a spreadsheet or investing in building a fully customized solution. Information on licensing can be found here. https://docs.microsoft.com/ /power-platform/admin/pricing-billing-SKUs
Conclusion
The next time you have a business requirement that needs data tracking or database application, do not be tempted by Excel. Consider using the Power Platform and creating a model-driven Power App. The platform is not only great for your custom apps but is what is now used for many of Microsoft’s own Dynamics 365 applications. While some technical skill is required, you don’t need to be a hard-core developer to either build apps for your application or build a career on the Power Platform. The PL-900 certification is a great way to start to learn more details about the Power Platform.
