Making SQL sense

+44 (0)20 3051 3595 info@coeo.com
coeoClose

Making SQL sense

+44 (0)20 3051 3595 info@coeo.com

Careers

We're looking for people who share our commitment to excellence in Microsoft's data platform to join us consultants working on exciting business intelligence, analytics, and SQL Server projects on-premises and in the cloud.

PowerApps Blog Series - Recursion Using a Numbers Table

The Coeo Blog

This post is the first in a series I'll be doing, showing you how to achieve certain things in PowerApps that aren't immediately obvious but are very useful to enable you to create powerful, feature rich applications.

Today's post is on how to create a numbers table in PowerApps, when you don't want to use external data sources to do it.

What is a "Numbers Table" and why do I need one in PowerApps?

That's a good question, theoretical reader of this post . . :)

A numbers table is basically exactly what it says on the tin, its a table (or in our case, a collection) that contains numbers. It is literally a collection with one column and an increasing number in each row.

So that's the what, but what about the why? Well, those of you familiar with SQL Server will hopefully know what numbers tables are used for there, to avoid recursion and to operate in a more set based manner.

In PowerApps however they are quite the opposite, they allow us to do recursion (looping i.e. a "for I" loop) when we don't have a thing to recuse over.

One practical application of this is creating a collection containing dates, in order to populate a calendar control. (There is another blog post in this series which details this).

I'm sure there are lots of occasions when you may want to do a specific action X number of times, where X may or may not be a variable value.

Ok, I'm sold, So how do I do it?

It's reasonably straight forward actually, with the use of the ForAll() function. The code below is how you achieve this and we'll break it down afterwards

Code

ClearCollect(colNumTemp, [1,2,3,4,5,6,7,8,9,10]);

Clear(colNumbersTable);

Clear(colDummy);

ForAll

(

colNumTemp,

ForAll

(

colNumTemp,

ForAll

(

colNumTemp,

Collect

(

colDummy,

{dummy: 1}

);

 

Collect

(

colNumbersTable,

{Number: CountRows(colDummy)}

)

)

)

);

Clear(colDummy) ;

Clear(colNumTemp)

Breakdown

So in this first step, we manually create a temporary collection of numbers, I've gone to 10 here, however you can adjust this to increase the size of your numbers table.

ClearCollect(colNumTemp, [1,2,3,4,5,6,7,8,9,10]);

Next we just empty the collections we'll be using, just to ensure we don't end up with duplication

Clear(colNumbersTable);

Clear(colDummy);

In this step we now do a number of nested ForAll() function calls, using the colNumTemp created above. The number of nested calls is totally up to you as it will decide the size of your numbers table in correlation with the colNumTemp collection.

The calculation to determine the size is as below:

Size Of Numbers Table = colNumTemp ^ Number Of Nested ForAll

So in my example,

colNumTemp (10) ^ Number Of Nested ForAll (3) = 1,000

ForAll

(

colNumTemp,

ForAll

( *etc*

The innermost ForAll should be different however, as it does the actual work. It adds records to two collections. Firstly, a dummy collection is populated to get a running record count. Our actual live numbers table is then populated using the CountRows() function over the dummy collection, to give us one row with a running number

ForAll

(

colNumTemp,

Collect

(

colDummy,

{dummy: 1}

);

 

Collect

(

colNumbersTable,

{Number: CountRows(colDummy)}

)

)

The final step is belt and braces to clear out the collections we are no longer using, to save on application memory

Clear(colDummy);

Clear(colNumTemp);

Wrap Up

There you have it, we now have a fully populated numbers table (collection) that we can use in further calculations and recursion in our app.

I have created a sample app that contains demos for all the code in this series of blog posts, this app can be downloaded from here: 

Demo App

It looks a little something like this

 

NumbersTable-1

 

Make sure you don't miss out on the next PowerApps blog in this series - subscribe to the Coeo blog using the form at the top left of this page.

 

Subscribe to Email Updates

Related posts