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:
It looks a little something like this
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.