Flow - Format Number advanced tips and tricks

I put together a video to celebrate the new update in Microsoft Flow (Power Automate) and Logic Apps - formatNumber()

The video explains 3 tips and 2 gotchas.

• Tip 1: Use action in prod right now

• Tip 2: Use ### and 000 patterns in formatNumber() expressions

• Gotcha 2.1: May be don't use of $### - use $##0

• Gotcha 2.2: May be don't use $ - use C or C2

• Tip 3: Use formatNumber in collections with Select/Create HTML

If you want the clipboard paste of the Format Number action to use, use this.

{
    "id": "c1fa8a84-e2c0-4be0-823e-3a45-1e6834aa",
    "brandColor": "#a098e6",
    "connectionReferences": {},
    "icon": "https://psuxaustralia.azureedge.net/Content/Images/DesignerOperations/numberfunctions.png",
    "isTrigger": false,
    "operationName": "Format_number",
    "operationDefinition": {
        "type": "Expression",
        "kind": "FormatNumber",
        "inputs": {
            "number": 12345,
            "format": "C4"
        },
        "runAfter": {
            "Compose": [
                "Succeeded"
            ]
        }
    }
}

Power Platform Governance with Power Clarity

Clear the FOG - let there be Clarity

Some asked me recently how does my SaaS product Power Clarity work with Microsoft’s Center of Excellence kit.

We can look at this from two directions:

• Turnkey Governance with Power Clarity (running without CoE)

• Enhanced Governance with Power Clarity (running with CoE)

• ALM with Power Clarity

Center of Excellence kit (CoE)

I’m a big fan of CoE built by Microsoft and the community. I love that Microsoft acknowledges there is a need for governance, and the community rises to build tools to help. CoE starter kit is a collection of resources that will be a template to create your own Center of Excellence kit.

Power Clarity is a turnkey solution - you grant your credentials, it starts scanning. You get dashboards, forms and reports and workflows. I want to stress that Power Clarity is an enhanced version of Flow Studio - something I’ve been building since 2018, so it was developed in parallel to CoE, there are similarities, which leads to how they can be used together.

Here’s a list of roadmap on our first year (2020).

Turnkey Governance with Power Clarity (running without CoE)

If you are a company using the power platform and has not developed or deployed CoE - then Power Clarity can be used as a turnkey starting point.

If you don’t know where to start, or how to build one, we will get you started quickly to help you uncover the platform.

If you don’t have a power governance solution, and need one in 15 minutes, we are your… service.

• No install - we manage it and keep improving it.
  Power Clarity is a SaaS service so the constant improvements are baked into the subscription. (In my opinion, we aren’t very expensive either).

• Add one or many concurrent scanning accounts

• Use accounts that are not administrator accounts - Power Clarity can scan with or without admin permissions, or operating in mix mode between different environments. If you have environment admin - you can see everything. If you only has environment maker - you can still monitor assets that you own.

• Power Clarity performs hourly scanning, because it’s an incremental scanner and very light on API access. We understand at high API throughput - both source and storage can get upset. Power Clarity incremental scanning of a tenant of 1000 (flows/apps) is typically less than 3 minutes.

• Power Clarity does not require premium accounts

Enhanced Governance with Power Clarity

If you have CoE deployed and already has CDS entities - then Power Clarity can be used to greatly enhance that data collection exercise.

You no longer need to worry about ‘how the data is collected’, but can keep customizing your reporting and utilization of that data to drive your governance outcomes.
In fact, we give you more events, signals, and policy actions you can perform. We want you to have clarity.

• Writes to Azure Storage Table and CDS Entities (use Power Clarity to collect data instead of CoE’s admin sync flows)

  (Note: CDS write requires the scanning account to have Premium license for CDS access)

• Deep API reads into Flows - runs, run actions, run errors and remediation

• Recall run metadata beyond 30 days

• When events happen - you can get an email report, or get a webhook call so you can handle it yourself. Power Clarity provides complete tenant flow error reporting without try/catch pattern. Since it’s inception, Power Clarity is a Flow monitoring service as well as a Power Platform governance service.

• Advanced detection of cross tenant resources

• Advanced detection of flows utilizing premium licenses

• Advanced count of flows utilizing API rate limits

• White and black list filtering of resources (domains, URLs) and connectors

• Clash detection of flows and DLP policies

• Clash detection of flows using deprecated actions

Application Lifecycle Management with Power Clarity

Because Power Clarity maintains offline granted access, it can perform advanced active actions to the Power Platform on your behalf, actions that don’t currently have an connector.

• Flow Studio’s migrate feature (which lets us copy, switch connections and deploy flows), can be invoked from Clarity API call

• Call Power Platform management cmdlets via Power Clarity API.

• Continuous backup of flow definitions and Power Apps snapshots

• Power Clarity provides an advanced GraphQL view of the entire data set available within its stores.

• Read and reports on managed and unmanaged resources in Solutions

• History of changes across the entity tracking

See it for yourself

Send me an email john.liu at flowstudio.app to get started.

Running Serverless Apollo GraphQL on AzureFunctions with cheap Azure Blob Table database(s)

Merry Christmas holidays and happy new years.

This is a bit of a holiday reading, I wanted to start by give you the sense of what I’ve been tinkering as an experiment, and it looks increasingly like this is going into production before 2019 ends.

---

Plan

• Have

  • I have a bunch of Azure storage Tables (about 70k rows of cheap database - at $1 / month storage) - they are spread out across several containers but for the basic purposes - I think of them as independent tables that lack proper relationships defined between them.
    I think that’s appropriate to describe most no-sql databases.

  • Azure Functions based CRUD REST APIs wraps around the Azure Blob Table - problem - no good caching or relationship mechanism. I wasn’t a big fan of keep rolling out CRUD REST endpoints, feeling that I should try find a different way.

• Idea

  • Run a GraphQL server on Azure Functions

• Learn:

  • If you want to run GraphQL on Azure Functions - there’s a dotnet way and there’s a NodeJS way

  • The dotnet version

    • https://www.tpeczek.com/2019/05/serverless-graphql-with-azure-functions.html

    • https://medium.com/@seekdavidlee/eklee-azure-functions-graphql-a-serverless-graphql-implementation-on-azure-484611c3680b

    • I’ve personally made a stack decision to stick to NodeJS/TypeScript/JavaScript, so I won’t be exploring the dotnet server.

  • The NodeJS version

    • A quick look around will send you to Apollo Server which has done 3 videos with Microsoft Azure/Channel9 on getting started, running on AppService, and running on Azure Functions (on consumption).

    • Part 1 https://www.youtube.com/watch?v=7R33hGFV4f0

    • Part 2 https://www.youtube.com/watch?v=Mt4bBpOdwyE

    • Part 3 https://www.youtube.com/watch?v=unUeFApHeT0

• Write

  • Steps after Apollo + AzureFunctions 3-part videos.

  • Resolving GraphQL entities to Azure Blob Table

  • Resolving Relationships

  • Securing with Azure AD

  • Deploying to AzureFunctions

  • Reroute via Azure Function proxy

• Future

  • Switch to Apollo RESTDataSource

  • Apollo Client with Angular and Observable bindings

  • Apollo Server with Redis cache

Learn

After the three videos, particularly the first and the third - to understand GraphQL server, and hosting it on AzureFunctions, you’ll end up with a working graphql server running on localhost showing you books array from memory. Getting here after watching three videos was extremely fast ~30minutes. Very surprising how much shortcut we took to get here this fast.

View fullsize

const typeDefs = gql`
    type Book {
        title: String
        author: String
    }
    type Query {
        books: [Book]
    }
`;

const resolvers = {
    Query: {
        books: () => books,
    },
};

let books = [
    {
        title: "A",
        author: "Mark B"
    },
    {
        title: "C",
        author: "John D"
    }
];

const playgroundSettings: any = {
    "schema.polling.enable": false
};
const server = new ApolloServer({ 
    typeDefs, 
    resolvers, 
    playground: {
        settings: playgroundSettings
    },
    debug: false 
});
export default server.createHandler();

A really fancy thing Apollo did was to put the graphql endpoint on POST, and run the playground test environment on GET. Pointing a browser to the same endpoint will show the playground.

Here is the playground running the sample books query. Debug works wonderfully running in localhost.

By default, the playground will do continuous polling on the endpoint. That’s going to keep the Function awake and incur a lot of costs. It might also keep bumping into any local debug breakpoints. So let’s turn that off. I also set debug to false.

View fullsize

Write - Resolving Azure Table

Next, we need to extend from here. My original AzureFunctions has REST endpoints that calls Azure storage Tables via the Azure.Storage SDK. So bring those in.

import { ApolloServer, gql } from "apollo-server-azure-functions"; 
import * as azure from "azure-storage";

And start to switch out the code

const typeDefs = gql`
    type Flow {
        RowKey: String
        name: String
        displayName: String
        critical: Boolean
        environmentName: String
        state: String
    }
    type Query {
        flows: [Flow]
    }
`;

const resolvers = {
    Query: {
        //books: () => books,
        flows: (parent,args,context) => azureTableQuery('flows'),
    }
};

const tableService = azure.createTableService();
const partition = "xxxx-partition-key";

const azureTableQuery = (table) => {
    let pRows = new Promise((resolve, reject)=>{

        let query = new azure.TableQuery().where('PartitionKey eq ?', partition);
        tableService.queryEntities(table, query, null, (error, result, response)=>{
            if(error){
                reject(error);
            }
            else {
                resolve(response.body['value']);
            }
        });
    });
    return pRows;
}

Notes: Azure.Storage does old style callbacks, so I’m wrapping them in a Promise that I can resolve. When I resolve - I want the raw JSON from the service, not the ‘tweaked’ JSON in result (it does weird things to your JSON entities, that’s a blog for another time).

A really nice thing about Apollo Server resolve function is that it knows how to handle promises naturally. So this tweak basically is me saying - hey, go fetch from Azure Storage Table from the ‘flows’ container.

To run this locally, we’ll need to store an Azure storage key in local.settings.json

{
  "IsEncrypted": false,
  "Values": {
    "AzureWebJobsStorage": "",
    "FUNCTIONS_WORKER_RUNTIME": "node",
    "MyStore": "DefaultEndpointsProtocol=https;AccountName=mystore;AccountKey=FOOxxx"
  }
}

We can reference this like so:

const tableService = azure.createTableService(process.env["MyStore"]);

View fullsize

Write - Resolving relationships

const azureTableRetrieve = (table, key) => {
    return new Promise((resolve, reject)=>{
        tableService.retrieveEntity(table, partition, key, null,
        (error, result, response)=>{
            if(error){
                reject(error);
            }
            else {
                let entity: any = response.body;
                resolve(entity);
            }
        });
    });
}

const typeDefs = gql`
    type Environment {
        RowKey: String
        name: String
        displayName: String
        isDefault: Boolean
    }
    type Flow {
        RowKey: String
        id: String
        name: String
        displayName: String
        critical: Boolean

        environmentName: String
        ref_environment: Environment
        state: String
    }
}`;

const resolvers = {
    Query: {
        environment: (parent,{name},context) => azureTableRetrieve('environments', name),
        flows: (parent,args,context) => azureTableQuery('flows'),
        flow: (parent,{name},context) => azureBlobTableRetrieve('flows', name)
    },
    Flow: {
        ref_environment: (parent,args,context) =>
          azureTableRetrieve('environments', parent.environmentName),
    }
};

View fullsize

Notes:

I call “ref_” because I’m terrible at making property names. Lots of fields like “environmentName” or “environment” are already taken. It got too hard picking new names, I decided to call all entity references ref_

Deploy to Azure Functions

Need to deploy two parts:

View fullsize

View fullsize

Friends don’t let friends right click to deploy unless another friend put it in the right click menu. In that case, please don’t make me choose my friends.

Also, right click and upload local settings file to send our MyStore setting into Azure functions.

That’s two right clicks and deployment done!

Connect Azure Function proxy

My existing API routes are already covered with Azure Functions proxy. So I added a new route that takes graphql over to this separate Azure Functions. I also bury the functionkey in the route so users can’t see it.

Future

There’s a bunch more ideas for the future.

• Explore federated authentication and user level security.

• Figure out why Azure Storage SDK next generation doesn’t do Tables

• Switch from using SDK to Azure Storage REST and use Apollo’s excellent RESTDataSource implementation instead (it already implemented HTTP caching, pagination, top, etc)

• implement mutations, especially e-tag handling.

• implement server side Redis cache

• Implement Apollo Client to fetch data for my existing Angular front-end

• Implement client side cache and observable pattern

Let me know if you find this interesting and want to follow along. I wrote this mostly as documentation for what I’m doing and the incremental changes I’m making as I go.

Redirecting SharePoint list's NewFormUrl and EditFormUrl to Power Apps

In this scenario, we have a Power Apps app that takes a query param() “AssetID” and determines whether the app should create a new item or update an existing item.

We want to change the SharePoint default New or Edit items on a modern SharePoint list to go to our App. The app is a landscape canvas application and not an integrated portrait application (so we can’t use SharePoint form setting to switch this).

Because SharePoint’s New and Edit forms must be server relative pages, we can create a redirect page.

Steps

1. Use Param in Power Apps

2. Create a simple redirect page

3. Change SharePoint’s NewFormUrl and EditFormUrl to this redirect page

4. Test: Redirect to PowerApps

5. References

Use Param in Power Apps

https://powerapps.microsoft.com/en-us/blog/powerapps-deep-linking/

Create a simple redirect page

Create a redirect ASPX (HTML) page and upload it into the SitePage library. I saved this as "AssetEditForm.aspx"

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<%@ Page Language="C#" %>
<%@ Register tagprefix="SharePoint" namespace="Microsoft.SharePoint.WebControls" assembly="Microsoft.SharePoint, Version=15.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %>
<html dir="ltr" xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<meta name="WebPartPageExpansion" content="full" />
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Asset Form</title>
<meta http-equiv="X-UA-Compatible" content="IE=10" />
<SharePoint:CssRegistration Name="default" runat="server"/>
<script type="text/javascript">

function getQueryVariable(variable) {
    var query = window.location.search.substring(1);
    var vars = query.split('&');
    for (var i = 0; i < vars.length; i++) {
        var pair = vars[i].split('=');
        if (decodeURIComponent(pair[0]) == variable) {
            return decodeURIComponent(pair[1]);
        }
    }
//    console.log('Query variable %s not found', variable);
}
var play = "https://apps.powerapps.com/play/e3fdef18-2be5-4d25-ba04-3edf6411c0aa?tenantId=afa78dff-85bb-46f3-b036-b43bcf79c497";
var id = getQueryVariable("ID");
if (id) {
    window.location.replace(play+"&AssetID="+id);
}
else {
    window.location.replace(play);
}
</script>
</head>
<body>
<form id="form1" runat="server">
</form>
</body>
</html>

Change SharePoint’s NewFormUrl and EditFormUrl with PnP-PowerShell

Connect-PnPOnline -url https://johnliu365.sharepoint.com/
$list = Get-PnPList "Asset"
$list.ContentTypes[0]

# assuming list content type 0 == Item
# otherwise, you need to set this on the correct list content type

$list.ContentTypes[0].EditFormUrl = "SitePages/AssetEditForm.aspx"
$list.ContentTypes[0].NewFormUrl = "SitePages/AssetEditForm.aspx"
$list.ContentTypes[0].Update($false)

# no update child content types
# you may have to set $true if you have content types in your list

$context = Get-PnPContext
$context.ExecuteQuery()

Redirecting Magic

This will change the “New”, “Edit” behaviour on the SharePoint modern or classic list. Also, this will work in the edit menu. So if you want to redirect your users to the new Power Apps experience, this is one way to change the link everywhere.

Reference Reading

Please also read April Dunnam’s two part series on how to customize SharePoint via modern ListView JSON

• https://www.sharepointsiren.com/2019/03/open-powerapp-sharepoint-formatting/

• https://www.sharepointsiren.com/2019/05/opening-powerapp-using-sharepoint-column-formatting-part-2/
  
  

True Governance of the Power Platform

It’s very early Sunday morning and I’ve just returned from an red-eye flight home from a week spent in Perth with Paul, Ashlee and Terrie Culmsee, meeting their many clients, participating in Perth App in a Day, and talking about PowerApps, Flow and Power Platform Governance.

It is testament to Paul’s nurturing of his clients that every company that I talk to on this trip have the same forward thinking mentality.

Many of the companies I had talked to online, or here in Sydney also shares these same traits. They want to implement proper governance of the power platform.

You might be on the same journey - I wish you the best, and I want to support you in this quest.

True Governance is about creating a partnership

The scenario is the same, over and over and over. Business wants agility, they want applications that traditional IT struggles to deliver.

IT already struggle with supporting existing applications created by Business - they worry this is yet another thing they need to support - these “citizen developed apps” will fall into their laps, not following best practices, not documented, and yet highly business critical.

Power Platform is an opportunity, to ride this wave and fix the partnership problem in your business.

True leadership, are the managers seizing this unique opportunity to (re)engage Business and IT

Because traditional IT has become the department of “No” - modern business units and IT doesn’t really want to talk. And in the last few years, many businesses rode the Microsoft Power Platform submarine to get applications developed under the radar to reach critical mass first. Always easier to ask for forgiveness than blessing.

In this paragraph, I want to speak to you - the business. You must understand, there is an virtual ceiling you are about to hit. Let me explain.

See - your empowered citizen developers are learning as they go, their first apps may not be that fantastic, while they solve critical business problems - there may be bugs and we see there will always be more incremental improvements needed.

As the number of Power applications you create increases, your limited number of citizen developer will reach capacity. Without being able to properly support these apps, they can’t make more apps.

You still need more apps. But your citizen devs’ hands are now full. Worse, what happens if they take a role elsewhere? Who can support these apps?

The correct answer is, business should take a cost code to IT and ask IT to support these apps. IT should be paid to build internal capacity to understand, administer, and govern the Power Platform.

Ideally, someone that understands the Power Platform should lead this team and effort. They are the bridge that talks to Business and IT. The ultimate navigator that will unite the business and IT.

Look across to any successful enterprises now scaling to hundreds and thousands of Power Apps and Flows. This pattern is the same. Over and over.

IT must realize that part of governance is this great opportunity - go talk to your business. Find the super maker that is helping with adoption, and able to put together business plans that will include a maintenance support fee to IT. Stop being “free support” (see my previous blog post) and start being part of value generation in your business.

An emerging trend is that the internal super Power Platform maker joins the IT side of the business to be that navigator. Governance and adoption tools (I have a list near the end) support the quest of this navigator. Business pays IT to support and maintain more Power Apps and Flows.

Do you think this is not possible? This is a fairy tale? I saw these relationships, these bridges forming everywhere. Is this not your experience? Have a look around.

If you are an IT manager, rather than look at the Power Platform with fear, close your eyes, and reopen them and see it as the biggest opportunity you have ever had in the last decade.

A low code platform the Business wants, with tools and reports for IT to properly function and support this platform. Don’t squander this and drive your business away to some real Shadow IT platform and now you can’t even monitor that.

Governance tools are available - but true governance is People willing to talk

Business and IT not talking to each other is a problem. Here, is an opportunity to solve that. Take your off-shored IT back in-house. Be part of value generation.

If you want partnerships, I will help. There are many in the community that want to help you. Many Power platform champions are literally living this reality and working in this role. Borrow their job template and make it yours, initate the conversation - take it to your manager in business or in IT and say, hey, let’s do this.

I wanted to list a series of tools available. Some has (custom) tag and would need self assembly.

• (Out of box) Power Platform Admin portal

  • PowerApps admin portal

  • Flow admin portal

  • Environments, permissions, DLP policies

• (Custom) Microsoft’s PowerApps administration tool

• (Third party) Power Studio (freemium)

• (Custom) Build custom dashboard with Power Platform connectors

• (Custom) PowerShell cmdlets

• (Custom) Center of Excellence starter kit

• (Third party) Power Clarity

• (Guidance) Power Platform Adoption Framework

• (Guidance) Power Platform learning and tutorial list (many governance links)

Build tools, build bridges

Disclaimer - I build Power Studio and Power Clarity. The point is, there are tools to provide governance on this platform. This is FAR better than choosing a different tool that IT has no means of providing any meaningful governance.

It would be somewhat easy for me to run purely on fear and say buy “my tools” - they will lock everything down and give you governance. Build a wall. You need a wall. Buy my wall.

If some snake oil salesman comes to you and say that, I want you to understand, real governance is not a wall. There is already enough of a chasm between business and IT.

Real governance is an opportunity to take this platform and transform your apps, yourself, your business and the relationships in your business. It’s hard, potentially very rewarding work.

What do you build your governance for?

Today, governance is a word thrown around without a care of what it actually means. We need to do governance - for what? No that’s all, we just need to do governance.

I wanted to share an article from Paul Culmsee. Those that sees governance as the goal, and not the means to an end, there’s something we all have to let go. Why do we implement governance? What outcome do we desire?

https://medium.com/@paulculmsee/how-to-doom-office365-governance-over-and-over-again-9feede8ef14a

If you have never thought about why you are implementing governance, and you don’t know what your end goal is, then take this quest:

We build governance, because our IT and Business must form a partnership. Our entire governance strategy is to make that partnership work.

The choices are yours, but I think you shouldn’t take forever to think about them

Do the right thing. Chances like this don’t come that often. Don’t look back and think, ah, I didn’t take that opportunity and initiate that conversation, and a decade later, I’m still only a cost center, still fighting shadow IT, and the board still wants to offshore my team.

That’d be unfortunate.