Category Archives: Power Automate
Parse JSON and update excel using Cloud flow
Introduction: If you have an Excel with data in JSON format and do not want to use Excel Power Query to transform JSON, you can use the below actions in Cloud flows. Solution: Create a Manual trigger flow and upload the Excel with JSON data on SharePoint. Use List rows to read the excel data For every record in Excel, use the Parse JSON Action. After every parse, append the value to Output array with a comma. Use substring expression to remove the last comma of the appended value and store the new value. Then use Update a row to update the Output to same Excel.
Share Story :
Last and First Expression in Cloud Flows – Part II
We have already seen how to extract character from string in Cloud flows. Let see how we can get first and last value in array. I have array of list of records (account records), want to get 1st value in array. Array – Add a compose step in flow and use below syntax to get first value. Syntax- first(<array>) Output – Similarly, we can get last value in array. Syntax – last(<array>) Output – Hope this helps !
Share Story :
Multi Select Option Set is not visible in Cloud flow Trigger Body
Problem Statement: I triggered a Cloud flow and expected to get the multi select option set value as part of triggered body. However, the trigger body does not show the field. I checked with Common Data Service(Current environment) and Common Data Service Connector as well. Solution: This is more of a workaround, we do not get the multi select option set field, so we will use Get a row action to get the record that was created.
Share Story :
Loop on Multi Select Option Set Values Using Cloud flow
Problem Statement: We wanted to run an action on all the selected values in a multi select option set(choice).We get the data in a comma separated string. Solution: 1.When we get the values in comma separated values, we will need to convert the string to an array format. Formula: array(split(Input comma separated data),’,’)) 2. Get the output from the compose step and add in parse Json to create a Json object. 3.Add an apply to each on Json body and you can use the item(apply_to_each) to access the data from the Json object. Conclusion: Instead of complicating the flow with switch or if condition to check the data and then run the flow, we can loop on Multi select option set value using apply to each action.
Share Story :
Post Message in MS Team when Code is Checked-in Azure DevOps TFS
If you want to keep track of the Commits of any Critical Project and want to discuss with your Project Team Member regarding commits made on TFS. We can create a flow that will post the Commit details of that Critical Project on your Project Channel. Step 1: Create a Team and a channel to post the Checked-in Details. Here I have created a Team with the name “Project Team” and a separate channel “Azure DevOps” to post all the messages related to Development and DevOps. Step 2: Create a Cloud flow to post Message when someone commits any changes in Azure DevOps (TFS) Search for Azure and select the triggering condition as “When code is checked in (TFVC)” Configure the Triggering block with Project for which you want to post a message to MS Team. If you want to track the commits of any specific user then you can set a user in Team Member otherwise it will post a message for all checked-in commits made by all Project Members present in Member. Now, we will add Post a message (v3) block in Cloud flow. Make sure that you should select the right channel while configuring the post message block, after configuring your block it should look like the below screenshot: Save the Cloud flow and Test it by making commits from your Visual Studio or Azure DevOps from the browser. Testing. Here, I will make commit using Visual Studio: Result
Share Story :
Sort records in Cloud flows
In this blog we will see how we can sort list records in Cloud flows Lets say we want to retrieve all accounts on created by ascending. There are two ways to sort list of records in Cloud flows Order By – Fetch Xml – Order By – Use List Records step in Common Data Service Connector where all you need to specify is field schema name and Order (Asc/ Desc ) Syntax – Schema_Name asc/desc 2. Fetch XML We can use Fetch XML to retrieve only the attribute that you need based on Condition that can be easily added using advanced find. While adding condition we can specify the order in Fetch XML 1. Click on Configure Sorting 2. Add Sorting – Sort By -> Ascending Order and then download Fetch XML. Below is the downloaded Fetch XML – 3. Use this Fetch XML in Flow Hope this helps !
Share Story :
Send Email to not resolved Email Ids from Workflow/Cloud flows
Introduction: We might have a requirement to send emails from Workflow/cloud flows to Ids that cannot be resolved as Contacts, Accounts, Users in CRM. Solution: We need to make changes to system settings to allow messages to unresolved recipients In case of Workflow, directly add email address in the field( It will show ? as it is not resolved address) For Cloud flow, in create a new email record, click on “Switch to Input entire array”, populate the email in addressused field. This will let you send emails to an unresolved email address.
Share Story :
Flow to Replace Special Characters from any text Field in Dynamics 365
Consider a case where users create a record say an opportunity. The name of the opportunity may contain special characters such as ampersand “&” and many more. This might not look like an issue, but these special characters are not accepted if you are trying to fetch a record using query expressions, that may be Fetch XML or any other. What to do in such case. There are certain ways in which this issue can be resolved. Replace the Special Character Using Variable in the flow itself. Replace the Special Character in the Record. Or Skip the Special Character. Let’s have a look on how to Replace the Special Character in the Record.Consider an Opportunity Record with name as Given Below. As we see that there is an “&” in the name – “COCS – Planning & Discovery“. This can be a cause for a Bad Request error while fetching this record using query expressions. Solution: – The Flow will run the flow when an Opportunity is Created. The Flow will get the Topic of the Opportunity. The Flow will Check if the Topic Name Has Any Special Characters If YES The Flow Will check which Special character it is and replace the Topic name Accordingly. After the name is being updated the flow will Update the Opportunity Record. If NO The flow will terminate successfully. Let’s Begin with the Flow Implementation. Process Flow: – Step – 1 The Flow will trigger when an Opportunity is being created. Step – 2 The Flow will get the Created Opportunity Record. Step – 3 Also we need to initialize an array of Special Characters as given below Step – 4 I have used a Special Character Array “createArray(‘&’,’|’,’/’)” according to my requirement for all special characters you can go with the below expression. Step – 5 “createArray(‘.’,’@’,’ß’,’²’,’³’,’µ’,’`’,’´’,’°’,’^’,’=’,'(‘,’)’,’&’,’$’,’§’, ‘~’,’#’,’%’,’*’,’:’,'<‘,’>’,’?’,’/’,’|’,’ ‘) “ Step – 6 The Flow will check if the Topic has any special characters. Step – 7 If Yes, it will replace & with “And” using the Filter query “replace(variables(‘Topic’), item( ),’ and ‘)” Step – 8 Lastly the flow will update the Topic of the Opportunity. Results Hope This Helps !.
Share Story :
Set Dynamic Recipients of D365 Email in Power Automate
Introduction : We can send email using D365 Connectors in Power Automate. To know about how to send emails, refer my blog: https://www.cloudfronts.com/send-d365-email-using-power-automate/ In this scenario where, the activity parties (TO, From, CC) ae static, we can easily send emails. However here is how we can set the activity party dynamically on the Email. Solution: The create a record of Email message is as below, here we are entering the attributes of activity party, by clicking on “Switch to Input entire array” You will get the Array format, the participationtypemask is TO, FROM,CC while the partyid@data.bind is the email address. Now all you need to do is create a dynamic array, You can use append to array variable action to create an array with participationtypemask as per your requirement, pass this array as input to the Activity Parties field in create an email record.
Share Story :
Time Out and Retry Policy in Power Automate
Introduction: While using Power Automate actions, you may need to retry or set timeout for your actions. Solution: For Create, Update, Delete, Send HTTP request, Do Until Actions, simply go to Settings In Settings, you will find Timeout and Retry Policy options. Time Out: For the duration specified, the action will wait and then time out. Here P1D means 1day. For minute- specify P1M and S for Seconds. Retry: You can set the retry policy. There are 4 types: Default- The default is an exponential interval policy set to retry 4 times. None- Do not Retry Fixed Internal- Count( No of times to retry), Interval( time between retry). 4. Exponential Interval- Count(No of times to retry), Interval(Time between retry), Minimum and Maximum Interval (the Retry will happen exponentially[Increase or decrease] between the specified intervals) Eg: If Interval is 1H and minimum Interval is set to 1M- it will decrease exponentially till it reaches 1M.