Operators and Identifiers

NOTE: Microsoft Power Fx is the new name for canvas apps formula language. These articles are work in progress as we extract the language from canvas apps, integrate it with other products of the Power Platform, and make available as open source. Start with the Power Fx Overview for an introduction to the language.

Microsoft Power Fx operators are described in the following table. Some of these operators are dependent on the language of the author. See Global apps for more information.

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:

1. 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.

2. 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.

3. 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:

ThisItem operator

For example, in the following Gallery control, the Items property is set to the Employees data source (such as the Employees entity 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 are 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:

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 do not contain special characters do not require single quotes.

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

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, does not change after being created, usually does not allow spaces or other special characters, and is not 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, Power Fx will suggest them as choices and not suggest logical names. Although logical names are not suggested, they can still be used if typed indirectly.

For example, imagine you have added a Custom Field to an entity 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. The 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 is not suggested, we could also use the logical name for this field. This will result in the same data being retrieved. No single quotes are required since this name does not 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 entity 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 are not unique, the same display name may appear more than once in the same entity. 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 entities, option sets, 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.

[!INCLUDEfooter-include]