Power Apps Bulk Data Operations | Create, Update, Delete, Copy & Import | SharePoint List

hello everyone reza here in today's video i will show you how to perform bulk operations in powerapps against a sharepoint list we will leverage an editable grid wherein we will add the following bulk features bulk creation updation deletion bulk copying data as well as bulk importing data from a csv file so let's check it out in action to begin with bulk updating data in powerapps i have a gallery that is in the form of an editable grid experience we can update data in the grid we can create new records on the fly there are validations we can exit the grid view to save the changes or we can undo the changes we also have refiners to refine the data in the grid now all of this was demonstrated step by step in my first video the link to which is in the description for this video so do check that out for bulk operations in powerapps we need to provide the users with an option to select multiple items and to do that we will add check boxes for my gallery control in the template item we will go ahead and insert a checkbox control i will place it on the left hand side remove the text associated with the checkbox and reduce its width we will give this checkbox a background color and since this control is sitting over the first control which is work item the x property of this control would be the existing x property plus the name of the checkbox control dot width and the width would be the existing width minus the check box control dot width so when the user checks the checkbox we would like to store that information in a collection we will use the collect function we'll give our collection a name called selected we will store this item which is the context of the current item inside that collection on uncheck we will use the remove function to remove this item from that same collection which is call selected and the checkbox control has a property called default this item in call selected that means if this collection already has the context of this item so show it as checked so if i was to preview my app and let's say i make a couple of selections here if we go to view collections call selected has two records and that's those selected items now we also want to give the ability to the user to check or uncheck all the items on the grid in one go in my header container i will go ahead and insert a checkbox once again position it on the left remove the text reduce its width i have renamed this to check box all when this is checked i would like to load all the data in this grid into my collection which is called selected so i'll use the function clear collect call selected this would be my grid collection which is called grid data and when this is unchecked i would like to clear the entire selection so we can simply use clear call selected let's test it out if i select this all the items are selected of course i can uncheck specific ones if i uncheck all of them would be unchecked now these check boxes should only be visible when the grid is in the edit experience so for the main checkbox i'll go to the visible property and set its visibility to where grid edit and for the checkbox inside my gallery i will do the same thing which is where grid edit so now if i preview the app and exit the grid view we can see those check boxes are hidden however we get this little space here for the gallery control that's because of the related x and width properties the way we can fix that is for the check box in the gallery there's a property called width the width in my case is 40 can simply add a condition if the grid is in edit mode then put that value which is 40 else put the width as zero now if i go into the view mode of the grid we can see how the control now positions itself now let's try and perform all the crud operations against our grid in bulk mode first we will look at creating records we already have a button called new when i select this we add a record to the collection and that record shows up on the top and if i need to create multiple records i got to keep clicking on this new button but what if i want the user to decide how many records they want to create in one go in my button container here i will go and insert a text input place it next to the new button we will change the format to number the default value we will set it as 1 and this is where the user will define how many items they would like to create when they click on the new button now on click of the new button we had the code which was patching the new record context to our grid now all we need to do is patch this multiple times depending upon the number in this text box i have renamed my text box to txt number for all off we'll use a function called sequence here we will use the value in that text box which is txt number dot text it will return as a string so we will typecast this to number by using the value function so this function will run a for loop based on the number that's specified in that text box and for each of those we would like to create a new record in the grid let's test it out right now the numbers one so if i click on new creates one record let's say i want to create five records i change this to five click new and in one go it created five records in the grid let's give the ability to the user to copy items from the grid and this based upon the selections that the user makes in the check box so i've added a button in my button container the text is copy the visibility is where grid and on selective copy first we will set our global variable which we had created for storing a unique number so that we set the id of any items that are newly created in the grid a copy as well would be a newly created item just that it will have pre-filled values based on the item that was selected for creating the copy for all of collection selected we will collect in our collection of grid data we already have our variable new record that stores the value for creating a new record in the grid we will patch the id column first with our variable and then add information of all the other columns that you would like to copy so for the title property of that new record patch it with the title value of the item that's currently running in the for loop which is our collection call selected and the same thing i did for the other column values as well so you can decide which columns you would like to copy as well right here this completes the copying of the data and once the data is copied all those selected items in the check box now i would like to uncheck them so for that we can use clear of call selected and the main check box that we added we would like to reset that as well reset my checkbox all i have two records selected i'm going to click on copy and it goes ahead and creates a copy of both of those records and places it right here on the top now if undo changes was clicked none of the changes that have been made to the grid would be saved and if we look at undo changes all we do here is we go ahead and reload the grid with the data from our data source and then put the grid in view mode but here we also now need to ensure that we clear our collection which is called selected and we also go ahead and reset our checkbox if i was to multi select and click copy the copies would be created here let's say both of these tasks i want to assign it to reza and when i click exit grid view i have both of those items created in my data source next let's add bulk delete my button container i've added a button text delete visible property set to var grid edit and on select of this we need to first remove the items from our grid collection remove from call grid data and we would like to remove all the selected items which we can get directly from our call selected collection now this is being removed from call grid data but we also need to create a collection to record all the items that are to be deleted so that when the user actually clicks on exit grid we go and delete that from our data source i will use collect and create a new collection called call delete and here i will simply add call selected to that collection and once this is done we will also ensure that we clear call selected now when the user clicks on exit grid view we have the code here to go and patch our data source with all the information in that collection of grid data but with this we need to also now ensure that we remove those items from our data source that are in that collection called delete and to do that we will use the remove function against our data source which is task assignments and let's remove all the items that are in this collection which is called delete and once that is done let's go and clear our collection which is called delete if the user clicks undo changes in that case also we need to ensure that we are clearing our collection which is called delete let's say i select the flow in a day records and now i'll click on delete they both are gone from the grid if i click exit grid view it will also go ahead and remove it from the data source if i was to go back into the edit experience of the grid and let's say i selected all of the records and i deleted them right now they are just deleted in the grid collection not from the data source if i click exit grid view it will clear it all from the data source but if i click undo it will not execute the delete operation and all those values are still maintained in my data source let's try and look at bulk updating items now to provide the option to the user to update the properties of the items that they've selected all in one go i need to have another container added to my screen so for my main section container i will go ahead and add a container reorder this and place it right below my button container i'll remove flexible height set the height for this one to 60 let's give it a background color and i have renamed this to bulk update container now in this is where i can provide controls so that the user can fill values and then click an update button which allows the user to update the information and those controls directly into the selected items of the grid first one let's take progress so in this i've added a label for progress and now i need the values for progress instead of adding a drop down control i'll literally just copy the progress control from my gallery and paste it right here position it next to this the errors i'll just remove the formulas where the errors are occurring for this control and if i was to preview the app the user gets the progress drop down values now the value that the user selects here is what i would like to push and update against all the items that the user has selected so i've added a button the text is update and on select of this the formula would be as follows update if so we want to update our collection of grid data if this record is in collection of selected go ahead and update the progress column with the progress control that i added i renamed it to progress update so the name of that control dot selected once again i'll ensure i clear my selected items and reset my main check box which is check box all in case that was checked now this bulk update container only comes in play if the user has made at least two selections so this container if i head over to its visible property the visibility of this container would be var grid added so the grid should be in edit mode and count rows of our selected collection is greater than one so if i preview the app right now i don't see that bulk update container if i make a selection i still won't see it but the moment i make a second selection anything greater than one this container will come alive so let's say i picked the first four records and the progress for this i want to change it to completed i will go ahead and select update you can see this live in action all those four records the progress for those have changed on the fly to complete it if i was to select everything now i can go ahead and bulk update all the items in my grid in one go now in this case i've put progress we can add controls for all the other column values right here i'll show you one more my people pick a column so i've just added the label and brought the control here and for the update button click we will also update the assign to field with our assigned to update control dot selected so let's say both of these tasks which are currently assigned to james i would like to assign it to reza and change the progress to in progress here i'll pick reza let's click update and for both of these tasks we can see the status going to in progress and the assigned to here is reza if i click exit grid view it will push all those changes against our data source a bonus feature of this video what if i would like to bring in data from an excel file i have a csv file here comma separated values i have created 100 tasks i've given them a name i have the category defined the progress the priority and the start date and let's say i would like to bring all of this into that grid key point here is that this file is a csv file comma delimited if there is a comma inside the values itself that could potentially cause an issue in my case here it's extremely simple i don't have that scenario and the values for my choice columns in sharepoint i have those exact text values entered here in columns bc and d in powerapps we first need to give the option to the user to paste that data so i'll create a new screen using the responsive layout for the screen i'll give it a background image give it a header and in the main section container i will go ahead and insert a text input control change the mode to multi-line so the horizontal alignment i'll pick stretch and for the text input control i will turn on flexible height so right now it takes the full height i'll remove the default value i have renamed the control to csv data this is where the user will provide all that data in my header i'll add a couple of icons the back icon place this at the start on select of this we'll just execute the back function and then i'll also add the import icon now this csv file if i was to just right click and open this with notepad we can see all the data in that file is all comma separated now that is key because i will be copying and pasting this data into that text box and then when i hit that import button we'll be writing code to split each record with a new line character so i can get an array of each record and then we will also split it further by comma to get each of those independent column values so the user can simply go to the notepad version of that file copy it paste it right here there is any blank space at the end just need to ensure it's not in play otherwise it will add a blank record in our grid and once this is done the user will hit this import button to import all the data now on click of this button i'll go ahead and create a collection i'll call it call data import and here for the data in this text box control which i've called csv data i will first split it by the new line character which is character 10 and for each of those i would go ahead and further split it by the comma and once this function completes i will go and call the back function so we can go back to the previous screen now the previous screen would be our main screen where we have the grid so in our main grid screen in the header container i will go ahead and insert an icon the import icon and when the user clicks this we will navigate the user to the import screen that's the name i've given to the new screen that we just created so let's preview this click this takes me right here the user can paste the data from csv this time i'll click this import icon so it will load the data in that collection and if you go to view collections call data import this is every row which is right here as a record if i select this we have all the values right here in a column called result now comes the key part of importing all this data into the grid now on our grid screen we have btn load data that gets called whenever the user visits the screen so here after all the data in the grid has been loaded here we will first check to see if that collection of bulk import data if that is greater than zero that means it contains data then we need to go and add all that data into a grid so it's just like adding new records so for that we had that variable where we were creating a unique id that's exactly what i'm leveraging right here and then for every record in that bulk import collection go and add it to our main grid collection and there patched with the data that is coming from that bulk import collection we get the results in a record so the way we need to grab it is by going to that specific row in that record so title was in the first row that's why i have the function first to get its value category was in the second row and category is a column of type multi choice in sharepoint so it expects tabular data with this specific format that's why i have that format right here progress is a choice column it expects a record in this format wherein you have a value property similarly for priority and then for start date i had to ensure that i convert that value which is string into date so that it maps it and then finally we need to ensure that we clear that collection of bulk import data that was just imported so let's preview the app go to the import screen so i've already copy pasted the data now i'll click on import so it will load it into that collection it loaded all those 100 records in the grid if i was to now click on exit grid view this will save all those changes against my data source which is sharepoint in one go and this took approximately four to five seconds to bulk import a hundred records directly in my data source and we can confirm that in our back end data source which is sharepoint right here now let's try and play with all the features that we have so we added refiners initially so let's say all my tasks have the text sample so i'm going to filter by the text sample now if i go to exit in grid view select check all i set this to not started and i would like to assign all of these to a user james so bulk update in one go i click update the grid gets updated progress changed assigned to changed now if i click exit grid view once again all the data will go directly into my data source that's a bulk patch operation one query in a very performant manner let's take another use case all the tasks that are currently assigned to sarah so here's sarah's tasks i want to assign it to reza so now i go to edit in grid view select all of these i'll set progress to not started assigned to is reza let's update these the tasks are now assigned to reza exit the grid view all that information now gets patched to my data source and if i was to now filter on reza i should have all of reza's tasks show up so that's the full experience of an editable grid if you enjoyed this then do like comment and subscribe to my youtube channel and thank you so much for watching

Channel: Reza Dorrani

Views: 5,629

Rating: 4.9620852 out of 5

Keywords: powerapps bulk update, powerapps update multiple records, powerapps create multiple records, powerapps copy record, powerapps gallery checkbox, powerapps import data from excel to collection, powerapps patch multiple records, powerapps bulk edit, powerapps bulk patch, powerapps, power apps, microsoft powerapps, powerapps paste from clipboard, powerapps create multiple sharepoint items, powerapps delete from gallery, powerapps copy paste from excel, reza dorrani, sharepoint, excel

Id: P6yqIpjmPrs

Length: 24min 33sec (1473 seconds)

Published: Tue Sep 21 2021