Power Automate / Dataverse: Get first result

Power Automate has so many options and possibilities by clicking through the user interface. But it can do even more with the help of expressions. I think I use the following two experssion the most, empty() and first(). Thats why I wanted to share them with you.

Get the first returned value

When looking for a record in the Dataverse, but the record id is unknown we use the action List Rows. This action always returns an array of values. This even happens when only one record is returned. If you try to use the returned value to for example update the found record, then Power Automate automatically generates an apply for each loop. This can be confusing when developing the flow, why would it loop through all the values when there is just one? With the first() expression we can prevent Power Automate to automatically generates an apply for each loop.

  • Add the Dataverse action list rows and configure it to find the unique record.
  • In my example I look for the unique record based on the unique order id.
  • When trying to update the found record the following Apply for each will be automatically generated.
  • The creation of the Apply for each can be prevent by using the expression first().
  • Remove the Apply for each and the Update a row action.
  • Add a new Update a row action and open the expression tab.
  • Add the expression first(body(‘Internal_name_of_the_list_row_action’)?[‘value’])?[‘internal_field_name’].
  • This expression returns the first specified field value from the value array in the returned body of the list row action.
first(body('List_rows_-_BlackedOutName_related_with_Order_ID')?['value'])?['rc_id']

Check if the returned result is empty

In my Cloud Flow I needed to check if a List rows action did not return a value. This is not an option in the drop down list, but it can be done using the empty expression.

  • Add the condition action.
  • Open the expression tab.
  • Add the expression empty(output(‘Internal_name_of_the_list_row_action’)?[‘body/value’]).
  • This checks if the returned body have any values, with other words checks if the value is empty.
empty(outputs('List_rows_-_BlackedOutName_-_DTA')?['body/value'])

Power Automate: Custom Flow loging

When managing multiple Power Automate Cloud Flows it can easily become a big task to figure out what went wrong and where. My client has more than 10 Cloud Flows that run multiple time per day or per hour. The solution is to create a log table (dataverse) or list (SharePoint) where all the runs are stored. The log table contains all the data an administrator needs to maintain the Cloud Flows. In my example I create a Dataverse table.

The Dataverse log table

In the log table we store the following data.

The Cloud Flow

In every Cloud flow a try and catch scope is added. All the main actions are in the try scope, if one of these actions fails than the catch scope will be used.

  • Add a Scope with the name Try.
  • Add the action the Dataverse Add a new row action.
  • Select the log table by Table name.
  • Select the Log status Processing, I use Processing, Failed and Successful.
  • Add the Cloud Flow name to Process name.
  • Add the following code to the Environment field.
workflow()?['tags']?['environmentName']
  • Add the following code to External URL, use the code snippets for the fx code.
    This creates a link to the actual flow run.
workflow()?['tags']?['environmentName']
workflow()?['name']
workflow()?['run']?['name']
  • Set the Status Reason to Active.
  • Add the end of the try scope add the Dataverse Update a row action.
  • Select the log table by Table name.
  • Add the ID of the create log in the Row ID field.
  • Select Successful in the Log status field.
  • Add a Scope with the name catch.
  • Add the Filter Array action.
  • Add the result of the try scope in the From field.
result('Try')
  • Add the following filter Status is equal to Failed
item()?['status']
  • Add the Dataverse Update a row action.
  • Select the log table by Table name.
  • Add the ID of the create log in the Row ID field.
  • Select Failed in the Log status field.
  • Select Inactive in the Status field.
  • Add the following text to the Log details: Flow log action(s):
  • Add the following code to the Log details, to show the error message from the try scope.
body('Filter_results_with_failed')[0]?['outputs']

Power Automate: Advanced Flow building

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

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

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.

Office 365: What’s new from Microsoft Ignite

During Microsoft Ignite many innovations that transform the workplace communications have been announced. Its impossible to name them all, so instead I will share with you my favorite innovations. Note that some of these features are already live, being rolled out or still being developed.

SharePoint app bar

The SharePoint app bar might even be my favorite announcement from Ignite. The app bar will be visible on every site on your intranet providing a consistent navigation experience. But it’s not only for navigation, that is just the beginning. The app bar also shows personal relevant sites, news feed and the files.
The SharePoint app bar will appear after you set a SharePoint site as a home site.

thumbnail image 1 of blog post titled 
	
	
	 
	
	
	
				
		
			
				
						
							Introducing a SharePoint app bar that features global navigation
							
						
					
			
		
	
			
	
	
	
	
	

	
	
	 
	
	
	
				
		
			
				
						
							Re: Introducing a SharePoint app bar that features global navigation
							
						
					
			
		
	
			
	
	
	
	
	

	
	
	 
	
	
	
				
		
			
				
						
							Re: Introducing a SharePoint app bar that features global navigation
							
						
					
			
		
	
			
	
	
	
	
	

	
	
	 
	
	
	
				
		
			
				
						
							Re: Introducing a SharePoint app bar that features global navigation
							
						
					
			
		
	
			
	
	
	
	
	

	
	
	 
	
	
	
				
		
			
				
						
							Re: Introducing a SharePoint app bar that features global navigation
							
						
					
			
		
	
			
	
	
	
	
	

	
	
	 
	
	
	
				
		
			
				
						
							Re: Introducing a SharePoint app bar that features global navigation
							
						
					
			
		
	
			
	
	
	
	
	

	
	
	 
	
	
	
				
		
			
				
						
							Re: Introducing a SharePoint app bar that features global navigation
							
						
					
			
		
	
			
	
	
	
	
	

	
	
	 
	
	
	
				
		
			
				
						
							Re: Introducing a SharePoint app bar that features global navigation
							
						
					
			
		
	
			
	
	
	
	
	

	
	
	 
	
	
	
				
		
			
				
						
							Re: Introducing a SharePoint app bar that features global navigation
							
						
					
			
		
	
			
	
	
	
	
	

	
	
	 
	
	
	
				
		
			
				
						
							Re: Introducing a SharePoint app bar that features global navigation
							
						
					
			
		
	
			
	
	
	
	
	

	
	
	 
	
	
	
				
		
			
				
						
							Re: Introducing a SharePoint app bar that features global navigation
							
						
					
			
		
	
			
	
	
	
	
	

	
	
	 
	
	
	
				
		
			
				
						
							Re: Introducing a SharePoint app bar that features global navigation
							
						
					
			
		
	
			
	
	
	
	
	

	
	
	 
	
	
	
				
		
			
				
						
							Re: Introducing a SharePoint app bar that features global navigation
							
						
					
			
		
	
			
	
	
	
	
	

	
	
	 
	
	
	
				
		
			
				
						
							Re: Introducing a SharePoint app bar that features global navigation
							
						
					
			
		
	
			
	
	
	
	
	

	
	
	 
	
	
	
				
		
			
				
						
							Re: Introducing a SharePoint app bar that features global navigation
							
						
					
			
		
	
			
	
	
	
	
	

	
	
	 
	
	
	
				
		
			
				
						
							Re: Introducing a SharePoint app bar that features global navigation
							
						
					
			
		
	
			
	
	
	
	
	

	
	
	 
	
	
	
				
		
			
				
						
							Re: Introducing a SharePoint app bar that features global navigation
							
						
					
			
		
	
			
	
	
	
	
	

	
	
	 
	
	
	
				
		
			
				
						
							Re: Introducing a SharePoint app bar that features global navigation
							
						
					
			
		
	
			
	
	
	
	
	

	
	
	 
	
	
	
				
		
			
				
						
							Re: Introducing a SharePoint app bar that features global navigation
							
						
					
			
		
	
			
	
	
	
	
	

	
	
	 
	
	
	
				
		
			
				
						
							Re: Introducing a SharePoint app bar that features global navigation

Home site in Teams

Your intranet (home site) is coming to Microsoft Teams. With the home site app for Microsoft Teams users gain direct access to the SharePoint Home site in Teams. The home site app in Teams gives your users global navigation across sites, communities, and teams; quick access to sites they use regularly; and a personalized news feed.

thumbnail image 1 captioned The home site app for Teams brings the power of your SharePoint-based intranet home site seamlessly into Microsoft Teams.

SharePoint: News boost

Microsoft listened to our request; we are getting the ability to boost news! With this feature you wil be able to boost the visibility of your important news articles. You can boost a news post until it has been read, for a set number of impressions, or until a given date.

thumbnail image 3 of blog post titled 
	
	
	 
	
	
	
				
		
			
				
						
							Innovations for workplace communications and employee engagement in Microsoft 365

SharePoint: news digest 

Keeping up with all the all the news and information can be harder than it seems. With the automatically generated news digest you will receive an email summary of the news articles you have missed. This will ensure that everybody will be well informed. The news digest can be customized with your organization’s branding.

thumbnail image 5 of blog post titled 
	
	
	 
	
	
	
				
		
			
				
						
							Innovations for workplace communications and employee engagement in Microsoft 365

Teams: Dynamic view

Dynamic view makes uses of AI to optimize shared content and video participant, it intelligently arranges the elements of your meeting for an optimal viewing experience. The viewing experience will change when people join, turn on video, start to speak or when starting to present. You will still be able change the layout based on your preference.

thumbnail image 1 of blog post titled 
	
	
	 
	
	
	
				
		
			
				
						
							What's New in Microsoft Teams | Microsoft Ignite 2021

SharePoint spaces 

SharePoint spaces is a very powerful new tool for 3D interactions. SharePoint spaces enables you to make immersive and engaging mixed reality experiences for 3D content, models and 360° imagery.