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 – 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

AAD Dynamic Group – example and formatting tips

If your company has at least an Azure AD premium P1 license, then you have access to a powerful feature - Dynamic group membership. Microsoft has a lot of information on how to construct those rules -  https://learn.microsoft.com/en-us/azure/active-directory/enterprise-users/groups-dynamic-membership So in my post I would rather focus on some formatting tips: When designing a rule, using a Windows PowerShell ISE or any coding software is helpful for proper spacing Use CRLF (carriage return line feed) to separate blocks in your dynamic rule - AAD system interprets those as spaces without any issues  If you keep formatting nice and tidy (rather than a blob of text), Graph API will preserve that formatting when pulling data for reports Use a combination of -in, -NotIn, and arrays as much as possible - this approach is great to further scale your rule Put OR condition to the very end of the rule A rule below…

Continue ReadingAAD Dynamic Group – example and formatting tips

SharePoint Column Formatting – use JSON to show column data in a pop-up window

Sometimes a SharePoint column has too much information, which eventually stretches rows and distorts the look. An example below is a Multiple lines of text type column: This simple JSON code is useful to hide noncrucial information, while still allowing easy access to that data by hovering your mouse cursor over it. { "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json", "elmType": "div", "style": { "display": "=if(@currentField, '','none')", "font-size": "12px" }, "txtContent": "Hover to see more", "customCardProps": { "formatter": { "elmType": "div", "txtContent": "@currentField", "style": { "padding": "5px 5px 5px 5px", "border": "solid", "border-radius": "2px", "border-width": "1px", "box-shadow": "0 0 2px 0" } }, "openOnEvent": "hover", "directionalHint": "bottomCenter", "isBeakVisible": false } }

Continue ReadingSharePoint Column Formatting – use JSON to show column data in a pop-up window

SharePoint Column Formatting – hide Title column and use JSON to show View and Edit buttons in any column

It’s not always convenient to use a default Title column that comes standard with a newly created SharePoint list. PowerApps form can take care of it and add some logic to it but it is still a Single line of text column. To make it even more complicated to remove, that Title column ties to a "view/edit an item" link. If you still want to get rid of it and not lose any functionality you can use a simple JSON code below: { "elmType": "a", "txtContent": "@currentField", "attributes": { "href": "='https://contoso.sharepoint.com/sites/dev/Lists/TestList/DispForm.aspx?ID='+[$ID]" }, "style": { "padding": "10px", "font-weight": "bold" } } However, this method has quite a few downsides: when closing a form, it redirects to a Default view (it is possible to specify which view to go but that might be a lot of hard-coding depending on how many views you have); the link is hard-coded; does not seem to open "modern…

Continue ReadingSharePoint Column Formatting – hide Title column and use JSON to show View and Edit buttons in any column

SharePoint Document Library – show Path column in the view and make it clickable to open that path

How to add / show Path column in a view in a SharePoint Document Library appears to be a question with hundreds of different answers to it. The reasons for such a variety are: different versions of SharePoint (on premise, online, 2010, 2013); two Workflow variations ("old" workflow, Microsoft Flow); SharePoint Designer (2010, 2013); using Web Parts and more. Why would one need to see a Path column? Take a look at the two examples below; however, there might be more use cases. Example 1 - when searching for files. Example 2 - when using a view with "Show all items without folders" option selected Here is a test Document Library with a folder and some document in it: It's not possible to show Path column using out-of-the-box tools, the Path column is just not available to be selected: Although the Path can be seen if you open the details pane:…

Continue ReadingSharePoint Document Library – show Path column in the view and make it clickable to open that path

SharePoint Column Formatting – customize the look of Hyperlink columns and their Display Text

There is nothing wrong with how SharePoint Hyperlink columns display information by default. However, some users believe that the look is too generic, hard to bring attention to, which I myself tend to agree with. Let's see how it looks by default: The issues in my opinion are: the Display Text is grey and no different than any other column if Display Text needs to always be the same (e.g. CLICK HERE), then it has to be typed in that way in every single list item. We will address both issues. 1. KEEP THE ORIGINAL DISPLAY TEXT AND CHANGE THE LOOK The following JSON code is applied to both Hyperlink columns: { "$schema": "http://columnformatting.sharepointpnp.com/columnFormattingSchema.json", "elmType": "a", "txtContent": "@currentField.desc", "style": { "color": "red", "font-weight": "bold" }, "attributes": { "target": "_blank", "href": "@currentField" } } 2. SHOW PREDEFINED DISPLAY TEXT AND CHANGE THE LOOK The following JSON code is applied to both…

Continue ReadingSharePoint Column Formatting – customize the look of Hyperlink columns and their Display Text

SharePoint Column Formatting – make choice type columns that represent boolean values visually stand out

If you have a SharePoint List that has a Choice type column with the following choices as an example: On / Off Active / Expired Yes / No True / False Enabled / Disabled (used in this example), then you might consider adding some column formatting that will help those different choices stand out. With Microsoft constantly simplifying Column Formatting it is possible now (as of 1/3/2019) to just select the color of the background depending on the choice. The editor is very limited though. See the screenshots below. All great, but let’s add our own formatting that in my opinion looks cleaner: To change formatting, click on Status column header – Column settings – Format this column. Once selected, there will be a Format column window on the right side. Paste the code from down below into that windows and save to get the result. The long version of…

Continue ReadingSharePoint Column Formatting – make choice type columns that represent boolean values visually stand out

SharePoint Column Formatting – show stars that match ratings from 1 to 5

Similar to what is done in the SharePoint Column Formatting – add different mood emojis that match ratings from 1 to 5 post, let's replace rating values with corresponding number of stars. Personally, I think it even looks better. Please note, the formatting doesn't adjust any data. If you export your data to Excel, you will still see numeric values. The icons are from https://developer.microsoft.com/en-us/fabric#/styles/icons To see how it works we will create a SharePoint list, then add a “Rating” column (Number type). For example purposes, five different ratings are added with scores from 1 to 5. To add matching number of stars to the “Rating” column, click on its header – Column settings – Format this column. Once selected, there will be a Format column window on the right side. Paste the code from down below into that windows and save to get the result. { "$schema": "http://columnformatting.sharepointpnp.com/columnFormattingSchema.json", "elmType": "div", "children": [ { "elmType": "span", "attributes":…

Continue ReadingSharePoint Column Formatting – show stars that match ratings from 1 to 5