Power Apps Editable Table/Gallery like Excel (Tutorial)

hello everyone this is raza durrani in today's video we will build an excel like editable grid experience in powerapps the grid will perform full crowd operations creating reading updating and deleting records from a data source and not just that we will also add auto saving and undo operation so let's go ahead and get started with the video but first my introduction so let's begin with creating an editable grid excel like experience in powerapps now first thing what is an editable grid for folks who are very familiar with sharepoint edit in grid view experience which is very similar to excel when i can interact with my sharepoint list directly in this grid view experience so for example if i would like to add some sample description right here or make any modifications right here i can easily go ahead and make the changes on the fly in this grid like experience and if i exit the grid view this will go ahead and store that information in the sharepoint list from a microsoft database for teams experience standpoint when you create tables these tables as well have an edit data feature which opens a very similar grid experience for us directly in the context of microsoft teams the example that i'm taking into consideration for today's demo is a table that i have built in dataworks so here in dataverse is a table called work task and as part of this table i have gone ahead and created five columns name is the primary column i have a column called due date to maintain the due date of my work task i have a couple of choice fields priority and the status of my task and finally i have an auto number field that automatically increments based on data or records that come into my table so now what i would like to do is in powerapps create an excel like experience so i can go ahead and perform full crud operations create read update and delete on my data table in this case my work task table now in power apps the first step is for me to connect to my data source so i will head over to data sources search for work tasks and just like that i have connected my table that i created in dataverse in my powerapp the same concept will work for any data source of choice sharepoint sql so and so forth now because i want to create a grid like experience and i'm going to deal with multiple records whenever you have to interact with multiple data always think of galleries first so in my case i will go ahead and go to gallery and select a blank vertical gallery experience so i will go ahead and just connect it to my work task data source now because i want to create an editable grid like experience so the users can interact with the grid let's go ahead and start adding all the different controls with respect to the grid that i would like to create now in my case first i would like to show the task id which is that auto number field now that field generates data automatically you cannot edit that field so i will go ahead and add a simple label control right here and for the text property of the label i will go ahead and set this to this item dot task number next i would like to add the name of the task now because i want the user to edit this i will go ahead and add a text input control right here next is the priority of the task priority is a choice field so i will go ahead and insert a drop down control right here i have the status of my task which is again going to be a drop down control for me and finally i have the due date of my task so i will go ahead and add a date picker control right here if i play this app you will notice that i don't see any data that's purely because my table at the back end is empty if i had any data the gallery would start showing the rows now in order for me to give the user an option to add rows i will go ahead and add a button and on select of this button i would like to go ahead and create a new record in my table and the way i can go ahead and create this record is by using the patch function so patch my data source which is work tasks because i want to create a new record i will use defaults of work tasks so it will use the default new item behavior of my table and then all i have to do is provide my record details now in my table the only field that's required is name if there were any other required fields we would have to ensure that we fill that when we are creating the item in my scenario it's only name so all i've done here is entered an empty string in the name property for my record that i am creating right here now if i play this app and if i click on add record this will go and patch my table and create a new record and as i start clicking this if you notice it will start adding new rows into my gallery the template height of this gallery is something that we need to reduce so i will go and edit this and ensure that i reduce the height of the gallery next step for my grid i want to create headers so how do i do that very simple i will just go ahead and create labels and give these labels the respective names so in my scenario this is the task number task name and so and so forth so that's the headers that i've created for my data table i will go ahead and add some background to this header so i'll go to insert go to icons and select the rectangle shape place it right on top of my header controls and then make sure that my header controls are brought to the front so i've gone ahead and set the header now for my gallery control i will just go ahead and add a border to this so i'll give it a watercolor of blue and i will ensure that the border thickness is once now another important thing to note about this is if i select a control right here and if i hit tab on my keyboard so it's going ahead and moving through my entire grid experience right here so i can make changes to this on the fly the information that's being displayed right here in this grid is not correct for example if you look at the task name text field it has a default value of text input but i want to get the default value from my record in my table and the way i can do that is change the default value to this item dot name and when i created these rows when i clicked on add row the name was empty and of course all the other fields are going to be empty so as you can see the task name now is showing me the correct value which is empty priority and status both of these dropdowns currently have sample values on them but i want to get the actual choice values from my table so in order to achieve that for the priority drop down for the items property i will change this to choices of priority right priority is my column so go and grab all the respective choices from that choice column that i created in my table so if i play the app this will show me all my three choices which are low medium and high now if you would like to have a blank value in here in short allow an empty selection all you have to do is set the allow empty selection property to true and this should now allow empty selections so if the value is empty this should be empty to start with now for the priority drop down as well if i head over to the default value for the drop down right now it's hardcoded to one so it'll pick up the first value so let's go and change this to this item dot priority that's the name of my column so it will default to the priority column value and i will take exactly the same steps for the status column so choices of task status allow empty selection is true and the default value is this item.task status for the date field if you notice the default date is today's date let's change this to this item.due date and now if i go ahead and play this app this is now showing me accurate information because right now i'm just creating empty rows in my database and for these rows i can enter the task name i can select the priority the status and enter the due date associated with my task now to go ahead and delete records once again i will edit the gallery item go to insert this time go to icons and select the delete icon and when the user selects the item i would like to go ahead and remove this item from my cable so i will call the remove function remove it from my data source which is my work tasks table and i would like to go ahead and remove this item now if i play this and if i start clicking on delete this will start deleting the records from my data source so you can see going and deleting items if i click on add it will create a new item and the task number which is the auto number field is letting me know that new items are getting generated the latest one now is item number five now comes the key part how do i make modifications to these records and then push these records out to my data source there are multiple ways of achieving this one option is we can push the changes on the fly live that means the moment i select a cell in this editable grid and make any modification so let's say i add here learning powerapps the moment i make this modification this change gets pushed out to my data source now in order to achieve something like that what we need to do is for all the controls in my gallery that are editable so in my case i have this text box drop down drop down and date control i have gone ahead and selected all of these four controls you can hold the control key and select all of them and then for all of these controls there is a property called on change that means whenever these values change i want to perform an action what i can do right here is this so i can go ahead and patch my data source which is my work task table what is the record that i want to patch the record is nothing but this item and then what are the updates that i would like to push through now in my case the updates are with with relation to four columns in this case the first one is name so i can select name and for this i need to provide the data from this text control now i named this txt task name so i'm going to leverage the text control dot text and this is going to give me the data for the name comma the next one is the priority so priority will be the priority dropdown dot selected dot value why because my drop down itself is coming from my data source and i leveraged the choices function to get that information same thing for the task status drop down column and then for the due date column i will pick my date picker control dot the selected date now if i play this app this will be like a live editable grid so for example let's say i'm going to say learn powerapp i've made a change i'm going to click out this is actually posting the data right now to my data source i'm going to make another change i'm going to say immediately this is again executing a query i made a change status is complete due date let's say was the first that's it and i'm going to go ahead and let's say delete this record i'm going to add a new record here's my next record notice that my information stays intact if i go ahead and change the priority year add some additional text here if i create another row notice the previous records are intact so this is like a live editable grid and at the same time if i tap through this as i mentioned and if i make changes right literally very very similar to excel i'm just making changes on the fly this is going ahead and making the modifications for me however having done this does this work yes is this the best way of doing it is this the most performant experience no why well every change that i make it is pushing out an update to my data source that means there is a query that's taking place power apps has to query your data source powerapps is designed with the mobile experience in mind you do not want a scenario wherein you are continuously executing queries against your data source there is also something known as api calls there are limits based on licensing as to how many api calls a user can make in a 24 hour period every change that i'm making here to any of the cells in this editable grid is executing a query against my data source that means i'm losing api calls as i'm doing that so why not call this only once the user has made all the changes and we can see two scenarios in which we can execute all the changes at once the first one i will show you is an on demand save once the user makes all the changes now back to that same on change formula for all my four editable controls instead of patching it directly to my data source what i would like to do is something different let's first store all of the changes that the user is making in a collection let's hold it in a collection locally in memory and once all the changes are done by the user the user can then click on a button that we will provide and only then all the changes will be pushed at once with one single query to the data source also think about this if i am pushing changes out to my data source in my case i have this work task table now right now i'm just making changes to these four columns or four fields in my data source however if i head back to my data source and if i actually go to all the columns you will note that in this scenario there are plenty of other columns most of them are those system columns or standard columns that get generated for me even sharepoint for that matter when you create a sharepoint list it creates a lot of those system columns like created by modified by however in my use case right here when i'm creating this grid i only want the user to modify these four specific columns now when i'm going to create a collection if i create a collection using this item the problem is that this item is going to contain the context of that entire record that means all the columns that are there in my table for that record but in reality all i need is those four columns because i'm just modifying these four columns so a little bit of extra work but it will give you a considerable performance gain so how do i go about first creating a collection that has all of those columns so that i can go ahead and patch my data source on start of the app let's go ahead and execute clear collect to create a collection i'm going to give this collection a name i'm going to call this my collection task update so whatever updates the user has made and now what i need to provide this is all the columns from my data source so the first step that we will execute is this clear collect collection task updates default of work tasks if i go ahead and run the onstart function and if i go to view and head over to collections so you will note that this has created a collection for me that has a lot of columns right here and also observe that it has all the internal names of my columns and it has created an empty record for me purely because my formula stated create a collection and use the defaults function which creates like a new item for my work task and i've added that to my collection now i need to do a couple of things one of course is to clear that record because i want my collection to be empty and the simple way i can do that is by executing a clear function and now if i run this and if i head back to my collection notice my collection does not have any record and the reason why i did that is only because i wanted the schema of my table and i've got all the columns of my table right here but i'm only interested in updating the four columns because my data grid is providing modifications for only those four columns performance so what we need to do right here is use a formula called show columns so i'm going to call show columns now it's going to ask me for my data source the data source has to be tabular in nature for show columns so what we can also do is decorate the defaults function with a table function and next step is for me to add all the columns that i require now in my case notice intel is guiding me and providing me all the column names so i need the name so i'm going to pick the name comma i need the due date i need the priority and i need the task status so i have got those four columns that i am modifying however one very important thing to understand whenever you are modifying records in your data source using the patch function you do need the primary key of your table so if i head back to my table which is my work task table if you look right here there is a unique identifier associated with my table and this is the internal name of my column for sharepoint folks the internal name of your column is the id column for folks from sql you would have to create a primary key in your sql table that would be the name of the column that you would need in order to make modifications to your data using the patch function so back to my app i will also add a fifth column here as part of show columns which will be that primary key and once i complete this this is how my show columns function looks like so let's go ahead and run this function again and this time if i head back to my collection notice my collection is empty and here's my schema it has all those internal names of the columns that i wish to update next is for us to go and leverage this collection and start loading any changes that the user makes in the grid in this collection and when do we start loading that when the user makes changes to any of these four fields so back to the onchange function collect into the collection that we created which is collection task updates what are the different details that i want to collect the first thing is i would like to have the primary key in there so i'm just going to copy the internal name and then i need to provide a value for this which would be this item dot work task and the task name is coming from that text box same concept as before but all i'm doing right now is patching the columns in my collection and this is what my formula looks like now so every time a change is made to any of these items in the gallery i would like to go ahead and start collecting the data in a collection but wait what happens if the user makes multiple changes to the same row they change the task name they change the priority they change the status well because i'm continuously collecting it in the same collection it will create duplicate records for me but that's something that i do not want to do and in order for me to avoid that i would have to check to see if this item is a part of that collection or not and the way i can check that is this if this item dot the primary key which is the work task if the primary key is already existing in the collection so the collection dot the primary key column internal name so if the item already exists in the collection then in that case i want to perform an update called the update function and what are we updating we are updating our collection okay which record do we want to update look up the collection where the primary key is equal to this item dot work task so this will give me the association of the record that is currently in the collection for the current running item if it is already existing the next step is the row that you want to update it with and that will be exactly the same as the data that i'm adding in the collection i'm just going to copy and paste that right here complete my update function for a comma and that now completes my entire onchange formula let's go ahead and play this app and let's start making a few changes to my grid once again so i'm going to change this to medium change this to low let's go and add some more data right here now if we head back and look at the collection right here notice the collection has three records only and all the changes that we've made is available right here inside the collection and where is all the data in the collection being held locally in the app for that user session right now although the user has made changes none of these changes are currently saved that means we need to give the option to the user to go ahead and save their changes i've just added another button called save so here's the on select function if that collection has data that means the rows are greater than zero go ahead and patch my data source with the collection that's all i have to execute just give it my collection why because the collection has the same schema as my data source and it also includes the primary key so it will go and update the records and then once it does that i'm just notifying that we've gone ahead and completed that and then i'm just clearing the collection so let's go ahead and clear this right now so i've made a lot of changes i will go ahead and click save in one go with one query this has gone ahead and patched my data source okay so this is on demand saving what if the user is making changes let's say i just made a change and the user forgets to hit the save button at least there needs to be some visual cue wherein i can tell the user that you have not gone ahead and saved your data please go ahead and save it otherwise you will lose your changes for the gallery i will go ahead and add an icon and i will select this warning icon right here and let's also go ahead and change this to the color red because we want to warn the user so for the visible property we will change this to this item dot work task that's my primary key if this is in the collection so the collection dot the work task id column so if i play this app right now it's giving me a warning that i have not saved my changes right here if i change this value right now it'll give me a warning here as well so this is a visual indicator to the user that there are certain changes that are pending so they better go and hit the save button also i have gone ahead and added the same icon right here and added a label that says you have pending changes to be saved and both of these controls that i just added i will show them only if the collection has data right so if the collection has data that means there's something that's not saved so if i play the app right now notice it clearly tells me that you have pending changes to be saved so that's a visual indicator to the user that please go ahead and save your changes otherwise you will lose your changes so once i hit the save button this will go and patch my data source clear the collection collection is empty none of the warnings are showing up i would like an auto save kind of a feature wherein the app automatically checks to see if there is some data in the collection if it is just go ahead and patch it every x seconds you can go to insert and insert the timer control now if i head over to the properties of the timer control first thing is i want this timer control to automatically start and i want this timer control to keep repeating itself once it completes its duration the duration right now is one minute i will change this for example to 15 seconds the timer control has a property called on timer end that means when this timer ends i would like to perform some action and the action that i want to perform on timer end is literally the same action that is on the save button now instead of rewriting the formula what you can also do is this here is the name of my button so on this timer control when the timer ends literally all i want to do is this there's a function called select i'm going to give the name of my control which is my save button so literally what this timer will do is once the timer ends it will go and select that save button for me if i play this app right now notice the timer is running right and i'm going to hide this timer so the user is not even aware of the fact that there's a timer running now if i start making changes right here right i change this i change this and i'm not clicking on save right i'm just making modifications or maybe or maybe i'm not taking any action notice what happened right here the timer went ahead and selected that save button so you can create like an auto save like experience for your users the next feature that i would like to add to this gallery is either i want to view the grid or actually make it an editable grid so for that i have an image that i'm going to add right here and i'm going to also add a button now for this button what i would like to do is every time this button is clicked i would like to first set a variable so i know what mode i would like the grid to be in so on select of this button what we are going to do is this i'm going to set a variable i'm going to call this where grid editable that's the name of the variable that i'm creating and what i would like to do is every time this variable is clicked i would like to go ahead and set this variable to not of that variable that means if it's true it's going to become false if it is false is going to become true so i am creating a boolean variable right here also for the text property of this variable if it is true then in that case the grid will be editable so the user can change it back to view mode so i'm going to call this view grid or edit grid okay so if i play this app right now if i select this this will change to view grid or edit grid based on my click now of course nothing is changing in the gallery that's purely because i need to write the formula to make it editable or not so for all of these four controls if i head over to the display mode property by default the display mode is edit if that variable is set that means the variable is in edit mode then show everything in edit otherwise change the display mode of these controls to view right so now if i play the app if i click on view grid this is going to show me the grid in view format if i say edit grid this is going to open up the grid in editable format for me also the delete icon needs to only show if the grid is editable so for that all i have to do is set the visible property of this to that variable which is the boolean variable so if i'm viewing the grid it won't show if it's editable the grid will show up right here now one final thing that i would like to add to this grid experience is the undo option so what if the user comes in and makes certain changes but they would like to undo all their changes so right here i've added an image for undo and when the user selects this image is clear the collection of changes and then we will execute a refresh function on our data source so that's how easy it is to go ahead and perform an undo action to the grid that we just created literally just clear the collection refresh the data source so here's a fully functional grid view in powerapps connected live to my data source built this in a performant way you can add rows you can delete rows you can update rows you can view rows you can save all the data at once in a performant manner you can even have a timer control which auto saves you also have undo basically a fully functional grid like experience in powerapps i really hope everyone enjoyed this video if you did then do like comment and subscribe to my youtube channel and don't forget to hit the bell icon so that whenever i post my new video you get the notification thank you so much for watching

Channel: Reza Dorrani

Views: 63,407

Rating: 4.97786 out of 5

Keywords: power apps editable table, powerapps editable gallery, powerapps editable grid, powerapps editable data table, powerapps gallery grid, powerapps editable table, powerapps inline editing, powerapps edit table, powerapps tutorial for beginners, powerapps grid gallery, powerapps excel like table, how create editable excel like table in powerapps, powerapps edit data table, powerapps edit excel table, powerapps, power apps, powerapps tutorial, data table, how to, editable table

Id: 8I0Pt_8I6k8

Length: 28min 41sec (1721 seconds)

Published: Tue Dec 08 2020