{"id":10914,"date":"2020-07-20T05:00:18","date_gmt":"2020-07-20T12:00:18","guid":{"rendered":"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/power-apps\/formulas-thisrecord-as-and-sequence\/"},"modified":"2025-06-11T07:58:11","modified_gmt":"2025-06-11T14:58:11","slug":"formulas-thisrecord-as-and-sequence","status":"publish","type":"post","link":"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/power-apps\/formulas-thisrecord-as-and-sequence\/","title":{"rendered":"Formulas: ThisRecord, As, and Sequence"},"content":{"rendered":"

Happy Monday!\u00a0 It’s a great day for ForAll<\/strong><\/a>!<\/p>\n

You can now refer to the whole record in\u00a0ForAll<\/strong> and other record scope functions with ThisRecord<\/strong>.\u00a0 You can even name that record with the As<\/strong> operator, allowing you to clarify formulas and work with all the records in nested ForAll<\/strong> and Gallery<\/strong><\/a> controls.\u00a0 And finally, we’ve added Excel’s\u00a0Sequence<\/strong> function to generate a table containing a sequence of numbers, perfect for iterating a specific number of times with\u00a0ForAll<\/strong>.<\/p>\n

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

ThisRecord<\/h1>\n

Have you tried to use Patch<\/strong><\/a>\u00a0or Collect<\/strong><\/a> within a ForAll<\/strong>?\u00a0 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<\/strong> and other operations.<\/p>\n

What is missing is the equivalent of the Gallery<\/strong> control’s ThisItem<\/strong>.\u00a0 \u00a0And that is what we have added with ThisRecord<\/strong>.\u00a0 We didn’t call it ThisItem<\/strong> 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.<\/p>\n

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

ForAll( Filter( Gallery1.AllItems, Checkbox1.Value ),\n        Collect( MyOrder, ThisRecord ) );<\/pre>\n
Without\u00a0ThisRecord<\/strong>, it would be impossible to Collect<\/strong> the whole record at once, instead each field would need to be referenced individually.<\/p>\n
\"\"
\nThisRecord\u00a0<\/strong>can be used with far more than just ForAll<\/strong>, it is available in any function that provides a record scope, include Filter<\/strong>, LookUp<\/strong>,\u00a0With<\/strong>, Sum<\/strong>, Sort<\/strong>, Concat<\/strong>, and many others.<\/p>\n
As<\/h1>\n
ThisItem<\/strong> and ThisRecord<\/strong> are great, but they only make the innermost record available.\u00a0 In nested situations, it is impossible to reference the record in an outer scope.\u00a0 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.<\/p>\n
Drawing on SQL syntax, we have adopted the As<\/strong> operator to name these records.\u00a0 It works both in the Items<\/strong> property for the Gallery<\/strong> control and the table source for record scope functions such as ForAll<\/strong>, With<\/strong><\/a>, Sum<\/strong><\/a>, etc.<\/p>\n
Let’s look at a simple example with two nested ForAll <\/strong>functions and then two nested galleries.\u00a0 The following formula generates a chessboard text string (Viva ASCII art):<\/p>\n
Concat(\u00a0\n    ForAll( Sequence(8) As Rank,\n        Concat(\n            ForAll( Sequence(8) As File,\n                    If( Mod( Rank.Value + File.Value, 2 ) = 1, \" X \", \" . \" )\n            ),\n            Value\n        ) & Char(10)\n    ),\n    Value\n)<\/pre>\n
Setting the Text<\/strong> property of a Label<\/strong><\/a> control to this formula results in:<\/p>\n
\"\"<\/p>\n
Let’s unpack what this formula does:<\/p>\n