Loops are a great way to reduce the lines of code in your app, and thereby increasing maintainability. Users with a background in VBA-programming may quickly look for For-loop alternatives when updating collections, contexts or datasets. This post details a solution for that as well as some challenges you may face.
The ForAll-Function:
The ForAll function will execute a formula multiple times for each post in a table. The formula can be both actions and calculations, and interact with connections. The table part can come from a connection or a collection.
This gives us the possibility to execute something we would like to put within a for-loop in VBA, and repeat it in a for-loopish fashion. It will be a very powerful friend that will make your code much more efficient, readable, and easy to maintain.
There are some limitations that are good to be aware of. For example, you can not alter the table that you are using for iterations. It’s not a delegable function, which may be limiting. But most important, you need to be mindful of the fact formulas aren’t evaluated in any table order. If you write or remove from a connected database, it’s not clear which order the rows will execute.
The last limitation means that we cannot depend an index variable that will increase each time a loop is executed. Instead, we need to determine how many iterations we want to execute the loop in before embarking into the loop.
Example:
Let’s take three fictional employees and look at their hours worked in a given month. We’ll use two collections: _employess and _months that look like this:
ClearCollect(_employees,Look at the collections codes
{Name: “Adam”},
{Name: “Bertrand”},
{Name: “Calvin”});
ClearCollect(_months,
{Month: “January”},
{Month: “February”},
{Month: “March”},
{Month: “April”},
{Month: “May”},
{Month: “June”},
{Month: “July”},
{Month: “August”},
{Month: “September”},
{Month: “October”},
{Month: “November”},
{Month: “December”}
We want to combine these collections into a third collection where we randomly generate the hours they worked that month.
To do this, I’ve added another button, with the following OnSelect property:
ClearCollect(_Combined,
{Name: Blank(), Month: Blank(), Hours: Blank()});
ForAll(_months,
Collect(_Combined,
{Name: "Adam", Month: ThisRecord.Month, Hours: 140 + RoundDown(41 * Rand(), 0)},
{Name: "Bertrand", Month: ThisRecord.Month, Hours: 140 + RoundDown(41 * Rand(), 0)},
{Name: "Calvin", Month: ThisRecord.Month, Hours: 140 + RoundDown(41 * Rand(), 0)}))The first ClearCollect-function is in order to clear the previous information in the collection. If you add to your data while retaining previous records, skip that part.
Second comes the ForAll-function. We are stating that we want it to execute the function Collect for each row in the _months collection. We are adding three items, one for each name. ThisRecord.Month is the way to extract the Month-value in the current row.
What if we want to add another employee, say David? In this current set-up we would need to add another item-clause, stating:
{Name: "David", Month: ThisRecord.Month, Hours: 140 + RoundDown(41 * Rand(), 0)}but that will not scale well, and be aweful to maintain.
Instead, we can use the same logic we used for the months: we can loop the loop for each employee name in the _employee-collection. This is called nested loops, and it works well for the ForAll function. Instead of having three items, we condense it to one item, but loop the value for the Name-column based on the rows in the _employee-collection.
ClearCollect(_Combined,
{Name: Blank(), Month: Blank(), Hours: Blank()});
ForAll(_employees,
ForAll(_months,
Collect(_Combined,
{Name: _employees[@Name], Month: _months[@Month],
Hours: 140 + RoundDown(41 * Rand(), 0)})))The code is much more compact, especially as the number of employees grow.
An important part here is the disambiguation operator [@…]. By writing it like this, instead of ThisRecord.Month, we can be clear to the function which record we mean. The structure is Table[@Column].
Now, in most cases, you would rather have the user input a number for a given month, instead of randomly generating it. How can we achieve this?
We can set up 12 input text boxes for the input, and maybe add labels on top of them for clarity. If you’re coming from any coding background, you may think the next step is naming the labels in order, so that you can call programmatically call on these from the loop.
However, here’s a major weakspot of PowerApps. Input boxes (or columns or similar) cannot be dynamically called upon. Nonetheless, it’s a good idea to name your objects with clarity, hence I have named mine inpJan, inpFeb, …, inpDec. We need to create a little bulky workaround with If-functions for determining the where to fetch a certain month’s value. The below code adds an If-statement that switches the Text Input from which to fetch from, based on the record’s month.
ClearCollect(_Combined,
{Name: Blank(), Month: Blank(), Hours: Blank()});
ForAll(_employees,
ForAll(_months,
Collect(_Combined,
{Name: _employees[@Name], Month: _months[@Month], Hours:
If(ThisRecord.Month = "January", Value(inpJan.Text),
ThisRecord.Month = "February", Value(inpFeb.Text),
ThisRecord.Month = "March", Value(inpMar.Text),
ThisRecord.Month = "April", Value(inpApr.Text),
ThisRecord.Month = "May", Value(inpMay.Text),
ThisRecord.Month = "June", Value(inpJun.Text),
ThisRecord.Month = "July", Value(inpJul.Text),
ThisRecord.Month = "August", Value(inpAug.Text),
ThisRecord.Month = "September", Value(inpSep.Text),
ThisRecord.Month = "October", Value(inpOct.Text),
ThisRecord.Month = "November", Value(inpNov.Text),
ThisRecord.Month = "December", Value(inpDec.Text),
0)
})))Note: If any reader of this post finds a better workaround, I would be eternally grateful!
In this demo, I’ve added two buttons, one for generating the name and month collections, and one for saving the input into the _Combined Collection.
Now, as a final thing, we might not want to add the same hours for all employees. Hence, we’ll add a drop down to select the employee, name it ddEmployee, populated with _employees.Name. Then, we adjust the code by removing the first ForAll-loop and directing the Name-column to ddEmployee.SelectedText.Result.
ClearCollect(_Combined,
{Name: Blank(), Month: Blank(), Hours: Blank()});
ForAll(_months,
Collect(_Combined,
{Name: ddEmployee.Selected.Name, Month: _months[@Month], Hours:
If(ThisRecord.Month = "January", Value(inpJan.Text),
ThisRecord.Month = "February", Value(inpFeb.Text),
ThisRecord.Month = "March", Value(inpMar.Text),
ThisRecord.Month = "April", Value(inpApr.Text),
ThisRecord.Month = "May", Value(inpMay.Text),
ThisRecord.Month = "June", Value(inpJun.Text),
ThisRecord.Month = "July", Value(inpJul.Text),
ThisRecord.Month = "August", Value(inpAug.Text),
ThisRecord.Month = "September", Value(inpSep.Text),
ThisRecord.Month = "October", Value(inpOct.Text),
ThisRecord.Month = "November", Value(inpNov.Text),
ThisRecord.Month = "December", Value(inpDec.Text),
0)
}))If we add some figures and press the “Save”-button, we’ll find that the Collection-set has been replaced with the new figures.
There we go! If we would have typed this out without the loop, we would have received something far less maintainable and efficient. However, for a developer coming from VBA, there are some unfortunate workarounds that need to be accounted for. Nonetheless, the ForAll-loop will be a powerful tool in your Powerapps projects.