Microsoft Power Platform

Developer
1. Use advance techniques in canvas apps to perform custom
updates and optimization
2. Automate a business process with expressions and
Dataverse actions using Power Automate
3. Introduction to developing with Microsoft Power Platform
4. Extend the user experience with client scripting and
command bar customization
5. Build basic code components with the Power Apps
Component Framework
6. Extending Power Platform Dataverse
7. Integrate with Dataverse and Azure
Use advance techniques in canvas apps to
perform custom updates and optimization
Imperative versus declarative development
This module includes some concepts that might appear to be very
developer focused but don't worry. The goal is to help you understand and
apply some of these concepts to build better apps.

Imperative versus declarative

There are two main ways to approach development:

Imperative development focuses on how to achieve a goal. Declarative

development focuses on what result you want.

Imperative development gives you more control because you define each
step of the process. That flexibility often comes with more complexity.
Declarative development is simpler and easier to read, but it can limit how
much control you have over execution.

Imperative

To better understand imperative programming think about the sandwich

that you want for lunch. In imperative programming, you focus on creating
the sandwich in your "code." You go to the kitchen, get the ingredients,
put the sandwich together, and then send it to the user. You spend
numerous time on the steps, but you have all of the specific functions you
want to make it exactly the way you want. No tomatoes? No problem. In a
completely made up programming language, your code might look
something like this.

Function Create Sandwich

{ Go to kitchen;

Get ingredients;

Remove tomato;

Assemble sandwich;}

Function Send Sandwich

{ Destination Mouth;}

As you can see, there are lots of steps in the process, but you get a
sandwich exactly the way you want. This is the approach you see in
languages like C# or other popular coding languages. The focus is on
pushing the data.

Declarative

For declarative programming, think of the same scenario, your sandwich

for lunch. The difference is now you're focused on producing the
sandwich, not how to make a sandwich. This is much less complex, but
you might also run into the issue with tomatoes. If the function you use to
get the sandwich doesn't support the option of no tomato you're out of
luck. Your code may be as straight forward as follows.

GetSandwich(Kitchen, Mouth)

That made up function takes two inputs, where to get the sandwich from
and where to send it. There was no option to remove tomatoes. It would
be up to the creator of the GetSandwich function to add an option for no
Tomato, which might look like this.

GetSandwich(Kitchen, Mouth, {Tomato: false})

Low-code tools like Excel use this approach to development. The focus is
on pulling data.

Power Apps supports both imperative and declarative

methodologies

Power Apps has capabilities for both imperative and declarative logic.
Throughout this training, there has been a focus on declarative formulas.
In the remainder of this module, the focus is on imperative concepts and
methodologies in Power Apps. The key component of imperative logic in
Power Apps is variables.

The three types of variables in

Power Apps
In Power Apps, you can use variables to temporarily store information that
you need to reference while an app is running. Common scenarios include
keeping a running count, managing UI state, improving performance by
avoiding repeated calculations, or temporarily storing lists of data.

Variables are a key driver for imperative logic in Power Apps because they
allow you to "build the sandwich" piece by piece.

To support you in these needs, Power Apps has three different types of
variables.
  Global variables -- The most traditional type of variable. You use
the Set function to create and set its value. Then you can reference
its values anywhere within your app. A common use is to store a
user's DisplayName when the app loads and then reference the
variable throughout the app.
 Context variables -- A context variable is only available on the
screen where you create it using the UpdateContext function.
Context variables are commonly used for functionality that controls
a pop-up screen, for example, where you want to use the same
variable name on multiple screens but maintain the value
separately.
 Collections -- A collection is a special type of variable for storing a
table of data. You can create the collection manually or by loading
another data sources table into it. Collections are available
throughout your app, like global variables, and they're created using
the Collect or ClearCollect function.

When choosing which type of variable to use, consider where you'll use it
and the structure of the data you want to store. When in doubt, use a
global variable as it has the most flexibility.

How all of the variable types are the same

With Power Apps, variables are easy to use. You don't have to initialize,
declare, or type a variable. You create the variable with the appropriate
function, and Power Apps does the rest. When you assign the value to a
variable Power Apps will automatically determine the type.

It's also important to note if you're new to variables that variables are
temporary and only available to the current user in their current session.
When the user closes Power Apps, all of the information stored in
variables is no longer available. If you need to store information for use
later or by other users, then you'll need to write that information to a data
source. Variables are temporary by nature.

Global variables
Global variables are the most commonly used variables because of their
flexibility. After you set the variable, you can reference it or update it
throughout your app. This allows you to avoid repetitive query for the
same information repetitively, to build out the information you need in an
imperative way, or sometimes just as a place holder.

Storing information for your user

A common design pattern in apps is personalization. For example, you
might display a welcome message that includes the user’s name on every
screen. In Power Apps, you can retrieve the user’s name in a declarative
way by using the following formula in a Label control:

"Welcome " & User().FullName

This formula displays the string Welcome and then queries Azure Active
Directory for the user's DisplayName property and displays it as text. But
if you include that function on every screen then each time a screen
opens Power Apps has to query that data from Microsoft Entra ID directly.
This creates repetitive calls to the network that slow down your app.

A better approach would be to store that information in a global variable

when the app opens, and then reference that variable throughout your
app. You could do this by Modifying the OnStart property of the app with
the following formula.

Set(varUserDisplayName, User().FullName)

Now for your Label control, you would change the formula to the following.

"Welcome " & varUserDisplayName

This formula gets you the same output as the previous formula, but
instead of having to go back to Microsoft Entra ID on every screen, Power
Apps can reference the value stored in the variable.

Tracking status in a variable

In a declarative mindset, you might hide or show controls based on a
query for data. For example, if you had an app for managing customer's
orders, you might have a warning icon that displays only if the customer
has more than three outstanding invoices. In addition to the warning, you
might have a requirement to get approval from a manager if the customer
would like to submit a new order when they have more than three
outstanding invoices. This approval workflow starts by the user selecting
an approval button.

With a declarative mindset, you would set the Visible property for the
warning icon to the following.
CountRows(Filter(InvoiceEntity, CustomerNumber = ThisCustomersNumber
And Status = "Outstanding")) > 3

If that's true, then the icon displays, and if it's false the icon doesn't
display. You would then repeat that same formula on the Visible property
of the Approval button.

The problem is this becomes a complex formula that you maintain in two
different locations, and that query will generate duplicate network traffic,
processing in the app, and processing on the data source.

A better approach is only to run the complex call once, store the result in
a variable, and then use that variable to control the Visible property of
each control.

To do this, configure the OnVisible property of the screen to set the

variable.

Set(varOustandingExceeded, CountRows(Filter(InvoiceEntity, CustomerNumber =

ThisCustomersNumber And Status = "Outstanding")) > 3)

The variable varOutstandingExceeded is either true or false based on

the result of the formula. Now set the Visible property of the icon and
button control to varOutstandingExceeded.

No additional formula or functions are necessary. This is because those

controls accept either true or false for the Visible property and the
variable will be either true or false. Based on your Set function in
the OnVisible property of the screen, Power Apps will set the type of
variable to Boolean and set the value to true or false based on the result
of the formula.

Small changes like this make your app both more performant and easier
to maintain. You should incorporate variables anytime you're repetitively
retrieving information that isn't going to change while you're using it.

Contextual variables
Contextual variables are similar to global variables except they're only
referenced on the screen where you create them. Although it’s possible to
set the user's name to a variable to reference throughout your app, there
are still advantages to the fact that contextual variables can't be used on
other screens.

Sometimes you have functionality you want to use on multiple screens

that is variable driven. For example, many apps use pop-up dialog boxes
to confirm things like deleting a record. A common way to implement is to
set a Contextual variable to true when the user selects the delete button.
You do that by setting the OnSelect property of the button to the
following.

UpdateContext({varShowPopUp: true})

You then set the Visible property of the pop-up controls

to varShowPopUp. This is similar to the example from the global
variables. The major difference is reusability. If you copy the controls
(using Ctrl+C) to another screen, then you have two instances
of varShowPopUp. These two instances use the same name, but can
have different values. The value of varShowPopUp on screen1 doesn't
affect the value of varShowPopUp on screen2 because each contextual
variable, even when they have the same name, are scoped to the screen
they are on.

Typically reusing variable names isn't recommended because it can be

confusing, but it’s great if you want to reuse functionality independently
on different screens.

If you are in doubt about whether you should use global or contextual
variables, typically global variables are the default answer. Global
variables are available everywhere, making them the most flexible.

One unique behavior of the UpdateContext function is that you can

declare more than one variable at a time. This isn't possible with
the Set function. To create more than one context variable with a single
formula, use a comma between the variables.

UpdateContext({varCount: 1, varActive: true, varName: User().FullName})

Note

In the previous module, we mentioned that using the User() function inline
will unnecessarily slow down your app and for that reason a global
variable should be used OnStart. While using a contextual variable to
store User information improves performance over inline use of the User()
function, it's ultimately less performative than a global variable, as you'll
still be calling on the data source each time that page opens. As such, it's
recommended to store User information in a global variable.

To do the same thing with Global variables, you would use the following.

Set(varCount, 1);Set(varActive, true);Set(varName, User().FullName)

In the next unit, you'll learn about storing tables of data in a collection
variable.

Collections
Collections are useful when you need to temporarily store structured data
for reuse within your app. This data can come from a data source, be
created within the app, or be a combination of both.

Using collections to increase performance

One common reason for using collections is to reduce repeated calls to
the same data source. For example, if your app needs to reference a list
of active projects multiple times, you can retrieve the data once and store
it in a collection. To store a copy of the Projects table in a collection
named collectProjects, use the following formula:

Collect(collectProjects, Projects)

This creates a collection named collectProjects that will have the same
rows and columns as the Projects table from your data source. Here's a
couple of considerations that you need to understand about using
collections:

 The Collect function isn't delegable. This means by default only the
first 500 records from the data source will be retrieved and stored in
the collection. For more information about working with delegation,
see Work with data source limits (delegation limits) in a Power Apps
canvas app
 Collections aren't linked to the data source after you create them.
This means changes to the data in the collection don't automatically
save to the data source. This includes changes you have made to
the data. If you want to update the data source based on your
changes to the collection, you'll need to build formulas to do so,
such as recollecting from the data source.
 Collections are temporary. When you close the app, the collection
and all of its contents are removed. If you need to store collection
data, you need to write it to a data source before closing the app.

Using dynamic collections

Collections don’t have to come from a data source. You can also create
collections directly within your app. This is commonly done to populate
drop-down controls, combo boxes, or to stage data before writing it to a
data source.

The following formula creates a collection named collectColors:

Collect(collectColors, {Name: "Shane", FavoriteColor: "Orange"},

{Name: "Mary", FavoriteColor: "Blue"}, {Name: "Oscar", FavoriteColor:
"Yellow"})
Name FavoriteColor
Shane Orange
Name FavoriteColor
Mary Blue
Oscar Yellow

After creating a collection, you can reuse it throughout your app and apply
standard table functions such as Filter, Sort, and CountRows.

One important limitation is that collections can’t be used directly with the
Form control, even though they store tabular data.

For more information about working with collections and the table data
they store, see Author a basic formula that uses tables and records in a
Power Apps canvas app.

Additionally, collections store table data no differently than tabular data

sources. The learning path Work with data in a Power Apps canvas
app has many concepts that allow you to work with and extend the power
of your collections.

Additional variable concepts

Now that you have an understanding of Power Apps logic concepts and
variable types, there a few additional concepts to expand on that will help
you integrate variables into your app.

Variables can self-reference

This concept applies to both global and context variables. Sometimes you
need to make a variable that points to itself. This is often done when you
want to either do a counter type variable where it increments a value or
you're appending a string. With Power Apps this is easy to implement.
Place the following formula on the OnSelect property of a button to set
up a counter

Set(varCounter, varCounter + 1)

Next to the button put a Label, and in the Text property,

put varCounter. The first time that you select the button your value will
be 1. If you select the button a second time the value will be 2. Use the
following table to see the literal translation.
Value of varCounter before the button Button Formula Values
press press
0 First Set(varCounter, varCounter + Set(varCounter,
1) 1)
1 Second Set(varCounter, varCounter + Set(varCounter,
1) 1)
2 Third Set(varCounter, varCounter + Set(varCounter,
1) 1)

When the app first starts, the value of varCounter is 0, and it's
incremented by 1 each the time the button is selected. It's important to
remember that the default value of a variable varies based on the variable
type if you don't set the default property.

 Text variables are ""

 Number variables are 0
 Boolean variables are false

A variable can store a single record

This concept applies to global and context variables. Collections differ
slightly because they're a table made up of one or more records, meaning
storing and retrieving a record is different for a collection.

In the previous units, you learned how to store a single value in a global or
context variable. You can also store a record in the variable. When you do
this, you can then reference the different fields or columns by using the
dot (.) notation.
In this example, you'll store the entire user record in a global variable
named varUser. To do so, use the following function.

Set(varUser, User())

This stores the entire user record in the variable. The user record has
three columns Email, FullName, and Image. You can retrieve the values of
the individual columns using the dot (.) notation. To display the user's
email address, add a Label control to the screen and set the text property
to:

[Link]

This example stores the record from an action-based data source. You
could also use the LookUp function as a way to retrieve and store a
record from a tabular data source, like Microsoft Dataverse, in a variable.

Variables don't auto update

A common point of confusion for people who are new to variables is that
variables don't automatically update. For example, they can use a
variable to store the number of customer invoices using OnStart for the
app. Then in the app, the user creates a new invoice. The variable doesn't
distinguish the number of invoices in the system that have changed. The
variable will only update when:

 The user closes the app and then opens it again. This causes
OnStart to perform the operation to calculate the number of
invoices.
 You implement functionality to update the variable after the user
creates an invoice.

Be aware of this common point of confusion if you're new to using

variables to track data.
Perform custom updates in a
Power Apps canvas app
Sometimes you need something more than
forms

When building canvas apps in Power Apps, you go to Galleries to display

records from your data source and Forms to view, create, and edit an
individual record, but sometimes forms are not enough. In those
scenarios, Power Apps has functions for updating your tabular data
sources directly.

Directly create and edit a record

In this module, you will learn about using the Patch function to update
your data sources without the use of forms directly.

Patch is most often used when you need to take action on the data
without user interaction in a repetitive manner, or your app design doesn't
allow for the use of forms. For example, if you want to update a logging
data source every time a user clicks a button to navigate to another
screen you could use the formula for the OnSelect property of the button.

Patch(LoggingTable, Defaults(LoggingTable), {WhoClicked:

User().FullName, WhenClicked: Now()}); Navigate(NextScreen,
[Link])

This formula would create a new record in the data source

named LoggingTable. The WhoClicked column sets to
the FullName property of the user who is signed in, and the WhenClicked
column sets to the Date and Time of when they clicked the button. This
would open the screen named NextScreen using the Cover screen
transition.

Delete a record
There are also functions available for deleting one or more records from
your data source. Those functions are:

 Remove and RemoveIf - These functions are used to remove or

delete records from the data source.
 Clear - Use the Clear function to remove all of the records from a
collection.
For example, if you wanted to give the user the ability to delete a record
from a Gallery control, add a Trash icon to the Gallery displaying the data
source CustomerOrders and then set the OnSelect property of the icon
to the following.

Remove(CustomerOrders, ThisItem)

This formula would delete the record for the item that was displaying the
Trash icon from the CustomerOrders data source. There would be no
confirmation, so you might consider implementing a check or pop-up
dialog to confirm that the user truly wants to delete the record.

Bulk changes to records

Patch and Remove are both functions that are used to affect one record. If
you need to affect change on more than one record, there are two
options:

 Use the ForAll function, which was covered in the previous module,
to loop through a table of data and run a Patch or Remove function
for each record in the table.
 Use the Collect function to write from one table to another. Each
record of the source table is added as a separate record to the
target table.

These topics are covered in other Power Apps learning paths and are not
covered in this learning path.

Collections are data sources

It’s important to remember that these functions can use a collection as
their target. Patch, Remove, and RemoveIf can all be used to modify both
tabular data sources and collections. As you build more complex apps
storing data in collections and working with those items is very common,
these functions will be a big part of that manipulation.

The remainder of this module will refer to updating a data source.

Remember that a data source can either be a tabular data source or a
collection unless stated otherwise.

Use the Patch function to create

and edit records
The Patch function is used to create and edit records in a data source
when using a Form control doesn't meet your needs. Patch is used most
often when you need to act on the data without user interaction in a
repetitive manner. You also use it if your app design doesn't allow for the
use of forms.

Use Patch to create a record

The Patch function can be used to create a new record in your data
source. To create a new record, there are three parts to the formula.

1. Include the name of the data source you want to edit. This could be
a tabular data source (such as Microsoft Dataverse or SharePoint) or
a collection. For the example, you use CustomerOrders as the
name of the data source.
2. The Defaults function returns a record that contains the default
values for the data source. If a column within the data source
doesn't have a default value, that property isn't present. By using
Defaults with the data source, this notifies Patch to create a new
record.
3. Include the columns that you want to populate in the new record.
Here you specify the name of the column to update followed by the
value to write to that column. For this example, you update the
Region and Country column with a string value.

The example formula is as follows:

PowerApps Formula
Patch(CustomerOrders, Defaults(CustomerOrders), {Region: "Americas",
Country: "Canada"})

This formula creates a new record in the CustomerOrders data source

and sets the Region to Americas and Country to Canada. Notice that you
don't define any primary key information (the ID column) that the data
source updates according to its settings.

Use Patch to edit a record

It's also possible to edit a record in the data source. To edit a single
record, there are three parts to the formula.

1. Include the name of the data source you want to edit. This could be
a tabular data source (such as Dataverse or SharePoint) or a
collection. For the example, you use CustomerOrders as the name
of the data source.
2. The record that you want to edit in the data source. The most
common way to specify this record is to use the LookUp function to
retrieve the record from the data source. Another option if you use a
Gallery and you want to update the current record is to use
the ThisItem function to reference the record. For this example,
you use a LookUp function.
 3. Include the changes that you want to make. Here you specify the
name of the column to update followed by the value to write to that
column. For this example, you update the Region and Country
column with a string value.

The example formula is as follows:

PowerApps Formula
Patch(CustomerOrders, LookUP(CustomerOrders, ID = 1), {Region: "Asia",
Country: "China"})

This formula updates the record with an ID of 1 in

the CustomerOrders table by setting the Region column to Asia and the
Country column to China. If there are existing values in those fields, it is
overwritten.

Update columns with Patch

The primary logic of most Patch functions is updating the proper columns
with the correct information. This is the source of most of your
troubleshooting of the Patch function. Use the following points to help you
work through Patch.

 Make sure you update all of the required columns from your data
source.
 You can update as many or as few of the optional columns as you
would like.
 Make sure your column names are spelled and capitalized correctly.
Column names are case-sensitive.
 Make sure you write the correct data type. For example, if your
column in the data source is a number type, then you can't write a
string value to it, even if that string contains a number.

There are four sources to pass values in your formula to Patch your data
source:

 You can hardcode a value. An example is if you want to patch the

status of the record with "Pending," your Patch formula would look
like:

PowerApps Formula
Patch(CustomerOrders, Default(CustomerOrders), {Status: "Pending"})

This formula creates a new record and sets the Status column to the
string value of "Pending."

 You can reference a variable. For example, you can store the string
"Under Review" in a variable named varStatus with the following
formula.
 PowerApps Formula
Set(varStatus, "Under Review")

Then your Patch formula would be:

PowerApps Formula
Patch(CustomerOrders, Default(CustomerOrders), {Status: varStatus})

This formula creates a new record and sets the Status column to the
string value of "Under Review."

 You can reference the value from the property of a control. An

example would be setting the value from a drop-down menu named
Dropdown1 that contained the regions. Your Patch formula would
look like:

PowerApps Formula
Patch(CustomerOrders, Default(CustomerOrders), {Status:
[Link]})

This formula creates a new record and sets the Status column to the
value of the selected item in the drop-down menu.

 You can use the output of a formula. An example would be setting

the value of the Owner column using the FullName from
the User() function. Your Patch formula would look like:

PowerApps Formula
Patch(CustomerOrders, Default(CustomerOrders), {Owner:
User().FullName})

This formula creates a new record and sets the Owner column to
the current user's FullName from Microsoft Entra ID.

Patch example
Let's take a look at another example, in this example you're trying to build
a solution for signing users into class as they arrive. This type of Power
Apps solution is common, and the Patch function helps you quickly
achieve results.
Solution breakdown

Here we have a simple Canvas app connected to our data source

(TrainingClassSignIn). The data source has the following
columns, Training
Class, FirstName, LastName, EmailAddress, SignInStatus. This is the
information we want to capture when a user selects the Sign In button. In
the formula bar, you see the following code:

PowerApps Formula

Patch(TrainingClassSignIn,Defaults(TrainingClassSignIn),
{TrainingClass:[Link],
FirstName:[Link], LastName:[Link],
EmailAddress:[Link], SignInStatus:"Attended"})

To elaborate, whenever someone selects the Sign In button, Power Apps

writes a new record to the TrainingClassSignIn data source. As to what
data is written back for the user signing in, you can see it's getting this
information from the different controls we added (color coded in the
formula). You might also notice that the code sets the SignInStatus each
time to "Attended" for each new record submitted.
Delete records from data
sources and collections
In this unit, we're going to cover the concept of deleting a record from a
tabular data source or collection. Unlike creating and editing records,
which have multiple controls and functions, for deleting records there are
only a few options including the Remove, RemoveIf,
and Clear functions. We most often add these functions to
the OnSelect property of a button or icon control to delete a record.

Delete a record
To delete a record from your data source, use the Remove function. Use
the Remove function to specify the data source and the record that you
want to delete. The most common way to specify this record is to use
the LookUp function to retrieve the record from the data source. Another
option is if you're using a Gallery and you want to delete the current
record, the ThisItem operator points to the record.

For example, you could use the following formula to delete a record.

PowerApps Formula
Remove(CustomerOrders, LookUp(CustomerOrders, ID = 1))

This formula deletes the record where the ID equals 1 from the data
source CustomerOrders.

Remove doesn't ask to confirm

Remove doesn't prompt for any confirmation before deleting the specified
record. If you would like to confirm that the user wanted to remove the
record, you need to create confirmation functionality, such as a pop-up
dialogue box with buttons.

Delete based on a condition

If you want to delete more than one record from your data source, you
can use RemoveIf. The RemoveIf function allows you to provide a data
source to delete from and a condition for selecting the records to delete.
This is the same logic that is used by the Filter function.

For example, you could use the following formula to delete all of the
records where the Status equals Expired from the CustomerOrders data
source.
PowerApps Formula
RemoveIf(CustomerOrders, Status = "Expired")
Delete all of the records
It's also possible to delete all of the records in a data source. This is most
common with collections where you can use the Clear function. If you
want to delete all of the records from a data source, you can
use RemoveIf.

Delete all of the records in a collection

The Clear function deletes all the records of a collection. The columns of
the collection remain. The only input you pass to the function is the
collection name.

For example, you could use the following formula to delete all of the
records from a collection called collectSelectedItems.

PowerApps Formula
Clear(collectSelectedItems)

This formula deletes all of the records from the collectSelectedItems

collection without changing the columns of the collection.

You typically see this type of formula when you want to clear out the
collection without having to redefine it, like in the case of a reset button or
selecting a new order. When working on collections, you also have
the ClearCollect function.

The ClearCollect function deletes all the records from a collection and
then adds a different set of records to the same collection. With a single
function, ClearCollect offers the combination of Clear and then Collect.

All three functions have their place. One way to think about whether you
want to use Clear and Collect versus ClearCollect is when the clearing of
the collection happens, compared to when you want to add records back.
Here are two examples to illustrate:

 All at once - For example, if you're reloading the items in a

collection for a drop-down menu when a screen becomes visible,
you would want to use ClearCollect. A single ClearCollect function
in your formula removes the old records and immediately adds the
new records.
 Multi-step - For example, if you're using collections to store user
inputs like in a shopping cart you can use Clear and Collect. This is
because the user might want to clear their shopping cart without
adding a new record.
Delete all of the records from a data source

It's possible to delete all of the records from a data source

using RemoveIf. This isn't a common scenario. Again there's no
confirmation before the formula processes unless you build such
functionality. Finally, there's no undo or recycle bin in Power Apps. If you
want to recover your data, you would need to go to your data source and
use whatever recovery process is available for that data source, outside of
Power Apps. Proceed with caution.

For example, you could use the following formula to delete all of the
records from a data source.

PowerApps Formula
RemoveIf(CustomerOrders, true)

This formula deletes all of the records from the CustomerOrders data
source without changing the columns of the data source.

The reason this works is RemoveIf checks every record in the data
source to see if the equation equals true. In this case, the equation is set
to true, so every record is deleted.

Note

Setting the equation portion to true also works with the Filter function.
This can be a valuable setting if you are trying to troubleshoot formulas
where you are not sure if Filter is returning data.

Use the Patch function to update

a Gallery
his hands-on lab shows you how to use the Patch function in a gallery.

1. Sign in to Power Apps.

2. From the Home screen select + Create from the left-hand
navigation panel, then select Blank app and then Create under
Blank canvas app.
3. Name your app "Patch Exercise" or another appropriate title of your
choice and select Create.
4. Select the Insert button and add a Button control, and set
its OnSelect property to this formula:

PowerApps Formula
ClearCollect(CityPopulations,
{City:"London", Country:"United Kingdom", Population:8615000},
{City:"Berlin", Country:"Germany", Population:3562000},
{City:"Madrid", Country:"Spain", Population:3165000},
 {City:"Rome", Country:"Italy", Population:2874000},
{City:"Paris", Country:"France", Population:2273000},
{City:"Hamburg", Country:"Germany", Population:1760000},
{City:"Barcelona", Country:"Spain", Population:1602000},
{City:"Munich", Country:"Germany", Population:1494000},
{City:"Milan", Country:"Italy", Population:1344000})

5. Set the button's Text property to "Collect". Then press and hold Alt
Key, and select the button. (This action creates the CityPopulations
collection and stores the data.)
6. Insert a Vertical gallery control and choose CityPopulations from
the data source.
7. With the gallery selected, in the right side Properties panel,
change the layout from blank to Title, subtitle, and body.
8. Also in the Properties panel, select Fields and update
the Body field in the data to display Population.
9. Insert a Text input control. From the Properties panel, set
the Accessible label property to "Country," and in the Tree view,
right-click and select Rename to rename the text-input to
"tiCountry."
10. Again, insert a Text input control. From the Properties panel,
set the Accessible label property to "text input," and in the Tree
view, right-click and rename the text-input to tiCity.
11. Repeat the previous step with a third Text input, naming it
tiPopulation and set the Accessible label property to "Population."
12. Select the Insert tab add a button control, set
its Text property to "Patch Country" and set its OnSelect property
to this formula:

PowerApps Formula

Patch(CityPopulations,Defaults(CityPopulations),
{Country:[Link],City:[Link],Population:Value([Link])}
)

13. Align your controls like the image below:

14. Before we move on, notice that the formula bar has
a Copilot icon on the left side. If you're relatively new to Power
Apps, or you're trying to figure out what the code in a formula
means, you can select the Copilot icon and ask it to "Explain this
formula." When you do, you should see something similar to the
image below.

The explanation has a Copy button where you can copy the text of
this answer and then add it to your code as a remark. Add the
double forward "//" slashes to add comments to your code like this:

Power
//This expression is used to patch a new record to the
'CityPopulations' data source. It takes the default values from the
data source, and updates the 'Country', 'City', and 'Population' fields
with the values entered in the 'tiCountry', 'tiCity', and 'tiPopulation'
controls respectively.

15. Now let's add more Countries/Regions to our Gallery. Put the
app in Preview mode.

16. In the Country text input, enter USA. In the City text input,
enter Orlando. In the Population text input, enter 280832.

17. Now press your Patch Country button and scroll down to the
bottom of your gallery; you should see the new record you just
added by using the Patch function formula from the OnSelect
property of the button.
Use Dataverse choice columns
with formulas
Introduction
A common requirement in business app data storage to help ensure data
consistency is a data column with a standardized, infrequently changing
list of values. Users enter data by choosing an option from a list instead of
typing a free-form value. Examples of these options include lists such as
countries/regions, incident priority, and satisfaction rating.

Microsoft Dataverse has a column type that supports this functionality,

the Choice column. Choice columns allow a user to pick values from a list
when entering data. By default, users can pick a single choice, but the
column can be easily configured to allow multiple values from a single list
of known values. For example, Primary Category could be a single
choice column, Other Categories could be a multiple choice column, and
both could use the same list of categories.

You can either use system-defined values or a custom list of values for
choice columns. The choices are stored as table column metadata and can
only be modified by an app maker, not an app user. This feature is
beneficial when building an app that works with a list of values, as your
formulas can reference the choice list name and the display names of the
values. For example, the following formula sets a color on a gallery item's
text Color property based on the Category column, which is a choice
column.

As you compose this formula, Power Apps will know the possible values for
the Category column, and will allow you to select the value to compare
from the list of known values for the choice column as soon as you insert
the "." after the column name.

Note
If you add or change values on a choice list and the new or changed
choices don’t immediately show in the editor, refresh the table in the data
panel. Refresh the table on the data panel by selecting the ellipsis (...)
next to the table, and selecting Refresh from the pop-up menu. Repeat
until your new values show.

Local versus global

You can create a list of available choice values as a local or global
list. Local choice lists can only be used in the column and table where
they’re created. The Global choice option allows the list to be used in
multiple columns, either in the same table or in many different tables.
When you create a new Choice column, the default value for the
option Sync with global choice? is Yes (which is recommended).
The Global choice option allows use of the same choices in other tables.
Unless the values only apply to a single column in a single table, use
global choice values.
With Yes selected, notice how the Sync this choice with becomes
a mandatory field, and you must make a selection for the choices.
When you select this field, a pane showing the various options
available will appear next to the new column pane. You can either
type in the choice-type you're looking for, or scroll through the list to
select one. Also, notice that when you hover over any of the choice
 sets, you get a preview of the choices that are already part of that
choice set.

It's also possible to create a new choice set by selecting the + New
choice, and by entering information on your choices.

Notice that you can also assign a Default choice for your column as you
create it. Don't worry, you can come back later and set or change the
default choice.

If you determine that you want to keep your choice options as

a local choice set, you would select No under Sync with global choice?.
In this case, you would define your choices just below the No button. (At
least one is required.) You'll just need to add a Label for the choice;
Power Apps will automatically assign an integer value for that item. You
can change the value, but it's not recommended. You can also select a
color for each choice by selecting the box just to the left of
the Label name. In the Select color popup, you can hard-code the color
value through the entry fields for the Hex or the R-G-B values.
Alternatively, you can adjust the color slider bar and fine-tune the color
slider by looking at box above the slider. The color that you select is
available only in model-driven apps.
You add more choices by selecting the + New choice button and
repeating the process.

If you need to allow the user to select multiple choices, you must check
the "Selecting multiple choices is allowed" box before you finish creating
the column, as this option will be disabled once you create the column.

Display column values

When the data for a choice is stored in a Dataverse row, only the numeric
value is stored, not the text. For multiple choices, a comma-separated list
of numeric values is stored to represent multiple selections.

How you display values in a control, such as a label, is different for single
and multiple choice values. Choice fields can be used to set the value of a
label to display the list text value. For example, if you had
a Category choice field for the category of customer, you could display
that field in a label in a gallery by using the following formula.

For multiple choices columns, the property on the record is of type Table.
It's a single column table with a Value column, with each row representing
a selected value. To display a user-friendly, comma-separated list of text
values, some preprocessing is required. For example, if you had
a Preferred Delivery column that allowed users to choose one or more
weekdays for delivery, you'd use the following formula to set
the Text property on a label.
This formula would result in the following display of the list of selected
values.

Choice vs. lookup

One common data modeling decision is choosing between a choice
column and a lookup column or between multiple choices and a many-to-
many relationship. There's no right or wrong answer. However, your
decision affects how you manage the list of values and the formulas that
you can apply. Consider the differences that are summarized in the
following table.

Remember that after you've created the column, you can't change the
data type. So before you create the column, consider how the apps,
automation, or reporting will be using the data.
Filter Dataverse choice columns
with Power Fx formulas
When you have a Dataverse table with a choice column, you’ll often want
to filter data using that choice column. The most common filtering
scenarios are:

 Filter the table rows for display in a gallery.

 Have a dropdown menu or combo box control with the list of choice
values, and then let the user select one or more. Then, you can use
the selected values to filter the table rows that you show in the
gallery.

For example, if you have a Category choice column on the Accounts

table, you can use the following logic to filter only preferred customers:

PowerApps Formula
Filter(
Accounts,
Category = 'Category (Accounts)'.'Preferred Customer'
)

Be sure to use the full [Link] string, and not just the column string,
'Preferred Customer' (like the image below), because it's an invalid
comparison. If you forget to add the table to the string, you see the
'Incompatible types for comparison' error. Since the 'Category (Accounts)'
is a table (or option set) itself, you can't compare the table value with a
text value. Therefore, you need to use the fully qualified
reference: 'Category (Accounts)'.'Preferred Customer' (also displayed
as [@Category].'Preferred Customer').

Frequently, you use a dropdown or combo box to filter a gallery so users

can select which categories of items to display. In the following example,
you'll use a combo box to allow users to make multiple selections of
account categories to show in the gallery.
First, add a combo box to the screen, and then set the Items property by
using the Choices function.

The Choices() function prepares a list of values for your user to select
from by using the metadata for the choice column [Link].

Next, you modify the Items formula for the gallery to include using the
combo box SelectedItems property.

Filter(Accounts, Category in [Link])

Using the in operator allows the formula to filter on any of the selected
categories.

This formula will only show rows in the gallery when at least one category
is selected. If you want to show all rows when no categories are selected,
you could add an IsEmpty check to your formula.

PowerApps Formula
Filter(
Accounts, Category in [Link]
|| IsEmpty([Link])
)
Filter choices columns
Filtering table rows on a choices column is complicated by how the data is
stored in Dataverse as a comma-separated list. As a result, any filter that
you compose that involves a choices column receives
a delegation warning, as illustrated in the following example.
One approach that you could take to avoid the delegation issue is to
create a Dataverse table view that filters the choices values and then use
the view in your Filter() function criteria. This approach would help you
avoid the delegation warning, but it won't allow the app user to provide
the filter criteria values. The following screenshot shows an example of a
Dataverse view filter for the Preferred Delivery choices column.

You could use this Dataverse view named Monday Delivery by using the
following Filter() function:

PowerApps Formula
Filter(
Accounts,
'Accounts (Views)'.'Monday Delivery'
)

Additionally, you can still include user-entered criteria for columns other
than the Category. For example, the following Filter() function shows the
addition of the Category choice column, which is filtered on the value
that the user selected from the dropdown list.

PowerApps Formula
Filter(
Accounts,
'Accounts (Views)'.'Monday Delivery',
Category = [Link]
)

Because of their standardized list of values, choice and choices columns

are useful in providing consistent ways to filter table rows.
Modify choice and choices values
The simplest way to set a choice column value in a table is to use an Edit
form. When you add a choice column to the form, it sets up the field to
complete the following tasks:

 Set up as either a dropdown menu (for a single choice) or a combo

box (for more than one choice). (When you add an Edit form,
Power Apps will set up both with a combo box.)

 Use the Choices() function to populate the list of values that the
user can select. (Again, this is done automatically when using
an Edit form).

 Set the control's current value from the row's column value.

 Save the value to the table by using the SubmitForm function on a

control, such as a button.

When a column is added to the form, it's editable by default. If you want
to display the choice column on the form but not have it editable, you can
select the control's DisplayMode and change it to [Link].
(You may need to first navigate to the Advanced tab in
the Properties pane on the left side of the canvas and Unlock to
change properties.)
Use Patch to create or update
You can also create or update choice column values by using the Patch()
function. This approach is suitable when updating only a few fields that
require little or no user input. For example, on a gallery item you can have
a button that, when selected, will use the OnSelect behavior to run a
Patch() function to update the row.

The following example shows that a button has been set up on the gallery
item to make the account a preferred customer. When the button is
selected, the goal is to set the row's Category choice field to Preferred
Customer. To accomplish this task, we've added a Make VIP button to
the gallery item.
The OnSelect property for the Make VIP button contains the Patch()
formula, as shown below:

PowerApps Formula
Patch(Accounts, ThisItem, {Category: 'Category (Accounts)'.'Preferred
Customer'})

If your table column uses a Choice set, when you use Patch, you need to
prefix your value with the Choice set name, else you get an
'OptionSetValue' error. You can learn what the Choice set name is by
going to your Table in Dataverse, selecting that column in Edit mode, and
looking for the field Sync this choice with. Just below that column will
be the name of the Choice set.
In a case like this, your Patch() on a button in a similar table would be
similar to this:
PowerApps Formula
Patch(Accounts, ThisItem, {'Customer Type': [Link]})

Reduce complexity in your data

model with Dataverse table
relationships
Introduction
When data is modeled in Microsoft Dataverse, separate tables are used to
represent distinct objects and concepts. Organizations can use more
tables to help secure specific information, avoid data repetition, describe
other properties, or make the reporting easier. Just as real-life objects are
related to each other, the relationships are used in Dataverse to link rows
from one table to another. Additionally, relationships can provide
constraints and behaviors that apply when actions are performed on the
records.

Essentially, Dataverse tables and relationships can work together to tell

the story of your data. When building a good user experience in a canvas
app from Microsoft Power Apps by using the tables and relationships, you
would want to hide unnecessary complexities of the data model. To
accomplish that task, your formulas and data usage must efficiently
navigate the tables by using the relationships. This module will examine
how to use the different types of table relationships that Dataverse offers.

Scenario: Contoso shared workspaces

This module will use a common business scenario to demonstrate how
Dataverse relationships work with canvas apps. Contoso, like many
companies, has multiple locations and allows employees to work from
home. Occasionally, employees need to come into the office and will need
a desk to use during their visit. Previously, Contoso instructed employees
to walk around the office location on arrival to find an available desk. The
company has since discovered that this type of shared workspace system,
sometimes referred to as hot desking, has created challenges for IT
support and licensing. As a result, the company plans to build a solution
that employees can use that will allow them to view the available desks
and reserve one in advance. The team that is building the solution has
identified the following tables as part of their Dataverse data model.
Dataverse relationship types
When you create a relationship between tables, the relationship type
defines the cardinality constraints of each side of the relationship.
Dataverse supports one-to-many and many-to-many relationships.

One-to-many relationships

The one-to-many relationship (which is also called 1:N or parent-child)

includes a primary (parent) table, where you can associate an individual
row with many related (child) table rows using a lookup column in the
related (child) table. The primary row is called the parent, and the related
table rows are called child rows. You can associate a child row with only
one parent row.

A one-to-many relationship is also referred to as a many-to-one (or N:1)

relationship, where you'll use the relationship starting at the child pointing
to the parent. It's the same physical relationship definition but from a
different angle. In a canvas app, if you're working with the child record
and want to display a property from the parent, you would use the many-
to-one relationship navigation property on the child row.

By default, a single table is used as the primary, and the lookup column
always points to a row from that table. Dataverse also supports multi-
table lookups (occasionally referred to as polymorphic lookups), which
allow a lookup field to point to a row from one of the multiple tables,
providing flexibility for more complex data models. For example, you can
set up a multi-table lookup column by creating a column of data type
Customer. Then, you can set the customer lookup value to point to a
contact or to an account table row. On all activity tables (for example,
email, task, and so on), the corresponding column can point to any table
that is enabled for activity tracking. Additionally, you can use the Multi-
table lookup column type to set up a custom multi-table lookup column.
When working with these multi-table lookup columns in Power Apps, you'll
use the IsType and AsType Microsoft Power Fx functions to determine
the parent table and to use the data.

In the module's example data model, the following one-to-many

relationships have been defined to support the scenario.

When setting up a one-to-many relationship, you can also set up the

relationship behaviors. Behaviors determine what should happen when
the primary table row is deleted, assigned, shared, unshared, or re-
parented. The default behavior is reference, which is set up to remove the
link between the two tables when the primary table row is deleted. For
example, the Location table has multiple desks and uses the default
configuration, so if your canvas app deleted a location row, then by
default, all associated desks would be orphaned. If the business
requirement is to remove associated desk records when a location is
deleted, then you can select the ForAll function, which will delete all
associated desk records first. A better option is to set up the parental type
of behavior for the relationship. This behavior type automatically deletes
the associated records when the primary record is deleted. This approach
ensures that you won't have orphaned desk records.

When you're building a canvas app and using related tables, knowing how
the behaviors are set up for that relationship will ensure that you
implement the correct logic.

Many-to-many relationships
The many-to-many relationship (also called N:N) includes a special hidden
table called a relationship table, occasionally referred to as an intersect
table. This table will map how the many rows of one table can be related
to the many rows of another table. Many-to-many relationships can track
the association, but you can't modify the intersect table to add custom
columns that describe the relationship.

In this module's example, Desk and Desk Feature would have a many-
to-many relationship. The list of desk features would be shared by all
desks, and each desk could have one or more associated features. Many-
to-many relationships allow users to know that the desk has chairs but
won't let you store how many chairs that each desk had.
Work with one-to-many
relationships
One-to-many relationships are the most common Dataverse relationships
that you will work with. This unit continues the scenario regarding the
shared workspaces (hot desking) solution in Contoso. To help explain how
to work with relationships in a canvas app, the ensuing examples will use
the relationship between the Location and Desk tables. The following
diagram is a visualization of the relationship and the corresponding data.
If you want to allow a user to select a location and to have that location's
desks show in a gallery underneath, you might create a screen similar to
the following example.

As with most data sources, you could use the Filter() function to filter the
desks to only show the desks for the selected location. Your formula would
appear similar to the following example, where FilterLocation_1 is the
name of your drop down.

Because you are working with a Dataverse one-to-many relationship, you

can instead use the dot notation to reference the location's desks by
using [Link], as shown in the following formula.
In this example, both formulas produce the same list of desks that are
related to the selected location. Using the dot notation is simpler and
more concise than using the Filter() function.

When you navigate a one-to-many relationship by using the dot notation

syntax, by default you will get all related records. You can use a filter to
apply more criteria to the related rows. The following expression uses the
one-to-many relationship and also filters the results on the active status.

Filter(FilterLocation_1.[Link], Status = 'Status (Desks)'.Active)

Additionally, you can use the relationship starting from the desk row.
Consider an example where, in the gallery, you want to show the location
address for each desk. You might be familiar with using a lookup to
retrieve the location record and then accessing the address column as a
property.

Instead of using the Lookup() formula, you can use the dot notation and
reference: [Link]

You are not limited to one level of relationship navigation. For example, if
you have a location that has a related primary contact, and you want to
show the full name column, you could compose the following formula:

[Link].'Primary Contact'.'Full Name'

By using the dot notation, you can quickly include related data, regardless
of which side of the relationship that you are starting from.

Add and update related rows

The simplest way to establish the one-to-many relationship is by using an
edit form to create or update the related row. When you add the lookup
column to the form, it uses the Choices() function to present possible
values to the user. The following example shows the process of adding a
desk row where the location lookup column is added to the form.
The advanced properties on the dropdown control show how
the Items property is set up.

By using the Choices() function, you will eliminate the need to add the
lookup table as another data source. The Choices() function result is a
table, so you can add more filtering and sorting, as follows:

Filter(Choices([@Desks].contoso_Location), Status ='Status

(Locations)'.Active)

If you already had the lookup value that you wanted to set (for example
when creating a desk record from the Location screen), you could set
the DefaultSelectedItems property on the data card value and then set
the form field's Visible property to Off. This setting would allow the
default value to be passed when the SubmitForm() function is invoked.
If you are using the Patch() function to set a lookup column, set the value
of the column to a record from the primary table. The following example
shows establishing a relationship between a desk row and a primary
location row that is currently selected in the location dropdown list.

Patch(Desks, ThisItem, {Location:FilterLocation_1.Selected})

You could also achieve the same result by using the Relate() function. The
first parameter is the list of the rows (desks) that are related to the
primary row (location), and the second parameter is the row (desk) to be
added to that list or related.

Relate(FilterLocation_1.[Link], ThisItem)

Similarly, you could use the Unrelate() function to disassociate the rows,
for example removing ThisItem (Desk) from the desks that are associated
with the selected location FilterLocation_1.Selected.

Unrelate(FilterLocation_1.[Link], ThisItem)

When using the Unrelate() function, remember that it will set the value of
the primary lookup on the related record to Nothing (or null). Avoid
having rows that are orphaned because the app might not have the ability
to display the row without the primary association. In the Contoso
example, if the list of desks is displayed only as related to the location,
then any desk without a location will be orphaned and inaccessible
through the app. This situation can also occur as a side effect of deleting
the primary row when the relationship behavior property is set up to
remove the link to related rows.

Work with many-to-many

relationships
Many-to-many relationships provide you with the flexibility to track when
multiple rows have the same related data. Unlike one-to-many
relationships, many-to-many relationships don't have a concept of a
primary table. The relationship is entirely symmetrical, and you can
access the set of related rows, starting from either side of the many-to-
many relationship. To continue with the Contoso workspace-sharing data
model, the following sections explore how to work with many-to-many
relationships in a canvas app by using the Desk and the Desk
feature items. The following diagram illustrates the relationship and the
corresponding data.
Each desk can have multiple associated desk feature rows, and you can
associate each desk feature with multiple desks. You could access the set
of desk features from a desk row by using the ThisItem.'Desk
Features' expression. From the desk features row, you can use
the [Link] expression to access all desks that are associated
with that specific desk feature.

You could use this expression to show a comma-separated list of values

for each desk in a gallery, as shown in the following example.

To accomplish the task of populating the label text, set the Text property
on the label to the following formula:

Concat(ThisItem.'Desk Features', Name, ",")

Be aware of performance implications when you use this formula,
especially if you have many records, because of how the data is accessed
from Dataverse. The following image from Monitor shows that one call
to getRows is completed to get the list of desks. For each desk, a call
to getNavigatedRowInTableRow is made to retrieve the desk features.

Alternatively, you might find it more beneficial to only show the desk
features after the user has selected a single desk row in a gallery or after
they've drilled down into the details of the desk row.

Another way to use the relationship is to allow a user to pick a desk

feature and then use the [Link] property to
populate items in a gallery.

This approach works well when you only allow a single selection in the
combo box. If you enable multiple selections, the logic gets more
complex. Currently, Power fx does not have a simple way to express an
intersection of two collections, which is required to make the scenario
work. Workarounds are possible. For example, you could iterate through
all selected features, collect related desks in a single collection, remove
the duplicates, and then use the collection as the item's source. However,
because of the multiple Dataverse requests (one for each selected
feature), the performance of this approach will quickly degrade as the
tables grow.

Establish the relationship

The primary way to establish a many-to-many relationship is to use the
Relate() function, similar to how you would with a one-to-many
relationship. The main difference is that it doesn't matter which record is
the first or second parameter to Relate() because the relationship has no
primary table.

Managing many-to-many relationships on a form is more complex than

many-to-one lookup columns. The many-to-many relationship is available
in the fields list; however, when you add the field to the form, the system
doesn't generate the formulas for the control to work, and you will receive
an error similar to the following example.

To resolve the issue, update the Choices() function in the Items property
for the table that is on the other side of the many-to-many relationship. To
accomplish that task, unlock the card from the Advanced tab.
In the Contoso example, you want to use desk features. After you unlock
the control, verify that the Items property shows 'Desk Features' as the
data source.

Note

The preceding scenario uses the form to add a row. To support the edit
capabilities, make sure that you change the DisplayMode property for
the card from the default View setting to Edit.

After you have adjusted the properties, the form user interface will work,
and you can choose items from the combo box. However, if you attempt
to submit the form, you will receive an error similar to the following
example.
To work around the issue, clear the Update property and manually
process the many-to-many association after the form is submitted.

After you have cleared the Update property, the form submission will
work. However, the relationships between the Desk and the Desk Feature
table rows will not be created. To establish the relationships, add the
following logic to the OnSelect property of the check icon that is used to
submit the form by default:

1. Save the desk features that are selected in the combo box as a
collection. This step is required because the form submission will
reset the fields, and the value will be lost.
2. Submit the form.
3. Use the saved collection of the desk features to establish the
relationship.

Other design options

User experience with many-to-many relationships is similar to experiences
where the Choices column is used. Choices values are predetermined by
the maker, and they can't be disabled or secured. For that reason, Choices
fields are suitable for scenarios with rarely modified data, such as a list of
countries/regions. Moreover, rows in the related tables can be
deactivated, secured, and added at run time. That ability makes a many-
to-many relationship a good option in scenarios where some flexibility is
needed at runtime, such as when you are tagging a solution where the
contact has a many-to-many relationship with a tag and tags need to be
added by the users.

Many-to-many relationships are beneficial for situations where you want

to capture the association between rows of two tables. The relationship
between the rows can't store other data. For example, if you had a
relationship between a Contact and a Language table, you could track
that a person speaks two languages.

However, you would not know how long the person has spoken each
language and how proficient they are at speaking it.

A common alternative design pattern is to create your own intersect table.

The following Language Spoken table is another custom Dataverse table.
You can add columns to this table for any other properties that describe
the specific relationship. Then, this new table will have N:1 relationships
to Contact and Language.

Working with these tables from your application is similar to working with
any other tables that have one-to-many or many-to-one relationships.
Because an extra table is involved, you might discover that some extra
logic will be required to ensure a smooth user experience. It is important
to understand the requirements of your application and to know if a many-
to-many relationship needs to track other data, especially considering that
you need to make this decision at the time when the tables are related.
Work with relational data in a
Power Apps canvas app