Microsoft 365 Community SharePoint, 29 Apr 2021

Previous Meeting Next Meeting

⏯

youtube image

►

From YouTube: Using field lookups with list formatting

Description

In this 16-minute Developer-focused demo, Chris Kent shows formatting capabilities available in a referenced sample. Topic: Field Type = “Lookup.” Lookups get values from a list and have limited formatting options. Of course, this limitation does not deter Chris from showing crazy, crazier, craziest formatting options for lookups. General approach = format the lookup column into which content from the referenced list will flow. Use advanced forEach property. This PnP Community demo is taken from the General Microsoft 365 Development Special Interest Group (SIG) - Bi-weekly sync call recorded on April 29, 2021.

Demo Presenter - Chris Kent (DMI) | @theChrisKent

Supporting materials:
• Repo - Multi-lookup fields and projected fields | https://github.com/pnp/List-Formatting/tree/master/column-samples/multi-lookup-projected-field
• Demos – Other formatting demos from Chris | https://youtube.com/c/Microsoft365PnPCommunity/search?query=format%20flow
• Documentation – Create list relationships by using unique and lookup columns | https://support.microsoft.com/en-us/office/create-list-relationships-by-using-unique-and-lookup-columns-80a3e0a6-8016-41fb-ad09-8bf16d490632

Learn more:
• Learn more about the Microsoft 365 Patterns and Practices community at: http://aka.ms/m365pnp
• Visit the Microsoft 365 Patterns and Practices community YouTube channel: https://aka.ms/m365pnp-videos

A

Hey everybody, I'm chris. Let's talk about uh some formatting, so I want to talk about something. That's always been a little bit tricky right. It's always lived in kind of its own space and that's lookups. So it's a field type right. These are lookups where you say: hey, go, get the value or values from another list on my site right and bring that value in and whenever I look at. My current list also bring those values in and there we go right and you can have one you can have many.

A

You can do all sorts of stuff. Now they live in their own little world partially, because right, there's a serious lack of support right for the types of things you can do with lookups right, so really you're. Just looking at you know, text number date and time, and although it's listed as unsupported, I'm telling you you could do calculated all right and then, of course, all of these other ones. You might want to do right.

A

Sorry, that's not going to work out, but that's okay, because this still gives us a lot a lot of power, but it does change how formatting is applied so just like it lives in its own world with column type when it comes to formatting. It is definitely unique and present some unique challenges uh if you're not familiar with it or if you've just been using other every other field type except for lookups.

A

So if you're trying to get to the lookup values themselves, uh if you just do like at currentfield you're going to get object, object and then you're going to cry and wonder what the heck happened right, but now you're going to use these sub properties of lookup id and lookup value.

A

In case you didn't want to just type in dot id or dot value type in the full lookup id all right. You look up the lookup id it's going to give you that list item id in the referencing list and then your lookup value is just going to be whatever the actual value is. So if it's just text, it's just going to be text. But if it's like uh you know some other field, I can get the subprops okay all right, but there is no url property.

A

So the moment you apply format you're going to lose that link back we'll address that in just a minute and then multi lookups to process those. You can use a four each, which is a bit of advanced topic. But again we'll take a look at that, but you can't do the same for additional fields or sometimes called projected fields, because microsoft says we're super helpful and join them for you with a semicolon in a space. So let's take a look: how do we handle these unique situations?

A

So here's the idea, though right go over to our horse's side. We got this list here. We got our battle plans and we've seen this before.

A

uh You know where we added this whole conditional flow and we added a nice little hover screen and if you're interested in that, there's a whole video of that and I'll link to that at the end. But that's not we're going to talk about today. We're going to talk about lookups, so I'm going to create a look up to my agents list. All right, so we've got battle plans. The warrior horses are always plotting at their battles.

A

You know but very important to know who are the agents on the ground they're going to be executing uh the enemy right. So that's what this lookup list is for this list here we're going to look up to it. So let's come over here and we're going to go, add a column and oh, don't look up. I got to go traditional classic column settings here all right in this case.

A

We're going to call it agent and we're going to say it is a lookup, so give it a chance to do a full post back, miss those days right and then we're going to choose our agents list here and in this case we're just going to choose the title column, which is the name of our our guide. But now we want to add an additional field as well like whenever we select.

A

You know for the title: let's also bring over say that phone number column right, because that phone number's got a nice format applied to it. Let's see what happens so we're gonna hit. Okay, we'll take a look now. We've got two new columns right, nothing in them. We just gonna edit that in grid view for a second, so we can add a couple of agents all right. We'll pick, uh I don't know our finished friend. That sounds exciting all right down here.

A

We'll pick, uh I don't know a norwegian chum that sounds great right, we'll exit the grid view and you'll see not only did that come over, but the phone number value came over, but you'll see that our formats did not right. So what can we do with that?

A

Let's come back to this in just a moment, but let's take a look here so by default, the kind of out-of-the-box experience is, you know, gives you a nice link when you click that it's going to come over here and it's going to open up and it's going to show all the column formats on your nice form. You've configured right with your header and your footer and that's great, you know- and that's awesome, but if you open this item first thing, you're going to notice is hey. I've got the same thing here.

A

Sadly, though, if you click this, oh no. Why microsoft? Why right now you've got a classic style dialogue right with a ribbon, and now you cry right: none of your formats are showing up. It looks very strange. You're confused your users are confused where's my x button. I don't know what to do right. So, let's fix that. So what we're going to do is we're going to apply a format here.

A

Column settings we're going to format this column in this case we're going to go to advanced mode, so we can type our own stuff all right. Let's make this slightly bigger and we're just going to start in here right, so we're going to say, elm type in this case we're going to make it a link, because we want to recreate that link where it's our text content.

A

It's just going to be our current field. All right sounds great. So, let's just preview that we haven't provided any kind of uh url anything but object object! That's the thing I mentioned earlier right. So here's what we can do. Instead, we say currentfield dot that little period there look up value with a capital v and we preview that ooh there it is. We still don't have our link. So let's add that in right, so to add a link in uh you add some additional attributes.

A

So in this case we're gonna say attributes I want to say bam and we're going to say thanks for being helpful, I'm going to say href all right and here we're going to build it right. So we need to do an equals. In this case, we want to say at currentweb.

A

Unfortunately, we don't have any information about that list, so we're just going to type it ourselves right, we're going to say, slash list, slash allies, because that's the actual name of the list and we're going to say, dis, form, dot, aspx, then we're going to rd all right and there we go and then we're going to say, plus- and in this case I at current field, just like before adoptable period, lookup id with a capital- I lowercase d, alright. So let's preview that now we get this nice link before.

A

I click that, though I want to make sure it doesn't. You know open on the new one, the current window, so I'm going to say target, I'm going to say blank all right just so we open a new window. Let's save that so now, when we click on that guy, we get that same behavior. We had before here's our finished friend right, but if we open up the form and click it there's our finished friend there we go so that's pretty exciting, but that's kind of how we start to work with things at the beginning.

A

Now. What if I wanted to take my phone number field right and I want to take that same format- I had before well good news. I just go over here and be fancy and format. This column, I'm going to click in there and hit ctrl a to get it all. I'm going to copy that, I'm going to head back over here and right on here, oh I'll, right-click and left-click impressive, I'm going to format this column advanced mode right, we're going to paste it in there and preview it and you'll see wow. We.

A

It just works right, so pretty awesome, so you can copy those things all around. But what if we want to do some other things right? So that's cool. We fixed one little thing, but let's go in here to our list settings and let's swap things around a little bit, so we're gonna go back down here. We're gonna go to agent and in this case what, if we said, you know what I want from. I actually want to put the phone number in the main title right. I don't know why you do that.

A

But let's say you don't want to take away that projected call right, so we're going to hit okay, go back to our exciting battle plans list, and now we get this we're still our format right, it's been applied, so it's got that link and everything. But what if we said? Okay forget the link.

A

I want that same phone number format, so I've still got it on my clipboard, so I'm going to paste it in there all right, let's scroll this over, so we can see it and let's preview, that no, it broke entirely right because it's assuming current field, because this is now a lookup value. We have to have all that now. If you start looking through here, like oh gosh, there's all these calculations for all this junk. Well, good news!

A

Now, I'm not using the awesome, sp formatter right now for the sample, I'm just using the out of the box right, but because this is also a monaco editor, I can hit control f inside this box. Ooh, hidden secret features right next to current field, and if I hit this little bit of care right here now I get a replacement, so I can say at current field, dot look up value all right. If I hit this guy, it's going to replace all of those and when I preview that bam, we just fixed it right.

A

So that's awesome right there. So now we do a lot with that. Now, let's go a little bit crazier even right, so I'm not going to save that. We'll just go back to our list settings now what if we decided to be even fancier right, we take our agents column. Now it's agents because now we're going to say: let's go back to that title field: let's allow multiple values all right and we'll bring over. uh Let's say the status.

A

That sounds great, just a projected field, and now, if we take a look at that, we go back to our battle plans list.

A

Now, we've got this and again I didn't save that of the format. So it's got the link and that seems to be okay right because we've got one. But what if I decided you know our finished friend needs a buddy. All right so become a near finish. Friend also needs to hang out with his russian comrade and let's say his dutch roommate right, so we're just gonna click off to save that and that's great. But oh, we got problems already right.

A

So now it looks weird and if you actually could see it's going to concatenate all of that together, it's a mess. None of that works anymore right. So, let's go to column, settings and format this column. So, instead of all of this, we actually need to do this a little bit differently right. So we need to do what's called the forage field. I'm just going to copy this and we'll take a look at it. I'm going to paste it in here all this is available as a sample which I will link to at the end.

A

So the idea is, we have kind of a parent element and then we in in a child element. You can't do it at the root element within a child. You can set up basically template element right, so everything in this sub element, this child is going to be repeated for every item that comes back and we do that by saying four each. When we put this on here, we have to specify an I iterator. We can name this whatever you want. I just named this one.

A

It item iterator, not sure, what's happened with my accent there that's good times, though all right so, but the idea here is now I can access like it was another column, but I do that same thing to get that lookup value right. So when I apply this, you know I've not done a link or anything, but I get this nice. You know list here and that's okay, so that works right.

A

So now I would assume I could take the same format again, I'm going to copy that- and I could just apply it here on my projected column right. So I'm going to format this column, though sorry tell them settings format. This column go to advanced mode, let's scroll over, so we can see it. Then I'm gonna paste that in preview that it totally busts here because again microsoft's tried to help us out and if we take this away right, so let's cancel so we can see it.

A

It's actually taken every value and it's just putting a semicolon in the space, not particularly helpful to us right. So what if we wanted to do something with that? So we can go pretty elaborate here and I'm going to take this multi-look up projected field part of the sample again I'll link to the end. Here.

A

It's a mess, all right, we're going to grab this giant thing, I'm going to explain all this in a moment. Let's pre-preview that, though, now it's working, so what this is doing, because we don't have a true split function and we can't use a for each we're actually going through and we are parsing one at a time. You know across those and we're finding you know if it's there if it exists and so on and each one of these gets more complicated.

A

So this sample supports, I think, up to five particular values, because you have to have one for each of these. So this is this makes you cry a little bit, but that's okay. What? If what? If we want to do something a little else right? We could. We could line these up and that works okay. But let's go one step forward further in our next four minutes. All right, so I'm going to go back to the list settings just to change this up now I mentioned before that.

A

Lookups can actually do calculated columns despite the uh official documentation. So let's see that in action right. So let's just take that away and we'll just say: uh use our exciting stats, calculated column, all right so and I'm just going to go and clear out this format. So we can see us looks and hit.

A

Okay, go back to our battle plans list, and here we go so now what I've done is I've created a calculated column that takes three the three fields that I want to look at here right and puts them all inside one column, all right, so I've just put a little pipe between the title right and their status and then their kill count over here.

A

So I can then extract those values myself. Now I'm going to paste one in and we'll just take a look at it just because we're running a little bit behind on time here. So let's just grab this paste that sucker in let's preview it and I'll show you what we're actually doing here so we're going to expand that. So it looks a little better. All right!

A

That's fine! Okay! This is my half attempted table, but the idea here is we're going to split those values ourselves using that for each so we know we get each value as a for each right. So we get that item iterator and again we're going to inspect now. The first thing we're going to do is we want to do something different at the top right. We want to close that border at the top.

A

You can get what position you are in that for each using this loop index operator and just pass in whatever iterator you're in right pass that in it'll tell you, and so, if you're, the very first one which is zero, then we add a you know a border. Otherwise we don't and then we get those individual values using a substring where we get the lookup. We look inside the lookup value right.

A

We start there and we say the first index of you know the pipe and so on we're kind of doing that for each one of those as we go. So that's one way to do that right, so you can take all that and kind of go that route with it. So if you want to go a little crazy with the table, you can uh but you've got other problems when you go this route right. So if I come in here and I want to filter- oh that is not helpful right.

A

So again you kind of cut away the pros and cons, uh but also understand that you're not gonna, be able to bring in certain values from those other columns right. So there are lots of ways to solve it when it comes to formatting. uh Those are the kind of main things to keep in mind, so the lookup values themselves have to come. You know, as sub properties, look up value and lookup id.

A

um If you do any kind of projected field or additional column know that you are now in a world of pain, uh getting those cola, semicolons and spaces and split all those apart, but again the sample is available for you and you can cut and paste that and then customize as needed. So let's switch back real quick for some resources that I promised.

A

Oh not yet there we go all right. So we take a look here. These are some resources for you. So I mentioned before, when we looked at the battle plans, where some of you might have been more excited about that hover card right in the conditional flow and all that, if you go to our official youtube channel and you just search format flow right at the top- are going to be three different demos.

A

uh That could show you how to do that, and we have samples for that as well, if you're interested in how we did that giant splitting for the multi-lookup projected field, that's available along with how we did kind of this stack where we addressed it um and the official documentation is here and if you're just interested in more list, formatting videos we've got tons of these.

A

Now uh there is a playlist set aside just for that uh all about microsoft list and the list view and everything we can do with that and check that out at the lf videos link here all right and that's all I got excellent. Thank you. Chris super super entertaining as always,.