Editable Grid in Power Apps | SharePoint List

hello everyone this is raza durani in this tutorial video i will show you how to build a responsive editable grid experience in powerapps with sharepoint as a data source with this grid you can create read update delete and copy data we will add validations filters freeze pane effect scrolling and all of this keeping performance in mind so let's check it out in action [Music] let's begin with creating a list in a modern sharepoint site we'll go to new and pick one of the microsoft list templates in this case work progress tracker use the template give this list a name i am calling it task assignments and click create now in sharepoint we have the ability to edit the data directly in a grid view fashion so if i click edit and grid view this will open that editing grid experience similar to excel and i can directly start adding content into my sharepoint list on the fly and as we can see this list has a wide variety of columns from single line of text to multi-select choice columns to choice fields date columns person type columns and more now once i've made the modifications here i'll click exit grid view so it'll go ahead and save the changes now let's try and build a power app i will create a blank canvas app we will pick the tablet form factor to begin with give the app a name and click create we need to enable the setting for responsive design we'll head over to settings under display ensure that scale to fit is turned off this now will enable the new responsive screen layouts that we can select so i'll head over to new screen and pick the responsive layout header main section and footer i've gone ahead and renamed the screen and let's delete the original screen in this case i will remove the footer in the header i will add a label and i've gone ahead and styled the label and added the text responsive grid we need to connect to our data source which is sharepoint so i'll go to data sources search for sharepoint connect to my sharepoint site connect to my sharepoint list we will add the headers for the grid experience so for that inside the main section container i will go ahead and add a container control and i have renamed this to grid header the flexible height is turned on let's turn this off and give this header a fixed height let's say a height of 40 pixels i will add buttons so here's my first header i will position it on the left i'll change the text to work item and this button i'm just using for displaying information so i'm going to change the display mode here to view i'll just copy this button and paste it position it right here add my second header now these headers that i'm creating i would like the width of these headers to be different because the controls that i will be positioning in the grid would be having different widths and here i would like to highlight a great blog post by mikhail here he showcases how we can create a gallery with a scroll effect and this is very handy when you're trying to build grid experiences where the number of columns would span beyond the width of the screen i've gone ahead and renamed all my header controls so i can identify them clearly just by reading the name of those controls the x property of category which is currently hard coded to 160 i'm going to change this to the following i will change it to the x property of my first header plus the width of that first header control i will do the same thing for progress the x property for progress would be the x property for its preceding column which is category plus the width of the category column so it sits right next to the category column and i will repeat this for each of the following controls once i've set this property for all the header controls observe as i increase the width of a particular header control all the header controls in the grid will automatically reposition themselves so if i increase the width of work item the other controls will move ahead if i increase the width of category they will move ahead and so and so forth it could even happen that it goes outside of the width of the currently available space but that is fine because we are designing a responsive grid and we will provide a slider control to the user so that the user can navigate through the entire grid experience now if i would like to increase the width of let's say the due date column i don't even see that column right here i can just select that control go to its width property and change the width manually so let's say i need double the width i'll just change it to 320. if i preview the app you see the due date column width has increased i'll just give a darker theme to the header of my grid now to create that excel like grid experience we would need to utilize a gallery control so inside my main section container i will go to insert go to gallery and select the blank vertical gallery and the data source for this gallery i can directly connect to my sharepoint list but here my goal is to create a grid experience that is also performant in nature and because it's a grid the user is going to make a lot of changes i would like to only push the changes once the user exits the grid experience my goal is to update everything inside a collection so that once all the changes are made we can patch that collection to our data source which is our task assignments list in one go so firstly to create a collection that can get the data from my data source in my home screen i will add a button i'm just going to call this btn load data so on select this button i will write the following function clear collect i'm trying to clear and create a collection called call grid data which is a collection that holds all the data of the grid and the data for the grid for now i'm just going to directly get the data from my data source which is my task assignments list now if i want this collection to have data i'm just going to go ahead and click this button and here as you can see we have one record in the list so that record shows up right here now for my gallery control here that i've renamed to gallery grid for the items property i will leverage that same collection that i just created called call grid data and in this gallery is where i would need to create all those controls so that the users can interact with that data i will click on edit gallery so i have the first template of the gallery selected work item is a text control so i will go and insert a text input and i'll place this on the top left i want its width to be the same as my header control width so that if i ever need to change the width i just change it in one place that is the header control for my text input control that i have now renamed to work item the x property for this control i will set it as the corresponding header controls x property which is in my case my header work item control which is this dot x and the width for this i will set it as corresponding header control dot width now the beauty of this is if i increase or decrease the width of my header control you will note how the text control also follows that same width now for the category column which is a multi-choice column i can go and add controls like i need to add a combo box ensure multi-select is enabled for progress which is a choice i can add a drop down and so and so forth choices in those combo boxes how do i populate the choices in my drop downs or how do i build a people pick a column for my assigned to field and that can be challenging so here's the trick around this go and create a new blank screen in here go and insert an edit form control the data source for this select your sharepoint list and what this will do now it will add all those columns from that sharepoint list right here if any columns are missing you can go to edit fields go to add field and add those columns right here so i need the category combo box control because it's a multi-select choice column i'll just click on this data card go to that control copy this go back to my home screen for my gallery click edit and for the first item paste it right here so i'm just going to position this right here on the top now the moment i add this control i will get errors so we need to fix these errors and all we have to do to fix it is literally click this go to edit in formula bar and whatever errors are coming up just remove those formulas so default selected items just remove this okay tool tip remove border color removed and just to test this if i preview my app i've created an item in my list the gallery will show one item so i can see the data here in preview my category column if i open this i actually get the choices from my data source and this is a multi-select combo box we need to set the x property and the width property for this control relative to its header control and all we have to do now is just repeat the same process for all the other column types and these column types could be anything lookups person type columns so now my next control that i need is my drop down control for the progress field so i'll copy this same concept as before now my next control that i would like to add here is due date if i head back to my form control and just copy the due date column i will go right here paste it all you need to do is ensure that it sits right at the top remove all the errors for that specific control and just position its x property based on the header control which in my case here is header due date and the width property would be header due date dot width even though i don't see it in the grid experience if i preview this i can see that the due date control is sitting right on the right hand side i can just do a ctrl a to select all of them the height that i've defined here is 40 and for the template size property of the gallery let's ensure that you set it to that same height which in my case here is 40. that way the size of the template and the height of these controls are exactly the same now blank gallery that you add also comes with some template padding the padding here is five i will just change this to one so it looks more like an excel grid so i can select a control and literally tap through all of these now the data in this grid is being loaded on click of this button firstly i'll set the visible property of this button to false and for my home screen on the on visible property i can just go ahead and use the following formula select my button which is load data the moment the screen becomes visible this formula is executed that selects that button it clicks that button so that button click is acting like a reusable code here we want to give the user an option to have the grid in view only mode or in an editable mode so to do that for the app object if we head over to the onstart function i'm going to set a variable called var grid edit and i will set it to false to begin with now i need this value while i'm building my app so the way you can execute this function is you can right click on the app object and say run on start so this variable now will have the value which is false now i need to create a button which the user will see on this screen so they can set this grid in view mode or edit mode for my main screen container once again i will go and add a container control this container sits right here at the bottom i can right click reorder and move it to the top so this goes right above the header section i will turn this off and i will give this a definite height of let's say 50 pixels and in this i would like to add my button i'll add a button inside this container position it right here for the text property of this button if the variable that we created which will have the values true or false so if it is true put the value exit grid view or set the text value to edit in grid view on select of this button where grid edit which is that variable just change its state so i will say not off that same variable which is var grid added as i select this the text will change because the value of that variable is changing for my gallery control there is a property called display mode if var grid edit display mode dot edit else display mode dot view exit the grid view grid now is read only i cannot make any changes if i click edit in grid view my grid now is editable and i am free to make changes now if you notice in the view mode of the grid the first text control has this border around it most of the other controls i copied from that form control experience that logic of the border being hidden when the gallery is in view mode is already covered there so for this control i would have to add that extra step and i can do that right here border thickness if the grid is in editable mode if yes set its thickness to 2 otherwise set the thickness to 0. if i move out of the screen so i go to screen 1 and come back to the home screen observe how the gallery reloaded that is because on visible of the screen we selected our load data function so it loaded the data from our data source now the data that is loaded is not showing the correct default values if you observe there are values associated with this item none of them are showcasing right here for each of these controls we need to go and set its default values so for my work item text box the default property i will set this to this item dot title for my category column because it's the combo box i will go to default selected items and use this item dot the name of my column which is category for progress as well i'll go to default selected items and use this item dot progress i'll do the same thing for priority for start date we will have a default date this would be this item dot start date due date would be this item.due date and assigned to once again would be default selected items which is this item dot assign to person type column let's preview the app we can see all the values now from my data source are correctly being represented right here in this grid however if i was to make changes to this so let's say i remove marketing and add planning in this these changes now need to be written back to the sharepoint list when the user clicks exit grid view whenever any of these controls are changed i need to update the data but not directly in my data source first in that local collection in powerapps this improves performance we are not consistently updating data in our data source we only update it once when the user clicks that exit grid view button so on change of all of these controls we need to write a formula so we can just do control a go to on change change this to select parent now what is the parent for all of these controls it's the gallery the gallery has an on select function patch my collection we are trying to patch the current item in the gallery and the context for that is in this item so i'll open the curly braces my work item is the title column and sharepoint so title colon work item this control dot text category my category column dot selected items it's a multi-choice column progress would be my progress control dot selected it is a drop down control in my data source so it'll always get one value just that selected value same thing goes for priority start date would be my start date control dot selected date similarly for due date and finally for the people picker control here which is assigned to it would be my column name which is assigned to dot selected because it's a single select person type column once i've completed this formula i'll close the curly braces and close the round braces to complete my patch function one thing i will recommend here once again from performance standpoint for text controls there is a property called delay output go ahead and turn this to true when you turn that to true the on change function only fires once the user stops typing so for example let's say i'm typing here automate once i stopped typing only then the on change will fire if that property is not set to true every key stroke that i make here it will execute the onchange function so that's a pretty expensive operation i'm going to select additional options for my category i'm going to change the progress and let's say i want to assign this task to a different user let's say i want to assign this to sarah now when i click exit grid view i need to push the changes to my data source and the way we can do that is by just patching that collection directly to the data source because that collection was itself created from the data source so the schema exactly matches now only when i click exit grid view is where i need to go and patch my changes to my data source if bad grid edit patch my data source now which is my task assignments list with the entire collection collection of grid data select of btn load data so let's preview the app and test this out i've made my changes i'll click exit the grid now moves to read-only mode this time if i head back to my sharepoint list i can see all the changes being reflected now i want the user to create new records on the fly i will go ahead to my button container here and insert another button change its text to new item and on select of this is where i need to add a record to my collection i will use the collect function selection grid data and here i can use the formula defaults of my data source which is task assignments the defaults function will act like a new record entry that's being added to the data source bear in mind it won't add the record in the data source is just getting that empty record and adding it to my collection now every time the user clicks on this button this function is being executed and this defaults function also is an expensive operation because every time the user clicks on this it's going to query the data source and go and get the schema with the empty record from there and this is something that is definitely not going to change during the user session so what we can do once again is go to the app object and here create a variable call it where new record and use the defaults function here now the beauty of this is that this function runs only once when the app starts i will run this for now because i'm at the edit experience of my app and this variable now should have that empty record context so in my new item function here instead of using this function i will just use that variable that i just created now this button should only be visible if the grid is in edit mode so for the visible property of this button i will set it to var grid edit now one important thing to note is that the variable that we just created if i go to variables and if i look at that very new record variable and click on this and click on the record here you can see that it adds this empty record now the key thing in sharepoint is that the id column is always unique and here since i'm just getting the context of a new record it's not yet created in my data source so this property is empty if i don't set a value in this property when the on change function fires of any of these controls in the grid we go ahead and patch our collection with this item now because id is empty this item will never understand which item it is trying to update and what it will do is instead of updating the item it will create additional items so it'll just keep creating items which would cause issues so what we need to do in this case is whenever the user creates a new item for this variable we need to set that id plus the new records that are being added here i would like to place them right at the top on the app onstart i will add a variable called var number and here let's give it a number that's extremely large so if you know your sharepoint list is not going to grow beyond let's say 100 000 right just go beyond that i am in the edit mode of the app so i will run the onstart function and if i check this variable it has that value now when the user edits the grid and clicks on this button patch that empty record and for the id property i want to give it a value and that value would be that variable that i created which is var number that variable that i added i need to also ensure that i'm incrementing it so every new record that gets created gets its own unique id so we will use set where number is var number plus one click on new item it created a new item and if i keep clicking this it will keep adding items now if you look at the items property of the grid it's that collection and inside that collection here if we look at the id we've got the ids defined right here the original id coming from sharepoint and then the fake ids that we are providing temporarily inside the powerapp so i can sort the grid id descending and now if i preview the app we can see that in action here's the latest record that just got created it's going right at the top now if i was to go ahead and click on exit grid view none of that data is saved if i go to sharepoint i don't get any data why the reason is because when we are creating these new items there are ids being generated for those items although they are fake ids id 1 actually exists in sharepoint so when we patch that collection to that sharepoint list it knows that it is related to this item so it updates it now these ids are not existing in my backend data source so it is not going to update it so the key is when i am patching the data back to my data source i need to ensure at that instant these ids are set back to blank but i want to set it to blank only for those items that were newly created inside my power app and to distinguish that for those empty records the created column which is the inbuilt column in a sharepoint list would always be empty for those scenarios so i can leverage this property to check if it is blank if it is that means those are definitely the new records that are being created so back to the exit grid view button for this collection we need to add one extra formula before this which is update if the created column is equal to the function blank set the id to blank here i've already created two items so i'm just going to fill in a few details here on the grid and now if i click on exit grid view observe how all the values are being maintained and now if i head back to my sharepoint list you will observe how those new records have gone in and been created inside my data source now what about validation scenarios what if my data source has some mandatory fields first thing is we need a visual indicator to the user that there are mandatory fields that they need to fill out so let's say i click on new item and we want to highlight that title is a mandatory field in the grid so for this text box control let's go to the border color property if is blank of self dot text then put the border color as red else keep the color as is here we can see that work item now shows a red border color because it's empty let's make a change to this record and empty this one too you can do the same thing for all the other controls i'll show you one more multi-select choice columns which is a combo box control here is blank of self dot selected items since it's multi-select and you need to add another check or you can also check if it is empty so is empty of self dot selected items then put the border color as red else keep it as the gray border color i haven't selected a category here so it shows the red border color the moment i select it goes away if the user was to exit the grid view now leaving this empty it will cause issues so i need to handle the fact that there are mandatory fields in my data source that have to be filled in here so exit grid view all i need to do is check in my collection if there is even one record where the mandatory fields are not filled out if that's the case then we need to give an error message to the user before patching we'll add a condition if not is blank and here we're going to write a function called lookup my collection which is call grid data where the title is blank or the title is an empty string or my category column which is that combo box is empty or as blank if this is true then i need to display an error message here i will use the notify function to notify the user that please fill required fields else i will go ahead and patch my data source and here i will close my if function use format text once the patch is complete i would also like to notify the user so here i will say that the grid data is updated select btn load data that is when we reload the data after patching here and after notifying the user we can add that function just go ahead and use this also right here this completes my function and this time let's try and click exit grid view it gives the notification please fill required fields and once i fill them out and now click exit grid view once it updates the data in my data source it says that the grid view has been updated also now since we've bought this function inside the successful check if this variable is true that means the grid is in edit mode then we perform all of these actions but what if the grid is not in edit mode then we just need to switch the variable where this condition ends we will once again add that set function now here let's say i've added multiple items yes i need to fill them out i can make changes to my grid but what if i want to undo all the changes i don't want my changes to be applied to my data source once again in my button container i will insert a button i will call this undo changes now on select of this i will go ahead and select that button which is btn load data and when i click on this button i would like the grid to go back into view mode so i'll use set var grid data false so let's test it out preview the app click this it goes back to its original state if i edit the grid let's say for update project status i change this to completed if i click undo changes it'll go back to not started because that change never got saved against my data source and this button as well should only be visible when the grid is in edit mode which is this variable now we've built this grid keeping responsive design in mind so let's say i was to go ahead and reduce the width of my screen so let's say on a tablet experience maybe this is the width that's available you will observe how as the width shrinks the grid columns are now being hidden because there is not enough real estate available so once again going back to the blog post that i highlighted earlier from mikael what we need is that slider experience so for that with the main container selected we will go to insert input and pick the slider control we will position this in the center let's say we give it a maximum value of 500 and the default value we will set it to zero now as the user moves the slider we want this entire grid to slide along from the point at which you want the columns to slide i wanted to slide from the first column onwards i'll pick the first column which is my header work item and for the x property of this i will use 0 minus the slider control dot value let's preview this and let's reduce the width observe how we're using the responsive layout so the app is responsive the slider control is still sitting in the center now here we go as i start moving this observe how i can just navigate through the grid and this would be based on trial and error i have more columns so i need to increase this number beyond so let's say just to play safe here i'll just go to thousand preview this let's try it out now there you go the user can navigate through all the columns now what if we need a free span like effect that is as the user is scrolling through the columns here the work item column the first column should always be frozen it should always show up now the x property for my first column i will change it back to 0 and for the second column now for the x property here the formula that we put in place i will add to it as minus slider dot value now as i start scrolling ideally the first column should have been frozen but it's not the reason is if i select my first column in the container here it is sitting right at the bottom so what i need to do is select this reorder and bring it right at the top and i need to do the same thing for the gallery as well i need to ensure that this column sitting right at the top now if i preview my app and if i move this check out the free span effect in action let's go to a smaller screen size and try this out let's try and add delete functionality in here so i'll edit the gallery go to icons pick the delete icon here position it right at the start for this icon i will set its background color to dark green and i will change its color to white now if you observe my work item text box the icon is sitting above it i need to position this to start after the icon so for the x property here we have the relation to the header item so plus icon delete which is the icon that i just added dot with so now it will move ahead however now it overrides the other control so for the width property of this text box where it's the header control dot width here we will subtract that delete icon dot width and even though i change the width of my work item you will notice that everything will follow suit even if i was to scroll through the freeze pane effect is still an action now when the user clicks on this button i would like to go ahead and remove this item from my collection not from my data source because i want to only perform the final action against my data source once when the user clicks exit grid view i will use the following function collect call delete so i'm creating another collection here to store all the information of the deleted records this item and we need to remove this item from our grid collection and when the user clicks on exit grid view once all the validation scenarios are met when we are updating the grid here right after this we will use the following formula remove from my data source which is task assignments remove that collection that i created which is called delete and of course once this is done i want to ensure that i even clear that collection so clear that collection called call delete and since the user has the option to undo as well if they select this we need to also ensure that we are clearing that collection so i'll do that right here so let's say here i add a couple of new items i delete learn powerapps i delete one of my new items and for this one i just add some data now if i click on exit grid view all those changes are reflected here the previous record will get deleted if i head back to my sharepoint list here i get my new item which is build project plan and that previous record got deleted we want to show the delete icon only when the grid is in edit mode so the visible property for this would be where grid edit you can also give the ability to the user to copy items once again for my gallery i will insert a copy icon position it right next to delete once again i'll have to make changes to the first control in my grid the width this time would be minus icon copy dot width and for the x property here i would have to add icon copy dot with now when the user clicks on copy we need to create copy of an item now this is very similar to creating a new item so i'm just going to go ahead and grab this code go to the copy item function and just paste that same code there the only difference now is we need to also copy some of the data from this item to the new item that is being created so here you can decide which columns you want to copy so if i want to copy the title column we can say title this item dot title if you want to copy the category this item dot category so you can define which column values you want to copy as well the title the category and the priority are those three column values that i want to copy over whenever the user clicks on this button let's say the learn flow expressions let's assign that to sarah and i would like to create a copy of this task and assign it to another user james so now all i have to do literally is just click on this button it will add a new item and we can see that right here on the top that's where all the new records go learn flow expressions we see it right here it has copied the title it has copied the category and the priority priority was empty so it went in empty i could have copied other values as well and now i can just straight away go and assign this to james and one final thing that i would like to add to this grid is the ability to filter data as well and i would like to provide that filtering option to the user when the user is viewing the grid now remember for the grid we are working with collections a collect function at a time can only load 2000 records in memory so it's important that you provide the right filters to your users so that even though your data set is large in my case sharepoint that can handle millions of records as long as i provide filters get data directly from my data source in a delegated manner using delegable queries i have a full video series on that link will be in the description of this video so do check it out as long as i do that it will perform the query against the last data set and send the response back to my collection which can hold up to 2000 records for that one single query for my main section container here i will go ahead and insert a container i've moved this to the top and right below my button container i'll set flexible height to false give this a defined height let's say 80 in this i'll provide my filters give this container a background color and let's see here i provide two filters so if the user makes any change to these i would like to apply my filter so on change of both of these we'll go ahead and call our btn load data button that will once again load the data from my data source and this filter container i want to only show if the grid is not in the edit experience so for the visible property of this grid i will set this to not off var grid edit so if the grid is in view mode only then we can see these filtering options now when the user makes a change to any of these controls we are calling the btn load data function so i can simply perform a delegable query here which will query a large data set so filter my task assignment list where so starts with function my title column this is the text box control that we added here dot text and now i can close my filter function and close my clear collect function let's format the text now if i preview this app this filter would be applicable so let's say i want to search for my work items where the title starts with project so once i type and once i stop typing click out it will go ahead and apply that filter now if i edit in grid view we are just working with that one record only so if i was to change the status progress and click exit grid view once again the filter is in place only that record value would have changed to completed and here on the backend data source we can confirm that and now to include this filter as well here is what my formula looks like filtering a data source as well i've done a lot of videos on this i'll paste the link in the description of this video i can pick in progress there are a couple in progress edit and grid view let's prioritize them to high let's exit the grid view if i head back to my data source both the in progress items now have the priority high if your requirement is that only show the data to the users on the grid that they have created all i have to do is this created by dot email is equal to the logged in user dot email and just to confirm that it works here in my task assignment list i have an item that was created by sarah called update id ticket status if i head back to my power app i do not see that data right here so this was the first part of the grid view experience i have a second version of this grid view as well here the user can select items and say to update let's say the priority values of these items that i have selected to hide if i click update you see it updates them on the fly right here or i can bulk select and create copies of these items or i can individually select and say i would like to delete all of these items so all the operations of that grid would be performed in bulk so i'm looking forward to your comments of this video let me know if you enjoyed it if you did then do like comment and subscribe to my youtube channel and thank you so much for watching

Channel: Reza Dorrani

Views: 12,436

Rating: 4.9892182 out of 5

Keywords: powerapps editable gallery, powerapps editable grid, powerapps editable data table, 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, powerapps editable table/gallery like excel, power apps sharepoint, powerapps person field, power apps editable grid, powerapps edit collection, powerapps grid gallery, learn powerapps

Id: wI6SHGQ9ATg

Length: 42min 49sec (2569 seconds)

Published: Tue Sep 14 2021