Research: Power Automate comments are stored in Dataverse

/

Out of curiosity, and because I think listing outstanding comments might be a product feature in Flow Studio, I did some research, experimenting with REST query and general exploring on a late Friday night. This was done on Twitter, a bunch of people chipped in but also we ended up with pictures all over the place. This blog post is to collect everything in one place as a reference.

The record is stored as a top level Container (with an artifactid)

Then top level records (kind=Threads) with Container(commentid = containerid)
Then records (kind=Reply)

We can query Dataverse to get the rows back. Here’s how to do it in one request.


We can query Dataverse comments from WITHIN the flow about the current flow (via workflow().name expression)

I think this fulfills some sort of inception criteria

This also qualifies this research to be #FlowNinja hack 125

Here is a Reply record.

Comments are tied to an Anchor - which is an action within the Flow.
The corresponding Flow has metadata.operationMetadataId created as a reference when this happens.

Comments can be resolved (state =1, Resolved) or deleted (statuscode = 2, Inactive).

I expect the same data structure design will be suitable for other Power Platform products, so I’m keen to see it.

Parse CSV through Code in Power Automate Custom Connection

/

I was inspired reading Alex Shlega and Hiroaki Nagao ’s posts on using code with custom connections. So I set out to give it a go and work on another common problem I have: Parse CSV

First a picture showing you how it works.

Give it text, the action returns array of arrays.

Microsoft’s docs are here.
Write code in a custom connector | Microsoft Docs

And particularly, I need to parse CSV without using additional libraries, and using only the existing libraries available here. I noted that we do have access to System.Text.RegularExpressions, so I started my planning there.

Because parsing CSV correctly is a problem best sorted via use of a tokenizer, I went looking for a regular expression pattern that treats each line as a series of tokens. There are many patterns, but I like this one that I found on stackoverflow the best for my needs. https://stackoverflow.com/a/48806378

Code

So the code takes all the content of the body and splits by line breaks, then the regular expression is run over every line using Matches (this method returns multiple matches giving us a MatchCollection of tokens). In each match, I look for Group[2] which is the value without quotes “ and “. But if failing that match, we take Group[1] value.
We do not take the Match.Value because that would include the comma.

/end of regular expression explanation.

We cast the matches back to array via Linq and then back to JArray and return that back to Flow.

public class Script : ScriptBase
{
    public override async Task<HttpResponseMessage> ExecuteAsync()
    {
        if (this.Context.OperationId == "csv")
        {
            return await this.HandleCSV().ConfigureAwait(false);
        }

        HttpResponseMessage response = new HttpResponseMessage(HttpStatusCode.BadRequest);
        response.Content = CreateJsonContent($"Unknown operation ID '{this.Context.OperationId}'");
        return response;
    }

    private async Task<HttpResponseMessage> HandleCSV()
    {
        var contentAsString = await this.Context.Request.Content.ReadAsStringAsync().ConfigureAwait(false);

        // (?:,|\n|^)("(?:(?:"")*[^"]*)*"|[^",\n]*|(?:\n|$))
        // https://stackoverflow.com/a/48806378

        var re = new Regex("(?!$)(\"((?:(?:\"\")*[^\"]*)*)\"|[^\",\r\n]*)(?:,|$)");
        var lines = Regex.Split(contentAsString, "\r\n|\r|\n");

        var result = new JArray(lines.Select(line=>{ 
            var matches = re.Matches(line);
            return new JArray(matches.Cast<Match>().Select(match => {
                return match.Groups[2].Success ?
                match.Groups[2].Value :
                match.Groups[1].Value;
            } ).ToArray());
        }).ToArray());

        var response = new HttpResponseMessage(HttpStatusCode.OK);
        response.Content = CreateJsonContent(result.ToString());
        return response;
    }
}

explain the regex and match groups

Swagger

This is the custom connection swagger YAML file.

swagger: '2.0'
info: {title: CustomCode, description: Custom Code, version: '1.0'}
host: johnliu.net
basePath: /
schemes: [https]
consumes: []
produces: []
paths:
  /Csv:
    post:
      responses:
        default:
          description: default
          schema:
            type: array
            items: {}
            description: Array
            title: Array
      summary: Parse CSV
      description: Parse CSV
      operationId: csv
      parameters:
      - name: value
        in: body
        required: true
        schema: {type: string, description: Text, title: value}
      x-ms-visibility: important
definitions: {}
parameters: {}
responses: {}
securityDefinitions: {}
security: []
tags: []

I want to add more parameters over time, and that will involve a tweak to the input parameters on the Swagger definition. But that’s probably a task for another day.

Links:

Write code in a custom connector | Microsoft Docs

C# code in Power Automate: let’s sort a string array? | It Ain't Boring (itaintboring.com)

Calculate Sum & Average in Power Automate using C# code in a custom connector - MoreBeerMorePower (hatenablog.com)

Tiny forward steps for me and Flow Studio

/
miriam-eh-kWWeA1DVQxY-unsplash (1).jpg

I’ve been taking little tiny steps forward, this is a good time to share what I’ve been up to.

Flow Studio

There are several Flow Studio updates in the works. v1.1.00 is coming out very soon, and there are several important updates:

  • The development build is on https://dev.flowstudio.app/

  • The major update are changes to the backend APIs that flow studio calls. I am really thankful for the extra time given for me to work on this.

  • In subscriptions link there is now a way to see and manage your own subscriptions, using the Stripe billing support page.

  • There are a number of UX fixes to improve performance and bug fixes since last update.

Please let me know if you have any issues with Flow Studio. Bug fixes are priority.

Branding and Product Offerings

I renamed from Flow Studio to Power Studio last year, and introduced a new product Power Clarity. This has created a lot of additional branding work that’s time consuming to manage. So I’ve decided to simplify everything.

Original Updated Notes
Power Studio Flow Studio
Flow Studio Flow Studio We are going back to our original name
Power Studio Free   Flow Studio Free The freemium offering
Power Studio Pro Flow Studio Pro US$20 Monthly / US$200 Yearly
Power Clarity Flow Studio Teams US$2000 Yearly
Power Clarity Flow Studio Enterprise   Enquire

Flow Studio Free is for everyone

  • quickly see all your own flows

  • manage them using a tool designed for bulk operations.

Flow Studio Pro is for power users who needs more operations

  • Editing Flow JSON

  • Migrate flows

  • Bulk cancellation

  • Export flow history

  • Restore Flow to earlier versions.

  • Administrators that wants to see all the flows in their environment

Flow Studio Teams is for teams to manage all the flows used by their team

  • See all your flows, across multiple accounts

  • Manage them in bulk

  • Continuous monitoring of flow runs

  • Continuous backups

Flow Studio Enterprise is for IT and compliance departments that wants to have full visibility to understand, manage and utilize all the flows used within an organization.

  • Licensing assistance

  • Backup, migrate flows between accounts

  • DLP policy assistance and violations

  • Continuous reports

  • Cross tenancy support


How are you doing, John?

I’m OK, thank you for reaching out. Some days are really good, other days I can’t do it and just take a break.

It is currently school holidays in Sydney and we are in a new lockdown, so stuck at home. Let me know if you want to chat, I can find time to chat (remotely is OK).

2021 - broken, I am missing you Lina Abidin

/

I told myself this eventually would happen, but I still am not prepared for it when it did. How can anyone be prepared for what happens next.

On March 25th, a couple of hours past midnight, the love of my life Lina passed away peacefully in her sleep.

We had been fighting cancer for the last 5 years, it really hurts that we couldn’t beat it, and she has to go so young. Lina barely made it past her birthday in February, she was 41. We met each other when we were 20s and she’s been grinning at me for the last 21 years.

Lina is someone I don’t write a lot about, but has always been the driving force of many things that I did (or rather, we did together). She understood what I do - she was the first to cheer for my MVP award (we went to celebrate with yummy food!), she took care of the kids, she took care of our business, she’s always got my back, she made sure I smile - she is someone that is always smiling.

P1010506.JPG

So now I have the near impossible task of raising our two young kids and I really don’t know how I’m going to do this alone.

Everywhere I look, I see her. I wish, I had more time to tell her how much I’m missing her.

20171022_132038.jpg
141752196_10158882493054220_7250287982114968951_o.jpg

In my January post I said I would probably take time off until April. I’m going to need more time. Right now the kids are in school holidays for two weeks, and I’m taking the time to care for them. As school resumes next semester, I’m going to see if I can ease myself back into work.

  • There’s work to do to update Flow Studio.

  • Have some ideas and plans for Clarity - but more importantly, I’m really keen to hear from friends and teams that needs automated flow monitoring. Let me know if that is you.

  • I also have consulting work to resume with a few clients.

Lots to do, but some days, I crash and can’t find the ability to pull myself together to work on anything.

In her final days, she’d grab my face to tell me:

Remember to smile, don’t bottle everything in your heart.

I don’t know how to smile anymore, I cry and wail, as someone whose heart is utterly broken does.

2021 - break and thrive

/

Hello, you.

This is a very personal post.

2020 was tough on everybody, and it looks like 2021 may eventually bring a little bit of reprieve. I only have a modest and simple wish:

kurt-cotoaga-9Nq1IUhhayg-unsplash.jpg


For an immediate, urgent and heavy family matter, I must take a really significant break to try and ensure me and my family survives 2021. For our young family, this is a time of immense personal pain and loss.

  • I must take a break to be with my family — at least until April, but may be many more months. There’s no hurrying this, I want to take a much time as we need.

  • My consulting clients are aware of what’s happening and are so supportive. I have some reduced work with them that I will fulfill.

  • Flow Studio / Power Studio will continue, in the short time there’s a bunch of additional updates in the preview dev build dev.flowstudio.app that needs to be validated and pushed over into production.

  • Power Clarity will continue, but in a much, much slower pace.

  • My writings on the blog or YouTube will be random. There are mornings when I can muster some energy to write, in code or in blogs. If you enjoy them and want to see more, you REALLY have to tell me because I need (and otherwise lack) energy to produce them.

  • I’m unlikely to respond to questions on Twitter or LinkedIn

What can you do for us?

  • If you use Power Automate - I would really appreciate it if you would have a look at powerstudio.app, either for yourself or tell people about it. If it’s helpful to you, consider subscribe to it. If it doesn’t help you - let me know what might push you over the line to be a fan.

  • If you are already one of our 5400+ users or fans of Power Studio and wants to see the future evolved version - check out powerclarity.app

  • If you like Power Apps - you really should check out my GamePad PCF component and tell people about it.

  • I’m keeping my circumstances personal, but a generic statements that has a time component like hope you’ll get through it quickly, or hope you recover soon can miss the mark because I want as much time as we can to still have together. A nicer thing to say is probably please survive as long as you can.

  • Please put it in your calendar to check in on me every month from now, may be beginning of March, or beginning of April. Future me probably need someone to check in on me.

  • Years in the future, when the pandemic is over, invite me to your home for a meal.

How are you feeling?

We are free falling. There’s no parachute. When we hit the ground, at least one of us won’t survive, but we may all fall apart. We are already falling apart.