Blog, Microsoft Azure

Blog - Use case: Creating a low code power app using the Microsoft Stack

Aug 8, 1:25 PM

Introduction

Use case implementation: provide a high-performance, low-code application built from Microsoft Stack components that is easy to maintain and flexible enough to address the changing nature of evaluation questions.

1. The purpose of the use case: balancing flexibility and low code

One of our customers wanted an application to enter the answers to their customer questionnaires. The main promise was the uniformity of the stored data. Since the data is stored in a database, this is a big step up from using Excel files that have to be standardized manually. The database also allows our client to draw conclusions about the data set rather than on a case-by-case basis.

A second advantage was the user-friendliness of the application. On the one hand, end users can navigate intuitively through the screens. On the other hand, the in-house employee responsible for maintaining the application is used to working with low-code applications. Therefore, no new skills had to be learned.

In short, we needed to provide a high-performance, low-code application built from Microsoft Stack components that was both easy to maintain and flexible enough to respond to the changing nature of evaluation questions.

2. The components of a successful solution: the proposed architecture

We have proposed the following architecture as a solution:

Power Automate will be used for :

load all data from Excel files to SharePoint lists. This allows for massive loads of large data sets.

b. control permissions for SharePoint list items.

notify users of certain events (when a questionnaire is scheduled or an event is added to the reviewer's calendar, for example).

Power Apps will be used to create the application. Power Apps interacts immediately with the flows and can, for example, start the authorization flow. Power Apps also connects to SharePoint lists through CRUD operations.

Finally, SharePoint lists will be used to store the data. These lists will later be used by Power BI, which will visualize the current data from the questionnaire.

3. The approach: from a uniform data model to dynamic screens

3.1 A uniform data model

As SharePoint lists will be used for data storage, referential integrity is extremely important. We proposed to our client a star schema with dimensions for questions and possible answers (multiple choice). We also inserted another dimension to keep track of the users' roles (reviewer, partner, etc.). A dimension was created to collect data from all questionnaires (assessments) and a dimension table was created to collect the actual answers per questionnaire. See an overview below.

3.2 Dynamic screens

As far as the application is concerned, we could have opted for a fixed screen per question, which would have considerably facilitated the development. However, this solution would have lacked flexibility, because if we wanted to change the number of questions, for example, we would have had to adapt the application. This interdependency should be avoided. Therefore, we chose dynamic screens with code-based navigation.

The following steps were taken to achieve this flexibility:

All questions were added to a collection in memory, sorted and indexed.

A single screen was used for all questions. Filters were applied according to the line number. For example, when moving from line 1 to line 2, the same screen was used, but the content was adapted.

The "previous" and "next" arrows either increase the index or move to the next/previous screen (the final questionnaire submission screen, for example).

4. Priority to security: access by role

Our client has defined three different roles:

The reviewer, who initially conducts the questionnaire

The Partner, who may choose to agree or disagree with the Reviewer's responses

The manager, who reviews all questions

4.1 Playing with the "visibility" variable in Power Apps

The same screens were reused within the application, but depending on the user's role (defined as dim_userrole), the aesthetics differed. This reuse of screens was a major benefit. It not only standardized the layout, but also simplified the code on each page.

4.2 SharePoint permissions: access to items rather than entire lists

Typically, a user must access the correct items/listings before they appear in the Power App. While the user typically only accesses the assessment through the Power App, it is critical that the user does not have unauthorized access when navigating the lists directly. For example, there is no need for the reviewer to have editing rights once the questionnaire is completed. Nor does the reviewer have the right to view items in a questionnaire that have not been assigned to them.

For a more thorough control of permissions, we chose to run several Power Automate flows from the application. These give rights to specific items rather than setting permissions on the whole list. See an example below.

4.3 Reporting on previous questionnaires using Power BI

The Power BI report generates an overview of past questionnaires, including several figures to aggregate the data. Again, only questionnaires belonging to a certain user were to be shown. For this purpose, row-level security was implemented, based on a group in Power BI (this group was added to the security properties of the dataset) as well as on the user's role in the Dim_userrole dimension. Find a detailed tutorial by Tringuyenminh92 on how to adopt dynamic row-level security in this article(August 11, 2017).

5. Exploiting the benefits

You may be wondering why our client chose a low-code application rather than building one from scratch. In addition to the reduced installation cost, this solution had other advantages.

5.1 SharePoint lists for user-friendly data storage

Although SharePoint lists are far from perfect (see disadvantages), they are easy to use and manipulate. The user interface is intuitive and can be used by ordinary people. While a truly relational database provides more consistency, knowledge of SQL is required or another application must be created to interact directly with the data.

5.2 No deployment of the Power App required

The application can be published directly, so there is no need for a hosting space, a deployment procedure or additional security measures. With Power Apps, an experienced user can develop his application independently, version control is integrated and all necessary components are available in Microsoft Stack.

5.3 Easy as pie formulas

Users who are used to writing formulas in Excel or DAX in Power BI easily become familiar with coding in Power Apps. In addition, code can be applied directly to drag-and-drop elements that are easy to interact with.

5.4 Ease of integration

As with all Microsoft products, the components used integrate perfectly with each other.

6. disadvantages of the components used

6.1 Less control over performance

In Power App, searches must be performed frequently to retrieve specific items such as the right question based on the index (see dynamic screens). These searches are significantly less efficient on large data sets, such as lists of thousands of rows.

However, several solutions exist to guarantee the performance of the application.

Filters can be applied after the collection is loaded into memory to reduce the number of rows that need to be analyzed. To perform the fix, you can first fix the collection in memory before fixing the SharePoint list.

Multiple indexes can be added to SharePoint lists. If a certain field is searched frequently, it makes sense to use this field (or these fields) as an index.

In order to reduce the number of lines, you can archive past evaluations that are no longer needed with a Power Automate flow.

6.2 No layout template

One of the most frustrating aspects with drag-and-drop settings is the inability to set up a layout template, as can be done using front-end templates such as React. Most elements still have to be manually copied and pasted, both in Power BI and Power Apps. Several solutions exist to simplify this process, but they are by no means sufficient.

Use of variables: colors, fonts, etc., can be stored in variables so that each element does not have to be modified when the internal style is changed, for example.
Use of components - this is a premium setting only available for paid Power Apps packages. However, this setting only does some things, as you can create an entire component like a menu bar. This component can then be integrated on all pages and will be automatically adapted to it if its layout changes.

6.3 Limited reuse of code

The big drawback of Power Apps is also one of its strong points: you can store code directly on the elements, which increases the intuitiveness. However, it is impossible to store the code in separate variables in order to apply it to several elements. Again, this leads to a lot of copy and paste.

In addition, there is no search function to look for the code. So you have to click on each element separately, risking human error. This disadvantage can be set aside for small applications, but for applications with several thousand lines of code, an evaluation function is an absolute necessity to store the code centrally in variables.

Conclusion

In short, the architecture proposed at the beginning of this article is suitable for smaller companies that like to hand over all control of the data and the application design to their experienced users. Aside from the necessary subscriptions, installation is relatively inexpensive and maintenance is fairly simple. Want more flexibility? Other solutions should be considered.