Live London Underground Line Status Solution Summary & Learnings From Converting To Azure Logic Apps

This is a follow up blog to my London Underground Line Status solution (blog post) that I developed in late 2020. I created a live London Underground Line Status board using SharePoint view formatting for the styling and Power Automate to retrieve the live line statuses.

I have now updated the solution to run using Azure Logic Apps for the processing instead of Power Automate. I will use this blog article to focus on my learnings from converting the processing for the solution to Azure Logic App from Power Automate. I will also provide instructions for installing the solution in Azure Logic Apps.

Below is a quote of my solution and an animated image of the solution:

Display on the homepage of your SharePoint intranet a live feed of London Underground line statuses using the publicly available Transport for London (TFL) API. The results are retrieved by Power Automate (Flow) Azure Logic Apps, then written to a SharePoint list and using SharePoint view formatting the list view is styled so it looks like a familiar TFL tube status board with correct line colours. It also reports on the Overground, Ferry, TFL Rail and Tram. The status(es) of the lines can then be clicked if there is any further information i.e. delays/suspension/closure etc.

It was from a personal point of view very enjoyable as I was invited to present my solution to the Microsoft 365 PnP – General Developer Special Interest group (29/10/2020) and you can see the YouTube video of my solution below from the show. I ENCOURAGE any other people out there who have a great demo to not be afraid to demo on a community call. There is also a great initiative from the PnP community where they will assist you with preparing for a demo under the Sharing is Caring initiative.

Learnings from moving the solution from Power Automate to Azure Logic Apps

I wanted to recreate the processing part of the solution, the part that connects to the London Underground API on a scheduled basis and retrieves the latest line statuses in Azure Logic Apps. Although Power Automate and Azure Logic Apps are very similar in terms of the platform underneath there are some differences mainly Logic apps is charged per actions executed (consumption model) rather then Power Automate which is typically a per-user licence.

Logic Apps names it’s loop slightly different and by default runs all iterations at the same time rather than sequentially.

Logic Apps names its looping control For each rather than Apply to each and they provide the same functionality to loop through a collection/array.

There is one BIG difference in terms of the defaults for the concurrency of the loop. By default an Apply to each in Power Automate runs sequentially. An example being if there are 10 elements in an array then each element will be processed by the loop in sequence and the next element wont be processed until all the actions in the apply to each loop have finished executing (see image below for the settings).

By default in Logic Apps the For each loop runs at the same time/parallel (see image below for the default settings). This means that if you had a an array of 10 elements the for each loop would race through all of the iterations at the same time without waiting for any of the loop to finish. Meaning any loops within loops then any text manipulation that relies on setting an order of variables for example will result in incorrect values. In my case it gave me mixed up variables of Line Statuses as the loops inside the main for each loop had not finished and I had for example Northern Line statuses associated to the District line :-).

I then realised from reading online and twitter that the behaviour was different between the loops in Power Automate and Logic Apps. To recreate the behaviour of the loop I had originally created in Power Automate I needed to turn on Concurrency Control and change the Degree of Parallelism to 1. Then my string manipulation and combining linestatuses and reasons worked fine and for the correct lines!

Logic Apps – HTTP Connector is Built-in Action

My solution makes use of a HTTP action in Power Automate which is a premium connector and requires a Power Automate premium licence.

In Logic Apps the HTTP connector is a Built-In action and costs around $0.000025 per execution so super cheap!

In Logic Apps you are billed per action and Standard and Enterprise connectors cost more so is more pay as you go. Whereas Power Automate each user has an allocated number of actions included in their licence and cannot exceed these.

Removed all Parse JSON Actions

I removed lots of unnecessary Parse JSON actions which were not needed – I realised in Power Automate and Logic Apps they were not needed and For/Apply to each could accept a JSON BODY directly.

Unable to export from Power Automate to Logic Apps Format

Often in Power Automate for your Flow the Export to Logic Apps template (.json) option is not available in the Export drop down and only export to Package (.zip) is available (see images below)

Now with the new PnP.PowerShell module there is new commands for managing Flows. See my blog regarding these new commands and also a script to export all the Flows in your environment

You can now use the Export-PnPFlow commands to export your Flows as a Logic Apps template in JSON that can then be exported into Logic Apps.

Microsoft list the steps here to export from Power Automate to Logic Apps.

In practice the process documented above did not work for my complex London Underground flow and the import to Azure Logic Apps failed on validation (see error message below) but has worked for importing simpler flows.

Problems loading reference ‘http://schema.management.azure.com/schemas/2014-10-01-preview/Microsoft.Authorization.json#/resourceDefinitions/roleAssignments’: Unable to load schema from ‘http://schema.management.azure.com/schemas/2014-10-01-preview/Microsoft.Authorization.json’. No schema request service available(768)

Recreated Flow from Power Automate in Logic Apps by creating it from scratch and visually copying the logic/actions

Due to export functionality not being available in Power Automate to export the flow to a Logic Apps Template (.json). I had to recreate the Logic App from scratch. On one screen I had the flow in Power Automate and on the other screen I had Azure Logic Apps open.

All of the actions/connectors I used in Power Automate are available in Logic Apps or in the case of loops are named differently. So I was able to recreate the solution in Power Automate by hand – copying the inputs and array manipulation etc.

Limits between Power Automate and Logic Apps – i.e. Power Automate fair usage.

My solution in Power Automate unfortunately will often exceed 10,000 executions (of actions in a flow) which exceeds the limit for executions per 24hrs for the low tier (aka seeded Microsoft 365 plans). Microsoft started enforcing this in October 2019 and started to turn my flow off as it was executing too many actions.

Microsoft are currently not strictly enforcing this 10k limit exactly during the transition period and look to be allowing 20k of executions per day before finally turning off the flow.

Unless you purchase Power Automate premium licences for user of per flow then this solution is much more effective to run in Azure Logic Apps.

Pricing of the running in Azure Logic Apps

It could cost using Logic Apps around 13 pence (British Pounds) per day that’s running on the hour of every hour.

So would be 55 pence (British Pounds) per day to run every 15 minutes instead of hourly.

Add the Solution to Azure Logic Apps in your environment

I will now outline the steps to install the solution in Logic Apps in your environment. If you havent previously installed my solution or you have never seen it before you may want to refer to my previous blog for installation instructions. There is still a Power Automate flow required to install the SharePoint list inc view formatting to your SharePoint environment.

I have done all the work to migrate the solution to Logic apps so you can now just install from a template using the instructions below.

• Create an Azure Logic App in your Azure subscription (can be named anything i.e LondonTubeStatus)

• Select to create a “Blank Logic App” from the list of Logic App templates.

• IMPORTANT STEP: In Logic Apps Designer add one trigger SharePoint – When an item is created. Connect to your site and choose the Tube Status list (you may need to authenticate to your M365 tenant). This is an important step as it will help you retrieve the sharepointonline connection details which are unique to your environment.

• Once authenticated using SharePoint go to Code View on the top Logic Apps Designer bar.

• Make a copy of the the current parameters block of JSON from Code View and paste it somewhere safe into a notepad. i.e. see example of below of a parameters block to copy (it will have your Azure subscription ID, resource group and azure location):

• Now go to my solution in GitHub and open LondonTubes-TFLStatusAPI.json and copy all of the JSON to your clipboard.
• Go back to the Logic App Code View – Select All of the JSON there already then Paste the JSON from previous step to overwrite the existing JSON.
• Retrieve the parameters block from your Notepad and overwrite the parameters block in Code View (this is adding your SharePoint connection details to the Logic App json).

• Now go to the Logic Apps Designer view and three SharePoint actions will need to be updated. Update the actions to point to your site and choose the Tube Status list.

• Save the Logic App and run it.
• Head over to your list and you’ll see the Tube Statuses being updated using the view format! By default, this list will receive updates every 15 minutes.

Summary

What started off as a COVID Lockdown side project to learn SharePoint view formatting after moving continents grew into build a live interactive London Underground Status board including a demo to the PnP Community.

Now the solution can be run in Azure Logic Apps and I have documented my learnings and provided instructions on how to install.

Go ahead and install of your intranet today or use the idea to create something similar for your local transportation system.