Microsoft 365 Community SharePoint, 2 Feb 2023

Previous Meeting Next Meeting

⏯

youtube image

►

From YouTube: List Formatting Magic – Query string options and look up field handling

Description

In this 20-minute developer-focused demo, Chris Kent delivers three useful tips/tricks. 1) Adding a lookup in your list to display items located in another list. Format multiple items in single list field using forEach to render each item on a new line within the current field – table like formatting. 2) Make a % field look prettier using the Number Battery sample. 3) Appending SharePoint query strings to page/list URLs to effortlessly remove navigation. This PnP Community demo is taken from the Microsoft 365 & Power Platform Development Community call recorded on February 2, 2023.

Demo Presenter
• Chris Kent (DMI) | @thechriskent

Community Call Conversation
• aka.ms/Feb2-Demo2

Supporting materials
• Samples - List Formatting Samples | aka.ms/list-formatting
• Sample - Number Battery | https://github.com/pnp/List-Formatting/tree/master/column-samples/number-battery

Learn more
• Microsoft 365 Unified Sample gallery - https://aka.ms/m365/samples
• Microsoft 365 Platform Community in YouTube - https://aka.ms/m365/videos
• Microsoft 365 Platform Community - http://aka.ms/m365/community

A

Foreign, that's me I'm Chris, all right. Let's talk about some stuff, so uh Bob mentioned the uh the dolly two stuff. Well, guess what I also have been looking at those fun images myself using uh the new Microsoft designer it's very exciting, uh but the whole point I wanted to talk about here is I was talking to David the other day. uh You know he made a silly joke because we were talking about a show we liked and- and you know so- I'd mentioned it to the warrior horses and the warrior horses.

A

You know they they love to do Netflix and nay and they've had a great time with it. But unfortunately you know they're sharing their passwords across the entire. You know, Battalion uh and with the new crackdowns they're looking for a new streaming service, so they've been taking a look and uh I gotta tell you they've really gotten into HBO.

A

So a little surprising uh and I don't know exactly how such they can separate fiction from non-fiction, but hey I, don't judge I'm the one talking about Warrior horses on a Microsoft call right, but when they got into it they started to get some ideas.

A

So they created a new site and let's go take a look at that, so we come over to their classic Warrior horses, Society we've all got, and uh now they have a whole new one here, so they've created a site uh called the Boston Q Z right, so they're working here in this quarantine, Zone and they're, using this for training, I hope right. So they've got some information on here about how to train and new episodes that are coming out and uh as part of this they're trying to you know, collect information about their quarantine.

A

Zone and part of that is, they need to know who of their Warrior horses is there, so we want to create a list to handle some of that. So let's go ahead and create a new list, and since it is the people from the uh the qz, why don't we just call this? The uh of US list sounds great, so we'll just call that of us we'll create that with no spaces, so the URL doesn't have any spaces.

A

You can click here and we have our list of us there. We go hilarious. All right, we'll save that, and now, let's add a couple of columns to it. That's the first thing we're going to do is we're going to rename this title column to their name correct. What's the name of the warrior horses there and, let's add a uh a number column, now we're just going to make this like their power level. That sounds exciting, we'll make this a percentage sounds good and of course that is required, because that sounds good all right.

A

So now we've got a power column and, let's add one more here: let's do a now. If we wanted to list, you know multiple items right. So let's say in this case we want to list out their special skills or their abilities right. We want to kind of quantify that and if we just have a standard list of those well, then a choice field is the right way to go for that right. So we can do a multi-choice field and we could pick all their skills out of there. But what?

A

If you've got more information about each individual skill or ability right in this case, we happen to have a whole other list here called special skills. If we look at our special skills list right, we've got a list of skills that each horse could have, and actually you know what here's a here's- a sub tip a little bonus tip for free we're going to make this a little easier to see. You know we can do this kind of thing and that's all right, but we still have this header up here.

A

Did you know there are query strings? You can type a little question mark and then type in EnV equals and I can say, embedded with a capital E and when I do that, hey I lose that the whole thing up there, that's pretty cool now. One thing to note is that if you have Hub site navigation, that's still going to show up, uh but if we type in webview with a capital, W and a capital V, let me hit that it's going to look the exact same except your Hub site.

A

Navigation is going to go away and there's even another. One which is webview list where we had a capital l for the list and we hit that oh, it looks like Microsoft lists now: we've got that uh purple pink whatever and your icon and stuff showing up there. So those are just a couple of helpful query: strings we'll go over those again a little bit, but just for handiness we'll go webview here just so. We can see this a little easier without all that extra junk on there.

A

But the idea is we have this list right and the reason we've done. This is because we've got multiple columns. We want to associate with each individual skill right, so we want to know their weakness. You know their base power level right. So this is a power level, three for their laser eyes and so on and we've got this cool little icon and that's really fanciness right, and we want to use that over here all right. So let's go ahead and do this and we're going to scroll down to do that.

A

We're going to do a lookup next and we're going to call this skills and I'm going to say uh from our source. Special skills is our list right, we'll pick the title column, and that sounds good. The only thing we're going to do here is we're going to come down. We're going to say, allow multiple selections. This becomes a multi-lookup field. I always say that now, let's enter a few of these guys just so, we can have something to look at right. So we're going to say uh I, don't know.

A

What's a good horse name Joel the horse I just randomly made that up all right, so Joel the horse has a power level of uh let's say: he's got 90 very powerful right and his skilled or we'll just pick at random. There you go he's very skilled right and we'll add another one uh say Ellie. The horse I get randomly picked, but uh 50 uh Power for her and uh again we'll give them some random ones.

A

Yeah talking to fish, of course, a an essential skill, all right, let's just add one more just for fun and we'll call it uh Ed all right. Just make me laugh and we'll do a 10, and uh this guy's got like three skills: perfect. Okay, so there's our skills, and now we can exit the grid View and what an ugly list right. Well, why don't we make that a prettier list? You have good news.

A

The first thing I like to do is I almost never leave a percentage field alone, um just because I like it to look prettier than that, and so one of the ways we can make that look. Prettier is we can head over here. So if we go up here to our list, formatting kind of sample, site and I'm sure David was already posted, the link in the chat or he's about to right and when you head here, we've got all our list of samples and I've shown this a number of times.

A

But groupings is the number one way to go, find what you're looking for and if you go by column type you scroll down here. We know we just added a number column. So let's look under number hey. We got a bunch of samples now what I really like? Is this one by uh tetsuya, karahara he's one of our top contributors here he's contributed a ton of samples and they're all beautiful right.

A

Look at these now for our case right as much as I like these I'm just going to go with the standard theme ones, all right, so I'm just going to hit this number battery Jason and I hit this little copy button. Long. Oh something went wrong, that's straight all right! So we copied it and we head back over here to our list and we've got our power column, column, settings we're going to format this column advanced mode, get rid of that deal and I'm going to paste that preview it! Oh now, I got power level.

A

That's a battery! Now that's fancy all right! We're gonna! Save that that's cool! That's just for fun! We didn't need to do that, but wasn't that fun! Yes, it was all right. So let's bring that over here, but the main thing we're interested now. Is this multi-lookup right so by default, uh when you get this kind of the standard is each one you picked is shown right and it's linked to the individual item right.

A

We can click that guy, it's going to open up and say grenade juggling and here's those details about that right and that's cool. uh And if we wanted to edit that right, we could. We can buy format into this, so we go formatting we're just going to go advanced mode and we're going to type around just to see what it looks like I'm going to say.

A

You know it's an Elm type of div and we're going to say our text content just to see what it looks like is at current field, so take whatever the value is and just show it there. Oh gosh object how helpful right, so we can make that a little better right. In this case we actually want to say because the lookup value there are two sub properties, there's lookup value with a capital V and there's also look up ID with capital I.

A

In our case, we want that value, we'll say, look up value and there we go now. We've got just that lookup value, but it's got this like semicolon the space, that's not great. Let's, let's do an expression, let's say equals, and what we'll do is because this is actually array of values, we're going to join, that all right, we'll say a comma and then what do we want to separate them by? We could say a comma and a space and we'll close that and if we preview that looks a little better right.

A

If we wanted to do this, something fancier we could. But what? If we wanted to do something like add a border around each of those or even have them on their own line, just like Microsoft was doing right with individual links and so on. So those are actually individual elements rather than this. Just fancy text manipulation we're doing well, so one of the things I'm working on have just really started. So this is kind of the first is providing formats that make the same as the standard.

A

So you can begin to working with it right, because this is harder to work with a blank canvas here, so I'm going to go ahead and paste that in and we'll get these published. But the idea here is: let's preview that so now, we've actually recreated uh the actual out of the box kind of formatting and we'll just take a really quick look at that. We've just got some fancy Styles up here. This just makes it so it doesn't go all the top um collapse that and we have our children here.

A

So we just have one child and the way this works is we're using a four each so a for each. The idea is when you create a format for a column. This acts as a template and gets referenced each row so each row that gets drawn when it gets to that column. It pulls out that format and uses this as a template to generate your HTML right and that's cool and it just applied here.

A

Then it gets applied here and they're each end applied separately, which is why you don't get that you know be able to reference other lists. Our other list items and then we can actually do a little template inside our template. Template exception right and that's what this for each is. So when you say four inch, you're saying this element right here is a template now, so what I want you to do is draw this. Everything in here I want you to render it each time.

A

We have an item right and we can actually read this so for each item iterator in current field. So we're really saying is this: is our array all right? It's current field, so it's all those values we saw before right here and then we're saying what you want to call each item. While we're doing this, we can name this whatever we want. I, usually use iterator. Just so I know what I'm looking at right. You call this Fred.

A

If you wanted I mean it is for each one of those, that's the value, that's how I'm going to reference the value from now on. In fact, that's exactly what we're doing here with this text content we reference it like it were any other column and because we're in the template, it knows. Oh, whatever one I'm on yeah, that's the one just grab the lookup value all right and then we've done some uh silly stuff here. Oh that's, not right um special skills. Unfortunately, with the lookups, we don't actually get their originating list.

A

So we have to hard code that portion, but you can see we're hard coding, just a portion of it here to that lookup ID, and when we do that right, then we still get that, like click on that, we can see ah now we can see bulletproof and that's very exciting right, so we've recreated it and then this is just some style stuff to make it match. Microsoft Styles and then this Ms link class, that's just the all that's used for- is that when you hover over it it adds the underline okay.

A

So that's pretty cool, but the idea is by looking at that. We can see this idea of a four each and we can start to work with these things right. So very cool we're just going to leave that for now, so we can take a look at this now we did this specifically, not because we wanted, you know just a single list of things. We actually wanted those extra Fields right. So if we were to edit this, uh you know the easiest way to do that is we just? Do these add additional columns right?

A

So if we wanted to see that weakness and that base power right, we could save that and you'll see. They'll show up here, probably have to refresh to see them. Yeah I, don't know whether that's not showing up that's good, but that's one way to do it and that does work. The problem is, it gets scattered across here and it doesn't look quite the way we want it to look especially right now, because apparently it's blank right now, which is interesting.

A

Let's get rid of that I, don't know either way, that's not really our point, but what, if you wanted to do something a little more compact, you wanted to put it all in the same thing right, you want to do something cool with it. Maybe you want to do some manipulation. Well, good news, that's what we're going to show as quick as we possibly can here. So, let's take away these additional columns and we're going to save that, and one of the things we can do is a is a little trick.

A

We can head over to the special skills and, we can say add column I'm going to go down to the see all column types hit. Next, that's gonna take us to this classic guy and we can pick calculated wait for that post back there we go and we're going to call this concatenated.

A

You know because that's fancy and what we're going to do our equal sign and then for each information. We want all right we're just going to bring that over so I'm going to click on icon and then I want to put an ampersand to say: I want to just join it and I'm going to put a character that I want between. Why don't we use the pipe character because I think that is easier right.

A

So, let's just do that and then another Ampersand all right, so basically icon, then a pipe character, let's copy that so I'll type, that each time and we'll put the title and it'll paste that Ampersand we'll put the weakness and it'll paste that and we'll say the base power there we go so what we've done is basically made a string that puts all this together. Single line of text sounds good hit. Okay, did we do it yep?

A

So now we got this ugly thing right, but it has all of the information that we want in one field. So we can come back over here and if we go to our column settings we edit now we say instead of the title, we can actually pick concatenated and we hit save. Oh look, how ugly, but that's good news right now. We have this idea that each individual item has all of the information. So what can we do with that?

A

Well, good news, I'm, going to tell you all right and I'm going to do as quick as I. Possibly can so, let's do it so the first thing I'm going to do is: let's get rid of all this junk. We don't need. We don't need all this extra junk in here. Do we yeah? We don't need a link and everything all right. We just want uh this idea of for every one of these. Let's just do a span right, so we just see the uh let's go.

A

Dev I love dibs right, so we just want to see the value output and we preview that all right so I just got the text. We've simplified things a little bit and all we could do then is we can create a sub Link in a loop which is interesting right. So now, instead of text content of this lookup value, let's replace that with children all right, so we want to for every one of these items. You know we want to draw this div that has children in it and this children this child.

A

This children is going to be a nested 4-H all right for each that's the one all right now, we've hit okay, iterator name and field name is okay, but that's not what we want this time right. We actually want this to be.

A

um We want to take it as a field iterator, it's the way we're going to do. That is we're going to type in just to make it clear. We're going to say feel not named iterator again. Iterators are something: is a convention I follow you don't have to, and we're going to do is we would reference the one item iterator right and we want the look which is the lookup value, but that's not an array. So let's make an array right.

A

So we're going to split this guy comma, and we know we did a pipe right so we'll put a pipe we're going to split it by that pipe and boom, and we'll say just so. We can see it. Let's put our own type as a div and I'm going to say our text content is going to be the field iterator right. So we want the value of that, and just for uh so we can see that we've got separate items here. Let's put a style of uh Foreigner everyone. Does this for testing right?

A

One pixel, solid red sounds exciting and preview that oh look at that. So now, although it looks a mess, we can actually work with each individual Field's value and we can work with them across multiple things here. So we can start to do some really interesting things right and not only that we can tell what position we're in uh using a nice thing called The Loop Index right. So if I wanted to show you what that looks like there is a function called Loop index and the way that works.

A

Is you pass in the name as a string of the uh the iterator? Why I don't know, but that's what it is? Let's get rid of that extra guy. Let's preview that and so the idea here is, you can actually see it restarts at zero, so zero one, two, three zero one, two three right, because we've got the four Fields we're bringing in each time right now what we haven't set up. Anything to to show the loop index of the item iterator right- we could do that here as well.

A

So if we said item iterator all right, we can see that each one of these is grouped into those. This one has apparently six of them right, so it starts at zero and so on. So that's kind of cool. Now, obviously, don't do this. This is very strange, all right, so I'm going to grab because for time should I have none I'm just going to grab this and paste that but oh boom and boom. So let's scroll this thing out, so we can actually see it.

A

So what we've done here is we've taken that to the extreme and we've applied this and again, this is a sample. You can take a look at I'm using that Loop index to conditionally apply borders, right or icons, and so on and I've created a header right when it's only the first one, so I've created a little inline table, showing everything I've got from the other guy right, which is really neat now.

A

Here's the last thing I want to show um very quickly is why you might also want to do this, even if it's a one for one matching right. So if I head over my special skills and I'm going to go, not all items, let's go. Oh because I've got the webview ah bit me right now, no web View all right now, because we only go to list settings. I'm gonna go to my old school settings because I'm just going to permissions for this list, all right, I'm going to stop inheriting permissions bye, all right!

A

So, oh before that I should illustrate something here which is: if I've got another user. I've got this user named Dan right, he's logged in here. Let's refresh so we can see The Last of Us list on the list of us. I didn't mess it up all right. So we can see everything here. uh We didn't save our format. Apparently, uh let's save that format before we forget I was still saving we'll come back to that. But the idea is, he would be able to see this right.

A

But if I come in here and I, say, I've got unique. Permissions and I say buy visitors, which is where Dan is right. He no longer has permissions to the special skills list.

A

So now, when I come back and I, don't know why that's not saving we'll just refresh and we'll reapply all right, so boom boom preview save all right now saved. So now, if we go back to Dan all right, so Dan had this view now in refresh, he sees nothing right. He can't see those he could see the rest of the list. He can't see those items and if individual items themselves had permissions on them, he would only see the ones he's allowed to see. Now this isn't a great experience here. So we can do.

A

Is we can come back to our yeah, the other guy right and come here? Column, settings format, this column and we're gonna go just down to the bottom, all right. So we have this idea of children we'll decide another child here and we'll just say it's an Elm type of div. Actually, let's just copy this because we're out of time and we'll just paste it in and what you'll see here is what I'm doing is I'm saying: hey if you don't have anything, show a big lock sign right.

A

So let's save that and let's go over here and now we can see that he says install fish at clearance right and the idea is, you may even have a one for one right. Maybe you've got budget items in a list that are only for certain people to edit, but you want to show them an overall roll up in your gallery view or something because lookups are security. Trimmed. You could take advantage of that here and build these kind of little tables out all these extra columns and so on all right.

A

So let's wrap it up and be done. So last thing: here's the just a note about those query strings. You can check those out if you just type in SharePoint, query strings. There's actually several of these. um You know that are really helpful for modern pages and stuff, like maintenance mode and all sorts of other things. So this is the equivalent of the is dialogue we used to have in classic right just remove these things.

A

One thing to note is they do not remove application customizers, which good or bad you decide and then here's those samples. So that's it. We did it.

B

Awesome thanks: Chris I really really appreciate it. What was that Mr Ed? No, no, no Mr Ed. He doesn't love Joel and Ellie more because he gave him more skills. Don't worry, Chris we're gonna have to have a talk with Ed all right.