Poweruser's blog

Blog about the Power Platform

Category: Microsoft Flow

Old School Blog: “Flow through my eyes” – Flow

Today we are going to write an old school standard blog. Not a short trick to solve a problem but my current opinion about flow, the good, what is useful and what can be improved.

The power of Flow

Every day I am amazed how Flow can solve a problem that used to require hours of encryption in no time. And this across different platforms! We hear our technical Microsoft 365 puffing over a connector that they have to encrypt for a combination of 2 platforms. Dynamics 365 CRM and Dynamics 365 Business Central are a good example of this… They used to be used to create a mapping, to compare code, to be in constant communication with each other,…

In short, a lot of work that the arrival of Flow will save them. An organization that has a Flow expert in house can easily save a lot of hours on development. Let’s not even talk about the combined power that Flow radiates together with PowerApps. Applications that used to be custom-made (for example a layer on Business Central, or an invoicing program, etc.) can now be made easy with PowerApps and Flow ensures that the data arrives at its destination. And this without or little knowledge of coding languages such as C++, Java, CSS, HTML, JQuery, Javascript,…


The accessibility of Flow for end users is also unseen. It seems very simple, with a few blocks to hang together, can I save my attachments from my received emails in SharePoint? YES, it is possible! The simple flows can be made by the end-user himself and it saves them a lot of work by figuring out which step (or even better: template) they need from the FlowStack. Making those little Flows, which make their lives a lot easier, can trigger them to switch to the bigger work.

What if I want to do this, can I do it this way? Simple questions that make the user think about bigger things. Without realizing it, they start to reason, see new things that are possible and that can be of great value to the company they work for. Employees are going to look at their day-to-day tasks in a different way, which will give them new insights into their job.

What is useful?

What is useful and what is not, this is a question I have every day. Is it necessary to provide a flow for every action? A good conversation with the Key Users can provide you with a lot of clarity about this. In Flemish, we have a good saying for this: “Is it worth the salt? This means that the effort we put into a flow will pay for itself. And then we have to look for alternative solutions. However, flow cannot solve everything either.

A good example of this is when a choice has to be made as to whether data is stored, processed, passed on or duplicated. We already have our data in place X, we want to copy it with flow to place Y, where we have an overview. After processing the data we can then save it from place Y to a fixed location at place Z. Then we have to see if everything can be done in place X with the necessary operations there. Flow is a miracle cure but don’t sprinkle it around too generously.


A flow that is less than 20 steps or actions long is readable. In the meantime, I have found some “hacks” that make building longer flows a lot easier. These are just a few of the hacks in a row.

Think about whether everything in an Apply to Each needs to be what you need for the rest of the flow. An Apply to Each makes the flow a lot less readable, especially when you are dealing with a nested Apply to Each loop. A solution can be to complete the necessary steps in the Apply to Each and then continue with the rest of the flow outside the loop.

Use variables! These can be declared at the beginning of the flow and you can reuse your entire flow. When you have completed a “Get Rows” or “List Records” action, the flow will automatically apply an Apply to Each. Write your variable with the value coming from the array. Then you can still use your value from the Apply to Each loop in the variable.

Just like with the Apply to Each loop, try to limit the number of steps in a condition statement. Certainly, a Condition in a Condition will cause your screen to navigate as if to see the entire flow.

Last but not least Copy – Paste. Will you use an action a lot? Click on the 3 dots of your action and choose “Copy to Clipboard”. Click on add a new step, find my clipboard and choose for your step that copied it. This saves a lot of time!

The minorities

Despite all my praise for the software that is Flow, I still have worse points. So your cursor always moves to the beginning of the field when you delete a dynamic field, even if there is text in the field. Sometimes a little curse word escaped from my mouth when I clicked next to a course by mistake and my formula that I spent 15 minutes building it (was complex) is gone! AAAAAARH but there’s nothing else to do but to start over, Flow do something about it?

Also seeing and not seeing connectors is something that can be commented on negatively. You start a flow with Common Data Service as a trigger, but then you can no longer select actions, come to flow really? Just switch to the Dynamics connector.

The biggest underlying force

But one thing cannot remain unsaid, or unwritten in this story. The greatest strength of all is his community. Do you have an issue or are you stuck developing your flow? No problem, ask your question to the community and you will be helped in no time. Flow is a powerful tool, which amazes me every day what the possibilities are. But I don’t think we’ve seen the end of the possibilities yet, this target will extend exponentially!

If you have any questions about Flow, or if you just want to share some knowledge, you can always send an email to info@svenpauwels.com. You can also write down your comments about Flow in the comment box!

Thank you for reading this blog and continue the good flow.

Update: Create a file in SharePoint with Base64string in Flow! – Flow

Dear readers of my blog, to close the week I will quickly post an experience I had this week. This week I received a request from a customer to upload an image on SharePoint by using PowerApps. There are plenty of blog posts on the internet about how to implement this. So I won’t explain the whole solution, but I would like to point out to you a curious thing that I have discovered.

The Problem

When we use a custom connector for Flow and PowerApps we could capture this Base64 by using the action ‘When an HTTP request is received’. In the past, it was enough to give this Base64 to the action ‘Create File’. However, this week I ran into an error message here.

The input you get without creating the object. Then the step automatically creates content-type: image/jpeg. Here I got an error message.

When we gave the Base64 string as the input to the action, the ‘File Content’ property was automatically assigned ‘application/octet-stream’ as Content-Type. As a result, my images were not visible in SharePoint. And this is where the search for a workable solution started.

The Solution

Thanks to a colleague we were able to find a solution to this problem internally. The solution is very simple, but not really obvious. That’s why I wanted to share this with you quickly.

The compose step where we’re going to declare the object.

The action now expects an object, instead of a Base64 string. We declare this object in a Compose action. Entering the Content-Type of the file wasn’t rocket science either. As we got this from the ‘ When an HTTP request is received’, we could get it from the body of the request. We gave this value in the ‘Compose’ action. The code looks like this:





Then we give the output of the compose step to the ‘Create File’ action.

Now we can see our images or documents in SharePoint! As a control, I’ll give you the output of the action after it’s been run. The output looks like this:

Note: It is important that the Content-Type is no longer equal to “application/octet-stream”.

Important! This way of working is accompanied by a custom connector that we wrote for PowerApps and Flow to forward this document. How to implement this can be seen in the blog below.


If you don’t use a custom connector for Flow and PowerApps, there is a hack I discovered this week. Our customer did not have premium licenses for Flow/PowerApps so I was allowed to look for an alternative. Thanks to this blog post I was able to avoid this: http://johnliu.net/blog/2019/4/flowninja-hack-87-lock-microsoft-flow-powerapps-trigger-to-upload-images-to-sharepoint-with-ease. Thumb up for this post, it helped me a lot.

I hope you have an answer now if you also run on this error message! Have a good weekend and see you at the next post!

Control Site Creation in SharePoint with the PowerPlatform. – Flow & PowerApps

Is there a need to give end-users the power to create a team/communication site on SharePoint? Keep everything under control with PowerApps and Flow. In this blog, I will work out a simple example, feel free to make this even more challenging by trying the extras that will be at the bottom of the page.

In order to work out this example, we need 3 technologies:
– PowerApps (as the central entry point for the variable data)
– Flow (creating the site + securing a widget growth of sites)
– SharePoint (site creation)


Go to www.powerapps.com and log in with your account. Then choose the option “Canvas App from Blank”. We choose this option because we are not going to use any data sources in this solution. Also, we will only use one page, with only a few elements. Automatically generating 3 pages would be a waste of time.

In PowerApps we make a simple PowerApp with a title, 3 labels, and 3 TextInputs. These will then give the end-users the possibility to enter the variables to feed the Flow.

Little tip:
Change the property “Mode” to MultiLine. This allows end-users to enter the description of the site easily and conveniently.


Before we can finish the PowerApp, we first have to assemble our Flow. To do this we go to https://emea.flow.microsoft.com/en-us/. Here we choose “Instant – from blank”, then we select the PowerApps trigger.

The steps

In the first step of our flow, we will start an approval. This approval will allow us to get an overview of the requests to create a site in SharePoint. The approval will warn us if the end-user wants to create a site. Then we can give our consent if the site can be created, or not. Approval also gives us the opportunity to give comments to the end-user, informing him why we don’t want to create the site.

The next step will be waiting for our answer. Since our response can be positive or negative, we’re going to put our response in a condition.

If we accept/refuse the request, we can keep the end-user informed by email.

As the last step in our flow, we are going to create a site. This is possible if you enter the parameters as shown in the image below.


Last but not least, we’ll stop one more action behind our button. We go to the “OnSelect” property of the button. Here we will feed our flow with the input fields of our PowerApp.

Once we have selected our flow in PowerApps, we can supplement it with our input fields. The fields that flow needs are the following:

  • ApproverToEmail: “”
  • ResultFollowUpToEmail: User().Email => This formula will put in the email of the user who is signed in
  • Startanapproval_Title: TextInputSite.Text
  • Startanapproval_Details: TextInputDescription.Text
  • CreateSite_Body: TextInputAlias.Text

The end

I hope this blog was helpful to you. The extra “challenges” I mentioned in the beginning I will list below:

  • A challenge can be to make PowerApp visually more attractive.
  • In addition, you can also look to do extra things with Flow. For example: expand the mail with extra information, add extra things to the SharePoint site via Flow (Document Libraries, Folders,…). There will certainly be posts about this on this blog.

Thanks for reading this blog, see you at the next post!

Need to add many folders to a Document Library? Consider using Flow! – Flow

Hello everybody, sorry for the long absence but I was on vacation. Here we are again with a short but easy to use tip for Flow users who work a lot with SharePoint. This tip will make your flow a lot shorter, because you don’t have to copy all the steps. The simple answer to this problem will be explained in this blogpost.

The Problem

We are asked to add 10 folders to a document library on a SharePoint site. You can use this tip for one-time use to adjust a SharePoint site, but you can also automate this to apply it to multiple sites.

The Solution

First, create a list of all folders who needs to be provided in the document library. We do this by using the action ‘Initialize variable’.

Then we provide a loop that will cover all the elements in the list. For each element a folder will be provided. Instead of the black square, you place the name of the document library. The flow will create the different folders in this location using the ‘Current Item’ which is given as ServerRelativeUrl in the Body of the POST HTTP request.

The Code in the Uri:


The Code in the Body:

“__metadata”: { “type”: ‘SP.Folder’ },

Now Flow will take care of the several folders in the document library. This was again a short tip but no less handy! Next week there will be a new blog post, stay tuned…

A quick tip, to end the week with: play with dates. – Flow

There are several options in Microsoft Flow to start manipulating a date. You have the functions adddays(), addmonths(),… and then also the actions Add Time and so on. However, these don’t always give you the desired result. So here’s a quick fix to get your desired date.

We’re going to divide the date in the flow first into day, month and year. After that, we will edit these components separately and in the last step, we will merge them into the new date.

The Flow

Step 1 – Initialize the date

Enter your date in the ‘Value’ place. The advantage of using a variable is that you only need to initialize it once. After that, you can use the same name for each step. The date can come from any data source: SQL, CDS, SharePoint,…

Step 2 – Modify the date parts

As you can see we do 3 operations on the date. One of each segment of the date. If you only want to change the month or the year this is also possible. Then you only need to use the Flow block that applies to you.

Formula Year

add(int(formatDateTime( variables(‘date’) ,’yyyy’)), number_to_change)

Formula Month

add(int(formatDateTime( variables(‘date’) ,’MM’)), number_to_change )

Formula Day

add(int(formatDateTime( variables(‘date’) ,’dd’)), number_to_change )

The number_to_change is the parameter you need to change in the formula. this will take care of the number of days, weeks or years that will be added to or subtracted from the date.

Step 3 – Recombine the date parts

In the penultimate step, we are going to overwrite the variable date. this is done by means of the action ‘Set Variable’. The outputs are the outputs of the previous 3 compose steps.

At the latest, we are going to convert the date to a UTC Format. In this example, it is set up according to the European standards but you can change this to your own interest.

Formula Compose

convertToUtc(variables(‘date’),’W. Europe Standard Time’,’dd/MM/yyyy HH:mm’)

So this was my first quick tip for Flow. Hopefully you will be able to apply it as well. See you next time!

SQL SERVER Connector, how to filter the date? – Flow

The Problem

Last week I was asked to retrieve a number of rows from SQL Server (on-prem). These rows have to create a site on SharePoint in real-time. That’s where we bump into our first problem: getting real-time rows from an SQL Server. Unfortunately, it is impossible to use this trigger for a SQL Server on-prem. As explained in this blog post: https://flow.microsoft.com/en-us/blog/introducing-triggers-in-the-sql-connector/. In consultation with the end-users, we have agreed on a certain period of time during which we will pick up the rows.

After solving the retrieval of the rows, we collide with the next problem. Making a date filter to pick up the rows that were made during the last period. It was my very first time I implemented a date filter in flow. After a while, I managed to solve the puzzle. I want to share my solution in this blog post because I don’t think it’s the easiest solution to figure out.

The Flow

Step 1 – The Interval (here 5min)

To avoid the problem with the trigger, we will use the ‘Recurrence’ trigger in combination with the ‘Get paste time’ action.
This way we get every 5 minutes, the time of 5 minutes ago. The data in the ‘Created on’-field will have to be greater than the calculated time.

Step 2 – Get the rows out of SQL Server

In this step, we are going to retrieve the rows from SQL Server. These must be filtered based on the syscreated field which has DateTime as a datatype.

It is obvious that we are going to use the ‘utcNow()‘ function? This function is included in Flow by default and provides us today’s date. But here is the catch, the formatting of both DateTime fields does not match. I used different formatting for the ‘utcNow()‘, but none of the results were positive.

So I decided to change tack. Instead of using the full DateTime as a filter, take a look at both parts of the field itself, the date and the time part. This is the solution that worked for me:

Filter Formula:

date(syscreated) ge date(@{utcNow()}) and time(syscreated) ge time(@{body('Get_past_time')}))

Note: syscreated is the DateTime field from the SQL Database.

Step 3 – Create a SharePoint Site

In this last step, we are going to create a site with the data we extracted from SQL Server. The body statement, provided with parameters will fill in the following information on SharePoint:

  • displayName: This is going to be the site and group name.
  • alias: This will provide the Url of the site (example test.sharepoint.com/sites/alias
  • isPublic: This parameter will set the group visibility to ‘Public’ (if isPublic=true) or ‘Private’ (if isPublic=false)
  • Description: This will be the site description.
Afbeeldingsresultaat voor first post ever

So this was my first blog post ever! I hope you enjoyed reading. I’ll see you later for a new post!

© 2020 Poweruser's blog

Theme by Anders NorenUp ↑