Mastering SharePoint List Relationships in Power Apps | Create Responsive Repeating Tables

hello everyone Raza here in this video I will show you how to work with related SharePoint lists in canvas powerapps we will build a fully responsive power app wherein we will show the data from our main list as well as from our related list all combined and connected together in our responsive canvas app for the related list we will create an editable grid like experience for later sponsor horizontal scrolling we will look at the patch function working with collections there is a lot to unpack in this video so let's get started with it right away [Music] this is a power app that is connected to multiple SharePoint lists that are related with each other the home screen showcases data from the projects list the user can select a project look at the details of the project edit the details they can add new projects delete projects full crud operations for a specific project the user can go to its related tasks and here we have an editable grid like experience that showcases the tasks that are related to that selected project user can make changes to the data they can click save and the updates are made to the data source if I go back to look at the tasks for Project B we can see the updates in action I can add new tasks for the specific project save and the new task gets added to that specific project I can even go ahead and delete specific tasks and this entire power app is fully responsive if I was to view this on a tablet experience if I go to the tasks view here is the grid view experience I get a nice horizontal scroll experience here if I change the orientation of the device if I go to tasks once again I have my grid and here's my scroll bar which also has that nice sticky header like experience and here is the application in immobile form factor so let's try and build this application from scratch first let's understand the schema of our SharePoint lists I have a list called projects in which I have created Columns of different data types to store the metadata related to a specific project if I was to create a new item in SharePoint title is single line of text the name of my project status is a choice field I can Define the start date of the project I can Define the end date of the project project manager is a person type column so I can search for users from active directory I can give a description for my project and if I click save all that data will be stored in my SharePoint list now one project can have many tasks so I need a one is too many relationship between my projects list and my list of tasks this list also has columns to store metadata about a task the title of the task due date of the task description and priority but what I need to do here is create a relationship more like a connection in SharePoint between the two lists and to do that I will add a column of type lookup this makes a column from one list become the choices of selection in another list so I'll click next I will name it project lookup type is look up now select a list as a source here I will pick my projects list so I'll pick the title column from my projects list you can also go to more options and here deletions from The Source list affect this list Now by default this is no meaning if a project is deleted the tasks will still exist but here if I turn this on I get two options Source items cannot be deleted meaning if there are tasks related to a project the project cannot be deleted first we have to delete the tasks and then the projects and then the second option is deleting Source items deletes values in this list which is a good option in my case here since if a project is deleted the related tasks at that moment have no value so I'll select this option and I will go ahead and click save if I was to create a new item in the task list I can give a name to my task I can give a description Define a priority for the task pick a due date for the task and here is Project lookup this will list out all the projects from my projects list currently I only have a single project so that's why it shows me the option for that one project which is contoso if I select this and click save the task gets created and there is a lookup to my project so let's create an app I will start with a page design Gallery connected to external table so I'll select this choose a data set to start I'll pick SharePoint I'll pick my SharePoint site pick my SharePoint list which is my projects list and click create app the app is ready the connected list is my projects list if I click preview here's the project that I added here is the details of that project I can click new to add a new project I can give my project a name fill out the form click submit and now I have a second project that gets added to my SharePoint list I can edit the details of a project for example I'll change the status of this project in progress I can delete a project full crud operations and this entire screen is responsive in nature to add tasks related to this project and create a full editable grid like experience something similar to what the grid view experience in SharePoint itself allows for that I will first connect to my tasks list I'll go to data add data fix SharePoint pick the SharePoint connection pick my SharePoint site pick my tasks list and connect to create my tasks screen I will add a new screen I will pick one of the responsive layouts I'll pick header and footer here in the header section similar to what I have in the main screen if I select this label control it lives in this header container so I'll simply copy this label control go to my newly created screen and pick the header container right click paste so it adds that label the text for the label I will change this to tasks next I need to create that grid which I will create in this main container and here I have a footer container above the grid I would like to give those options for the user to either go back or create an item in the Grid or save the grid so I'll right click reorder and move this container up so it sits above the main container from the project screen I need an option for the user to go to the tasks screen I have a container here so I'll go to insert I will go and insert a button control the text for this button I will call it as tasks and when the user selects this button I will go ahead and use the navigate function to navigate the user to my task screen which in my case is called screen one so if I was to click on tasks it takes me to the tasks screen for the middle container here I would like to give an option for the user to go back to the previous screen so I'll select this container and add a button I'll give it the text back on select I will use the function back so if I was to click back takes me back to the previous screen if I click tasks it takes me to the tasks screen now in my project screen I am making a selection for a project so I'll go to tasks for the title I will say tasks for I'll put an ampersand here to concatenate this with records Gallery which is on the main screen dot selected Dot title so now we can see that we are working on the project called info path to powerapps if I go back that's the project that's selected in the gallery if I pick contoso and go to tasks it changes to tasks for contoso now when I land on this screen the first step I would like to take is to load all the tasks that are related to this specific project from my tasks list and show them in a gallery to do that on visible of this screen that I will rename to tasks screen on visible I will go ahead and create a collection I'll use the function clear collect I'll give my collection a name call grid data I will use the function filter my tasks list where my project lookup column dot value is that equal to the main gallery on my home screen records Gallery one dot selected Dot title as that was the column I performed the lookup on if I was to go back to the main screen I have contoso selected if I click tasks this collection will load with data and it's loaded with one record as my tasks list has a record that is related to contoso now to show these tasks in main container I will insert a Gallery control I will select a blank vertical gallery I will rename this gallery to tasks gallery the items property for this Gallery will be the collection that I created called call grid data and since I'm creating an editable grid like experience in this Gallery I will add controls specific to my column types title is a text column I will edit this gallery and insert it text input control so I'll pick text input I'll place this on the top left I will rename this control to txt title the value property for this I will set it to this item Dot kind of so it will pick up the title of the related tasks for project contoso now on very similar lines I will add description due date and priority description is text value is this item dot description due date I'll pick date picker value is this item dot due date and priority is a choice column so I'll pick drop down the items property I will use the function choices in my tasks list my column is called priority which is of type choice so this will load all those choices for the drop down control fields I'll go to edit add field pick value so this drop down now has the choices coming from my SharePoint Choice column and for the default selected items property of this drop down I will use this item Dot priority now these are input controls where the user can make changes and every time A Change Is Made I would like to store those changes in a collection and only submitted to the data source which is my SharePoint list if the user clicks on a button that I will be adding right here in this middle container and set the text of this button to save now this is more like an action button where an action will be performed back is taking me back to the previous screen so for this button I'll change its type to secondary now whenever A change is made to any of these controls I would like to store the updated data in another collection in this tasks gallery I will insert a button I'll position it here for now I will be setting its visibility to false I'll call this vtn update and now I will go ahead and select all of my four main controls that the user can change on change of all of these I will use the function select and select that hidden button that button has an on Select Property collect call grid updates and I will store the entire item that's the current item in this gallery and there could be a possibility that this updated grid already includes the current item because I can make multiple changes to that same row so in order to avoid duplicates if is blank lookup all grid updates where ID that's the primary key in a SharePoint list is this item dot ID so if this is blank meaning the item is not existing in this collection only then go ahead and add this item to this collection and right after this update the data in my new collection which is called grid updates where ID is this item dot ID and here I will Define The Columns that I will be updating the title column its value will come from txt title dot value comma description will be txt description dot value and priority I need to create a record that has a property called value Erp priority dot selected dot value and when the user clicks the save button on select I will patch to my tasks list I would like to patch data from that collection of mine which was called grid updates it is important for us to use the show columns function to only pick the key columns that we want to patch as well as the unique identifier which is the ID column so show columns from call grid updates I'll put all the names of my columns title description due date project lookup and do not forget the all important ID column so that's the function that I will use to patch the data to my data source and once this patch is performed I would want to clear all the data in this collection which is call grid updates so if I preview the app now let's make a few changes project plan talk let's say I'll change the date to the 24th change the priority as well if I click save this will go ahead and update that specific item in my SharePoint list next I would like to give the user the option to create an item in the middle container I will insert a button I'll call it add now and this is selected use the function patch my tasks list defaults of my tasks list the title new task I would like the priority to be defaulted to medium project lookup value my records Gallery one dot selected dot title and I also need to set ID records Gallery one dot selected Dot ID curly brace curly brace round breast like format text so if I click preview now if I click add this will add a record to my SharePoint list but I don't see the value in the grid every time we make an update to the data source we need to ensure that we are reloading this collection to avoid repetition of this formula I'll add a button on this middle container BTN load grid the logic that I wrote on visible I will copy that code and for button load grids on select I will use that same formula task screen on visible I will change this to select BTM load grid when I click add right after adding the new row I will call that same button when I am saving after I save I will call that button and this Logic for clearing that collection this as well I will add it to this PTM load grid so now if I go back to my main screen and click tasks notice it shows that there are two tasks related to contoso and if I click add it will add a third task and so and so forth now my gallery has a lot of white space on it if I edit the gallery I have a property here called template size this I will set it to a number so I do not get any white space in my case so this now looks more like a grid experience and notice I can simply tab through I can start updating values if I click save those updates are being made in my tasks list and if I go back and pick a different project and go to tasks I have no tasks for this project the save button should only be enabled if the user has made any updates to the grid so for the display mode property of this button if count rows call grid updates if this is greater than 0 meaning an update was made set its display mode to edit else set its display mode to disabled so for the display mode property of the add button if count rows of call grid updates is equal to 0 meaning no changes were made on the grid then it's added else disabled I have the option to add I haven't made any changes I can keep adding rows the moment I start making changes you can see now I have to first save I have the option to back out from the changes that I have made and if I go back I'm back to the original values and this time if the user was to make some changes and click save you could also notify the user here by using the node F5 function if I was to make certain updates so let's say I updated these two values I go back and come back to tasks those values are not withheld that's more of a timing thing for this grid for the items property of the grid we can say filter grid and here we'll just use a variable VAR reset and that hidden button that I had which is BTN load grid I will set that variable where reset and set it to false and then we will set it to true that variable switch logic that I just added make sure that the grid loads the accurate data on a tablet experience notice I see the grid but the problem is that there's no option for me to scroll so for the gallery I'll edit and insert a button now this button I would like to place to the right of this control notice I have no real estate to even move this so to create that horizontal scroll experience my gallery the alignment and container I will select start and now I can start defining a width for this the width is hard coded to 640 whatever control you want to place on the extreme right hand side of your Grid in my case I'm placing that delete button I'll simply copy the name of this button and for the width of the gallery I will use that controls name dot get its X position Plus that control dot width plus I'll add an extra 10 pixels now all the controls that I added in this Gallery I simply just added them and just placed them manually now the first control is fine right I'm exactly positioning it right here because I'm beginning from the left but the second control I would like to position it relative to the first control so description it's X property txt title that's the name of my title control dot X plus txt title dot width Plus I'll give a gap of 10 pixels and the same technique I will keep applying throughout so date picker is the next control after description so X property for date picker would be txt description dot X Plus txtd description dot with Plus 10. let's do the same thing now for priority I am positioning this relative to the date picker control and now the button that I added I will make it relative to the priority drop down now if I preview the app I still do not get any option to scroll to the right so what I need to do in this case is for the container in which the gallery is held there is a property called horizontal overflow I will change this to scroll now if I preview notice I get this nice scrolling experience my button is cutting off a little at the end so I just need to make a little bit of adjustment to the gallery width let's give it a little bit more distance here when I click add the new task gets added at the bottom if you would like to sort this where the latest tasks show up on the top for the items property of the gallery I can also sort it based on ID in descending order so the newly created tasks would be right on the top and this button I'll set its text to delete on select of this button I will use the function remove from my tasks list I simply need to remove this item and once I'm done removing it since it's an action against my data source I will select that button to load the grid again whatever logic I added for display mode for ADD I'll add that same logic for display mode for delete so right now I can delete but if I make a change I should not be able to delete or add anything in my data source until I've saved my changes I need a header experience for my grid in this main container I will insert a simple container control this container I will move it to the top I will turn off flexible height and give it a defined height of let's say 40. now in this container I need to add labels to Define what those headers are so I will add a text control I will call it title now I would want its width to be the same as the width of my txt title control so with txt title dot width and X I'll just begin from 10 pixels now I can simply copy and paste the same control its X position description dot X and with description dot width it's text I will call it description and all I literally have to do is follow the same process for my other two columns and that positions my headers this container just give it a background color alignment I'll set to start and width I will set to my tasks Gallery dot width let's preview that's tested in a tablet experience here is my horizontal scroll and here is my sticky header 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: 52,898

Keywords: Sharepoint list relationships, power apps, powerapps, powerapps sharepoint list, powerapps multiple sharepoint list, powerapps edtiable gallery, horizontal scroll in vertical gallery powerapps, powerapps vertical gallery with a horizontal scrollbar, sharepoint list relationships, microsoft powerapps, power apps with multiple sharepoint lists, learn powerapps, reza dorrani, relationships, power apps tutorial, repeating table in powerapps, sharepoint, lookup columns, responsive, learn

Id: 1Qsf2RPyp8s

Length: 32min 36sec (1956 seconds)

Published: Mon Sep 25 2023