Microsoft 365 Community SharePoint, 6 Feb 2020

Previous Meeting Next Meeting

⏯

youtube image

►

From YouTube: Extracting values for column formatting when building a custom rendering experience

Description

This PnP community demo is taken from the bi-weekly SharePoint Developer Community - General Development Special Interest Group (SIG) call recorded on February 6, 2020.

In this 11-minute video, Chris Kent, adding to his previously delivered sessions on list formatting, demonstrates using "IndexOf" and the "substring" operator to add an icon + summary status text to list column entries. This function is particularly useful when facing a long menu of choices and the options presented are a mash up of 2 or more thoughts. For example: Not Started: New, Not Started: Delayed, Not Started: Unassigned… Of course, you are free to use a bunch of "if" statements or calculated values for this conditional formatting task. Also, you are welcome to write your own json, but why not save time formatting your list by simply grabbing the generic Substring status json from the GitHub library of 80+ list formatting script samples available to you today?

Presenter: Chris Kent (DMI) | @theChrisKent

Supporting materials:
• SharePoint List Formatting Samples | https://sharepoint.github.io/sp-dev-list-formatting/
• Generic substring status formatting sample | https://github.com/SharePoint/sp-dev-list-formatting/tree/master/column-samples/generic-substring-status
• Doc Column formatting | http://aka.ms/spdev-column-formatting

All SharePoint PnP Developer Community demos: https://www.youtube.com/playlist?list=PLR9nK3mnD-OWSbg0o9a7mx_E7s2u7h_o2

Learn more about the SharePoint developer community at: http://aka.ms/sppnp.

A

B

Right everybody so I'm, Chris Kent, let's get started. Let's talk about some things all right, so there are classic warring a horse's sight. Fun fact. I can't seem to change my theme right now, so maybe this isn't so bad. We'll just keep that. But you know what warrior horses used a lot of cool formatting to do a lot of cool stuff, but even they have to have their own IT department for issues tracking and and upgrading hardware.

B

So if we go over to the warrior horses IT site all right over here, we can see they've got. You know a nice idea and one of the things they're doing because I didn't want to pay for any other software. They created an issues list right here and their team site. So if we go over to the issues list, we can see here. It's pretty standard, pretty boring issues list right. You got your issue here when it was submitted, who it's assigned to and a status all right.

B

One of the things we've seen in the past, which is nice as being it'll, take something like the status they bump that up a little bit in size. Here we go come to the status, we go to column, settings, I'm, gonna, format, this column. Alright, we can choose from the nice design mode here and apply some cool colors directly to those statuses and that's cool, but what if I want to do icons right? So this is pretty pretty straightforward. It's something!

B

We've done many times before, and I'm gonna copy and paste a format and we'll talk about it here. Alright. So if I preview, that all I'm doing here is I'm, taking a look all right code, so the value can be not started in progress or resolved, and all I'm doing is this kind of. If statement here at the icons right, current field not started, then use this icon, otherwise use this icon, and if it's none of those use this icon and then do the same thing for the color.

B

That's pretty cool, that's straightforward, and you do something like that and you know the the PM or your scrub master.

B

Whoever is really excited Wow now I can tell what's going on, but what, if they decide to get fancy which often happens right coming here and now, they've taken this status column and instead they've created this uber status column because they want an even more information alright, so we apply that we start to see now now they've taken that and then combined, basically with two different fields into one right, they've taken that overall status of not started and I'll give it a separate reason for it so check those out right, they've, gone crazy.

B

Look at all this all right! They got all these different versions are not started in progress and resolved, and now we're stuck like how do we get those cool icons back all right? What is the answer that I create even a larger switch statement like that's insane? Well, they don't go through that and, of course, they're gonna, add more because hey they're, PM's or scrum masters, and that's what they do all right.

B

So I could go in and again modify this format to have lots and lots of if statements for all these different things or it could be a little smarter and use something like index up now. We've talked about index of before and I'm just gonna edit this format, so we can kind of see it well. It adds up this format here.

B

So if we come here and we format, this column, I'll go to advanced mode and now, if we wanted to say let's just for testing purposes here, say um title just make it a div cuz, that's exciting right! You know, we say our text content in this case is at current field. Just so we can kind of see what it is right. It's not gonna look almost any different than this. You know. It's also that it's getting cut off.

B

That's you know you can obviously can fix that here, but that's a personal setting and you know letting people know that ah fun stuff right. So we do some like that. That kind of fixes that and that's already better right, but what, if I, wanted to tell you those icons? I can use a cool formula, so one of the things we've talked about before was this idea of index of alright, where we can actually look at the value we can inspect it.

B

So we say at current field: I want to look inside there and we say what's the index of say, you know not started all right, and so, if we do that, that guy will start to see Wow all the not started ones, it says it's zero and one that doesn't have it or negative ones right. So that's kind of our contains.

B

So we could do it a check to see if you know if it has the words not started and resolved and done, and we could write something up with that or we could say, starts with means. It'd have to be a zero, so now we're getting a little smarter. So, let's see if we take a look at that as with icons I'm coming here, we get pasted. This may be that you can see. If we look a little bit here, we've actually done this index out current field not started if it equals zero.

B

It starts with not started, then we do the same thing we were doing before: hey that's alright and index stuff is really helpful for inspecting elements right so and we did a whole video about using constants contains and the start with there's a few different samples available. If you go to the YouTube channel, you'll be able to get to this. But what, if we want to do something with the actual value right, this just returns, whether it's in there or not right. So the value is always a number right.

B

What if I want to be able to do something? Fancier? Alright, if we take a look right, let's preview these values, we have something in column, common right. It's got this colon right here. So what if we could find out what's before the colon and what's after the colon and use those values directly?

B

Well, we can all right so there's there's an operator again, we'll time to type our, so the starter, one here content, there's an operator called substring all lowercase like this, so substring and we're gonna say, look inside current field and tell me: what's you know, starting at 0 and ending at 4, so these are indexes of the string. So this is the number of characters right. So 0 is the first character. 2 is the second character and so on. Alright, so give me the first four characters.

B

This thing right just show me that now, if we preview that and now we're getting somewhere but you'll notice that these have different links, right so not started as obviously different than resolved and in progress in terms of link, there is a link function available to you.

B

Don't be, don't be confused by it, though it's not the same as like a Nelion function as you'd expect, if you're a developer, who ever tell you the how many characters in a string instead, it will just tell you one if it's a string or 0 if it's empty, it's really for arrays. So in this case, though, all right I just want to see you know up to that semicolon all right, so I can actually combine that index out.

B

All right, I can say, look inside current field and tell me where is the colon and I'm going to use that value as the index I want to go up to I want to preview that Wow, hey I'm, starting to extract that value right, and the same is true if I wanted to say go to the end, all right so now we'll take that out, and so I say this I'm gonna say done.

B

The nice thing is you just put in a hundred here because it'll just go past the string, so my preview, that I did not close that, let's close that there we go, you gotta get your NC's right. Let's try that there we go now we start setting up the second part of the value right, I could say plus two to get rid of that. So you know the colon in the space now I've got the second value, and I can do some really cool stuff with that alright.

B

So now we can take a sample available for you here in the SP dev list, formatting github repo be able to post the link here in a second I'm sure able to come in here into our column, samples and we scroll down here.

B

We've got this nice new one called generic substring status, and here, if we just grab this JSON as it exists and again I'm right here in github, but I'm not doing the fancy, github stuff, I'm just grabbing all that I'm gonna copy it and I'm gonna go back and I'm just gonna paste it in this box and when I preview that now we start to see something cool, alright, I'm doing the same detection we did before with the index of as it starts with the semi font color, my icon name, but not actually breaking that value apart and showing it a new way so that I can really see exactly what I want to do.

B

So you start to see that using substring like that can be really really really really powerful. I'm save that, and now it doesn't matter if you're scrum master, your PM adds 40 more columns to this thing. You can handle it and make it visually awesome right and the CEO can come by and say: oh, it looks like works, getting done right, whether it is or isn't, and that's great news.

B

So if you come back just to review again, there's a whole separate video about these contains and starts with with the index of the main thing to note is if it, if the value is not found inside the string you're looking inside, you get a negative one. So all you have to do to check to see if the value is in there is determinate of that value is not negative one and we're gonna do it starts with you just see if it's zero, okay, but here's the extracting value.

B

So the substring again has three parameters: it's a string to look inside the start index and the end index. Fun fact is the start index and the index end index. Don't have to be in the right order. So, if you want to say start index is one hundred and index is zero. You can sew, which is nice, you're, not gonna, get an out of range exception or something along those lines, but again keep in mind. These are zero. Based indexes, this no length function will come up at some point.

B

We're trying to do certain types of string extraction, but if you just use a huge number, you'll go all the way to the end and that works pretty well. But if you're trying to do something like getting value from the right side, that might be a little more difficult and again, if you use that index up inside there, you can split a value. I say split in quotes there. This is not true splitting right, you wouldn't split everywhere, where there's a colon there's, also no last index of right.

B

So there's a couple of things there that you know would be nice to have, but this is still super awesome and a huge leg up, and it allows you to do a lot more without having to create these calculated columns right there. Creating your kind of you know mashed-up value, while you're still looking at multiple values in different columns, and you have to have all in your view, and it gets really that's sad right. So now you can use the substring to do all that so check out the full documentation on it.

B

There are examples of how to use the substring, how to use the index of, and lots of, other operators that you can combine in here and be sure to check out our list format of samples repo. We got 80 plus samples there of how to do all sorts of different things with us called formatting or view formatting. Plus this specific sample I showed you. The generic substring status is available to you so go check it out. That's all I got Thanks.

A

Excellent touch your crease on just one note: on the key top top you run into any SP any column or views formatting issues, please open up those in the SPD box. We do have engineering actually watching on those, but if you have issues were there any on these samples, please use SP. Devilish formatting is open up the sample in here, so just make sure that we are on the right, location and and krisis about actually show the URL of SPF docs right yeah.

B

Sp dev issues right.

B

A

If there's an issue channel issues related on anything on on colon formatting of your formatting, please let us know there are people in our engineering on day to day basis watching those, and so please, please, let us know excellent. Thank you Chris for that. One.