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.
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.
Posts Related:
- Top 10 Letterhead Template - Free Download 2022
- Power BI Dashboard Showcase & Makeover ( REPLAY: Sep 16)
- Webinar Make, Airtable, and Typeform Better Together
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.
