Power Automate: Advanced Flow building

Ben Prins Power Platform

Power Automate is one of my favorite tools from the Power Platform. It is extremely versatile and can be used to automate tasks between online services and automate processes ranging from simple to highly complex. In this post, I will share with you 3 advanced expressions I have used recently on my project. One part of the project is to convert XML data to data we can store in the dataverse.

Convert XML to JSON for easy access

For a project I needed to read multiple XML files with millions of rows and store data from the files into the dataverse. XML is harder to use in a Flow then JSON, so with a simple expression I transformed the XML to JSON.

  • Add a compose action with the name XML to JSON with the following code.
json(xml(variables('XML')))
  • Change the variable(‘XML’) to your XML content or store your XML content in that variable.
  • Add a parse json action set the Content to the output of the XML to JSON compose.
  • Add/create the JSON schema.

Using path in JSON

In most cases when you need to save data from JSON you can use the dynamic content to find it.
But sometimes you are looking for a field name that is not unique. In my case I needed a field called country related to the company. But the country field was used multiple times for various blocks. You can select the correct country by using the path (location) of the field in an expression.

  • Select the JSON through the dynamic content.
  • Copy the code from the dynamic content to the Expression.
  • Add the path add the end of the code.
  • I added .company.country to select the country of the company.
body('Parse_JSON').company.country

Dataverse lookup field

Lookup fields in Dataverse are really useful, but when you select them through the dynamic content the value will be the id not the display value. If the data must be readable for users, you can use the following steps to select the display value.

  • Add a compose to the flow.
  • Select the lookup field in the Inputs through the dynamic content.
  • Copy the code from the dynamic content to the Expression.
  • Your output looks something like this.
outputs('company')?['body/rc_countrycode']}
  • Add the @OData.Community.Display.V1.FormattedValue’ after rc_countrycode (your field name will be different).
  • The end results looks like this.
outputs('company')?['body/rc_countrycode@OData.Community.Display.V1.FormattedValue']

Power Automate: Find the current environment

Ben Prins Power Platform

When working with an ATOP setup you might need to know in which environment the Power Automate flow is running. For my solution I needed to know the environment because each environment uses a different Gateway and database credentials. In this post, I will share with you how to find the environment GUID and name.

Creating the flow

  • Create a flow and use the trigger Manually trigger a flow.
  • Add the action Get Environments under Power Apps for Makers
  • Add the Compose action and use the Workflow() expression, to get the current instance of the flow.
  • Parse the Output in a Parse JSON action.
  • Initialize a variable with the name environment as a string.
  • The Value is the EnvironmentName form the Parse JSON output.
  • Now you have the GUID of the current Environment in the variable.
  • To find the name of the current environment we need to go through the results of the Get Environments action.
  • Add a Condition control action and check if the environment variable is equal to the name from the Get Environments actions.
  • This will automatically add an Apply to each, this is because the Get Environments action might return more than one environment.
  • Add a Set variable action in the If yes section and set the variable environment to displayName.
  • Now you have the Name of the current Environment in the variable.
  • The final step is to add a Switch control and switch based on the name of the current environment.
  • The flow will now look like this.

Power Automate: 6 flow building tips

Ben Prins Power Platform

Power Automate is one of my favorite tools from the Power Platform. It is extremely versatile and can be used to automate tasks between online services and automate processes ranging from simple to highly complex. In this post, I will share with you 6 tips and tricks when building flows in Power Automate.

Flow templates

A good way to get started with Power Automate is to use a template. Microsoft created a huge library of templates to choose from. You can browse by category to find your scenario, and then follow the steps in the template to create a flow from the template. You can also us the templates to figure out how to setup certain actions.

Equal to empty

Over the years many colleagues have asked me; how do I check if a value is empty? You can do this with the null expression! Note that sometimes you need to place the null between ‘ ‘.

Use parallel branches

Most flow builders forget to use the parallel branches. With parallel branches you can have two or more actions that run at the same time, after which the flow will only proceed once all parallel steps have completed. Parallel branches can be very useful for approval flows. For example, you have a request that needs to be approved by both IT and Sales, but the approval doesn’t need to be in a particular order. To save time you can run the approvals parallel.

Use scopes

We can use the action scope to group actions to make the flow easier to read. There is however another great use for them. The scope action encapsulates a block of actions and inherit the last terminal status (Succeeded, Failed, Cancelled) of actions inside. This in combination with the Configure run after setting we can create a try and catch logic in our flows. In this example the second scope (catch) only runs if the first scope (Try) failed.

  • Create a scope with some actions
  • Create a second scope after the first scope
  • Set the Configure run after setting of the second scope to has failed, is skipped and has timed out.
  • In this scenario the catch scope will only run if the Try scoped failed.
  • The flow will look like this.

Add redundant owners

If you have a flow that is used by your entire team, make sure you add a couple of co-owners. Then you will not be bothered during your vacation when the flow breaks. If you keep adding the same colleaguesas co-owners you can consider creating a security group and add the security group to the flows. Also make sure you add the co-owners to all the resources required by the flow. For example, the shared mailed that is used by the flow.

Connections

Actions use by default the connection (if required) of the creator of the flow. This is not always the best way to setup the connections. For example, if your flow updates a list you might not want to see your name as the modifier. I recommend using a dedicated account (service account) for most shared flows. The added benefit of using a dedicated account is that the flow will keep working even if you change your job.

Power Automate: Creating and updating a document set in SharePoint

Ben Prins Power Platform

Many colleagues have asked me how to create or update document sets with Power Automate. I had never done this before so I told them to use a web service. From now on, I can point them to this blog post. The flow is created with the help of my colleague Jasper Voskuilen, because this web service is a bit more complicated to setup.

Creating the flow

  • Open Power Automate and create a new flow (automation).
  • I created a flow with the trigger, for a selected item.
  • In my situation I get all items from a list and then create a document set for each result.
  • Create the following variables with the related values.
  • This will make the flow more dynamic for later changes.
  • SiteURL as a string, fill in the Site URL.
  • ContentType as a string, fill in the content type id.
  • URLLibrary as a string, fill in the library name part of the ULR.
    For this example it is: Library
https://domein.sharepoint.com/sites/ExampleSite/Library/Forms/allitems.aspx
  • TitleLibrary as a string, fill in the title (name) of the library.
  • WebServiceURLBibliotheek as a string, fill in the Library URL used by the web service.
  • You can find the web service url by using the following URL
https://domein.sharepoint.com/sites/ExampleSite/_vti_bin/listdata.svc/
  • Add the action Send an HTTP request to SharePoint
  • Use the varibles to configure the web service.
  • The name of the document set needs to be unique. I used a value from my list item, your situation might be different.
  • If required you can update the newly created document set.
  • Add the action Parse JSON and parse the Body of the Create Document set HTTP request.
  • Add the action Send an HTTP request to SharePoint
  • Use the variables to configure the web service.
  • The properties you need to set might be different than mine. I used values from my list item but your situation might be different.

PowerApp Search and Filters

Ben Prins Power Platform

In PowerApps we often show lots of information and a good PowerApp will provide the user with the ability to find the relevant data quickly. We can do this by providing the user with search and filter capabilities. Therefore it not a surprise to me that I get many questions about searching in PowerApps. In this blog I will show various examples of search and filter solutions. I created a small PowerApp to support all the examples.

Small PowerApp

  • Create a SharePoint list called FAQ with the columns Title and Priority as a single line of text.
  • Create a Canvas PowerApp.
  • Connect the PowerApp to the FAQ list.
  • Add a gallery and connect it to the FAQ list.
  • Create a dropdown control for the filter.
  • Create a text input control for the search box.
  • Create the PrioFilterOptions on the OnStart of the Home_Screen. 
    ClearCollect(PrioFilterOptions, "", "1", "2","3")
  • Connect the PrioFilterOptions to the dropdown control.
  • Add the following items to the FAQ list.
    • Title: Question 1, Priority: 1
    • Title: Question 2, Priority: 2
    • Title: Question 3, Priority: 3

Filter using contains

The most frequently  asked question is; How can I search using contains? It’s possible to search like this by using the in operator. In all my examples I will be using the in operator.

Filter( Table, value in Field )

Filter with a search box

A search box is a text input control, we can use this control as a search box.

Filter(FAQ, Home_Search_Inputbox_SearchBox.Text in Title)

Filter with a dropdown

Filter(FAQ, Home_Search_DropDown_FilterPrio.Selected.Value in Priority)

Filter with a searchbox and a dropdown

This example has a little issue, after selecting a dropdown value you can no longer filter only by using the search box. If you select the empty value then the filter will use that as a filter value.

Filter(FAQ, Home_Search_DropDown_FilterPrio.Selected.Value in Priority && Home_Search_Inputbox_SearchBox.Text in Title)

Filter with a searchbox and a dropdown (when not empty)

In this example the user is able to ‘deselect’ the chosen option from the dropdown. I recommend always using this example when using a dropdown filter.

If(IsBlank(Home_Search_DropDown_FilterPrio.Selected.Value),
Filter(FAQ, Home_Search_Inputbox_SearchBox.Text in Title),
Filter(FAQ, Home_Search_DropDown_FilterPrio.Selected.Value in Priority && Home_Search_Inputbox_SearchBox.Text in Title))

 

PowerApps: Use a SharePoint list for PowerApps Styling

Ben Prins Power Platform

When developing with PowerApps​​ we often want to store information in a SharePoint list and access the information in the PowerApp. The three main reasons are to store the styling code, settings or just for data storage. In this post I will explain how to connect a SharePoint list to PowerApp and use it for the styling of the PowerApp. You will have a central location to maintain the styling of one or multiple PowerApps.

Connect​​​​ to PowerApp

  1. Create a SharePoint list called PowerAppStyling ​with the following columns.
    • ​​Title (single line of text)
    • Setting (single line of text)
  2. Activate versioning on the SharePoint list.
  3. ​Create a PowerApp.
    • ​In my example I created a PowerApp from a SharePoint list.
  4. ​Click on the tab View followed by Data sources.
  5. Click on Add data source.
  6. Click on SharePoint (for me the last option).
  7. Select the correct site or provide the URL and Click on Go.
  8. Select the the list and click on Connect.
  9. The list is now connected and can be used in the PowerApp.

Use for SharePoint list for s​tyling

We need the styling as soon as the PowerApp starts, so we need to store the styling in a collection when the PowerApp launches. If you can avoid creating collection on startup do so. You want the PowerApp to laod as quickly as possible. ​In the SharePoint list add the required styling code. You can use the following example.

  1. Open the PowerApp.
  2. On the first screen, find the OnStart action.
  3. Create a collection called Collection_Styling to store the PowerAppStyling​ datasource.
  4. When accessing the styling code, always get it from the collection. This is faster than accessing the SharePoint list.
  5. Create a label called Label_Style_Example.
  6. Set the Text to This is my style.
  7. Set the font color of the label.
  8. Set the fill (background) color of the label.
  9. Set the border color of the label.
  10. Set the border color of the label.
  11. The label will now look all black because the Collection_Styling is still empty, the PowerApp has not been started yet.
  12. Run the PowerApp and see the result, sometimes you need to restart the PowerApp multiple times.