Home
Contribute Contact Us Browse all meetings
Home Contribute Contact Us Browse all meetings
Microsoft 365 Community / Column and view formatting tips / 3 Oct 2019
Microsoft 365 Community / Column and view formatting tips / 3 Oct 2019
Previous Meeting Next Meeting
⏯
Add meeting Rate page Subscribe
youtube image
►
From YouTube: Community demo - How to use rounding and data functions within column and view formatting solutions

Description

This community call demo is taken from the SharePoint General SIG community call recording on 3rd of October 2019. In this video, Chris Kent (DMI) is showing how to use new rounding and data functions within your column and view formatting solutions.

Presenter - Chris Kent (DMI) @theChrisKent

You can find more details around SharePoint column and view formatting from the following article - https://docs.microsoft.com/en-us/sharepoint/dev/declarative-customization/column-formatting

You can find tens of open-source community samples around column and view formatting from following location - https://sharepoint.github.io/sp-dev-list-formatting/

More details on the SharePoint dev community calls from http://aka.ms/sppnp.

A

Hi everybody: it's been a while I'm Chris, Kent I'm a office about minifee I'm on the core team for P &, P I'm, currently out of Indianapolis Indiana, although in like two days, I'll be at Knoxville Tennessee from then on. So that's pretty exciting stuff, but even more exciting is what I got to show you today.

A

So if we go over here, there are classic warrior horses sight and, as we all have, and your horses have decided to be, we've got several fallen comrades right, so they've got a lot of people that have died in the course of their work by people. I mean horses. You know in what better way to remember those people than by listing them in a SharePoint list, no very kind. So we go to our fallen comrades list here, and this is the nice Wow, but all these people cooking this a little better.

A

So luckily, with a couple of new functions, an operator we we certainly camp first thing I want to show you is what, if we want it to say on there before and that's what they died. That's great, but I want so I'm. Gonna add a format only column by going in here, door, more and I'm gonna create our calculated column and in minute all right. There we go just call this age, all right, like this calculated column and again the reason I use these calculated columns with AD formula equals.

A

Double quote: double quote, so they don't show up in the new and edit forms, but I can apply format too so hit OK. Here, let me go back here, not this time. It's column like in format. So let's resume list will format. This column, column, settings format, this column and we'll just type it in directly here, so what dots per se yeah so type in our little squigglies. Alright, we'll say this is gonna, be an element type of div.

A

That sounds great alright, but the thing we care about is we want to set our text content right. So in this case we can actually set this to calculate this age right in the past. This has been fairly difficult.

A

You know just because of rounding issues. So let's take a look. So if we wanted to take something like equals, number all right, we'll say died all right. So this is our died column. So if we convert that to a number- and we convert it to the other date born to a number, we can actually get the number of milliseconds since 1970, whatever all right. So obviously, this is not a very helpful number here.

A

So we'll talk about why that was their age in milliseconds since 1970, so that's raised in milliseconds really really helpful, but if we take that and say we divide that all right, so we'll throw this on here and we'll divide this by say. Oh my /, you know a thousand for the milliseconds times 60 for the seconds times, 60 for the minutes times, 24 all right and we'll preview. That I did not do this just copy and paste what I got over here and the other screen how's. That.

A

There we'll paste that all right, so that's the this- will be their age in years. Right, I, trust their age in here, because if we take away the 365 right, we've got their agent days great, but that is not helpful. Nobody cares that they lived. You know. 0.18 I mean probably their family, okay, but one of the things we can do with dad right. So we can add these nice functions here, so we could say a ceiling right because you live part of a day.

A

You should get credit for the whole day, alright, so we'll say, ceiling now, look at that we'll knock it off we'll just round it up to the very next integer right now, if we're doing that by year, I saw the times 365 yeah, you don't get credit for an extra year, so we're gonna do something different. We're gonna be four. So that's gonna round it down on to the nearest integer right. So that's how we get their age, which again can be really really helpful.

A

You did these rounding functions for and seem to be awesome for things like our pie, chart or mats or some of the other data bars when you've been showing those this percents we haven't been able to get rid of until recently. You know, like the nine digits, a precision there, so this will way you get rid of all that and really clean up your formats in a nice way. All right. So that's beautiful will to save that exciting this and that's okay.

A

But what if we want to do something else right like maybe we want to highlight people that have died. Just there's eat here right, so we can format the current view. I'm gonna, do a nice thing here. We're gonna use an additional row class, so an additional row class provides a way to provide you know a class across your entire row and you can do that conditionally. So, if I typed an additional row class I can do this and I can just say you know.

A

If there's a nice new function here or I could say, I need certain parts of a date, so I can get the year. I get the month, I can get the day. So in this case, if I wanted to say yeah yeah year, it just looks like this at now. So that's the current date and time equals it. Equaled another year of the safe died.

A

And I want to apply the class. Ms bgcolor will say steam tertiary because that's so popular kill it right. Otherwise, we don't really wanna highlight anything. Let's try that out and bam everybody that died in 2019 is now highlighted. So how kind of us now we can see exactly who died but say me want to take this even further. We want to apply something even further to that. We want to really provide a way to to honor our comrades. Well, we can use something with our new tile props, this and fun fact.

A

You can actually combine tile props with an additional row class, just like I'm doing here when I preview that I gotta type in the formatted correctly. Let's try that again, let's grab all that paste it in and we'll just leave off, but you can provide your additional real class inside here as well or even an additional format, because when you do tile, props I'll just save that now you get this nice tiles feature right, so I switch, some tiles I can see. We have a nice memoriam right to our fallen course.

A

Prints right we've gone ahead and set that up and you'll see a couple of things we're doing here that are interesting. Besides, you know, playing everything on a tombstone come down here. You'll see this massive looking thing here, that's beautiful!

A

What it's doing is it's looking at the month right and then it's substituting that month, we're actually gave it a custom date format. This way right we're looking at when it's 0 its January. You know when's the first, it's February because for whatever reason, JavaScript decided that month, sorry 0 based index either way- that's true here in formatting as well, but this allows us to provide really nice date for writing we're in the past. We could only do you know, time or date strings and your locale right.

A

So now you can really go in here and you can calculate dates between things right. You can say how long it's been between something highlight it based on the current date, all sorts of really cool stuff, so that is the brand-new functions. So, let's just review real quick. We jump back over here. One I didn't cover is the new pal function, and this will just do do exponential power, so 4 squared you type in power and for them to write.

A

There's gonna be 16, but here's our floor again, it's gonna collapse and then Stephen is gonna collapse up to the next highest integer, all right, very exciting. These are very helpful over here. Those are date, part operators right. So we got the get date which is just as confusing as in JavaScript right because get date actually for a reason is the day of the month right. So that's gonna be anywhere from 1 to 31 depending on the month and then yet month is gonna start with 0, which is important. Remember that's gonna, guarantee!

A

That's gonna cause your problem at some point. So just add a 1 right if you're looking for the number, the numeric value and then gate here is the full four digits for a year. All right! So that's gonna, be you know, 2019, not just them all right. So all of those samples, I just showed you are available or they will be as soon as I process. My own pull request. Take a look at those. These are gonna, be really helpful for doing all sorts of cool stuff inside SharePoint all right. That's all I got.