009 - Load Sharepoint list into PowerApps

hello everyone welcome to the channel my name is saya jafri and today we will be talking about powerapps and the sharepoint list so many people were asking me how we can load the data from sharepoint list into powerapps uh which is pretty straightforward but there are some limitations so in on the sharepoint list you can load 5000 rows uh on the power apps there is a data row limit where you can load 500 rows or you can go max to 2 000. so how we can work on the sharepoint list and the power apps where we if we have a more than 5000 rows in our sharepoint list so how we can manage that in the power apps so let's see how it's going to work and we start working together to figure that out but before we start we forget something what we forget yes you forget intro [Music] okay so let's start and we will pull the data from sharepoint list so i already have a sharepoint list the sharepoint list name is financial sample and i do have a number of records which is more than 5000 so if i show you a here index key and an id so id is a auto generated number in the sharepoint list but i did create a separate column which is called index key and the reason is that i want to keep my number in the sequence in order to pull the number of records uh it's a best practice for the performance also that you pull the data that is only you need it so if you have a seven thousand ten thousand rows doesn't mean that you have to pull all ten thousand words rose apply the filter uh and reduce the number of records and just limit it so for example if you're working and you're gonna pull the data for today or for this week just try to filter that data don't pull because it's gonna impact the performance so when i created an index key uh it's just not only the column i create the index as well so i'll show you how i did it so go to the uh list settings and so even if you notice here when i pull this uh settings it says 5 600 items list view threshold is 5 000 right so there is a threshold and i already already have a 5 600 rows so the index i created i keep the name also the index key you can give any other name you can the sequence number or something and i created a index so i can create so i create one index and i do have that uh i can create uh one of maximum 20. so and how you create it you just go create a index and once you select and then you have to provide the column name so you already have the all the columns that you can create an index so i created on this column i'm gonna cancel this one okay so now we'll go back to our powerapps where we will pull the data of this financial uh sample 5600 rows so i will first use the gallery in powerapps so let's go to the powerapps i'm going to create a new canvas app but before i'm creating a new i already have one template i'm going to use this template so i will open this template and save as a file name that i'm going to use it for my application okay so the file is open uh i do have just the header on top where is the logo and the title and the picture whoever is logged in so i will save as let's say sample and save so first i will show you that how i can pull the data from sharepoint uh and how the sharepoint list get handled in the powerapps and i don't have to do anything but threshold is going to stop at the threshold so i will insert a gallery here so before i insert a gallery i'm going to bring my sharepoint list and go to the view data sources and data and i'll provide the name sharepoint and sharepoint and it's asking me the location so it's gonna pull the existing one uh i it's already exists on the digital files and it was financial sample it's here i'm gonna connect now the uh sharepoint list is connected to the powerapps i will insert gallery go insert use the vertical one and i'll just choose some filter here okay i'm gonna change the layout i don't need the image one so let's go back to the gallery and i will use the [Music] so let's use title and subtitle one let me get this one a little smaller column i'm gonna move this one on the top here so i have two rows i'll reduce the sizes 20 is too much 11 11 and the forms open sense so i'm gonna use this okay so font is same but it's bold i'm gonna make it regular normal move okay on the top put it on the top okay i don't need this one remove it i'll make it the smaller size so i can have more rows now i'll define the data source so the data source i will directly point it to the sharepoint list which is the financial sample sample okay so i have a columns the column name is title and the second column is a country i'll bring a couple more columns here i'll ctrl c and ctrl v just paste it as well i'm gonna change the name again for this one another copy paste and another copy paste i'll do another one okay so i have one two three four five six columns so i'll change the title of this one uh so let's see what we can bring so we already have tile country product and unit sold i'll change this one to product and this one is unit sold and i'll pick price and the amount so price is sale price and i'll put the sales amount size sales price i'll put this one the sale amount so which is just sales okay so i have these columns i'm just going to put labels on top quickly so this is that they can say title or segment actually and i'm going to use the let me get this on bold and i will just copy paste the segment was a country okay first i will paste all and then i'm gonna change the name okay just putting the headers quickly just make it a little nicer here so this one is the product and this one is the unit sold next one is the unit price or it's a quantity so units so first one was the sale unit sold and the sale price okay so this one is price and the last one is the amount [Music] okay so we have these columns now let's find out that how many rows we have here in our so i'm gonna put one more label here and i'll say total rows let's find out what i'm gonna do here i'm gonna concatenate and i'll do count rows of gallery my gallery name is gallery one all items okay so when we load the data um in powerapps from sharepoint it load hundred rows and it's automatically calculate so when i'll run this application and scroll it down it's gonna keep loading so if you notice that it's going to keep changing so it's 100 rows right now once i move it's 200 and since i will keep moving it will keep adding the number of rows so let's go let's see where it goes so i'm just gonna go and then i'll come back when it reached to the last row and we'll see where where we are okay so i have reached to 4 800 rows uh by keep scrolling this one so if i go further let's see the threshold is 5000 so it should stop at 5000 if it's not we'll see so it's 4800 it's already loaded now it should load 100 more which should go to the 4900 okay and the next batch should more hundred so five thousand so we reach to the five thousand rows and let's see if i keep scrolling down so it exceed or not okay so it succeeded it goes 5100 let's see how further it goes keep scrolling 5200 5300 and it's loaded five thousand three hundred and it's stopped so i totally have five thousand six hundred it still reached to five thousand three hundred which is interesting so let's see what happened i do have an index key uh which is the sequence number so let's see if i put the sequence number here and i'll change the name of this column as well to say it's a country so i'm gonna change this column name to country okay so if you notice that there is an error message so let's see what happened here i'm gonna change the name first it's a country it's total five thousand three hundred rows loaded and i got an error message says the request operation is invalid uh server response financial sample failed that time operation is prohibited because it exceeds the list view threshold so it does says that gallery loaded 5 300 i got an error message uh let's put the index key column here and find out that how many rows do i have so if i'll say ctrl c and ctrl v creating another column here so it's just gonna bring the name is the column it's called index key okay and let's see so it's 5 300 i did get an error message but i want to see that how many rows showing at the last key here so it did load 5 300 so even though it's exceeded from the threshold which is really interesting but we did get our error message so the practice is the best practice is that we should apply the filter here rather than pull all the data so our goal is to create a gallery where we can load all 5600 rows without having any error message uh and we are not going to use the gallery uh feature that it's gonna load hundred records uh whenever we scroll it down so what we will do i'm going to create a copy of this or let's make a duplicate screen of the existing one and i will load the data in a partially uh into the collection so i'll change the name of this low partial data screen and what i will do here instead of using the direct sharepoint list i will use the collection here so let's see if i write here collection partial load it will give me the error message because there is no collection exist and i will use the collection at the time when my screen is gonna be loaded or it's going to be visible so on visible i will write it here clear collect so clear collect function clear the existing sharepoint the collection so another sharepoint uh the existing collection and load the fresh data into there and i'm gonna say partial so whenever my screen will open or load it's gonna clear the existing collection whatever the data in the existing collection and load the fresh data partial load and i will be loading the financial sample into here okay now whenever i will load this one it's going to load the first 500 set of records so in this case let's see what is here we have uh we're using a gallery item here and we already assigned the call partial load here just want to make sure that the name is correct and we put it on visible of this screen that load this collection so i just want to match the name is correct yep so that's the same name you're using it and let's run this application so there is an error message and that was that a message i think for the previous gallery ever we had exceeded the threshold so let's go back yeah so let's run this application and i'm going to use uh so if i'll click on this screen it's going to start loading the data because it's i wrote that on the on visible that collect into this collection uh from financial sample and if you notice that there is only 500 rows were loaded so our goal is to load all 5 600 rows so what i'm going to do in this case i will put a button here uh where if you normally notice that when you go to the microsoft any uh either where there's a list of too many rows available there is a button at the bottom it says load more so we're going to use the same concept and we will insert a button here at the bottom and we're just going to say here on the text load more okay and when we will click this button it's going to keep loading the data until it's reached to the maximum limit okay so the buttons here we are loading first set of 500 records into the gallery and now we have to identify that how many rows do we have in our sharepoint list so like by going and looking into here i can see okay 5 600 rows but how the application will know so we have to get the latest or the last number of our index key to identify that how many rows we have and how we can do that so we'll go back to the application where the application is start so we will find out the last record number or the index number or you can say any sequence number whatever the column you want to set that what is the last value so i will store that value into one variable and i'm going to use the set function a sent function is stored the values of the either it's a record or a variable uh either a column you can store into a variable so let's say last number and i need to find out the so i'm going to use a first function which is going to give me the first record or i can get a specific column as well and i will sort my uh sharepoint list which is the financial sample and i'm gonna sort it by the index key and i will sort it descending so which is gonna give me the so if i'll started descending the first row is gonna be the uh the last number okay so i pick descending and then i'm gonna get an index key and i will get one more time for my so this one is gonna give me the whole row and i need the first the index key column only to store the var last number here so i'm going to use again index key and it's close so if i will run this one i can see what is my uh last number so if i'll run this on a start and if i want to see what is stored in my last number i can go to the file and the variables so if you notice that it's the last number is 5 600 so now we know that we have total 5 600 euros so then the next step what we're going to do when we hit load more it's going to keep loading until it's reached to 5600 okay so in order to do that i have to write a command here on select of this button that's what we have to do we're gonna load further records so we already have 500 we're gonna load more 500 so i'll write it here that collect the reason i'm using collect here if i will use clear collect it's going to clear all the data and load the uh the last set of the data so if i'm using collect it's going to append not clear so i'm going to use collect and i will use the same uh collection that i use for the first time which is my call call partial load and what i will do here i'm going to use the filter of my financial sample sharepoint list and what i'm going to filter here that the index key so right now my index key is 500 so i'm going to use the index key is greater than i'm going to count the rows of my gallery count rows of gallery so this is gallery one underscore one gallery one is for my previous screen so gallery uh need only one bracket not two gallery one and i'm gonna find how many rows we have all items and i'm gonna second condition so what i'm looking for that my index key is created so filter the data for my financial sample uh where my index key is greater than of count row so how many we have in the controls right now we have 500 rows in the count rows and my index key column is less than again count rows of the gallery item dot all items and at 500 one record so i can say less than equal to 500 or i can just put less than 500 plus so i'm adding 500 records into my collection and i'm just filtering from my sharepoint list from within the range so the bracket is closed for the account and i'm going to close the bracket for the uh collection so now it's going to collect 500 whenever i click button it's going to pull the the 500 set of records so let's see run this application and check so i have a 500 rows right now uh in my list also here and because i'm using the uh collection now so it shouldn't be loading more 100 sets gallery because if i connect it to the sharepoint list directly then it's going to load 100 sets of record but i'm connecting to the collection so that is in in my control now or in through the button i can control so let's see what is the last record here okay slow so it's 500 now total rows are 500 i'm gonna click load more and it should go to thousand right now because i loaded 500 more records so if you notice this la 1000 and if i keep going here it's 1 000 row loaded so if i keep clicking here 5 500 keep adding now we have 1500 now we have 2 000 rows we keep loading two thousand five hundred three thousand so in this way we are not impacting over the performance of our application otherwise if you load all 5600 in one time it may impact your application performance and it start freezing at the start and maybe stakeholders doesn't like that and it does happen normally even if you have a lot of data and loading in at once so if you notice we reached to the 5000 and now we are on 5500 and if you load more uh it's gonna reach to 5600 which is the maximum records uh if i scroll it down and see the last record here so five two nine four five five one one and five six zero zero so we all have five thousand six hundred rows uh my load more button is still there so if i click again nothing's going to happen because it's going to keep trying to load the next filter record but there is nothing so it's not going to do anything so what we will do we will hide this button or we can disable it if you don't have any data so for do that uh what i will do i'll just go on this button i'll put on visible so on select we are pulling a data of 500 sets of record and once we all load it all i can go to on visible so visible right now it's true but i will apply a condition that okay if my var loss number is less than or equal to count rows which is my gallery item and my last number variable is 5600 value and i'm gonna ch check the count rows of gallery one dot all items okay so if it's less than or equal to uh then make it false otherwise make it true okay so if you notice that the button is disappeared because we already reached two so if i will go back and run this application again so we'll start again 500 and you will notice that button is there so let's do it quickly i'm just gonna go run the app again from start okay one thing i'm gonna do it before i'm doing uh on start i will do the clear of the collection because my collection is already uh have a data and i have a collect com function over there so collect is going to keep appending so i have to clear the collection on the load of the application so let's do run on start it will wipe out all the data and then i'm gonna go to this partial load data screen because it's gonna be visible so it should start loading the data if i go here and then click on this one so on visible it should start 500 rows so ago is loaded 500 i can run the application i can do it directly from here so if you notice 100 1000 records keep loading 1500 2 000 2 500 3 000 and let's keep going so once it's reached to 5 the button will be invisible so then nobody will again click again and again so they will know that we have reached to the final setup right good okay and then a button is gone so this is how we can load all the data into our collection and use uh but again i said the best practice and for the performance try to use the filters as much as you can to reduce the number of rows so hope uh you will like this video please like and subscribe my channel thank you so much for watching this video

Channel: Digital 5S

Views: 141

Rating: 5 out of 5

Keywords: #digital5s, #powerapps, #powerplatform, #sharepoint

Id: ZR5UJ9Yh0So

Length: 26min 22sec (1582 seconds)

Published: Mon Jul 05 2021