Power Apps Editable Grid using Gallery & Modern Controls (2023)

hello everyone this video is a step-by-step tutorial on how to build an editable grid like experience in powerapps we will Leverage The Gallery control and use the new modern controls to create our grid experience this grid will support full crud operations undo sort filtering it is fast performant and it can patch all the updates that are made on the grid in one go against your data source so let's check it out in action [Music] let's begin with a blank canvas app I'll give the sap a name I'll pick the tablet form factor and click create we need to First connect to our data source the data source in this scenario that I will be selecting is a SharePoint list however the same concept will work with the database table SQL or any other data source of your choice to connect to my SharePoint list I'll go to data add data search for the SharePoint connector I can create a new connection or pick from my existing ones connect to my SharePoint site I'll plug in the URL click connect and search for my SharePoint list called student tracker click connect and the list is now connected in my power app great experience that we will create we will build it entirely by using modern controls and to enable them I will head over to settings go to upcoming features and under preview we have try out the modern controls I've turned it on and if I go to insert right here we have the modern controls to select from we will leverage under layout a blank vertical gallery for the data source property of this Gallery I will connect it to the student tracker list and if I head over to edit gallery this is where I can start inserting controls now my data source has a couple of text columns title and last name I will insert the text input modern control and I'll insert a second one and place it right next to the first one then I have a choice column called subject and a lookup column called grade and for both of these I will leverage a drop down control then I have a date column so I'll pick the modern control date picker and then finally I have a yes node column type and SharePoint for that I will insert the checkbox control now for the template item of the gallery which is the first item in the gallery if I select it I can reduce the height of the template item in this Gallery I have multiple rows these are actually the rows of data coming in from my SharePoint list because the gallery items property is my SharePoint list and this gallery has a template padding property I'll change it to zero and if I was to preview the app and if I click on the first text box you can see how the modern control highlights that I have selected that specific item and the best part I can even tab through the entire grid experience my first control that I will rename txt first name this control has a property called value I will set it to this item which is the current item of the gallery control Dot the title column from my SharePoint list so this plugs in the first names from my SharePoint list that's coming from the title column let's try the same for the last name control rename to txt last name value this item dot last name subject is a choice column here I have a drop down control that I have renamed to DRP subject there's a property called items here I will leverage the function choices my data source which is student tracker Dot the name of my column which is subject now this returns a table that has a column called a value we go to the properties panel on the right for fields we click edit we go to add field select value and add and here we can see the values for the subject drop down control now this control has a default selected items property I will leverage this item dot subject and we can see all the subject values being populated we have the lookup column called grade for this drop down controls items property I'll use choices of my data source Dot the name of my column which is grade lookup columns look up to a different list in SharePoint you can see the table has multiple columns the ID of the item in the other list and its value so in the properties panel on the right we go to edit fields go to add field select the value click add and now we have the grade values loading in this drop down based on the lookup list and for this control default selected items will be this item dot grid then we have a date column for the date picker control for the property value we will leverage this item dot reported date for my yes no type column it's a checkbox control it has a property called checked this item dot the name of my column and this checkbox control has a label property that I will change to active now this Gallery works like an editable grid where the user can make changes to any of the values I will leverage one collection and one variable so for my gallery if I go to edit gallery I will insert a bottom now this button I will place it on the right hand side the button has a property called on select now this button is what we will Click by calling the select property of the button whenever any of these control values are changed I will use the function collect to collect data in a collection that I will call as call grid updates and in this I will first collect the entire item so basically I'm adding the current row into this collection however what if this row already exists if is blank lookup call grid updates where ID which is the unique identifier of every item in a SharePoint list is equal to this item dot ID in that case go ahead and collect the current record in the collection let's format text so we are checking to see if the current Gallery Row item ID is it existing in that collection if the response is blank meaning it doesn't exist in that case only go and collect the current row information in the collection to update the item that is in this collection with the latest values coming in from these controls we will Leverage a function called update if so update my collection if the ID is equal to this item dot ID and this will show all the columns in my SharePoint list so let's begin with the title column so I will use txt first name Dot value comma last name this will be txt last name dot value comma subject my drop down control subject dot selected comma grade DRP grade dot selected comma reported date date picker control dot value and finally my column called active my checkbox control dot checked I will close the record I'll close the updated function I'll click format text and this is what the formula looks like I'll hold the control key and start selecting all of my controls and all of these controls have a property called on change the select function my button control is called button canvas one so I'll select this I'll preview the app for my first student Reza I'll change the subject here to English for James I'll change the grade to B so I made changes to two different rows that means it would have clicked that button to add two rows in my collection and on the left navigation we have this new variable section and if I go to collections here is our collection called call grid updates let's go to view table and we can see those two rows right here I'll close this and I can make multiple updates to those same rows even if I do that I'm still modifying the same row so we can see the collection still has two rows but if I start modifying any other row we can see the number of rows on the collection being updated we head over to the visible property for this button control I will set it to false so the user does not even realize that that button is existing in order for the user to save the changes I will insert a button control the text for this button I will call it save you can even add emojis the type for this button I'll set as primary on select of this button patch to my data source which is student tracker comma the name of my collection now this collection includes details about all the columns in my SharePoint list and there are certain columns that are of type read only so what we need to do here to ensure that it successfully updates in one go I will use the function show columns on my collection of grid data and here I'll start providing the names of those columns my columns are tidal last name subject grade reported date and active and one more column that I need to ensure that I add here is the ID call this column will help the patch function identify which item it is trying to update in my SharePoint list because ID is that unique column I'll click format text next I will use the nodify function to give the user a notification message notification type success and I will show this for three seconds I need to ensure that I clear my collection because the grid updates collection has been patched to my data source so if I preview the app I had made some changes let's click save says the grid updates have been saved successfully if I go back to the SharePoint list I can see those changes however my gallery is not showcasing the latest updates that's because I need the gallery to kind of reset and to do that when I'm performing save right at the end here I will create a variable called where reset I'll set it to false and right on the next step I will set it to true basically I'm changing the state of a variable and this variable is what I will use to filter the items property of the gallery and I'll simply use that variable now you can see that the grid is showcasing the correct values from my data source let's try making changes again I'll change the grades to a for the first two I'll change the dates to 28th for the next two I'll click save is performed the values are reflected here on the grid as well as in my data source now to this grid We'll add column headers I'll go to insert I'll first add a badge control this I will position it at the top now change its appearance to tint and then I'll start adding text controls the text property for this control I'll call it first name in the modern controls we also have an info button control I'll give the content student first name so when the user selects this it will show the information undo the changes I will insert another button the text for this I'll use an emoji I'll select this one and when the user clicks this clear my collection which is called grid updates and since I want the gallery to reset I will set that variable of mine to false and then set that same variable to true so here as I make changes if I select this button it will reset all the changes so for this buttons display mode property if count rows of my collection is equal to 0 meaning no changes were made on the grid then you set the display mode of this button to disabled else you set the display mode of this button to edit so we can see how it's disabled the moment I make some changes it comes alive if I reset it will undo the changes let's try create for that I will insert a button call this new on select of this I will leverage patch to directly add a record to my data source called student tracker to create a new record we have a function called defaults so this will use the default values for a new row plus I also have the option to set some values on that newly created row for example for my title column which is mandatory I'll just put a text called new student and I'll go ahead close this function once again I want the gallery to reset so if I preview the app click new notice I get an error that says one of my Fields is required so any required Fields I have to make sure there is some value associated with it so my last name is a required field so in this case I'll pick last name I'll give it a string which has a blank space so now if I click new it created a new record in my data source now that record is sitting right at the bottom for the gallery controls items property I will leverage the function sort to sort the results based on the modified column and this I will shorten descending order so my new student showing up right here at the top of course I can make changes to this click save the changes are made if I click new new it will keep adding those new rows now once I start making updates to the grid if I now click new it will add a new row and reset the gallery so I will lose the updates that were made so what I need to ensure is for the new button I will set its display mode property too if count rows of my collection if that is 0 in that case I'll set this button to edit else I will set this button to disabled so notice I had made a couple of updates so I cannot create another item I have to either save this or reset this now I am free to create more new rows for deleting I will select my gallery go to edit insert the trash icon I'll place this on the right hand side on select of this I will remove from my data source this item and I need to reset my calorie so I've gone ahead and set my variable to false and then true this button its visible property I will show it only if count rows of my collection is equal to zero I can delete the data and if I make some changes notice the delete option goes away because now I am technically editing data and since this entire gallery is connected directly to our data source we can also add filters to our gallery and I'll add a drop down here the items for this will be choices of my data source dot I call them subject go to fields add value so we have the subject options here to select from I have this reset is clicked I will use reset on the specific drop down control so this will go back to empty and then I can pick any of these options where I'm filtering based on that reset variable I'll add an and condition is blank that filter drop down dot selected dot value or my choice column and SharePoint called subject dot value is equal to that filter drop down control dot selected dot value subject is blank shows me everything if I change it to maths it will only show me the values for maths geography English and when I click save I'll also ensure that I reset that drop down control so right now it's filtered based on maths I'll change a few here to English if I click save all the changes are saved against my data source if I check English you can see the rows have increased and you can make changes to the logic of this grid based on your scenario for example if I was to pick English and add a new row I would like that row to default the value English so when I was creating the new row my subject will be the value from this drop down and once again if I make changes and two it will save if you enjoyed this video then do like comment and subscribe to my YouTube channel and thank you so much for watching

Channel: Reza Dorrani

Views: 197,577

Keywords: powerapps editable gallery, powerapps editable table, powerapps inline editing, powerapps tutorial for beginners, powerapps, power apps, powerapps tutorial, how to, Power Apps Editable Grid, microsoft power apps, reza dorrani, sharepoint, power apps editable grid, powerapps edit collection, powerapps grid gallery, learn powerapps, powerapps edit gallery item, editable grid, modern controls, editable grid powerapps, grid, editable, table, repeating table, powerapps gallery grid

Id: 76RJyaoW0BQ

Length: 23min 49sec (1429 seconds)

Published: Mon May 01 2023