Microsoft 365 Community Column and view formatting tips, 19 Aug 2021

Previous Meeting Next Meeting

⏯

youtube image

►

From YouTube: Operators for Precision within List Formatting

Description

In this 14-minute developer-focused demo, Chris Kent shows how Microsoft Excel like formatting in a SharePoint list is possible. Once you understand what you have – numbers or strings, precision formatting is easily achieved. Learn how to display folder and file sizes or numbers generally in lists with 2-digit precision / formatting (0.00), using various operators - toString, indexOf, substring, padStart, padEnd, ceiling, floor AND/OR with formulas in sample code. This PnP Community demo is taken from the General Microsoft 365 Development Special Interest Group (SIG) - Bi-weekly sync call recorded on August 19, 2021.

Demo Presenter: Chris Kent (DMI) | @theChrisKent

Supporting materials:
• Documentation – Operators | https://docs.microsoft.com/en-us/sharepoint/dev/declarative-customization/column-formatting#operators
• Documentation - Use column formatting to customize SharePoint | https://aka.ms/spdocs-column-formatting
• Samples - SharePoint List Formatting Samples | https://github.com/pnp/List-Formatting
• Videos - Microsoft Lists | https://bit.ly/lf-videos

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

So we're going to talk about some list format and stuff, I'm chris. Let's do it bam all right, we'll head over to our classic warrior horses site- and uh you know the warrior. Horses are locked in battle all day, long uh constantly playing strategies and so on, but they're also concerned about the inner lives of their horses and their. You know so. They've got a collection of diaries here that they put together uh the horses store in sharepoint, because that's where you want to put your diaries that makes total sense.

A

We're going to use this uh as an example to look at a couple of things that maybe are easier in other quote-unquote languages, but sometimes can be a bit challenging here in this format. I'm going to give you a couple of tips on how to do that. We're going to go as quick as we can all right. So the idea here I've got this beautiful document, library, right and I've got uh I've created a custom tiles view. You know it's very beautiful.

A

It shows the thumbnail and one of the things I'm trying to show is the file size right. So how do we get that, and how do we look at that in different ways? So there's several things involved there and the first thing we want to look at is how we deal with precision in numbers, so to do that. We're just going to take a look at this random.

A

Some number column here, which you can see just has the numbers in I'm going to go column settings we're going to format this column and all we're going to do here is we're just going to go to advanced mode. So we can see what's going on here and we're just going to make this. Let's say a div.

A

And we're going to say our text content all right. Let's just show the current field so at current field and that's how we just get that value that number. If we preview wow, it looks exactly the same right. Well, what if we wanted to do some math on that all right, so now we're doing an expression we say equals and we're going to just divide it by 3., and in this case we start to see we get some pretty ugly numbers now, the ones that divide fine, that looks great.

A

You know, we've got one that has one you know one decimal spot and some that are locked uh also, I am in the united states, so we use the uh period for the decimal and what I'm going to show you capitalizes on that, because certainly swap out what I'm doing with a comma. If you are, you know almost anywhere else in the world. Okay, so we take a look at what can we do here? How can we start working with something like this?

A

So the first thing we need to do is is find a way to do. Precision now, there's no! You know two string here where we specify you know the number of decimal digits. We want or anything like that, not when we're doing math. So first thing we want to do is find out. Do we need to do anything, so we can inspect this to see if there's a period in there right.

A

So in order to do that, we're going to put an if we're going to say if that current field has a period so to do that, we're going to use this index of operator index operator now it needs a string. So that's the number of values we're going to use the two string operator on that. So now we're going to wrap this so we're going to convert this string just means text right. So I'm going to put two string on there, I'm going to say, look inside there and tell me: is there a period now?

A

We know it won't have a period if it equals negative 1, because it's going to give us the position of the period so we'll say if it equals negative. One right. Well then, just put out our value, so we're gonna, say current field divided by three. Now we have to do the math again because there are no local variables here, which is you know sad. Sometimes it makes our expressions a lot more complicated, but it does work, in other words, we're just going to put a question mark right.

A

So if we preview that hey now we're detecting when we need to do something right, so when there's a period we're going to do something different, otherwise we're going to show it now in this one. Our goal is to have it with two precisions right so to the hundredths right. So we want, you know: dot, zero, zero or dot. You know whatever afterwards. Now that's pretty easy in this case right because we know it doesn't have any. We can just say plus a little string. You know zero, zero right and hey.

A

Look at this hey there. We go. We fixed that one number so as long as all of your numbers evenly divide, you're all good to go with this. But if you live in the real world we gotta do a little more all right. So what can we do with that secondary part? What we've got is a question mark right now.

A

Well now we can start to use things because we've already looked at as a string, we can do some of our string operators all right so again that just means text in this case we're going to use a sub string and so we're going to grab a substring of that value. So again we have to convert it to a string to look inside it or to get the value right. So we're going to say at current field divided by three to string and we want to say inside that text we want to start.

A

You know at the beginning so zero and we want to go. You know to some value in this case. We want to go just past that period, so we're going to say index of actually take our whole index of from before, because this is telling us where that period is grab that guy we're going to put that right in here, instead place that in there and then we're going to say plus 3, of course, because we want to have the period and 2 digits after it.

A

So if we preview that there we go now, we've got two. You know digits of precision and that's great. But what about this guy right? This 12.5 is messing our stuff up right now there are a couple of cool operators, there's a pad end and a pad start which could help us with that, and you might think I'm just going to slap this on here.

A

I'm going to do a pad end and the idea of a pad end is you give it a string which is this one and we're going to say we want a total number of digits, we'll say 10 and we want to throw zero on the end right there. We go we'll preview that ah we have a disaster, and that is we don't actually know how many digits there are before the period and all the way to length operator that length operator is for arrays and doesn't really work on text. So what can we do here?

A

Are we out of luck? No or I wouldn't be showing it right, we're going to move our pad in. So let's get rid of the pad end and what we can do is we can take advantage of the fact that substring does not care. um If you go outside the the. What is it called the bounds right of the string, so in other words, even if our string is two characters long and we specify we want zero to a hundred, it doesn't care. So what we can do is right here with our two string.

A

We can actually pad it here, so we can pad the end here right there, and we can say in this case we'll go just like a hundred. We don't care a hundred and then throw a hundred zeros on the end of that string, no matter what we're still using the index of on the original calculation, and we do that bam. We have our two digit precision, so that is a lot of little operators and nested. If statements to make that work, but we've got it so that's very, very exciting.

A

So now, how do we use that for say our file size all right? So, let's save that just we've got it. So our file size on the document library is in the additional columns. We have this file size column. We can apply that and now we see that in this case it actually is showing the value right. Bytes, megabytes, kilobytes it'll go up to gigabytes right and then for folders. It actually shows the number of items in there. So that's interesting! So can we access that value ourselves?

A

Well, let's just use the same some number column again we're just going to look directly at that file size in this case we're just going to say file size. So we access this with a wonderful name of file, underscore x0020 underscore size very catchy all right, so we got that what the heck is that right. So now we got some weird value.

A

What that is, is the number of bytes right, so it's not giving us this fancy value. If we try to put a display value like we can with other things, it's still going to be the same thing right and it's not showing anything for folders. We don't get this fancy one item or or so on. So what can we do with this? How can we work with this value?

A

Well, good news, we're going to do a lot of what we just did, but we're going to add a bunch of operators and some other things there to kind of calculate those out and so the way we do that is first, we want to check you know. Is there anything there, so we're going to say equals, and we say if file size, right, we're going to say. If it's nothing well, then just output. You know nothing right.

A

We don't care otherwise, then put it out right and so then we'll put out that file size again, so I'll underscore x, zero, zero, two zero underscore size right. So that's gonna kind of accomplish the same thing, but the reason we're doing this is to protect our operations here a little bit. But what if we wanted? That kind of item count right there like they've got so the good news is that's a different column. So if we go to the show hide columns, there is an item. Child count column.

A

So we can apply that and we can see that the values there we can use those too. So we can come in here and say instead of this we can say you know, let's show the item child count right. It doesn't have any extra things in it. So there we go so now we say: if there's no size we'll show that right we get a little bit fancy and we could say you know, I'm just going to cut and paste this one for time right.

A

But if I've got an item, title account and I could say inspect that to give myself some fancy stuff so say that, plus, if it's greater than one items otherwise just say, item and give an extra parenthesis there, let's see yeah. So we got one item, that's pretty cool, but we still aren't really working with our file size. The way we want to so the first thing we might do with our file size, um you know, is take a look at it right.

A

So we want to know hey if this file size, you know, if that file size is greater than say 1024, you know, or it's less than 1024. Then we want to show it in bytes, which it already isn't right. So we're going to say then just show the file size file underscore underscore size.

A

You know we're going to say plus bytes, very exciting, otherwise we're going to say question mark just so we can see what it is right. We preview that something kind of weird that happened right I mean it worked, but then we got these and that's interesting. Now. One of the things to note is that file size is actually coming back. As a number I mean not as a number it's coming back as a string, which is very strange so a lot of times.

A

We have to convert it to a number when we want to do any kind of math on it. So let's jump ahead a little bit right, so that works just fine, if we're doing uh say the bytes right. But if we wanted to do say kilobytes now well, then that gets a little more complicated. So let's check the next one. So now we're going to say if we're just going to put it in a number, so we can operate on it operate.

A

You know what, let's? uh Let's not have you read that out repeatedly all right copy that paste that in if this thing right is less than and we're going to use the pow function right, so this is going to raise it to the power of 2 right. So we're going to raise it to the power of 2 or say: if it's less than that right, then we want to put it out in kilobytes.

A

So this is going to be kb, otherwise we're going to say, question mark again right and we're going to put our extra things. We're going to have to do is we're going to start inspecting that now this gets a little crazy. So I'm just going to copy and paste this huge formula because we're going to use everything we just used in that sum number column earlier, except we're doing all these raised to the power of and all this other fun stuff. So let's just copy and paste that and kind of go through.

A

That looks real pretty right, but if we preview that, oh that's fancy, so it's got it out to precision. It's got the kilobytes, they got the megabytes and the bytes, and if we had something large enough, if the diary they really keep, writing we'll, have it in gigabytes too right and then, if we want to throw back in our item count, we can do all that too.

A

So that's interesting right and if you take a look at this, this is a sample available to you and you can check it out afterwards uh to really kind of dive into this fun thing. But what's really happening is the nested. If statements and we're doing is we're converting that string value that comes back to a number we're comparing it first to see if it fits within bytes and then use it there.

A

If it doesn't, we move on to kilobytes and so on, and we raise it with power to see if it falls into those categories, and then we do the math to divide those out and then we're doing that same kind of sub string and two string with the index of to get all of our precision on there as well. So we don't end up with crazy numbers. So then we can just take this very exciting formula. We just did and I'm just going to move over to our tiles view.

A

Let's go to tiles where we've got that size placeholder and I'm going to format that view and all I'm going to do is go down here where that is. I'm going to say instead of size, let's copy that same form. We just did and we'll preview it and there it is so whoo. Now you can work with file, size and item child count and so on, and all that works pretty nicely. Also.

A

One thing to note here is that uh this thumbnail, if you ever haven't seen this- you just say app thumbnail large wow- that couldn't be easier right and that'll just be the source for the image for the file, so check that out all right, so very, very quickly. We'll do a quick wrap up, and that's that there are a series of operators you can use for precision right.

A

If you want to just skip all of this kind of string and text manipulation here, you can just round them up or round them down right with the seal on the floor and just cut them off and don't care about those anymore.

A

And then, if you're working with the file size, remember that it is in bytes and the value is text and it's going to be empty for things like folders, but also things that might surprise you.

A

Things like a onenote file, for instance, oftentimes, doesn't have file size and so on so you're going to want to check for those conditions before you start doing operations on it to avoid failures, and then, of course, itemchildcount is available to you as well, and both of these don't necessarily have to be in the view, because it's a document, library they're going to come back and you can just use them. So there you go. Here's some resources check out the documentation.

A

We got over 100 plus samples on all sorts of things, from this to views to forms, to columns to every column, type and so on, and of course we have all of these videos. We do have a couple of weeks and you can check all those out on the playlist available on our youtube channel here and that's it for me cool. Thank you. Chris.