Microsoft Power Apps Cookbook

App Maker Basics

When comparing technical and culinary cookbooks, you will find certain similarities. Both are based on recipes that introduce the reader to creating something by following detailed instructions.

For example, in Italian culinary books, there is often a chapter describing how to prepare the sauce that many recipes will use throughout the book. The same concept applies to this cookbook. This chapter will explain the basic requirements for the most common scenarios when working on Power Platform recipes.

The following recipes will serve as a template for specific situations throughout the book:

How to build a Power Platform solution
Preparing tables in Dataverse
Building metadata-enabled storage – SharePoint lists and libraries
Gathering information with Microsoft Forms
Setting up a cloud data source – Azure SQL Database

How to build a Power Platform solution

A Power Platform solution consists of a package that stores all the pieces that compose the solution you’ve created to solve your business need. It aids in deployment, distribution, and management. Examples of these pieces can be Power Automate cloud flows, model-driven apps, Power BI reports, or Dataverse tables.

Creating a solution is a straightforward process, but that doesn’t mean you don’t need to follow specific steps to include best practices for your solutions.

For example, if you need to interact with a particular service, the platform uses a connection reference. When working with solution components such as cloud flows, the platform will try to help you by creating connection references for you but using random naming. It becomes even more complicated if multiple solutions exist within a specific environment. One component from one solution might use a connection reference from another without you noticing.

Setting up the connection references before creating components can solve these issues easily. This way, the platform will automatically use the solution’s connection references and the naming pattern you decide.

Getting ready

After creating the solution, we will need to choose a data source to define the steps required to configure a connection reference. For this example, we will use SharePoint.

How to do it…

Go to the Power Apps maker portal, https://make.powerapps.com, select Solutions from the left pane, and click + New solution from the toolbar.
Before describing the solution, click + New publisher and enter the display name and name in the Display name and Name fields respectively. Enter a meaningful two-to-eight-letter Prefix value that will be used for all the elements you will create. Check the Preview of new object name for an example of how a component will be named. Keep the default Choice value prefix and click on Save.
Back in the New solution pane, enter the desired Display name and Name for the solution, select your newly created Publisher, and click on Create:

Figure 1.1: Solution settings

Once the solution is created, from the toolbar, click New | More | Connection Reference.
From the panel that opens, set the Display name to AMPI-SharePoint, leave the Name as it is, and choose SharePoint from the Connector dropdown. If you already have a connection, select it. If not, create one with your credentials by selecting New connection.
Repeat Steps 4 and 5 for any other service your solution might require.

How it works…

This creation process will prepare the solution and open it automatically. The SharePoint connection reference will be available to every component that can use it, such as a cloud flow or a canvas app. This way, we will get a more organized solution for our projects.

Regarding the publishers, from the left pane, click More, Discover all, and then on Publishers inside the App Management section to see the list of all publishers included in the current environment.

Preparing tables in Dataverse

Tables are essential to every data source since they represent the backbone of the data structure. Their designs include column definitions, indexing mechanisms, relationship configurations, and more.

Almost every Power Platform solution requires related tables to support the business process, and that’s why Microsoft Dataverse is a compelling choice. It was created as a business-oriented relational database.

To discover more about the distinctive features that set Microsoft Dataverse apart, please visit https://learn.microsoft.com/en-us/power-apps/maker/data-platform/why-dataverse-overview.

In this recipe, we will learn how to build tables for this database. This will become a standard task for many projects, such as the one showcased in Chapter 3, Building from Data with Model-Driven Apps, where we will create a help desk solution based on Dataverse.

Getting ready

Each table on a database serves a specific purpose. For this example, we will create a table that will hold the data of all the members of a girls’ youth basketball team.

Team players

Display name	Data type	Format
Full name	Text \| Plain text (Single line of text)	Text
Jersey number	Number \| Whole number	None
Position	Choice \| Choice	-
Gear size	Single line of text	Text
Emergency contact information	Multiple lines of text	Text

Table 1.1: Team players table columns

How to do it…

Go to the Power Apps maker portal and open a solution from the list of solutions for the current environment.
From the toolbar, click New | Table | Table. Set the Display name to Team player. The Plural name will change to Team players automatically. Depending on your table’s needs, you might want to Enable attachments, for example, including extra information about the team player, such as parental consent or team enrollment.
Click on the Primary column tab. Enter Full name as the Display name. Remember to choose something your end users can use to select the records when listing them.
Once you have entered all the required information, click Save. The system will start creating the table with the specified primary column and the rest of the business-oriented columns. The table will open automatically once finished.
Click on New | Column from the toolbar. For the Display name field, enter Jersey number, set the Data type to Number | Whole number, and click Save.
Click New | Column again to include the Position column. For Data type, select Choice | Choice. Under the Sync this choice with dropdown, click on New choice:
1. Set Position as the Display name, and under the Choices section, add the term Point Guard.
2. Click on New choice to add each of these items: Shooting Guard, Small Forward, Power Forward, Center.
3. Click Save.
Back on the column configuration, select the previously created choice from the Sync this choice with dropdown, and click Save to continue.

Using global choices is a great way to reuse a Choice column for different tables if they are going to hold the same elements.

Repeat Step 5 to add the Gear size column using Single line of text as the Data type and Text as the Format.
Repeat Step 5 to include the Emergency contact information column using Text | Plain text from the Multiple lines of text section of the Data type dropdown. Leave Text as the Format.

How it works…

Now that we have added all the required columns, the table can be used in our solutions. The following is an example of the Team player table:

Figure 1.2: Team player table base structure

Building metadata-enabled storage – SharePoint lists and libraries

SharePoint Online, included in Microsoft 365, provides the perfect platform for secure content management, collaboration, and communication through dedicated sites.

This platform is commonly used as a data source for Power Apps solutions since it provides Microsoft Lists. This feature allows you to build a table-like storage with rich metadata columns. It’s not comparable to a full-fledged relational database, but it’s simple to use and comes included on most Microsoft 365 licenses. Please refer to Chapter 4, Choosing the Right Data Source for Your Applications, where we will perform a data source performance comparison, including SharePoint.

It also provides document libraries, which are file containers that inherit the same capabilities as Microsoft Lists, enhancing categorization and organization of the file storage, which helps improve business processes.

Getting ready

Since we are using SharePoint as our data source, there are no additional requirements besides a regular Microsoft 365 license. We will create a document library with some metadata columns to improve the library’s categorization.

Player documents

Here are the metadata columns to be added to the document library:

Type	Name
Choice: Photo ID, Medical certificate, Enrollment form	Document type
Date and time	Expiration date
Yes/No	Requires signature

Table 1.2: Library columns

How to do it…

Go to the SharePoint site, which will hold the document library. On the main site page, click on New | Document library, as seen in the following screenshot:

Figure 1.3: Document library creation

This action will open a dialog with options to create the library. Click Blank library and then enter the name and description of the library. Enter Player documents for the name, leave the rest as default, and click Create.

Whether creating lists or document libraries, the platform will try to help you when designing them. Providing options such as From existing list/library, From Excel/CSV will allow you to speed up the creation process.

Templates is another capability that provides metadata examples gathered from many business scenarios.

Once the library opens, click on the + Add column link at the end of the column header, as seen in the following screenshot:

Figure 1.4: The + Add column option

Select the Choice data type from the open dialog and click Next. Enter Document type for Name and click each of the choice examples to replace them for Photo ID, Medical certificate, and Enrollment form. Once completed, click Save.
Repeat Step 2 to add another column. Select Date and time and click Next. Enter Expiration date for the Name and leave the rest as default. Click Save to finish.
Again, repeat Step 2 to add the last column. Select Yes/No and click Next. Enter Requires signature for the Name and click Save to complete the process.
Back in the document library, reorder the columns as you see fit by dragging them. You can also hide columns by clicking on a particular column and selecting Columns settings | Show/hide columns.
Add files to the library by selecting Upload | Files from the toolbar or by dragging them. Once the upload is complete, click Edit in grid view to update the file’s metadata in bulk, like when working with an Excel spreadsheet. Click on Exit grid view when finished.

How it works…

The document library creation process is now complete. If you upload some files, your library should look like the one in the following figure:

Figure 1.5: Player documents library

Gathering information with Microsoft Forms

Web forms are an excellent tool for gathering information. Whether you require feedback from a specific process, an event registration, or even a simple contact form, you must clearly define the fields to collect the data in these forms.

Microsoft Forms gives you an easy way to create them with an easy-to-use interface that allows you to quickly create forms, quizzes, and polls. Most importantly, it offers a way to easily gather information from external users to your organization, as we will discover in Chapter 9, Empowering Your Applications with AI Builder, where we will use this tool to gather possible complaints from our customers.

Getting ready

Microsoft Forms is a service that goes beyond Microsoft 365. You can even use it with your personal Microsoft accounts. However, you must create the forms inside your tenant to use it with Power Platform. For this recipe, we will learn the necessary steps to build a form.

Summer basketball camp registration

Questions to be added to the form:

Field type	Required	Text	Extra
Text	Yes	Child’s Full Name
Text	Yes	Parent / Guardian
Choice	Yes	Campus options	Choices: 2 weeks regular student price 2 weeks external student price 2 weeks sibling’s price

Table 1.3: Form fields configuration

How to do it…

Head to Microsoft Forms by typing this address: https://forms.microsoft.com, and once there, click on the New Form button. This action will open the designer for you to start setting up the form.
Click on the Untitled form text to define the name, description, and image. You can also click Style on the toolbar to change to one of the built-in themes.

The following is an example using one of the immersive styles:

Figure 1.6: Microsoft Forms style

Now, let’s start including fields. A list of options appears to select the type. You can choose from the following field types:

Figure 1.7: Field types available in Microsoft Forms

Click Quick start with and select Text. Change the Question to Child's Full Name and enable the Required setting.
Repeat Step 4 to add the Parent / Guardian question.
Again, click on + Add new question, but this time, select Choice. Replace the Question with Campus options. Replace Option 1 and Option 2 with 2 weeks regular student price and 2 weeks external student price, respectively. Click + Add option to include 2 weeks sibling's price.
Click Preview on the toolbar and, depending on the layout and style of the form, click Start now afterward. You should have a form just like the following:

Figure 1.8: Registration preview

From the toolbar, click on Back to leave the preview mode and click the gear icon to configure who can fill out the form. Regularly, you should select Anyone can respond. You can change it as desired.

How it works…

Now that our form is ready, we could start building Power Automate actions that will get triggered when we receive responses.

Setting up a cloud data source – Azure SQL Database

Azure SQL is a suite of SQL database services on the Azure cloud. It offers automated backups, high availability, and advanced security, making it ideal for traditional SQL database needs with cloud scalability and integration.

While Azure SQL provides extensive control for database management, Dataverse offers a higher level of abstraction for faster and easier development. Depending on the needs of your project, sometimes having granular control of your database is a must.

This recipe will help you build an Azure SQL database that might be needed for an app.

Getting ready

To start working with Azure SQL databases, you will need an Azure subscription. You can request a free version with credit for 30 days to test all the services available on this cloud platform. Apply for it at https://azure.microsoft.com/en-us/free.

How to do it…

Go to the Azure portal at https://portal.azure.com and click Create a resource. This action will take you to Azure Marketplace. Using the search field, enter SQL Database and hit Enter.
From the list of services, select SQL Database and then click Create. This action will open the Create SQL Database page, as seen in the following screenshot:

Figure 1.9: Creating an SQL database from the Azure portal

Select your subscription and a resource group (create a new resource group if necessary). Enter the database name in the Database name field and select a server.

If you don’t have an existing server, click the Create new link below the servers list and enter the required information: a server name and a location close to you for better response times. Leave the default Use Microsoft Entra-only authentication as the Authentication method and click Set admin to look for one user, group, or enterprise application to act as the database administrator. After choosing one, click Select and then OK to create the server.

Select No for the elastic pool setting and choose Development for the Workload environment setting. Click Configure database on the Compute + storage section, change the Service tier to Basic (For less demanding workloads), and click Apply. The Estimated storage cost/month in the Cost summary section on the right should be around USD 5.
Select Locally-redundant backup storage in the Backup storage redundancy section.
Go to the Networking tab and select Yes to Add current client IP address.
Click the Additional settings tab and select Sample from the Use existing data section. Click OK on the dialog that appears.
Select the Review + create tab and then click on Create. This action will start the provisioning of the database.

How it works…

Once the provisioning process is completed, you will be able to access your database.

You can use Azure Data Studio at https://learn.microsoft.com/en-us/azure-data-studio/download-azure-data-studio or the Azure portal itself.

Accessing it through the portal is straightforward as you don’t need to install any software. Just navigate to your database, and from the left pane, click Query editor (preview) and select Continue as <your username> from the Microsoft Entra authentication section.

After authentication, you will be able to interact with the database, as seen in the following figure: