Flow

Power Automate – format names to proper case using Select action

  • Post category:Flow
  • Post comments:0 Comments

It is a bit baffling, but as of 2023 and the time of this article, Power Automate still doesn't have toProper() function. There are multiple ways to work around that, to name a few: Select action, Apply to each (loop) action, Azure Automate (PowerShell in the cloud). Personally, I would use Azure Automation (especially when formatting something more than people names) but that would be a more complex approach. The method below works in a pinch. You may ask why one would need to format names to a proper case. Well, when you are dealing with older databases, it's possible the data is formatted with UPPERCASE. Pros: simple and works within Select actions; very fast and doesn't require looping or Azure Automation; handles "up to" triple barrel names; works well in a real life scenario. Cons: only handles "up to" triple barrel names (anything more complex will not be properly converted);…

Continue ReadingPower Automate – format names to proper case using Select action

Power Automate – add more data to a CSV log file

  • Post category:Flow
  • Post comments:2 Comments

This Power Automate workflow adds data to the same CSV file after each run. The most common use case is saving results of the workflow to a CSV log file. Of course, it would be a bit more complicated than my test scenario but the concept is exactly the same. Pros of the workflow: puts the most recent data at the top of the CSV file; saves data into a single file rather than multiple (although each approach can have its use case); the latter makes it much easier to search through. The initial file can be empty (just the headers) or have some matching data: 1) Initialize variable action –  varArray variable (Array type). Value: [ { "Name": "John", "Date": "10/12/2022" }, { "Name": "Jeff", "Date": "09/16/2022" } ] 2) Create CSV table action. From: variables('varArray') 3) Initialize variable action –  CRLF variable (String type). Value: uriComponentToString('%0D%0A') 4) Get file metadata using path action. Peek code: "parameters": {…

Continue ReadingPower Automate – add more data to a CSV log file

Power Automate – get all data through paging in Graph API

  • Post category:Flow
  • Post comments:9 Comments

To make it simpler, I will quote an article from Microsoft website: Some queries against Microsoft Graph return multiple pages of data either due to server-side paging or due to the use of the $top query parameter to specifically limit the page size in a request. When more than one query request is required to retrieve all the results, Microsoft Graph returns an @odata.nextLink property in the response that contains a URL to the next page of results. More on that here - https://learn.microsoft.com/en-us/graph/paging The Power Automate workflow below shows how to handle paging and extract all of the data (in my case, the display names of all users). Important! This example is for Graph API only, any other API might structure data differently. The latter will require some adjustments in the workflow. 1) HTTP action. Peek code: { "inputs": { "method": "GET", "uri": "https://graph.microsoft.com/v1.0/users", "queries": { "$top": "4", "$select":…

Continue ReadingPower Automate – get all data through paging in Graph API

Power Automate – format data to output a table with row and column headers

  • Post category:Flow
  • Post comments:0 Comments

Power Automate has an amazing Create HTML table built-in action. Whether you format that table or not after is totally up to you; however, the output table always has standard headers. The flow below shows how to automatically format your table into one with row and column headers at the same time. 1) Initialize variable action –  varArray variable (Array type). Value: [ { "Property": "First Name", "Current": "Lisa", "Requested": "Elisabeth" }, { "Property": "Last Name", "Current": "Smith", "Requested": "Brown" } ] 2) Select action. From: variables('varArray') Select: item()?['Property'] 3) Create HTML table action. From: variables('varArray') 4) Compose action. Inputs: <style> table { border: 1px solid #1C6EA4; background-color: #EEEEEE; width: 70%; text-align: left; border-collapse: collapse; } table th { border: 1px solid #AAAAAA; padding: 3px 2px; } table tbody td { font-size: 13px; } table thead { border-bottom: 1px solid #444444; } table th { font-size: 15px; font-weight: bold; color: #FFFFFF; background:…

Continue ReadingPower Automate – format data to output a table with row and column headers

Microsoft Flow – extremely flexible email merge without any hardcoded mapping

  • Post category:Flow
  • Post comments:0 Comments

Imagine an Email Merge process that doesn't require any mapping per se. A process that is as easy as composing an email. Look no further! The pros of this workflow are: uses a single SharePoint list that can be easily reused for another Email Merge need; use the same SharePoint list to send email merges with a few or lot of fields; preserves all custom html formatting of an email; doesn't use paid connectors and premium Power Automate license; no need for data mapping - you just compose an email using special tags; can have as many columns and as many tags as you would like. Please note, as this workflow is quite complicated logically, I would encourage you to make my example fully work on your side first, then apply its concept to your business needs second. PREREQUISITES: 1) Let's create a SharePoint list. Rename its Title column to…

Continue ReadingMicrosoft Flow – extremely flexible email merge without any hardcoded mapping

Microsoft Flow – parse email and extract information

If you have a 3rd party system that sends standardized notifications and you would like to automatically process those notifications, then this Flow can be helpful. The flow below parses a predefined email, extracts information for you to take further actions with it. Those actions can be: saving data into a SharePoint List; forwarding part of an email to another system; removing all formatting and so on. The flow is pretty simple and consists of these steps: 1) Trigger - When a new email arrives. 2) Action - Html to text - convert email body from HTML to plain text. 3) Action - Initialize variable - store a carriage return value in it. Peek code view: { "inputs": { "variables": [ { "name": "CRLF", "type": "String", "value": "\n" } ] } } 4) Action - Compose - split output from #2 into an array. Expression: split( trim( uriComponentToString( replace( uriComponent(…

Continue ReadingMicrosoft Flow – parse email and extract information

Microsoft Flow – archive SharePoint List data into a single Excel file

  • Post category:Flow
  • Post comments:0 Comments

This post is about a quite simple task to achieve - archiving SharePoint List data into a single Excel file that is stored in a Document Library. I think it is important to go through this process to show the basics and have it as a prerequisite for a more complicated process - archiving data into multiple excel files. There are several reasons why one would archive data into Excel: excel file is easy to move or share; it is very fast to work with (no paging, no 5000 item limit view threshold). Let's create a SharePoint List with different types of columns (single line text, date, number, choice): Then we add a few test items: After that we create a Document Library and put an Excel file into it: That Excel file should have a table in it and the same columns as our SharePoint List. The formatting of…

Continue ReadingMicrosoft Flow – archive SharePoint List data into a single Excel file

Microsoft Flow – 2 methods to not use Apply to Each action when only a single filtered item is expected

  • Post category:Flow
  • Post comments:14 Comments

In this post I will show you 2 methods when using Apply to each Flow action is not necessary. By not using them you make your Flows look cleaner, less complicated. Please note, these methods are mostly good when only a single item is expected after filtering an array. I created a SharePoint List with two text columns - Title and Color. Both columns are required. Title values are set to be unique (!). When using Get Items action, an array of items is returned regardless if it's a single item in it or not. The moment you try to use a Condition action on its result, the system will insert an Apply to Each action to go through every single possible item. Again, it's redundant when by design a single item is returned after filtering. In my test flow I'm using a very simple Filter Query on purpose - Title…

Continue ReadingMicrosoft Flow – 2 methods to not use Apply to Each action when only a single filtered item is expected

SharePoint – use Microsoft Flow to enforce uniqueness of a combination of two or more columns

  • Post category:Flow
  • Post comments:1 Comment

In SharePoint there is an out-of-the-box way to enforce uniqueness of fields. Let’s say you have a SharePoint list with events and dates and by design you would like to allow any combination of events per day as long as they don’t have the same name and date/time at once. According to the requirements, this is not allowed: Team Lunch – 12/29/2018 12:00 PM (same name and date/time) Team Lunch – 12/29/2018 12:00 PM (same name and date/time) And these combinations are allowed: Team Lunch – 12/29/2018 12:00 PM (same name, different date/time) Team Lunch – 12/29/2018 2:00 PM (same name, different date/time) OR Team Lunch – 12/29/2018 12:00 PM (different name, same date/time) Vendor meeting – 12/29/2018 12:00 PM (different name, same date/time) If you make both Event and Date fields unique then you won’t be able to achieve that. Would be great if we could create and use a…

Continue ReadingSharePoint – use Microsoft Flow to enforce uniqueness of a combination of two or more columns

Microsoft Flow – populate a multi choice field in SharePoint

  • Post category:Flow
  • Post comments:95 Comments

How to populate a multi choice field in SharePoint is a frequent question on Microsoft Flow forum. Officially, at the time of this post there is no confirmation that such functionality exists. So I started testing possible ways of doing it and eventually came up with a solution that works and kind of makes sense. If the Trial and Error part is no interest for you, then feel free to scroll down to the Solution. TRIAL AND ERROR 1) I created a Color Choices SharePoint list with Colors being a multi choice column. Choices are Red, Blue, Yellow, Pink, Green, and Orange. Then from the list itself I submitted the 1st item with Red and Yellow values. The goal was to see how data would look like from the Flow perspective. To do that a simple Flow was created that works on selected item and gets data from it. The result was:…

Continue ReadingMicrosoft Flow – populate a multi choice field in SharePoint