Microsoft 365 Community SharePoint, 12 Nov 2020

Previous Meeting Next Meeting

⏯

youtube image

►

From YouTube: Live London Underground Line Status – SharePoint View Formatting & Power Automate

Description

In this 18-minute Citizen Developer-focused demo, Leon Armston shows viewers how in approximately 15 minutes, they can add a live London Underground Line Status Board to their intranet using SharePoint and Power Automate. The solution uses Power Automate to provision a SharePoint list and to retrieve and process line status. Solution uses OOB List web part. Leon steps through transforming SharePoint list with view formatting json to reproduce the colorful appearance of the Tube’s status board. This PnP Community demo is taken from the General Microsoft 365 Development Special Interest Group (SIG) - Bi-weekly sync call recorded on October 29, 2020.

Demo Presenter: Leon Armston (Intelogy) | @LeonArmston

Supporting materials:
• Blog - Live London Underground Line Status – SharePoint View Formatting & Power Automate | https://www.leonarmston.com/2020/09/live-london-underground-line-status-sharepoint-view-formatting-power-automate/
• Repo - London Underground Line Status | https://github.com/pnp/sp-dev-list-formatting/tree/master/view-samples/london-tube-status

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 welcome to my presentation today on creating a live london underground status board using sharepoint view formatting and power automate a little about me. My name is leon, armstrong and I'm a microsoft 365 solutions consultant at intellij in london in the united kingdom. You can find details below and how you can connect with me. You can check out my blog leonardson.com or find me on twitter, linkedin or github.

A

I will now outline the problem I wish to solve. I wanted to create a full end-to-end solution to display live london, underground line statuses on a sharepoint intranet.

A

As you can imagine, employees in many organizations in london will benefit from having a live london underground online status board on their sharepoint internet employees can quickly see before they leave work for home. If there are any delays on the london underground below it's a bit of background regarding the london underground, it's the underground underground transportation system for london, it's known, informally as a tube, and it was first opened in 1843.

A

I will use these diagrams to explain the solution. My solution, which consists of two template power, automatic flows, it's very quick to install and would take less than 15 minutes to install and configure on your intranet. So we have power automate blow. This is run one time only and it deploys the customized tube status sharepoint list, including all the fields, views and view formatting required for this solution, and then we have power ultimate flow 2, which it does the heavy lifting. It runs on a scheduled basis.

A

Currently every 15 minutes- and this connects to the london underground line status, api and then updates the tube status list. If there are any changes, I.e, delays on the line, sharepoint list view, formatting is used to create an interactive live application using the out of the box list web part style to look like a london underground status board.

A

You can see a little sneak preview of my view, formatting for the line status board in sharepoint there there's no development required. Are you creating an spfx web part and then that would typically require advanced permissions to install?

A

I will now demo my solution for you.

A

So in front of me is the transport for london tube line status page. We can see the tube status board here. We can see that each individual line has its own color. We can see the status and we can see it as a special status that it's interactive, that we can click on the status, see if there's any closures. If what the delay is so I wanted to replicate that on people's internet.

A

So all of the information we've just seen is available via a web endpoint by the transport for london organization, so it's published in a json format. So we can see. All of this json format is information about the lines. So, if we just briefly go through this, we can see that the first line is the bakerloo line. We can see it it's a tube and then we can also see that it's got good service at the moment.

A

All of the other lines are listed within this json tech. So what I wanted to do was get this json text and sort it and then put it into a sharepoint list, but first I needed to deploy a sharepoint list and I wanted to make this solution easy for people to install.

A

So I created a power ultimate flow, or do you call it power, automate flow or just flow? Who knows, but anyway, um this deploys the list to a site of your choice and then uses rest api to add the fields and views and apply the view formatting. So you can see that it's manually triggered.

A

We change this url to be the url that you wish to deploy the list onto, and then we can also see here is the view formatting uh that is applied to transform the sharepoint list view into the tube status web part we'll go into that further when we demonstrate it on sharepoint, but I just wanted to show it you there because we'll be applying it when we set the default view to the sharepoint lists. So these are all uh rest queries.

A

So this rest query in powerholds make creates the sharepoint list. It then renames the list. It adds columns to the list so all the columns we need for this solution. It then adds the columns to views and then finally, yes applies a view formatting and creates other views so that yeah deploys your infrastructure.

A

Then the second flow is what I mentioned: it does the heavy lifting. So this runs on a scheduled basis. Every 15.

B

A

Connects to the transport for london tube status, api and then sorts the json, and then, if there's any changes, it will update the sharepoint list. So I'll just go into edit details for this flow and we can see that it recurs every 15 minutes.

A

It connects to the um uh sharepoint lists that we just deployed, and then we can see that it has a http action which connects to the url that we just saw in this tab here. So this is all of the json, so we'll go back to the flow. So when this is run, this http.

B

A

Returns all of the json that we just saw in the previous page and then it passes the json and then it iterates through each line. There are currently 15 lines on the london underground, so here is a bit of string, manipulation and formulating all of combining all the line, names reasons all for the text required and then there is a check here.

A

So if there's any changes, it updates the sharepoint list. If there's not any changes, then it won't update the sharepoint list, so this is now updated. So in summary, this connects to the api every 15 minutes, um sorts the results and updates the sharepoint list. If there's any changes so now we can see the sharepoint list with all of our data.

A

It looks uh not very user intuitive, not very friendly and yeah. It's not easy for employees to quickly identify their line and whether there's any delays. So I wanted to use view formatting to style this, to make it look like a tube status board. So we'll now change the view to the view wiz view formatting and ta-da as if by magic. uh The sharepoint list is now styled to look like a tube status board and we can see that it's interactive.

A

We can click on all the all the special statuses and see information about the delays.

A

So now I'm going to go through how the um view formatting json is put all together, so we'll go into and format the current view and then we'll see the json, and then we can see that there's 133 lines of json that transforms the sharepoint list into the tube status list. That is styled on the left hand side.

A

So I'm just going to go through this and show you how it's all put together. So the first element is a div and this div is the master container. So this is, on the left hand, side. This is where all of these uh colored cells and line names and statuses uh sit.

A

They sit as a children element within the master.

B

A

The master container, so I'm just going to minimize all of the children elements of which there are form and then we will go through each one individually to show how it's put together.

A

So we can see it has four children elements and then we can see that the first child element is a div, and this is the um colorful background for each line and the line name. So it's all of these um boxes on the left hand side. So we can see the class has like an if statement and yeah depending on the line id. It will set the color to be black or white, the font color.

A

Then we can also see the background. Color there's a big if statement, so each individual line can have its own color, so the bakerloo line is a brown. The central line is a um red color and then we can also see this txt.

A

This displays the line name so that displays bakerloo's central district, for example, so I'll just minimize this and move on to the next um element. So this again is a div, but what's special about this is in the style. There is a display. So this has an if statement. So if the line status equals good service, then we display the um div. If not, then it's hidden. So these are the white uh good service uh boxes um here.

A

So we can see the background color white and we can also see the txt content so that displays a good service there.

A

So now we'll move on to the third element, so the third element is again a div and it's conditional online status, not equally good service. So that's a special status. So that's uh these yellow boxes up here that say severe delays. Pop closure plan closure, so we can see that it has a background, color of yellow and then, if we scroll down, we can see that it also has the attributes icon name. So that's this plus symbol here so that uses the fluent ui library.

A

So we just put in the name of the fluent ui icon that we want to add. In my case it was a plus symbol, so that was called add so finally, moving on to the last child element again a div, but this is uh what's special about my solution is to make it interactive. It has uh hover cards, so this has a um display, so this div is only displayed. It's a special status, so not equal in good service, and then it uses this custom card props.

A

For the hover cart, so we can see the hover card there. It has a open on events, click, so we it opens when we click on the um the div and then it has a directional hint of bottom center. So we can see that the pop out is on the bottom and then there is some text transformation, so it adds spaces. If there's multiple reasons and yeah, that's all the view formatting. So, as we can see view, formatting is very powerful.

A

um We can yeah change back to the view and see it as boring, go back and see if it's exciting and you could yeah slot this onto your intranet um on the right hand, side and yeah users could yes see the line status. You may not be based in london, but I'm sure there's lots of other freely available apis. You could use for transport.

A

So I'll. Now return back to my presentation.

A

So, to recap: this solution uses power automate for the quick deployment of the sharepoint list for the retrieval and processing of the latest line. Statuses using the freely available transport for london live status. Api then sharepoint list view. Formatting is used for the ui to make an interactive live application using the out of the box list web part in modern sharepoint.

A

I found this a great gateway project to learn in more detail, sharepoint view formatting, and it opened my eyes into what is possible with sharepoint viewfinder.

A

It also gave me ideas solving many other problems by creating interactive live applications like this.

A

Thank you for your time today and for watching my presentation below are some related links, my blog leonardson.com and a direct link to the blog post for this solution with further details and detailed installation instructions. My github repo, where you can download the template power ultimate files and yeah view the json formatting.

A

You can download the files today and install on your internet directly from there then. Finally links to the pmp sharepoint dev um list. Formatting community submitted samples which are in github. There are many samples that you can use to show the capabilities of sharepoint list formatting.

A

I use these to learn about many of the advanced list. Formatting functionality in my solution. Please, let me know if you have any comments or questions regarding the solution. That's all from me. Thank you.

B

Thanks leon thanks leon, um I will ask: don't don't stop sharing it uh just one minute, a quick discussion: there was a really good set of questions from luis frise related on the solution and the question, and I'm sorry for putting it on the spot, but um now what about using actual logic app? So what made you the the decision? What for you? What was the decision making, let's say aspect for choosing to use power automate rather than azure logic, app.

A

Now I have uh started to do it in azure logic, apps, so I've yeah nearly finished the solution.

A

I think uh yeah. I just started it in powerapps. It was a uh activity that I did during covet lockdown, um but um I can see the advantages of doing it in logic, apps, because it does use a http connector, which is a premium feature in power automatic.

B

Yeah yeah, that's obviously the licensing impact this one and then, if you haven't, have an access on azure subscription, then you can easily do this. But if you don't, then it's not that simple and and again obviously everything depends um and that's. Why there's so many consultants in the world, because everything depends uh from the customer cases?

B

Does the customer allow their end users to have an actual subscription or access there, who's going to have access who's going to create anything in a logic app or do we empower end users and that's what power automate and the list formatting is doing really well as a normal end user. You can build amazing stuff like this. So thank you leon on that, one for sure. So.

B

Super super cool demo and a real real world super usable and valuable thing for a lot of the people who are living in london, because I think everybody is dependent on this transportation logic, so really really cool stuff.

A