top of page
Search

Power Apps working with Collections – April 2023

Updated: May 2, 2023

Shane has updated one of his most popular videos on working with collections. In this post, we’ll introduce you to everything you need to know about Collections in Power Apps. From creating your first collection, to using the Collect function to shape and display your data, this is a good stop for new and experienced Power Apps users.


See the video here.


Video Chapters:

0:00 - Introduction

1:31 - The Collect function to create your first Power Apps Collection

3:19 - Collecting multiple records at once

3:56 - Using a gallery to display the Collection data

4:24 - The Clear function to empty the collection

5:00 - The ClearCollect function to empty and update the collection

6:36 - Utilizing Input Controls to populate the collection

9:43 - Real-world examples of using Collections in Power Apps

11:45 - Caching Data Source using Collections

14:40 - Delegation Considerations

15:15 - Writing data from a Collection to the data source

17:24 - Removing an item from a Collection


What is a Collection and why use them?

A collection is a special type of variable known as a table variable. A collection captures rows and columns of data vs. one item of data. There are two reasons why we use them: one, to collect up a bunch of data before sending it to our data source, and two, to cache information so we don’t have to keep going to our data source to grab it. Since a collection is a table of data, and our data source is usually a table of data, collections give us an efficient way to interact with our data in Power Apps (even offline).


How do you create a collection?

One way to create a collection is to use the Collect function. The Collect function can be used with the OnSelect property of a button control. With the app in preview mode, selecting the button with the formula below will add a record with two fields (Name and PN) to a collection called colStuff each time it is selected:


Collect(colStuff, {Name: “Lori”, PN: 12});

If you press/hold your Alt key first, after pressing the button control several times, you can see how the collection grows:

You can add another record to the Collect function in your formula to add a second record each time like this:


Collect(colStuff, {Name: “Lori”, PN: 12}, {Name: “Tom”, PN: 33})

Each time you edit the formula for your Collect, you’ll notice that the collection is reset, but selecting your button control multiple times populates the collection with two record entries each time it is pressed:

Next, we can insert a gallery control to view the collection, because the collection is a table of data and that’s the type of data that a gallery is looking for. We tell our gallery that the data source is the collection (you can check your Items property to make sure your collection name is displayed there).

We can modify the Layout and adjust our fields to display what we want. Then we can adjust the fields inside of the gallery to show the items we want from our records. In this case one field displays the “Name” and the other displays the “PN”.


The Clear function

We can use the Clear function to erase all the items from our collection. In the demo, we create a second button and add a formula to clear the collection, in this case: Clear(colStuff). Selecting our second button erases the collection and causes the gallery to go blank. So, while Collect adds records to the collection, Clear empties the collection of all records.


The ClearCollect function

The ClearCollect function first empties or clears our collection, then it adds records. If we add another button to our app, we can demonstrate what the ClearCollect function does with our collection. We define a collection that we’re going to clear and then add data to, then we specify what data to add (or collect) immediately after the collection gets cleared.


ClearCollect(colStuff, {Name: “Buddy”, PN: 1});

If we select the button with this formula, each time we select it we’ll only see the one record we are collecting. ClearCollect erases the collection, then adds our 1 record, no matter how many times we select it.

However, if we go back to the Collect button and select it, the collection we created with our ClearCollect button does not get wiped, and we can add more values to it. Selecting our Collect button does not wipe the collection, it continues adding values each time we press it.

We want you to understand the behaviors of these different functions so you can decide which function you want to use for your app.


How to use input controls to populate the collection

You probably won’t want to hard-code the details of your collection, you’ll probably want your users to be able to input data into your collection. To do that, you can use some Text Input controls with your Collect button. In Shane’s example, he uses the inpName.Text as the Name value and Value(inpPN.Text) as the PN [phone number] value. We use the Value() function so that Power Apps understands that we want to put a number in that field instead of a text value, so the Value() statement declares that. Notice now that his Collect statement becomes dynamic, according to what is entered into the Text Inputs.


Collect(colStuff, {Name: inpName.Text, PN: Value(inpPN.Text)})

Notice how each time we change the text inputs and select the collect button, it adds one record at a time. We must change the inputs each time, but we have now made it so that users can input data.

Once you define your collection, it doesn’t care how it gets the data. It doesn’t matter if we use our original Collect button or the new collect button. It can be hard coded, or manually entered. It could use a formula, too, such as 12+12 for the phone number. If we provide Power Apps what it wants, we can give it whatever collection information we want.


“Don’t overthink these things. They just want what they want. Give them what they want, and they will be happy.” – Shane Young

What are some Real-world examples of using Collections in Power Apps?

An expense report app is one place where you would use a collection, as you categorize individual expense items. In the demonstration Shane enters the Expense Item and the Expense Cost, which are Text Inputs. When he selects the ‘Save’ icon, he’s simply adding these two fields into his collection. You can get a peek at how Shane builds this particular app here.

Likewise, you could build a collection based on items you select from a gallery. In this case, the OnSelect property of an item in the Gallery itself would Collect that item into the collection, one item at a time.


Caching Data Source using Collections

Another application for a Collection is for caching data. In this example below, we have two different data sources. The Gallery itself is using the Employees list, and we have added a Manager field which uses a LookUp to the Departments list to match this particular item with the employee’s manager. This is a terribly inefficient way to display data because each item in the Gallery must make a data call to the Departments list. So, if you have a hundred different items in your gallery, each one does a separate look-up from the other list (100 separate lookups). Do not set your gallery up to do it this way.

Instead, we can use a single Collect function to call all the items from the Departments list, since it is relatively small, and create a collection to use for the LookUp. Using ClearCollect, we can pass records from an entire table into our collection. In this case, we pass the entire contents of the SharePoint list called Departments into our Collection called colCow with this formula:


ClearCollect(colCow, Departments);

Since your collection resides in the app, it doesn’t have to make expensive calls to your data source to get the same information. Just one call gets all the data and loads it into memory inside of the app.


Now we can replace our costly lookup on the label in our gallery to connect to our collection, without the external calls to the data source:


LookUp(colCow, Title = ThisItem.Department).Manager)

This technique provides the same results but provides them much faster since we’re checking memory and not making a hundred calls to our data source.


Delegation Considerations

You need to keep in mind that Collect and ClearCollect are not delegable functions so if the list you’re trying to cache is larger than your delegation limit, you will not be able to bring in all the items (which, by-default, is 500 items, 2000 max). Watch the video on delegation several times (Shane says watch it “four times”) if you don’t understand what that is all about. You’ll know delegation issues by the blue lines and yellow warning signs that your app displays for you. You cannot cache a list that contains 10,000 items. There are some techniques people try to stack collections in their app to get more than 2000 items – don’t do that! Don’t ever build collections that are more than 2000 items if you can avoid it.


It is irresponsible to build Power Apps if you don’t understand delegation. – Shane Young

Writing data from a Collection to the data source

Another thing we can do with our Collection is to write the entire thing to our data source. To do this, we change the syntax of our collection statement. We would collect to our data source instead of from it, like this:


Collect(DataSourceName, CollectionName)

The formula above takes whatever is in the collection and saves it to our data source. The caveat is that the data structure from our collection must match our data source data structure for this to work, think about required columns matching up. The implications are that you can take bulk data and efficiently send it back to your data source.

If you want to learn some other techniques for sending bulk data from Power Apps to your data source, be sure to check out this video on Performance Optimization.


Removing an item from a Collection

Remember that a collection is simply a table of data. If we want to remove an item from the table, we can use the Remove function. In this case, Shane has added a trash icon to the gallery containing the collection and the OnSelect property of the icon is:


Remove(colStuff, ThisItem)

With the app in preview mode, selecting this icon removes that item from the gallery. It doesn’t remove the item from the data source, just the collection.

With a collection you can use all the table functions, including AddColumns, ShowColumns, Remove, etc. to shape the data in your collection to get it exactly what you want.

A final caveat, you cannot use collections with forms, and Shane is not going to teach you shenanigans for how you might possibly achieve that.🙂

12,461 views

Comments


bottom of page