Formulas: ThisRecord, As, and Sequence - Microsoft Power Platform Blog (2024)

Happy Monday! It’s a great day for ForAll!

You can now refer to the whole record inForAll and other record scope functions with ThisRecord. You can even name that record with the As operator, allowing you to clarify formulas and work with all the records in nested ForAll and Gallery controls. And finally, we’ve added Excel’sSequence function to generate a table containing a sequence of numbers, perfect for iterating a specific number of times withForAll.

All of this is available with version 3.20065 or later. There is a bug fix required to do the chessboard example with galleries that is coming in 3.20072. Full documentation is being reviewed and will be posted shortly.

Have you tried to use Patchor Collect within a ForAll? You quickly realize that while all the fields of the current record are readily available, the record itself is not and you need the record for Patch and other operations.

What is missing is the equivalent of the Gallery control’s ThisItem. And that is what we have added with ThisRecord. We didn’t call it ThisItem because we don’t refer to records as items except in the gallery and form controls and we also wanted to make it easy to use record scope functions in a gallery without needing to name them.

Let’s look at a simple example. Here we have a gallery of products that we’d like to copy into a collection of products for this order. The key formula in the animation is:

ForAll( Filter( Gallery1.AllItems, Checkbox1.Value ), Collect( MyOrder, ThisRecord ) );

WithoutThisRecord, it would be impossible to Collect the whole record at once, instead each field would need to be referenced individually.

ThisRecordcan be used with far more than just ForAll, it is available in any function that provides a record scope, include Filter, LookUp,With, Sum, Sort, Concat, and many others.

ThisItem and ThisRecord are great, but they only make the innermost record available. In nested situations, it is impossible to reference the record in an outer scope. Further, it isn’t always obvious what they are referring to, requiring the reader to look back up through the formula or Items property to understand the source of the record.

Drawing on SQL syntax, we have adopted the As operator to name these records. It works both in the Items property for the Gallery control and the table source for record scope functions such as ForAll, With, Sum, etc.

Let’s look at a simple example with two nested ForAll functions and then two nested galleries. The following formula generates a chessboard text string (Viva ASCII art):

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 the Text property of a Label control to this formula results in:

Let’s unpack what this formula does:

• We start by iterating an unnamed table of 8 numbered records generated with the newSequencefunction (more about this function below). This outer loop is for each row of the board, which are referred to as Rank on a chessboardand so we give it this name.
• For each Rank, we iterate another unnamed table of 8 columns, and we give this the standard chessboard name File.
• If Rank.Value + File.Valueis 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 newline after each Rank.

Let’s look at the same example using nested gallery controls instead ofForAll. The logic is essentially the same but distributed across three controls. Note that a bug fix is needed with version 3.20072 to make this work properly.

Let’s start with the a 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 )

One of our motivations for doing this work was resolving naming issues within a Gallery. With the introduction of CDS relationship and polymorphic lookup support, it is easy to have the name of an entity conflict with the name a of a field. In an effort to be easy to use, we had been promoting all fields of ThisItem to be directly available within a Gallery, but this was now making things more confusing.

This is what we have had in our polymorphic lookup documentation, within a gallery with Items = Accounts:

If( IsType( ThisItem.Owner, [@Teams] ), "Team: " & AsType( ThisItem.Owner, [@Teams] ).'Team Name', "User: " & AsType( ThisItem.Owner, [@Users] ).'Full Name' )

The global disambiguation operator [@…] must be used in this case because Accounts has Teams and Users as One-to-Many relationship. Implicit ThisItem would send the wrong thing to IsType and AsType.

We have therefore stopped supporting implicit references in galleries. Don’t be alarmed, your apps should have kept working fine as we applied a converter to add ThisItem. to any implicit references. For a long time we have only been inserting field references prefixed with ThisItem.so there should not have been many instances of this. But if you had manually typed an implicit reference in a formula it has been updated.

With our change, the above formula now works property with simply:

If( IsType( ThisItem.Owner, Teams ), "Team: " & AsType( ThisItem.Owner, Teams ).'Team Name', "User: " & AsType( ThisItem.Owner, Users ).'Full Name' )

For record scope functions, before we introducedThisRecord, implicit references were the only way it worked. For the time being we have decided not to stop supporting implicit references in record scope functions. Name ambiguities are still possible but far less common as the Gallery throws in additional names with the controls. We’ll monitor this and listen for your feedback.

Excel recently added the Sequence function to generate an array of numbers. We’ve been wanting to add something similar for a while and this seemed like a good opportunity, ensuring consistency with what Excel had done. We did modify it slightly as we don’t have two dimensional arrays.

It is pretty simple to use: Sequence( 10 ) produces a table of 10 records, with a single Value column containing the first ten sequential numbers starting with 1. It is equivalent to writing[1,2,3,4,5,6,7,8,9,10].

You can now iterate a specific number of times as we have already seen in this post. Another example that doesn’t use the value itself, you can generate 1,000 GUIDs with ForAll( Sequence( 1000 ), GUID() ).

Additional arguments to the function allow you to control the starting number and increment. Which means that unlike [1,2,3,…] notation, Sequence can be dynamic. Here is another example that produces a character map (full details are in the Char function documentation). The outer horizontal gallery has a simple formula that counts 8 times, starting at 0, and incrementing by 16 each time:

Sequence( 8, 0, 16 ) As HighNibble

The inner vertical gallery fills in the values between the HighNibble increments by counting 16 times starting with the base value:

Sequence( 16, HighNibble.Value ) As FullCode

And then the label control inside the second gallery just needs to covert FullCode.Value to a character:

Char( FullCode.Value )

Note that Sequence is limited to 50,000 records. We believe it is a large enough number for most scenarios, but not so large that major performance delays should be experienced by end users. A reasonable limit continues to prevent infinite or near-infinite loops. But please use large values with care. Generating a large sequence table isn’t a problem, it is what you may do with it in ForAll that could take a lot of time or memory.