Categories
Power Apps

ForAll Loops for updating collections

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:

Look at the collections codes

ClearCollect(_employees,
{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].

Result from the code. Note that your figures may vary

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.

After adding buttons. Code next to each button for clarification

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.

Categories
Digitalization

Digitalize your financial processes

Finance professionals have operated digital tools for decades. Accounting and ERP systems were early applications in the digital era. But without the information in your organization, their data is nonsense. Therefore, tools like Excel and Powerpoint have become essential for gathering and presenting analysis of information.

This leaves your organization with scattered and unstructured data, unsuitable for the automated BI-reports you would like to roll out.

Traditional IT development is too expensive and generic to justify digitalization of smaller processes. But the progress within low-code application-makers, like Microsoft Power Apps has changed that.

Invisible Hand helps your organization design, implement and take ownership of your development pipeline.

Categories
Digitalization Manifest

The Manifest

A spectre is haunting business – the spectre of digitalization. All the old powers have gathered to profit from it: management consultants, IT behemoths, and bestselling futurist soothsayers alike wield the buzzword of the day to pierce their way into companies’ IT budgets. Where is the company that refuses to invent new jargon to push the promises of a new digital era, and instead delivers digitalized processes with existing tools, working as smooth as a Big Four pitch sounds.

Two things result from the current state of affairs:

  • Organizations want to reap the benefits that is being promised to them. The problems organizations want to address is to reduce internal and external transaction costs, i.e. allowing their business to focus on just that – the business – and not the needless inefficiencies that is a reality for companies today.
  • The gap between theoretical digitalization and its prophets, and the business’ reality is wide and widening. The snakeoil peddlers dream up new slideshows with little practical connection to the trenches. At the same time, highly effective, widely available, and tragically under-exploited weapons are readily available for the corporate soldiers. The wise leaders that employ these tools will get a more productive workforce that also serves vital business information to them on a digital, fully automated, and transparent silver platter.

To this end, the Invisible Hand have assembled to sketch the following manifesto. It outlines.

On the ladder of digital transformation, they flatter you by reckoning that you have taken the first necessary steps toward the digital avante garde, and their particular transformation journey/catch-all system/methodology will propel your organization above and beyond Silicon Valley. In the future everything is digital, they say (as they continue to pitch their monolith ERP system). By going down that trodden path, your organization adjust their digital capabilities to the technical specifications that comes out on the other end of a multi-million, year-and-a-half implementation project. It is the lowest common denominator functionalities that The Leviathan IT Company thought you, and a hundred other businesses bearing no similarities to yours, might find useful.

After some training, your talented and excited workforce gets a good grip on how to perform their old tasks in the new environment. Some feel recognized because the organization has invested in a glossy interface for them. Some find it easier to perform previously time-consuming tasks. Many will say, “wouldn’t it be nice if we could do [insert brilliant idea] as well”, and at best be told that the Leviathan engineers might look into that in the future. No one has been given the tools to explore these ideas themselves. No one has been digitally transformed. As the new system’s lifecycle rapidly closes in on a new multi-million upgrade, the excitement morphs into envy of some other department’s seemingly cooler IT tools and frustration of those long-forgotten specification requests.

In the middle ages, religious acolytes and feudal warlords had monopolized the written word. The immortalization of ideas – a revolutionary invention enabled by pragmatic Middle-Eastern accountants in the dawn of civilization – had been bastardized as propaganda and delusion. But things evolved. Replication costs plummeted with the printing press, and the We, the people, demanded access to education. The pedestals of the litterate class finally became crowded, and prosperity ensued. Similarly, the codification of processes were invented by brave academics and engineers in the 20th century. At the advent of the digital age, the new litterate class of IT professionals entrench their positions, not with brute force, but with impenetrable jargon and perpetually increasing air of complexity in the upcoming theoretical concepts. Nonetheless We, the people, are kept at arms lenght from the tools that can propel us to never before experienced levels of wealth and wellbeing.

The deep moat of code litteracy has given rise to a new societal function: the pseudo-litterate consultant that bridge the moat: a fragile bridge of vague understanding of both your real problems and the jargon. This highly specialized creature has evolved to first create an urge in your organization to embark on a digitalization journey. Then, it can freely charge you for running back-and-forth with liberally interpreted messages in languages it does not fully master. The digital age has lowered informational transaction costs immensely, but transactional costs in creating sound processes for capturing said information has risen at the same pace as the interpretor-consultants’ bank account.

Our epoch, the epoch of digital promises, deserves something better. It is time for us to arm ourselves with tools tailored for our specific circumstances and requirements. It is time for us to be empowered to customize and add to these tools as our circumstances change. It is time to yet again crowd the pedestals of the litterate class, and tear down their monopoly of information creation. And it is time to rid ourselves of the yoke of expensive misinterpretators of our problems. Wield your no-code pitchforks, and take control of your own digitalization adventure.