PowerApps ForAll Function Explained [ Many Examples ]

The PowerApps ForAll function confused me in the beginning. Coming from a programming background I was expecting something like a classic for loop. But ForAll is a little different!

The most important aspects I needed to understand:

• ForAll is not a classic for loop Iike in JavaScript, C# or Java
• ForAll is a function with a return value like every function
• ForAll function is not executed sequentially and therefore it is prohibited to keep any kind of state

What I mean by this will be explained in the article. Furthermore you will get lots of example of common use cases for ForAll PowerApps function.

PowerApps ForAll Function

The ForAll function lets you apply one or multiple functions on every record of a given table. The result of the function calls will be returned in a table.

Note: Please be aware that the ForAll function comes with a few limitations: Certain functions can not be used within a ForAll function, the table ForAll is using can ot be modified and ForAll can not be delegated. (More details here)

Syntax

ForAll(Table, Formula)

Parameters

• Table (mandatory): The data structure you want to iterate over. This might be a collection, a table or a data source like a SharePoint list or a SQL table.
• Formula (mandatory): The code you want to apply to each element of your table.

Return value

• Returns a table matching the result structure given by the used formula. Every entry reflects the result of the applied formula for the record of the table. The order is the same as for the input table.
• Returns blank, if there is no record within the given table.

ThisRecord

Within your formula you can reference the current item in two ways:

• Use ‘ThisRecord’ for the entire record or use ‘ThisRecord.propertyName’ like this for instance ‘ThisRecord.firstname’
• For properties you can skip the ThisRecord, so instead of ‘ThisRecord.firstname’ you can just use ‘firstname’

Let’s have a quick code example to demonstrate PowerApps ForAll ThisRecord for accessing the current value of ForAll.

First set up a collection:

ClearCollect(
    employees,
    {
        firstname: "Joe",
        lastname: "Smith"
    },
    {
        firstname: "Sally",
        lastname: "Miller"
    }
);

Now we want to calculate the fullname of each employee. Take a look how at how firstname and how lastname are referenced within ForAll.

PowerApps ForAll ThisRecord Example

ClearCollect(
    fullnames,
    ForAll(
        employees,
        firstname & " " & ThisRecord.lastname
    )
);

ForAll Limitations

There are some limitation on what you can do within the formula of a ForAll function call.

Prohibited modification of the table

It is not allowed to modify the table you are iterating over within the formula. You will get an error looking like this:

This function can not operate on the same data source that is used in ForAll.

Just think about what the example result could be: An endless loop or a collection of 1,0,2,0,3,0?

Prohibited functions

Within your formula in the ForAll function you can not uses the following functions:

• Clear
• ClearCollect
• Set
• UpdateContext

When you try to use one of these function, you’ll see the following error:

This function cannot be invoked within ForAll.

So basically everything that could be used as state within the execution of the ForAll function is prohibited.

At some point this limitation will drive every PowerApps developer nuts.

Why is it not allowed to have variables within a ForAll function?

The order in which items of the table are processed might not be the same as within your table. So your first record might be processed as last record, because of parallel processing of items.

Having state within in the non-deterministic order brings a lot of risk. That’s why it is not allowed to have state.

Note: The Collect function is allowed, but when you use it, do not rely on the order of your table!

ForAll can not be delegated

Since ForAll can not be delegated, be carefull with larger data sources like big SharePoint lists or database tables.

PowerApps ForAll Examples

Simple ForAll Example

To understand how ForAll works, let’s start with a simple example.

1. Create a collection called ‘numbers’ with content [1,2,3]
2. Use ForAll to multiply every item in numbers by 2
3. Assign the result of ForAll to numbers

ClearCollect(numbers,1,2,3);

ClearCollect(
    numbers,
    ForAll(
        numbers,
        Value*2
    )
);

Within our example the content of ‘numbers’ changes as shown in the tables.

Note: ‘numbers’ is only changed, because we do a ‘ClearCollect(numbers,…)’. Within the ForAll function numbers is not modified and can not be modified.

ForAll multiple actions

You can easily do multiple actions within a ForAll function. Just use the semicolon to seperate the function calls.

Be aware, if you care about the return value of ForAll. The order of your function call do matter. The last function call we be the winner for the return value.

ClearCollect(
    employees,
    {
        firstname: "Joe",
        lastname: "Smith"
    },
    {
        firstname: "Sally",
        lastname: "Miller"
    }
);

ClearCollect(
    fullnames,
    ForAll(
        employees,
        Notify("Hi," & firstname );
        firstname & " " & ThisRecord.lastname;
    )
);

ForAll Patch SharePoint List

To create or update multiple SharePoint list items at once, you can use ForAll and Patch.

For example, we have a SharePoint List called ’employees’ looking like this:

ID	name	department
1	John	Sales
2	Sally	IT

Now we want to add multiple new entries. Here is a collection of the new entries:

// new employees we want to add to employees
ClearCollect(
    newEmployees,
    {
        name: "Mike",
        department: "Logisitcs"
    },
    {
        name: "Susan",
        department: "CEO"
    }
);

Now we can use ForAll to add every new employee to our SharePoint List via Patch.

ForAll(
    newEmployees,
    Patch(
        employees,
        Defaults(employees),
        ThisRecord
    )
)

ForAll with If condition

Within yourt ForAll formula you can use If conditions. See the example below:

ForAll(
    newEmployees,
    If(
        department = "IT",
        Patch(
            employees,
            Defaults(employees),
            ThisRecord
        )
    ) 
)

ForAll And Sequence

If you need ForAll to iterate based on a count, use the Sequence function.

The following example demonstrates how to create a collection with dates for the next 10 days from now.

ClearCollect(
    next10Days,
    ForAll( 
        Sequence( 10 ), 
        DateAdd( 
            Today(), 
            Value, 
            Days 
        ) 
    )
)

Ambiguity within ForAll

There can be situation where it is not obvious, which property is used.

For example, we are having two collections:

• Employees: Name and Department
• Supvisors: Name and Department

Let’s define them:

ClearCollect(
    Employees,
    {
        name: "Joe Smith",
        department: "IT"
    },
    {
        name: "Sally Miler",
        department: "Sales"
    }
);

ClearCollect(
    Supervisors,
    {
        name: "Mike Doe",
        department: "IT"
    },
    {
        name: "Sarah Hingston",
        department: "Sales"
    }
);

ClearCollect(
    res,
    ForAll (
        Supervisors,                    
            Filter(
                Employees,
                department = Supervisors[@department]
            )
    )
);

Based on the collection we want to define a data structure that lists all departments with their supervisor and a table of all employee in the department.

You may have noticed that Supervisors and Employees both have department property. This can lead to problems because it is not obvious which department should be used.

You can solve this by using Ambiguity operator. See the example below:

ClearCollect(
    Departments,
    ForAll (
        Supervisors,        
        {
            supervisor: ThisRecord.name,
            department: ThisRecord.department,
            employees: Filter(
                            Employees,                        
                            department = Supervisors[@department]
                            // NOT department = department !
                        )
        }            
    )
);

This line is the important one. See the brakets and the @ symbol defining which department is meant.

department = Supervisors[@department]    

In case would be using the below, the filter would return in every iteration of ForAll all departments, because the Employee department is compared with the Employee department.

department = department   

You can solve the situation by using ‘As’ as well:

ClearCollect(
    Departments,
    ForAll (
        Supervisors As s,        
        {
            supervisor: s.name,
            department: s.department,
            employees: Filter(
                            Employees,                        
                            department = s.department
             
                        )
        }            
    )
);

ForAll within ForAll – Nested ForAll

You can use nested ForAll function calls.

The question that comes up when using nested ForAll calls: How do you reference ThisRecord of a parent ForAll? ForAll & ThisRecord does not work in these situation, since ThisRecord always refers to the records within the current function.

The solution is to use the As operator. In the example below we use ‘aRecord’ as an alias for ThisRecord of the ForAll call on ‘a’.

ClearCollect(a,2,3);
ClearCollect(b,10,100);


ClearCollect(
    result,
    ForAll(
        a As aRecord,
        ForAll(
            b,
            ThisRecord.Value * aRecord.Value
        )
    )    
);

You might wonder, what is the outcome of this ForAll inside ForAll. What does result contain? Can you guess it?

The result is a table with two entries. These entries are tables too:

ForAll with LookUp

You can use LookUp calls within in ForAll. Please use As in case you want to reference ThisRecord of the parent ForAll call like shown below. Otherwise ThisRecord refers to the record of the LookUp.

ClearCollect(
    forAllResult,
    ForAll(
        MyGallery.AllItems As galleryItems,
        {
            Firstname: galleryItems.firstname,
            Supervisor: LookUp(
                Employees,
                employeeId = galleryItems.supervisorId
            )
        }
    )
);

ForAll with Gallery items

To use ForAll to take action the gallery items, you can simply reference the items by callinf AllItems like shown below.

ClearCollect(
    forAllResult,
    ForAll(
        NewEmployeesGallery.AllItems,
        Patch(
            employees,
            Defaults(employees),
            {
              Firstname: ThisRecord.first,
              Lastname: ThisRecord.last
            }
        )
    )
);