In the ultimate Power Apps user guide you can use data sources directly or you could use collections or you could use variables. But what is the difference?
Table of Contents
In my example app I’m reading data from a SharePoint list and display this in three galleries. Within the app startup I’m setting a variable and a collection to the content presented by the connection to my SharePoint list.
The variable and the collection are set using the following two lines of code in my app on start code. The first line is creating a variable using the Set Function and the second line is creating a collection using ClearCollect.
Set(varItems, Connectors);
ClearCollect(colItems, Connectors)Now as my app loads the 3 galleries, we can see a difference quite quickly. When I count the items in the three galleries I’m finding 100 or 500 items. So that means that the connection loads only the first 100 items in my connection.
And as we scroll through the data, we will find that the gallery needs to load the next page of data and the item count is increasing by 100 every time I try to read the last items in the gallery.
The galleries that load the data using the collection or the variable however don’t show this behaviour.
So should we always use collections/variables?
In the above two short videos you can already see that the collections/variable based galleries took a lot longer to load, while the gallery that used the connection directly performed a lot better straight away.
But there are a few more differences.
Before the data has been loaded, the variable behaves in a different way from the collection and the datasource.
You might have noticed earlier that the variable returns 0 on an empty table, while the other two options return a blank value.
The variable of the type table, has been initialized as a table, while the collection has not been initialized until the data has been read.
Then the final major difference between variables and collections is the way Power Apps is managing them within the UI. If we first look at the collection overview, we can see that the data in the collection is being displayed.
When we compare the collections overview with the variables overview then we can see where the variable is defined (in our case in the App.Onstart) and where it has been used. While we can still see the data in the variable as well.
The above variables overview could sometimes help when you try to debug you app.
Recently Microsoft added AI Functions to Dataverse that can be used in Power Apps. In…
One of the outstanding issues with the new Power Automate Designer is Copy and Paste…
Within the SharePoint admin centre there is that little detailed overview, telling you the available…
Within Power Apps there are various ways to document your app. In this post I'm…
Recently I've been asked quite a few times to duplicate SharePoint lists as part of…
Today, I was asked about how to create a lookup to a document or item…
View Comments
Hi Pieter, would it be possible to filter datatable using another datatable. Trying to filter CDS table based on lookup table.
Yes, you can filter any data table on any data. Do you have any more details on what you are trying to do?
I am trying to build canvas app control for Quotes which I can embed on form for the subgrid(lines). There are items in item table but for the account on quote we have contract which contains set of items not complete and each based on the price list. If the price list match then only pull those items and show in Grid(another) for adding in the grid.
Quote -> Account(lookup)-->Contract(N)--> Contract Items--> Item --> Price List(N)
so Ideally I am looking for a way to grab all items with associated pricelist on Quote that fulfills Account contract as well. I know we have done it via JS but trying my hands on canvas app to see if this is possible.
Thanks
Hi Manish,
Your solution reminds me of the work I did here a bit:
https://sharepains.com/2021/02/18/create-pdf-documents-data-power-automate/
Within apps you could indeed use some variables or collections to get to the right data that you want to present in your app. You will probably have multiple queries to collect all the data from the various tables.
I just came across this and wanted to see if anyone has seen behavior I've been noticing:
1) On screen load, OnVisible has:
Set(varTempT1,
Table(
{
A:"Row1",B:1,C:1
},
{
A:"Row2",B:2,C:2
},
{
A:"Row3",B:3,C:3
}
)
)
2) On the screen that loads has a button with OnSelect code as:
ForAll(Sequence(CountRows(varTempT1)),
UpdateIf(varTempT1 As K,K.B=Value,{C:Mod(K.C,CountRows(varTempT1))+1})
);
Set(varTempT6,varTempT1);
Set(varTempT3,LookUp(varTempT1,C=1).A);
Set(varTempT4,varTempT1);
Set(varTempT5,varTempT1)
//;RemoveIf(varTempT1 As K,true);
//;Set(varTempT1,varTempT4)
3) On the screen, there are few labels and data table to observe the variables:
Label1 has Text: LookUp(varTempT1,C=1).A
Label2 has Text: With({T:LookUp(varTempT1,C=1).A},T)
Label3 has Text: LookUp(Table({T1:varTempT3}),1=1).T1
DataTable1 has Items: varTempT1
DataTable2 has Items: varTempT4
On click of the button, Label1, Label2 and DataTable1 never show updated value on screen, while Label3 and DataTable 2 do, why so?
By uncommenting the last two line in the button code, and restarting the app and loading the screen:
- varTempT1 is populated, other variables are empty
And on one click of the button:
- varTempT1 and all other variables (varTempT4, varTempT5 and varTempT6) become empty, It'd be expected that no variables would be empty
- if RemoveIf(varTempT1 As K,true); is replaced with Set(varTempT1,Table()) or Set(varTempT1,Blank()), and the last line (Set(varTempT1,varTempT4)) is commented out, the variable varTempT1 doesn't become empty nor does code show any error
Is there something I'm doing wrong, or Power Apps not working, or I shouldn't be using variables as Table type and instead use collections?
I would probably need to have a look at the app itself to get that one resolved.