Microsoft 365 Community SharePoint, 8 Sep 2022

Previous Meeting Next Meeting

⏯

youtube image

►

From YouTube: List Formatting Magic – Data and Time formatting options

Description

In this 23-minute developer-focused demo, Chris Kent’s solution shows you how to eliminate times aligned to dates for clean date-to-date comparisons. Here’s four functions for Zeroing out dates – Date () (convert text to date based on locale), getDate () (get day of month), getMonth () (get month index), and getYear () (get 4-digit year). In list, display number of days since list item was modified. Show date formatting in list and gallery views. Apply conditional formatting for clarity. This PnP Community demo is taken from the Microsoft 365 & Power Platform Development Community call recorded on August 18, 2022.

Demo Presenter
• Chris Kent (DMI) | @theChrisKent

Supporting materials
• Documentation - Use column formatting to customize SharePoint | aka.ms/spdev-column-formatting
• Icons - Flicon.io | https://flicon.io/
• Repo - Farrier Community-tooling | https://github.com/pnp/community-tooling
• Samples - SharePoint List Formatting Samples | https://github.com/pnp/List-Formatting

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

Okay, I'm chris uh there you go we're gonna talk about some uh list, formatting stuff, and uh so let's head on over to our classic glory horse's day, all right, so we're on our warrior horses site and a few months ago we looked at a list that we created that the warrior horses are using to track assassinations right, so we've got our hit list and as you do, when you're working on murdering your enemies, so you often track that inside sharepoint right and a list is the perfect place for that, and with that, uh we previously discussed a few of these different formats here and all of these are available as samples uh you know, for instance, the threat level here, uh there's one that can be edited on the fly right.

A

It's got the inline edit and so on, and you can find that here in our samples, repo uh under column, samples- and these are so column- samples are organized by column type. So we have a ton of different things for number columns.

A

You can see they all start with number down here and, if you're looking for that particular one, it's number level bar. So, if you're looking for this one right there, it is, we've got a bunch of those. So that's exciting and that's what I've chosen here and what we're going to look at now is we want to take this a little further right, so we want to actually start tracking some progress or we've got a status and that's okay right, uh but we really want to you know: it's been three months.

A

The only one that's actually been murdered here is martha washington uh because she was already dead. So that's pretty lame right. So how are we going to track these right? Make sure that our assassinations happen on schedule, so oftentimes you're going to add a you know a date column, so we're going to have a date and time column here and we're just going to actually we're going to come back to that. First, I want to look just at columns in general, so let's show hide columns and I'm just going to show them modified right.

A

So I want to talk in general about uh dates and times how we're working with those right. So we've got this modified date. You can see it's got this friendly display where it's showing you know. I added these just a little bit ago, the other ones again back in may, right and and that's okay. But what if we want to do a little more with that? So if we format this column advanced mode, so we can see that let's go over a little bit and let's hide up hide that stuff.

A

Okay, let's get that a little bigger, that'll, be fun that way and just for funsies. That sounds good. uh We're going to type an l type here and we're going to say it's a div right and that way we can just get to our text content right and if we were to say you know, act current field, that's our magic placeholder token it'll, take whatever the value. Is there we preview it there.

A

Now we get this interesting thing right and you can see if there's dates and times and even if you select now this one modifies always date and time right. But even if you pick a date time field- and you say date, only it's still got a time inside of it.

A

It's just not showing it right because that's more about the editing and the view experience, so you will have to account for those when you start doing your own formatting, and so what I want to look at is how do I determine how many days ago, something was right. Now, that's something that comes up a lot or we just want to be able to do date, calculations based on days or something else. Now we don't have a you know a date. You know ad or a date.

A

uh One of those types of functions get the date difference. Unfortunately, but we can do some cool things with that, but because time is in there it starts to mess with us right. So one of the things we can do is we can say equals and we could have all sorts of different things. So I'm going to say locale is a date string. Let's try that string right. It should look pretty much the exact same, except now. It's just the date right now. We've got a couple, others there's a date time.

A

Let's see nope, oh it's just a little cal string. I think. Actually, you know, because that makes sense there we go okay, so that idea we can start to do that and I'm doing that for a reason, so we can make sure we're always seeing the same format here now. If I wanted to break this thing down right, one of the things is, I can get certain values out of this. I can say you know I can get the month.

A

I can get the the day and I can get the year and if I wanted to zero this out, so the problem is that time. So, if I'm starting to do calculations between these, the only real way to do that is within converting these dates to numbers, which is the millisecond since 1970, which you know, confuses everyone, uh and we want to make that a little easier because we don't want to account. For you know this is 512 right, but it also has this extra 10 and a half hours on top of it right.

A

So we don't want to mess with when we're doing those kind of calculations. Since we want to compare to now- and we want that date- you know the number of days to not change, as our clock goes up throughout the day right. We don't want this now that we're at 10 30 and not 10 25. Now it has an extra day. That's not what we're really going for technically accurate, but it's not what we're looking for we're looking for the date to date. Comparison!

A

So we're going to talk about zeroing out these dates, so we can do that uh by using a lovely little thing called the date all right. So when we use the date function and we put in a date here right, you can type in a date, so we type in 12, 26, 1981 a wonderful day right and we'll preview. That and now you can see it has just the date right, but it actually pulled out a time. So it's actually converting that to a date time from a string now this is locale dependent.

A

Unfortunately, so you will have to take that into account. So in my locale here in the us right, we do month first because uh you know that's how uh freedom and stuff okay. So in this case, if we wanted to build our own right based off of the current value, we can actually build this as a little string inside here.

A

So we can say you know get month and we could say our at current field all right and then we could say add a slash make sure we had a plus right we're building a little string inside here, we're going to say a plus and we're going to say get date instead of day uh match the javascript nonsense right. So current field, alright so get date, and then we're gonna plus we're gonna, add another slash. And then, if we add the year right, that's gonna get our four digit year.

A

So there we go now we run that we're going to see an issue here, and that is month in javascript's, infinite wisdom is zero based index of the month right, so january is zero, and so because this is going to string we're just going to say plus one to it and there we go, we just corrected it. So now you can see we've actually zeroed out our date right. So now it's at 12 am for all of these and we don't care about time anymore.

A

We have a zeroed out date, which is pretty cool and we could take that exact same thing. Let's get rid of this extra thing and we can zero out our current time right. So, let's just copy that for simplicity and we're gonna say you know, put that in there we'll come back to that. We're gonna swap out at current field with at now, which is a magic placeholder token as well, and that is the time at render.

A

So when you load this view up- and it applies the format that is the time that it applied the format so there he goes now- we've got these two things and if we wanted to do math on them well, we need to convert these to a number. So we're going to wrap this guy in a number I'm going to come down here, we're going to wrap this guy in a number. So we can subtract this guy and we're going to say all right.

A

So now, we've got a number subtracted a number, and if we look at that, that's going to be really pretty right, but you'll notice that, because we've zeroed out the dates, you can see that we have an exact zero, because this is the number of milliseconds in terms of days very helpful right, but we can make it more helpful and the way we do that is we wrap this whole thing and another set of parentheses. Just to make sure I don't mess up our order of operations because uh you know I do all right.

A

We're gonna subtract that now we could just type in the actual value. But I find it's a lot easier to read if you can call any of this easy to read by you know the number of milliseconds times the number of seconds times the number of minutes times the number of hours in a day right. If we divide that, then we get hey there's the number of days ago and we could even put a plus space days ago right ooh fancy.

A

But the idea here is before I did the calculations on the dates I zeroed them out, because I didn't care about times. So I know that that's that's a very friendly formula there, uh but that's why we tried to build it as we went up right. So that's exciting, so we're going to save that wow wow wow, all right! So now, let's talk a little more right. So now we want to do something else with dates right so a lot of times.

A

We want to actually compare dates, so, let's add a new date time column right, so we can hack track when this is done. So this is eliminate, buy right and uh we don't really care about time. We're not going to include it, although we know it's going to be midnight uh inside internally, we're just going to say save we got eliminate by thank you for covering up my column.

A

Go away all right. I don't care about the status anymore. There we go we'll just look at the status perfect. You know we edit that in grid view, so we can just set a couple dates here, all right, so we had uh we'll say you know, eliminate this guy by uh next month, sometime right and we're gonna eliminate this guy. I don't know the next month after that- and I don't know I'm picking these at random here november 30th and we'll say this one.

A

You know martha washington, we don't care, it doesn't matter all right and patrick yeah. We gotta, we gotta get rid of him, but we can wait till january on him all right I'll. Let him get a little more get another release of pnpjs. First and uh little timmy will just say uh you are overdue right that should have been yesterday all right, so that's exciting!

A

Now we've got some exciting sample dates in there and one of the things we can do if we go to column settings we're going to format this column and I'm going to scroll over. So we can see that all right, we're going to come in here- and I just want to change this slightly all right. So I'm going to say elm type is a div and I'm going to say the text.

A

Content is at current field and if I want to look, you know the way I've configured the date to look, I'm going to say display value at the end, all right and that's not going to change much of anything right there. But that's giving us that, but the one nice thing we can add is inline edit field. So I don't have to switch to quick edit all the time right- and I just put at current field all right and when I do that it doesn't change much visibly, but we can actually when we hover.

A

Now we get this now we can click it, and now we have a date picker right here without going into that quicken right. So that's really nice right. I have to go to the grid view to see all that we can just edit those dates as needed. Now we're going to come back to that. So that's cool! Now, we've got a date, but let's do something with that date.

A

So we're going to come here and we're going to format the current view, because now we're going to apply a view format, all the other ones have been column formats. So far now we come in here and we can write some conditional, formatting rules and those work great, but I don't want to demo those so we're going to come in here and what we're going to demo is the additional row class all right.

A

So this is the idea that the entire row I can apply some class on it right, so just a saving site, msbg color red, because who doesn't love red, alright, now everything's red right and that's okay, but we can do this all conditionally, as we've seen a few different types right. So let's do that by figuring out where my other stuff is okay, perfect, okay! So now what we're going to do is we're going to write this. You know a lot of times.

A

We just did that crazy, math right when you're, comparing the numeric fields and it's like. Does anyone actually want to do that? uh No, you have to sometimes, uh but in our case we want to make this a little simple right. So what we want to do is we want to turn things red if they're within 30 days, right and if they're in 60 want to make them orange.

A

If they're like 90 days, we'll make them uh yellow right and otherwise don't worry about it, we don't need to flag them out, they're, not really coming up. So that's kind of our plan here. So how do we start doing that now? It sounds straightforward, but when you start to look at the math, I don't want to zero out dates and compare that in a multi-nested if statement and that gets that does come up. These questions about dates come up all the time on, like the stack exchange network, um the issues list and so on.

A

So I'm just going to show you a quick trick that I use for this, um and the first thing I'm going to do is I'm going to say equals. Let me say if right- and the first thing I want to check is the status because I don't want to do anything if they're deceased right so first thing, I'm going to check is status, I'm going to say if it equals deceased right, then don't do anything. Otherwise we're going to do something right.

A

So otherwise we would say you know msvg color uh whatever or something right, we'll come back and fix that. But the idea here is we. What do we do is equals all right? Let's try that again, boom right, so we've already eliminated we're not going to apply anything to deceased. So now we're going to go into our else statement. We'll write the rest of these things right.

A

So now, let's compare so the easiest way to compare instead of doing something like crazy math right is, we can say, if add day so add days, we'll take a date right in this case. We're going to say now and we'll add a number of days to it, so we're going to say 30 right and so we're going to say if 30 days from now is greater than our eliminate by. I think that's what we call that column all right. Let's make sure we pull that over.

A

So we can actually see that column very easy to read with that green. All right, then, we want to say that's going to be ms vg color. What do we say red right and then otherwise we're not going to find anything just yet, let's preview, that there we go so now, we've got a 30 day, ones all right, and then we can just keep this little pattern of nested if statement.

A

So instead of our else here we can come in and say you know now we can say if uh days you want to do this in in ascending order right, so the earlier ones get caught before that. So now it's 60 and we're going to say is greater than the eliminate by all right and I'm going to say, that's also an msvg color of orange and then otherwise. Nothing all right so now we start to see now we've got an orange and we said we want to do one for 90 days.

A

Let's do just one more here say if days at now is 90 days. Right is greater than r eliminate to buy right, then we're going to say that one was uh an spg color will say: yellow, yellow and otherwise again we don't want to do anything.

A

So, let's preview that, oh probably we didn't have any within that time frame or I didn't do right- let's see- I probably shouldn't pick something within that time frame: okay, but that's the idea, so we can start to apply these types of things, and if we save that you know, let's, uh let's get one within 90 days, how's that so maybe we just need to go back a little bit, how's that yep there we go there's one within 90 days again! That's why we added that nice inline edit field.

A

Now the colors are not great right. Maybe we change it slightly. We say orange light, let's preview, that, let's see, if that looks a little better, that's slightly better, and then we can add. You know other things for like the the text right. So we could say ms font color in this case, which won't be white or something all right. So you can read that you'll notice, because I have one: that's already got a few things.

A

It is affecting a couple things there and the title field doesn't necessarily have the font colors being applied because it has one applied automatically. So you want to mess with that just a little bit if you're, looking for where I'm getting these classes. If we go back to our samples, repo right up at the top right, we've got this link right here and if we go to our groupings, that's where I'm getting all these class listings right. So we've got a background. We have these msbg colors. Those are all the the ones and bg cubs.

A

It sounds like bg's, that's funny! Okay, uh we got all these right, so there's a ton of them. You can look through all those. um If you want to do the more traditional ones that are seen with a lot of the list, formatting like the wizards and other stuff, that's these sp css background color ones where you're gonna see like the bg, coral right and each one of these actually has like.

A

So if you see bg dark red here all right, if we wanted to to take that instead, let's just grab that set of our red and we can mix and match these as needed all right. So instead of our red here, we're just gonna put that guy in there all right, we'll preview that so now it's kind of an interesting red, but because these have these, also, if you check over here, uh we've got so we've got dark red.

A

We can come all the way down and we should have a dark red text I'll just I know it's in there, so I'm going to type it here. So each one of those has a matching sp css background. Color, it's a dark red text has a matching text. So, let's see there you go, uh it doesn't look great, but that's what they have set up here. So you can uh so that I actually may not type that one right. So maybe I should have looked it up here.

A

uh We'll come out here to the red, dark text: red, dark, red text. Okay, I think it's red text red background red text, but the idea is, you could use those I'm going to eliminate the text portion right now, because I don't want to mess with that, because I typed it wrong and why would we mess with it right so we'll just type that ms bg color red and we'll put that ms font color as white? But again all of those classes are listed for you there and that's a great place to reference those.

A

The point is: we now have this interesting way of calculating those dates without doing all of that kind of nightmare stuff. We had to do in this case right uh of course, I messed with all the modified dates here.

A

That's all right, they're all zero days now, but if we save that that's pretty cool right, let's go ahead and copy this out, because I want to show you a couple other areas now if we want to use this across the board right so now I want to use this in say my gallery view all right, so let's say make sure I didn't save it before I switch.

A

If I go to the gallery view, which is not actually a separate view right, uh let's switch to design mode, you know it's it's obnoxious, let's go back and you know we go to format card view, and now we can see we can edit our card right, move things around. Let's make sure eliminate buy is in there and that's cool like we've got our different things here. The first thing you'll notice, though, is it's not really showing the same way? uh You know when you click this.

A

We don't actually get edit ability right, it'll, show it here and there's our threat level and so on and that's cool. uh If we go to advanced mode once we get these kind of where we want them right. um We go to our advanced mode, any one of those we could come down here. uh Let's go to our modified, for instance, right we're showing the three things again. We could hit control, f and say modified there. It is we found it and so right here it, these cards are generated with there's the title up here.

A

If you selected to show the title and then the next one is going to be the actual value. Now, that's nice that they generate all that for us thanks. No thanks right because we're just going to say, column, formatter reference in this case we're going to put the uh modified column there as our column of reference, make sure you put that in quotes, because you know, apparently it likes it to be in quotes there. We go let's preview, that there we go so now.

A

Our format is showing up there right now, it's zero days and we do the same thing right for our next one, which is our uh was it eliminate by all right. So we can have that inline editor, we'll do the same thing here, we'll type in a column, formatter reference.

A

And then we'll just say: what's it called eliminate by eliminate buy there we go, and we preview that now we have our inline editing, which is really cool right and that's neat.

A

Now, if we were to do something where we put a button, for instance, in fact, let's do that very quickly just so, I can show that, let's go back to just so, I can see it we're going to put a very ugly button here, format this column, you can make buttons look a lot better, but we're not going to do that right now and there are plenty of samples of how buttons can look better, but we're going to do a button, we're going to say our text.

A

Content is our current field and we want to do something. We say: custom pro action, action and then we'll say our action we're just going to do share because I just want to show a button all right if we preview that we get that beautiful beautiful button. But when we click on it, we're prompted with the share dialog right and again, we've got samples on all that. That's not really what I'm trying to show what I'm trying to show here. So I save that- and I go back over to that gallery view all right.

A

So our titles view in some cases right and we go in format, this current view and we do the same thing for title field right. So we come down to our title. We gotta try display name, that's fine. We do our column formatter reference here, so column, formatter reference field name is not field name, it is title.

A

Oh battle, there we go: let's preview, that now we show a button, but when I click the button, oh no I've lost my functionality right and so that's lame, and the reason for that is by default when this thing gets generated. It's got this guy right here.

A

This top guy is a button that ends up hovered over the entire thing. That is doing a custom reaction of default click right and now our inline editing we'll work through that, and so will our uh edit fields like if we do some of those custom actions, that'll work, but our buttons don't. So if we just kill that thing, maybe that we lose our ability to open this, but we can double click it to open it.

A

Then we can then get our shared link. So I just wanted to point that one out as you're messing with these and doing some of that you can now one of the other things I want to point out is what, if I want uh that same kind of row class thing I did before right- that's where we don't have a real class for galleries, but we could take what we did before I'm going to leave that in there just so, we've got it and I'm going to take that same.

A

You know stuff we did before and let's take this guy, because we definitely the sb card container, all right, so we're going to say equals sp card container with a space and we'll say plus that right so now we should have ah so now. We've got that same logic here, but we don't have a column for a reference for the additional row class. We can do that same thing here.

A

If you're doing the calendars type stuff, you could do an additional event class. It's the same thing you can write. All of that. The only thing I would say with the calendar is be careful with the selection. You do need to handle if it is selected. We covered that two weeks ago. If you want to look up that video okay, so I know I showed a ton and tons of stuff, uh let's go and do a quick review all right. So the first thing we talked about was how to zero out dates.

A

So this is where you're going to take advantage of. You know these four different functions. um You know you're going to take date, which is going to take strings again, very important. It's based on locale. So if you're expecting this to work across multiple regions, uh you're going to get a little more creative with that, there are ways to do it. It's it's just harder, so check that out and then these individual pieces will pull out the parts of the date you need.

A

So you can eliminate the time and then, if you need to do math on those wrap those in a number on top of that, do the math and do some exciting division.

A

That's it for now, but check out uh various tools on the site for all that documentation to find out the class listings and everything else and uh reach out. If you need help, that's it for me, awesome thanks chris, that was a uh a mouthful or a horse full or whatever, so excellent, very, very cool awesome. Thank you.