【PowerApps】 The basics of Collection and the Collect Function and how to use them (ClearCollect、Clear、LookUp Functions)
This video is about the basics of Microsoft PowerApps’ Collections where you can store your data temporarily in your device and the Collect Function that is needed for the Collections with step-by-step a demonstration.
(Duration: 7:00)
What’s Collections? How to use the Collections
Hi, this is Mike Negami, Lean Sigma, Black Belt.
With PowerApps apps, you reach out to an external database and get or modify their data in the database. However, when you do it frequently, you may feel your app is a bit slow.
Therefore, instead, create a database temporarily in the memory of your device, add data or change data in the internal database, then at the end, store the completed data in the original external database. This will greatly enhance the user’s operational experience.
That internal database is called a Collection. It works like an array in other computer languages.
But if you have multiple users, be cautious about this point: you should make your apps in consideration of the possibility that while a user is using a Collection this way, other users may update the source data, which you cannot maintain the accuracy of the database.
For another application of Collection, immediately after launching the app, save all external data in the Collection and just refer to that Collection in the app. Then, you can create an off-line app.
Also, by using multiple Collections, you can do data processing and aggregation.
How to make a Collection: The Collect Function
Now, let’s try using a Collection on PowerApps. When you want to create a new Collection or add data to an existing Collection, you’ll use the Collect Function.
Add one button and on its OnSelect property, type [Collect(], you’ll see its explanation of the required syntax above. First, enter the name of the Collection you like. Here, I put [MyCol].
After putting a [, (comma)], next is ‘Item’. I’ll put [{Greeting: “Hello”})]. We’ll review the formula later, let’s look at the result first.
Put it in the ‘Preview Mode’ and click the button to test the formula. What changed now?
How to check Collections: ‘View’ ⇒ ‘Collections’
Here is how you see situation of the collection you made. From the menu bar, click ‘View’, ‘Collections’, then here is ‘MyCol’ that I made. In there, it contains ‘Hello’ in the ‘Greeting’ column.
Let’s look at the formula. PowerApps often uses this style of brackets. ‘{Greeting: “Hello”}’ We can see it from the result, the ‘Hello’ was assigned in the Greeting column.
The column name is on the left. In other computer languages the ‘=’ symbol is often used as the assignment, but in PowerApps, we use a ‘: (colon)’. Then, write data you like to assign on the right side.
Since ‘Hello’ is a text, you need double-quotation marks. If it’s number, you don’t need the double-quotation marks.
Let’s do one more thing. This time, I replace ‘Hello’ with ‘Good morning’. [Collect(MyCol,{Greeting:”Good morning”})]
Let’s see the preview. ‘Good morning’ was added.
Next, I’ll add [, (comma)], [Who] as a new column, [: (colon)] and [“Friend”]. [Collect(MyCol,{Greeting:”Good morning”,Who:”Friend”})]
Let’s see the result. Instead of using the preview mode, you can just press your ‘Alt’ key on your keyboard. A new record was added with the new data, ‘Friend’ in the new column, ‘Who’.
You should try each function like this. That’s a tip to learn new functions.
LookUp, ClearCollect and Clear Functions
Next, let’s use the data in the collection. For example, insert one label. In the Text Property, put [LookUp(MyCol,Who=”Friend”,Greeting)]. The label shows ‘Good morning’.
This function searches ‘Friend’ in the ‘Who’ column in the MyCol, then in the same record, it looks up the data in the ‘Greeting’ column.
The Collect Function adds records. If you put ‘Clear’ before ‘Collect’, the ClearCollect Function initializes the collection and adds a new record. [ClearCollect(MyCol,{Greeting:”Good morning”,Who:”Friend”})]
Insert another button. Then, put [Clear(MyCol)]. Then, try it. The Clear Function empty the Collection.
Put one whole table into a Collection.
Next, let’s put in one whole table. Create a Collection called ‘MyCol2’ with the Collect Function. I prepared an external data source called ‘Tbl_ProductMaster’ in advance.
I’ll Just put [Collect(MyCOl2,Tbl_ProductMaster)] with this button, then let’s see the result. You can see that the whole data source was entered.
I would like to do the reverse, which is to return the data of the last edited Collection to the first external data source, so I put [Collect(Tbl_ProductMaster,MyCOl2)], but unfortunately, you cannot use the Collect Function this way. I’ll explain what I should have done in another video.
Today we talked about PowerApps’ the basics of Collections and the Collect Function. Please see the official Microsoft information page about this. Its link is in the video description section.
＜＜Microsoft Official Page: Collect Function＞＞
＜＜Microsoft Official Page: Create and update a collection in a canvas app＞＞
“See these other popular articles.”
