SharePoint Power Hour: Dynamic List Filtering

[Music] okay cool that wasn't too painful hey everybody i'm back okay just in case it happens again i don't know what's wrong with xsplit i'm going to go ahead and ju i just got sharing my desktop already so our topic today is going to be dynamic list filtering and i put a link to it in the chat so dynamic list filtering is basically um we have connected web parts back so we have the ability to and i'm sharing my screen here so i'll just show you the final product first and then i'll show you how to do it so i have a list of customers down the left side and a list of projects and then i can i'll go pick a customer and then that filters the list of projects by just the projects for that customer all right so that's the end product and it's it's pretty simple but i'll i'll go ahead and show you kind of what is involved here so i have a sharepoint list called customers and this is my you know this is my demo site this is where i do all my demos and i've i've had this list you know for probably five years of these customers so this is just a generic list of customers and i have um a view called active in here and my active view is just filtered to show customers with a status of active and it's just showing one column the customer name and then i have a list of projects and my list and this is a pretty common scenario right joelle for when we used to be able to use connected web parts right customers and projects so we in classic sharepoint just a little rewind in classic sharepoint we used to be able to have connected web parts and we had all these different types of things that we can do with connected web parts so this interface that they've provided in the modern interface is not the same at all it's completely different but they're getting there they're getting closer so this is giving us at least um it's closer to what all the different options we used to have for all those different types of connected web parts and different um oh my gosh i don't even remember that what was the category of web parts called joel of like the query string filter just filter web parts i believe it was the filter web parts yeah you still love those things okay okay so here is my list of projects so i happen to delete these little test projects here get them out of the way and my my list of projects has i'll just show you what a new project form looks like and this is a customized this is customized in infopath that is what a project form looks like and then the important part right here is that there is a lookup field so when you create a new project you can tell it which customer that project goes with so let's go look at the list settings okay so here's the important part i have where to go where's my customer look up yeah oh yeah right there customer look up i don't know how well you can see it um [Music] how how is that clear enough on the screen joelle as to what those all say um i may not have caught up with you yet okay all right so here's my customer look up field and um hey everybody by the way we have a slack channel that we use if this is your first time um joining us i kind of didn't do my typical intro because of xsplit breaking but um we have a slack channel where we have a discussion going on and questions and just the whole time that's the fun part about having a live show so here's my customer look up and it is a column in my projects list and it is showing the custom the customer name from the customers list so the customer name is the column that is being displayed when you're doing the drop down to pick your customer and then i also have it says you can add a column to show each additional field some you can add other fields from the customer list not sure how necessary that is but i'm sure i was trying something out when i did that all right so then um that's that's the project list i also have a time sheet list and when i'm filling out a time sheet whoo that is ugly i got some good lsu colors going there right so in my timesheet list here is what i have i have a look up here too so my in my timesheet when i'm filling out my timesheet entries it looks up to a project from the project list all right so that's it's kind of three it's kind of three different things it's kind of cascading down from customers to projects to timesheet so let's go do a new page i'll just go back to my home page and then i'll go ahead and i already created a page with the connected web parts but i'll just do a brand new one from scratch so you can see how it works so do a new page and i'll just make that plain so it's so i've got plenty of real estate i'll delete this little default text web part it puts on here and here's where i want to start i will add a list web part now if you you can still have this cascading drop down concept and the filtered webparts concept with libraries libraries can also have lookups in them these in my example these just happen to be lists so i'm going to start with a list webpart because because my customers is a is a list of customers um someone asked does this transfer and work the same way in a power app after setting up the list i was actually going to do this in a power app next because there's no way this one little thing is going to fill up a whole hour so it's going to show you how to do it in powerapps okay so i've got my customers list and it's asking me which view i want to show i don't want to show the all items view because i kind of want to save some real estate i want to give it some room so that i can put just the projects next to it so i can put the two web parts next to each other so i'll just remember i showed you i had that view that i had created in the customers list just called active so i'll just go ahead and apply that so that it's only showing my active customers and it's nice and skinny here down the left side so i'll have room to put the other one okay so then i'll go to my i'll close this panel and go to my section and this section is sort of the layout of how where kind of where you want to put your web parts so i'll do with a one-third left section layout and i can even change the background of it a little bit see i can make it look purple just do dark purple and then i'll go ahead and close this and remember my resolution when i share share my screen during power hour my resolution is very large so you can see everything but it doesn't look this awkward when you have just a normal resolution so now i add my second web part and this is also a list this is the list of projects and i'll go to projects pick my list and if you pick the library web part it would prompt you with a list of libraries to pick and then i have my list of projects and go to the projects web part settings and this is the important part so when i'm on my customers web part settings i don't need to use dynamic filtering on the customers because the the customer is what i'm going to filter by but i am filtering the projects list so that's the one that needs to have dynamic filtering so when i turn dynamic filtering on here's the meat of what we're doing in power hour here is i this is where i get to pick which column i'm filtering by so i'm filtering by my customer lookup that's that lookup field and then lister library containing the filter value so that's going to be it's going to ask me which other list basically is sending this information to this web part that's going to be customers and then um which column in my projects to filter did i do obviously i've done this i did this on the other screen and now it's being weird if not if this just doesn't like me i'll just go back to the other screen i literally just did 10 minutes ago customer look up and then customers apply okay and then let me go try that out so i'll save this as draft because it's very very awkward it's all very awkward on the screen anyway now so what i notice is if you click a customer like this if you click the name of a customer it takes you to the whole customer and this is how connected web parts worked before too so remember how awkward it was before with other um with just the classic view with clicking on something with a connected web part you had to click on the little double arrow thing so this is just as awkward so i'm going to do my little back go back to the page i was on and unfortunately you have to click this little check box and also unfortunately it lets you click more than one now let me it looks like it's not filtering it so let me go see let me go look at my filter and see what i did wrong i'll go edit my page i could tell something was off because it didn't prompt me for which column to send um the data from okay here we go see now it's got three options here see a minute ago when i was in here editing it it didn't have another option down below here so this is what i need so this is a bug because i think this happened the first time i tried it too i just thought it was me so you have to set this and pick that it's gonna be a dynamic filter and apply it and then save the page and then go back and then this third choice will be here hopefully it'll it'll probably be fixed you know pretty soon okay now this is where i need to pick which field in the this customer list is sending the value to the projects list i tried id first because that wasn't just my first inclination was because i know look up fields store the id and the name in there so id didn't work so customer name now remember when i created the lookup field in the projects list customer name was what i picked that i wanted it to be to display whenever that customer was selected so customer name is what i'm using okay so now i've applied my changes i'll save it let's go try this again okay so now i'm just a normal person looking at the page and i have to check the box next to iw mentor but if i check the box next to another one it it can't do two of them um someone was asking if it was multi-select would it still support dynamic filtering um we can try that and see um yeah okay so i so that's pretty much the gist of it is that you have to lay out these web parts it looks kind of nice having that little background color on the section because if not it's just one big white blob and it's kind of ugly and messy and not the most intuitive but it look at least it looks nicer with a background color on the section so yeah so i just have to uncheck the previous one and then check another one because it's never going to show me if i keep checking them it's never going to show me all of them at the same time it's just going to show the most recent one that i checked so that's yeah and then your users have to know to only use the checkbox and not to click on something too so let's go let's go try one more thing so i'm gonna go into my i'm gonna go to my customer list and i'm going to go to my active column and i can see that i've got my customer name that's got the link on it i'm going to go edit this view and there's another column see this one says customer name linked to item with edit menu i'm going to show the one that's got just customer name that's not linked to anything see it's just gonna be the words the name of the customer so i'll go ahead and save that now i shouldn't have to edit the web part again since all i did was just change the view and that view is what the web part is pointing to so let me go back to my um my page refresh it just in case okay so now i've got okay so see this is gonna be less confusing for people right so if i i can't i can't click on the word and of the customer and have it accidentally take me to another screen so that's good so that's a good way to kind of improve your user interface by having less things that they could potentially click on and be confused okay let's add let's see if i can add a couple more web parts i'll go add the timesheet and i think i have another one in this little demo set of things that's a list of statements of work for customers okay there's my timesheet now i'll go to my timesheet settings and go to dynamic filtering so you know see notice it's only got these two columns at first so column in the timesheet to filter that's going to be project so i'm not going to be filtering the timesheet by customer i have to pick a customer and then pick a project and then it will show me those timesheet items for that project so this is going to be projects so then i have to click apply and see that third column doesn't appear down here i'll just close this and just save it and go edit it again and see look there's my third option down there yay it's right over here okay now um what column i think it's project name when i pick a project that goes with the timesheet it's going to have the project name all right now let me say that and try it again while these list items are so old uh joelle looks like shane created a bunch of them that's from so many years ago okay all right i'm going to check contoso and then toso project and look at that so look um me and billy bob did a lot of good work on that contoso project and it even shows me what it's filtering in the little i like the breadcrumb trail don't you what do you think it's a really nice thing about that is it shows me what it's filtering by whenever i do it so if i change it to another one then see then it says projects iw mentor and then i'll see if this one has yep this one's got one time sheet entry down here timesheet iw mentor project yes stuart we all go way back um [Music] shane and joelle and i used to all work together it's a long story it goes back about 10 years so all right um let's see so that's what you can do is you can just add all your web parts now i've got this other i've got this app and it's my customers and projects app and i've probably demoed this one before customers projects demo i think that's it yeah i thought you knew that stuart he said he's known me for that long yeah valerie says billy bob is quite productive how about rogue i think rogue is laying around sleeping somewhere oh the vet told her yesterday that she um needs to lose a few pounds by the way so we need to get her off her rear end rogue is the dog everyone if you missed last week's episode okay customers and i've got contoso and then there are my time sheet items doesn't that look so much nicer in a power app than on those awkward web parts so let's let's go whip up a power app real quick to kind of do this kind of thing and then you can just embed it on the sharepoint page and then you can make it look exactly like you want to so all these years that um you know since the classic connected web parts kind of went away you know they're not available in modern and people have been asking how to achieve it and modern i usually just say use a power app so this is going to be what i meant by use a power app so i'll go just create a power app and um we've still got half an hour so i'll just whip up a power up real quick canvas app yeah see the power app is much and see with the power app you have much more control over what they see what they're clicking on what you know what it looks like how much you know space different things are taking up and those web parts can kind of get really awkward with different screen resolutions maybe in different columns like too many columns showing and um just maybe being confusing on a page for people so i'm going to create a blank app tablet layout says i hope for the day when i can say let's go whip up a power up real quick and i won't be secretly laughing real quick it's gonna be really ugly when you do it real quick right okay so i'll start with my um it's easy when there's zero functionality do what i said it's easy when there's zero functionality yeah when it's just a wireframe yeah yeah okay let's get my that's my demo site that's got all this test customer stuff customers and projects and timesheet i feel like i've done this demo before in a in a previous power hour who knows i know i've used these same data sources for lots of stuff because it's just a common thing that people do in sharepoint and powerapps okay so i will slap a quick gallery on here and um i'm gonna do blank vertical and just pick customers and then i like to put blank vertical on here and then just change the layout to one of these other ones so i'll just do title subtitle and body and look see it just gives me it just gives me my you know just guesses at some fields and puts them in here so i can go change um let's see like change it to something else um i've got a liaison like a person that's associated with uh each project so i'll just make it show the liaison okay so there's my customers and then i can also just squeeze my projects gallery in here too blank vertical um stuart said is rackspace still around um i don't i mean it's yeah i think it's still called rackspace but it got bought by another company and then i've got so these are my projects and see how again it's just guessing at um some fields project start that's fine all right okay so now the trick is to filter the list of projects when i pick a customer so this is where it gets a little bit complicated so you have to okay so when you're using a lookup field the lookup field is non-delegable so i'm going to filter it by the lookup field but what i usually like to do in the real world is when i'm creating this parent-child relationship like like the pro putting funny things in the slack it's awesome like the customers and projects i usually also have an extra field like in the projects list that's a number field that i call it like customer id and i will also as it's populating the lookup field to have you know the project matching the customer with the lookup i also put the id of the customer in that i that extra id field because of this kind of scenario because just in case you had hundreds of items over 500 it's not it's a non-delegable filter so i'm going to get the little blue squiggly error so it it's really only going to cause a problem if i had more than 500 projects here so some people just might build it that way and it's fine they're just doing something real quick or simple but that's just for in the real world for projects with that are bigger that's for now they're non-delegable and you'll see what i mean by by that so filter projects where the customer look up see you can see i had a field called customer id customer look up dot id equals gallery oop i gotta name my galleries i gotta name this something useful gal customers okay gal customers dot selected dot id so i'm just picking whatever the two fields are they they have to exactly match each other so now i don't have anything selected yet well it's gonna by default it's actually selecting that first one for me i think so contoso see when i pick contoso now it's showing all the contoso projects iw mentor adventure works so yeah done isn't that beautiful now i could also do my i could i could i could have it and take you to another page like i did in that other app that i showed you where it um it actually navigates you to um and i just have each gallery as a separate thing on each screen so it takes you to another screen with the filtered gallery and it filters it for you so you know it's it's up to you as to whether you want to if if they're bigger and they need more real estate have them on separate screens or or whatever you do oh let me save this before i forget um marie says why doesn't the mouse change on an item to a finger to show it is clickable well it it does when so you have these buttons here and the buttons that's a great question so when you have a gallery you have an on select for the whole gallery so i'm not i don't even have anything happening on select in this thing but i also have a button in the gallery that's got um let me scroll up here to the first row and the button is since it's a button the button is going to have like change you know when you hover over it that's what button controls do and it selects the parents so when i whether i click just to select a certain row just anywhere in the whole row in this gallery it's going to select that item and if i click to select this button it by default it selects the parent which is the whole row so clicking the button and clicking the whole row do the same thing it's just a lot you know people need to feel like they're clicking a button so what i'm doing over here is i'm filtering the gallery to just that whatever i happen to have clicked on in the customer's gallery so i didn't even make the buttons or anything in the customer gallery do anything so what you could do is you could um you could do something extra like say um give it one of those little uh where is it transitions pop so as i hover over things they pop out like that so that might give you a better anyway so that's what i'm doing so let me go ahead and i'll add the timesheet out here and then i'll put it in sharepoint because that was the whole point was to show how you could do this in a power app instead of with all the connected web parts um yeah now marie you could you could stick you know an invisible button control um on top of this whole thing and that way it would change to a little hand or you know whatever um so you can tell that it's clickable or just you know provide people this little button so that they feel like they're clicking on a button right it's just psychological because it doesn't matter where and just by default with the way i did this it doesn't matter where in the row you click okay so a third gallery timesheet and then give it some information too now the question is what do i want to happen when somebody actually opens one of these do i want to be able to have somebody click to open one click to open the project or click to open the timesheet or are we just drilling down to filter things so that would be so remember last week when i was showing um in the powerapp i was doing i was showing powerapp conditional formatting and i showed how you can put i just put the form for the task itself just in front of the gallery and so when you clicked on a task the form just popped up right there so we only had one screen and it was just the form control popping up in front of whatever i clicked on so you could do that in here too i have i have what about 20 minutes left so maybe i'll have time to do that but i'm not sure but i just did it in last week's show all right here um to see this item that's the hours and then that's the date so i'll just let it use whatever fields and you could add more in here so i could say like who who was it created by there so so you can add any other fields you want to show and arrange the galleries however you want and then i have to i can make these different colors so that it kind of you can tell them apart there there they at least look slightly different now even though they're still very ugly you can't make an out a power app look functional and pretty in in a half an hour all right so go to my items property and then i want to filter my timesheet where the project dot id equals and then that's oh gotta name my gallery something useful gal projects gal projects dot selected dot id so if i was going to pick the project dot name i would need to do the selected dot customer name because whatever two fields i'm comparing they have to match just like in the connected web part you just have to the data that you're picking just that you're decided to you know do the do the matching on it has the data has to match in it um okay so now there are my let's try that again contoso here on my contoso projects and then um it it defaulted to just show the prod the timesheet for the first thing in the list so i'm gonna go change this so now it's showing timesheet for all those now i will take this thing and save it and publish it and stick it in sharepoint as a replacement for having to do connected web parts because i don't know i don't know how that i don't know if the end users are it's that's up to your user base if they're going to be savvy enough to know how to do the thing with the connected web parts and click the little circle what do you think joel yeah i mean i think i think there might be a little bit of you know just training and stuff like that training involved yeah yeah at least an icebreaker introduction kind of thing yeah of course we have one in our tenant joelle right that an old one that i created a couple years ago that's a connected web part dashboard page and yeah i feel like every time we go in there we forget that you have to click the little double arrow yeah it's not super obvious no it's not all right okay customers projects um app plane and then oh another thing that you can do oop not that one in here is in the settings you have a setting to [Music] optimize embedding experience that is in powerapps so i'll do that to save and i need my details i need my app id because i'm going to embed it in a power apps web part and again with my resolution it might be a little large on here and i just paste that id in here and i decide whether i want to show the border of the web part i just note joe and joel and i just noticed today have you all noticed this in your tenant that you don't have an option when you create a new page or you click promote they took away the option to add it to the navigation i wonder if that was causing problems if everybody was just adding everything to the navigation and yeah i kind of wondered maybe if it was just something to do with you know people are that are using like mega menus and really like putting a lot of work into their navigation maybe it was causing users to disrupt that somehow or something okay um i also had a um send by email i had a powerapp one time that was a form on a page like this embedded and people would fill out the form and then they would click up here they would click send by email instead of clicking submit on the form yeah we had all these people clicking send by email and then typing the ladies name who's the one that usually is does the intake for the forms and she was getting all these people were sending her these links to this webpage in an email and they were she was getting multiple ones a day like everybody was doing this so i was like why do they keep sending her the link to this sharepoint page and i'm like oh my gosh they're filling out the form and then instead of clicking submit at the bottom of the form they're just clicking this ah you can't get rid of this though so you know what i did is i just didn't embed the app in a sharepoint page and i just when they click to go to you know go to that app instead of having it in a page i just let it i just took them straight to just that link that web link to the app so that it was in a new tab and it was all by itself and nobody was going to get confused does anybody else come across that it was apparently that was just for people who hadn't you know no training or anything you're just here you know go here and fill out this form that is the most obvious thing to them was to click send by email instead of the submit button that was on the form so i mean it you're giving them too many buttons too many choices to choose from when it's embedded in a page like this i guess so here is my app now embedded in a page and see i'm just um clicking through all my little projects and stuff and then the next step would be to um build either you know either just build the little form whatever they click on to pop up right here in front and or just take them to another screen in the powerapp but as you can see in this power app i have a lot more control over what they see but it's really really fast and easy to just put the connected web parts on the page and you know doing a power app could take you a few hours so you know it's i don't know what do y'all think valerie says she's had people edit the page to make changes to comments i guess don't give so many people the ability to edit pages too that's one thing that i started doing was when i when i give people a new site i just go to the pages library and break off from break off permission inheritance and just give everyone except owners read on the pages library because of stuff like that with people just who shouldn't be editing the web pages editing them um so we try to we try to think about our ui when we're doing things like this and think about what people are going to possibly click on or accidentally click on or possibly not understand so uh yeah so in this scenario there with customers and projects there are a few different things you could do i mean you can have just a whole bunch of data on here that people can see for them to read everything they need to know about the project or um and give them a little edit button if they want to open it up and start working on it so zach says the curious george users well so many people are new to office 365 and sharepoint and powerapps lately especially with everyone like jumping and running to the cloud you just have so many new people that you don't have like you know we have this sort of old-school set of people who've been using sharepoint for 15 years who just know how to do things in sharepoint and how sharepoint works well you have tons of new people now and they're just clicking on stuff right they don't know what any of this is so you just kind of have to think especially if your organization if you have um if you're newer to office 365 and you're implementing something like this you gotta really think about anything they could possibly click on on the screen so um oh yeah that makes sense valerie um what else what am i missing joelle that kind of wraps it up we can we can talk about data flex for a minute let me see because i think that's one of the huge new things that yeah did you i may have looked away for a customer email but i think lisa asked if it's supposed to get around the non-delegable issue i don't know if you had answered that or not yeah about dataflex um well dataflex is using completely different data sources using cds and i think cvs just has different types of columns that are delegable and non-delegable so i'm not sure off the top of my head which ones they are um yeah there's there's a webpage somewhere lisa that has a chart and it's got sql and cds and sharepoint and it's got a chart of all it's like different types of columns and it shows you which columns are are not which column types are non-delegal and in sharepoint they've narrowed it down and they've fixed a lot of them so the only ones really that are non-delegable now are going to be dates and those look ups so but they're the big the biggest negative i see so far to data flex and and what it gives you is that it search is not part of data flex so i guess in a power app you wouldn't be able to use the search um function i don't know it just said it had a it had a chart um in i don't know one of these web pages that i saw describing it that said that it described what was in dataflex versus dataflex pro and dataflex pro is just the cds that we know common data service and one the the main thing that jumped out at me that the pro version has that the regular version doesn't have was search and i had a lot of other different things that are basically settings and functionalities that i've never used before that didn't seem like they would be a big deal to not have but so i'm not sure all right so andy dataflex is this i'll put a link here in the chat i'll put it in the general channel there data flex microsoft just announced last week it is basically going to give you the ability to create power apps inside of teams so you can see this little video in their webpage it shows you you're in teams because you know so many people are going to teams now teams is the big new thing right or it has been for a year or two and you're creating tables so in cvs they were called entities and now they're called tables again you're creating tables just like you would have created tables in microsoft access like you create lists and sharepoint so you're and you're doing all this right inside of teams so you're creating tables and that's going to be where your data is stored and then you're creating your power app and your flows and you're doing it all staying inside of teams and then people can fill out their forums work in their power apps and you can share power apps and with other teams and give them to people or something but what it's doing behind the scenes apparently is it's getting its own little environment in in that team so it's just showing you an example of building a power app right there inside of teams and um using dataflex is the backend data that it's got any other questions about that um what um so what are you talking about with xml to pdf conversion oh well okay i'm not sure all right i'm missing some conversation about something um [Music] since teams is easy to use with external users will the dataflex powerapps be available for our external users who are also part of our teams i do not know not sure um lisa says can you turn this off at the tenant level you have cowboy users that are gonna make a mess with this yay cowboy users that's that's what we're counting on right we want a lot of people just jumping to this and clicking create an app all over the place right that's the goal um i don't know lisa if if you can turn it you know as us admins that's the first thing you think of when a new feature comes out right is how can you turn it off so that's elise is asking how can you turn this off i'm not sure i don't know um a lot of times they roll out something really cool and then it's just open and anybody can do it and then they get all the admin and screaming at them and they're like okay okay we added a powershell command or something to let you all turn it off um so but this is quote i'm not going to use the word free but dataflex is free because it's not the extra cost that cds or data flex pro is so data for x data flex pro is when you'd be paying that money that you the extra money that you pay i'm not a licensing person the extra money that you pay for cds but this data flex is this like lite version of cds so it is not extra money so notice that i said it is for i didn't say it is free i just said it is not extra money because i have no idea what your licensing is um but that's that's kind of why they introduced it because they have so many people wanting just create quick little apps like this and cds has become very robust over the years but sometimes you just need a little just simple right for business users so i think this is catering towards the the simple um so oh andy says she's trying to figure out a way to convert xml files in sharepoint to a pdf uh i don't know i think there are a couple of i think they're maybe there's a third uh no i don't know i'm not sure converting xml seems like that would require devwork or some kind of third party product valerie says i have no one in my organization who will use this all right let me flip back over to oh it still has my pre-shot there we go all right so that was a that was fun um oh eric says can a lookup be a multi-select field i am not sure so i didn't quite get to that but um yeah it's really easy just go ahead and try it out real quick just whip up a couple of lists and slap them on a web part page well not a web part page a page let's slap them on a page and uh give it a whirl all right um

Channel: Laura Rogers

Views: 6,101

Rating: 5 out of 5

Keywords: SharePoint, Microsoft 365, Office 365, Dynamic Lists, Connected web parts, Microsoft

Id: NyAwGF1QeE0

Length: 48min 29sec (2909 seconds)

Published: Wed Jul 29 2020