Operators and Identifiers in Power Apps

Article
12/16/2022
12 minutes to read

Symbol	Type	Example	Description
‘…’	Identifier	‘Account Name’	Identifiers that contain special characters, including spaces, are enclosed in single quotes
“…”	Text string	“Hello, World”	Text strings are enclosed in double quotes
$”…”	String interpolation	$”Dear {FirstName},”	Formulas embedded within a text string
.	Property Selector	Slider1.Value Color.Red Acceleration.X	Extracts a property from a table, control, signal, or enumeration. For backward compatibility, ! may also be used.
. [language dependent]	Decimal separator	1.23	Separator between whole and fractional parts of a number. The character depends on the language.
( )	Parentheses	Filter(T, A < 10) (1 + 2) * 3	Enforces precedence order, and groups subexpressions in a larger expression
+	Arithmetic operators	1 + 2	Addition
–		2 – 1	Subtraction and sign
*		2 * 3	Multiplication
/		2 / 3	Division (also see the Mod function)
^		2 ^ 3	Exponentiation, equivalent to the Power function
%		20%	Percentage (equivalent to “* 1/100”)
=	Comparison operators	Price = 100	Equal to
>		Price > 100	Greater than
>=		Price >= 100	Greater than or equal to
<		Price < 100	Less than
<=		Price <= 100	Less than or equal to
		Price 100	Not equal to
&	String concatenation operator	“hello” & ” ” & “world”	Makes multiple strings appear continuous
&& or And	Logical operators	Price < 100 && Slider1.Value = 20 or Price < 100 And Slider1.Value = 20	Logical conjunction, equivalent to the And function
\|\| or Or		Price < 100 \|\| Slider1.Value = 20 or Price < 100 Or Slider1.Value = 20	Logical disjunction, equivalent to the Or function
! or Not		!(Price < 100) or Not (Price < 100)	Logical negation, equivalent to the Not function
exactin	Membership operators	Gallery1.Selected exactin SavedItems	Belonging to a collection or a table
exactin		“Windows” exactin “To display windows in the Windows operating system…”	Substring test (case-sensitive)
in		Gallery1.Selected in SavedItems	Belonging to a collection or a table
in		“The” in “The keyboard and the monitor…”	Substring test (case-insensitive)
@	Disambiguation operator	MyTable[@fieldname]	Field disambiguation
@		[@MyVariable]	Global disambiguation
, [language dependent]	List separator	If( X < 10, "Low", "Good" ) { X: 12, Y: 32 } [ 1, 2, 3 ]	Separates: arguments in function calls fields in a record records in a table This character depends on the language.
; [language dependent]	Formula chaining	Collect(T, A); Navigate(S1, “”)	Separate invocations of functions in behavior properties. The chaining operator depends on the language.
As	As operator	AllCustomers As Customer	Overrides ThisItem and ThisRecord in galleries and record scope functions. As is useful for providing a better, specific name and is especially important in nested scenarios.
Self	Self operator	Self.Fill	Access to properties of the current control
Parent	Parent operator	Parent.Fill	Access to properties of a control container
ThisItem	ThisItem operator	ThisItem.FirstName	Access to fields of a Gallery or form control
ThisRecord	ThisRecord operator	ThisRecord.FirstName	Access to the complete record and individual fields of the record within ForAll, Sum, With, and other record scope functions. Can be overridden with the As operator.

Note

The @ operator can also be used to validate the type of the record object against a data source. For example, Collect(coll,Account@{‘Account Number: 1111’)

in and exactin operators

Use the in and exactin operators to find a string in a data source, such as a collection or an imported table. The in operator identifies matches regardless of case, and the exactin operator identifies matches only if they’re capitalized the same way. Here’s an example:

Create or import a collection named Inventory, and show it in a gallery, as the first procedure in Show images and text in a gallery describes.
Set the Items property of the gallery to this formula:
Filter(Inventory, “E” in ProductName)

The gallery shows all products except Callisto because the name of that product is the only one that doesn’t contain the letter you specified.
Change the Items property of the gallery to this formula:
Filter(Inventory, “E” exactin ProductName)

The gallery shows only Europa because only its name contains the letter that you specified in the case that you specified.

ThisItem, ThisRecord, and As operators

A few controls and functions apply formulas to individual records of a table. To refer to the individual record in a formula, use one of the following:

Operator	Applies to	Description
ThisItem	Gallery control Edit form control Display form control	The default name for the current record in a Gallery or form control.
ThisRecord	ForAll, Filter, With, Sum and other record scope functions	The default name for the current record in ForAll and other record scope functions.
As name	Gallery control ForAll, Filter, With, Sum and other record scope functions	Defines a name for the current record, replacing default ThisItem or ThisRecord. Use As to make formulas easier to understand and resolve ambiguity when nesting.

ThisItem operator

For example, in the following Gallery control, the Items property is set to the Employees data source (such as the Employees table included with the Northwind Traders sample):

Employees

The first item in the gallery is a template that is replicated for each employee. In the template, the formula for the picture uses ThisItem to refer to the current item:

ThisItem.Picture

Likewise, the formula for the name also uses ThisItem:

ThisItem.’First Name’ & ” ” & ThisItem.’Last Name’

ThisRecord operator

ThisRecord is used in functions that have a record scope. For example, we can use the Filter function with our gallery’s Items property to only show first names that being with M:

Filter( Employees, StartsWith( ThisRecord.Employee.’First Name’, “M” ) )

ThisRecord is optional and implied by using the fields directly, for example, in this case, we could have written:

Filter( Employees, StartsWith( ‘First Name’, “M” ) )

Although optional, using ThisRecord can make formulas easier to understand and may be required in ambiguous situations where a field name may also be a relationship name. ThisRecord is optional while ThisItem is always required.

Use ThisRecord to reference the whole record with Patch, Collect, and other record scope functions. For example, the following formula sets the status for all inactive employees to active:

With( { InactiveEmployees: Filter( Employees, Status = ‘Status (Employees)’.Inactive ) }, ForAll( InactiveEmployees, Patch( Employees, ThisRecord, { Status: ‘Status (Employees)’.Active } ) ) )

As operator

Use the As operator to name a record in a gallery or record scope function, overriding the default ThisItem or ThisRecord. Naming the record can make your formulas easier to understand and may be required in nested situations to access records in other scopes.

For example, you can modify the Items property of our gallery to use As to identify that we are working with an Employee:

Employees As Employee

The formulas for the picture and name are adjusted to use this name for the current record:

Employee.Picture

Employee.’First Name’ & ” ” & Employee.’Last Name’

As can also be used with record scope functions to replace the default name ThisRecord. We can apply this to our previous example to clarify the record we’re working with:

With( { InactiveEmployees: Filter( Employees, Status = ‘Status (Employees)’.Inactive ) }, ForAll( InactiveEmployees As Employee, Patch( Employees, Employee, { Status: ‘Status (Employees)’.Active } ) ) )

When nesting galleries and record scope functions, ThisItem and ThisRecord always refers to the inner most scope, leaving records in outer scopes unavailable. Use As to make all record scopes available by giving each a unique name.

For example, this formula produces a chessboard pattern as a text string by nesting two ForAll functions:

Concat( ForAll( Sequence(8) As Rank, Concat( ForAll( Sequence(8) As File, If( Mod(Rank.Value + File.Value, 2) = 1, ” X “, ” . ” ) ), Value ) & Char(10) ), Value )

Setting a Label control’s Text property to this formula displays:

Let’s unpack what is happening here:

We start by iterating an unnamed table of 8 numbered records from the Sequence function. This loop is for each row of the board, which is commonly referred to as Rank and so we give it this name.
For each row, we iterate another unnamed table of 8 columns, and we give the common name File.
If Rank.Value + File.Value is an odd number, the square gets an X, otherwise a dot. This part of the formula is referencing both ForAll loops, made possible by using the As operator.
Concat is used twice, first to assemble the columns and then the rows, with a Char(10) thrown in to create a new line.

A similar example is possible with nested Gallery controls instead of ForAll functions. Let’s start with the vertical gallery for the Rank. This gallery control will have an Items formula of:

Sequence(8) as Rank

Within this gallery, we’ll place a horizontal gallery for the File, that will be replicated for each Rank, with an Items property of:

Sequence(8) as File

And finally, within this gallery, we’ll add a Label control that will be replicated for each File and each Rank. We’ll size it to fill the entire space and use the Fill property to provide the color with this formula:

If( Mod( Rank.Value + File.Value, 2 ) = 1, Green, Beige )

Self and Parent operators

There are three ways to refer to a control and its properties within a formula:

Method	Description
By control name	Any control can be referenced by name from anywhere within the app. For example, Label1.Fill refers to the fill property of the control who’s name is Label1.
Self operator	It’s often convenient to reference another property of the same control when writing a formula. Instead of using an absolute reference by name, it’s easier and more portable to use a relative reference to oneself. The Self operator provides that easy access to the current control. For example, Self.Fill refers to the fill color of the current control.
Parent operator	Some controls host other controls, such as the Screen and Gallery controls. The hosting control of the controls within it’s called the parent. Like the Self operator, the Parent operator provides an easy relative reference to the container control. For example, Parent.Fill refers to the fill property of the control that is the container for the current control.

Method

Description

By control name

Any control can be referenced by name from anywhere within the app.

For example, Label1.Fill refers to the fill property of the control who’s name is Label1.

Self operator

It’s often convenient to reference another property of the same control when writing a formula. Instead of using an absolute reference by name, it’s easier and more portable to use a relative reference to oneself. The Self operator provides that easy access to the current control.

For example, Self.Fill refers to the fill color of the current control.

Parent operator

Some controls host other controls, such as the Screen and Gallery controls. The hosting control of the controls within it’s called the parent. Like the Self operator, the Parent operator provides an easy relative reference to the container control.

For example, Parent.Fill refers to the fill property of the control that is the container for the current control.

Self and Parent are operators and not properties on the controls themselves. Referring to Parent.Parent, Self.Parent or Parent.Self is not supported.

Identifier names

The names of variables, data sources, columns, and other objects can contain any Unicode.

Use single quotes around a name that contains a space or other special character.
Use two single quotes together to represent one single quote in the name. Names that don’t contain special characters don’t require single quotes.

Here are some example column names you might encounter in a table, and how they’re represented in a formula:

Column name in a database	Column reference in a formula
SimpleName	SimpleName
NameWith123Numbers	NameWith123Numbers
Name with spaces	‘Name with spaces’
Name with “double” quotes	‘Name with “double” quotes’
Name with ‘single’ quotes	‘Name with ”single” quotes’
Name with an @ at sign	‘Name with an @ at sign’

Double quotes are used to designate text strings.

Display names and logical names

Some data sources such as SharePoint and Microsoft Dataverse have two different names to refer to the same table or column of data:

Logical name – A name that is guaranteed to be unique, doesn’t change after being created, usually doesn’t allow spaces or other special characters, and isn’t localized into different languages. As a result, the name can be cryptic. These names are used by professional developers. For example, cra3a_customfield. This name may also be referred to as schema name or just name.
Display name – A name that is user-friendly and intended to be seen by end users. This name may not be unique, may change over time, may contain spaces and any Unicode character, and may be localized into different languages. Corresponding to the example above, the display name may be Custom Field with space in between the words.

Since display names are easier to understand, Canvas apps will suggest them as choices and not suggest logical names. Although logical names aren’t suggested, they can still be used if typed indirectly.

For example, imagine you’ve added a Custom Field to a table in Dataverse. A logical name will be assigned for you by the system, which you can modify only when creating the field. The result would look similar to:

When authoring a reference to a field of Accounts, the suggestion will be made to use ‘Custom Field’ since this is the display name. Single quotes must be used because this name has a space in it:

After selecting the suggestion, ‘Custom Field’ is shown in the formula bar and the data is retrieved:

Although it isn’t suggested, we could also use the logical name for this field. This will result in the same data being retrieved. Single quotes are required since this name doesn’t contain spaces or special characters:

Behind the scenes, a mapping is maintained between the display names seen in formulas and the underlying logical names. Since logical names must be used to interact with the data source, this mapping is used to convert from the current display name to the logical name automatically and that is what is seen in the network traffic. This mapping is also used to convert back to logical names to switch into new display names, for example, if a display name changes or a maker in a different language edits the app.

Note

Logical names are not translated when moving an app between environments. For Dataverse system table and field names, this should not be a problem as logical names are consistent across environments. But any custom fields, such as cra3a_customfield in this example above, may have a different environment prefix (cra3a in this case). Display names are preferred as they can be matched against display names in the new environment.

Name disambiguation

Since display names aren’t unique, the same display name may appear more than once in the same table. When this happens, the logical name will be added to the end of the display name in parenthesis for one of more of the conflicting names. Building on the example above, if there was a second field with the same display name of Custom Field with a logical name of cra3a_customfieldalt then the suggestions would show:

Name disambiguation strings are added in other situations where name conflicts occur, such as the names of table, choices, and other Dataverse items.

Disambiguation operator

Some functions create record scopes for accessing the fields of table while processing each record, such as Filter, AddColumns, and Sum. Field names added with the record scope override the same names from elsewhere in the app. When this happens, you can still access values from outside the record scope with the @ disambiguation operator:

To access values from nested record scopes, use the @ operator with the name of the table being operated upon using this pattern:
Table[@FieldName]
To access global values, such as data sources, collections, and context variables, use the pattern [@ObjectName] (without a table designation).

For more information and examples, see record scopes.