• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar

Tachytelic.net

  • Sco Openserver
    • Sco Unix Support
    • SCO Openserver Installation ISOs
    • SCO Openserver Binaries
    • Add a Hard Drive to an Openserver 5 Virtual Machine
    • Install SCO Vision SQL-Retriever ODBC Driver on Windows 10
    • License Expired on Virtual SCO Openserver Installation
    • How to reset the root password on SCO Openserver 5
  • Scripting
    • PowerShell
      • Add leading zeros
      • Check if a File Exists
      • Grep with Powershell
      • Create Environment Variables
      • Test for open Ports
      • Append to a Text File
    • VBScript
      • Check if a File Exists
      • lpad and rpad functions
      • Windows Update E-Mail Notification
  • Office 365
    • Connect to Office 365 with PowerShell
    • Add or remove an email alias using Powershell
    • Change Primary email address of Active Directory user
    • How to hide an AD user from the Global Address List
    • How to hide mail contacts from the Global Address List
    • Change the primary email address for an account with PowerShell
    • Change Primary email address of an AD User
    • Grant a single user access to access to all calendars
    • Forward email to an external address using Powershell
    • Convert shared mailbox to user mailbox with Powershell
  • Get in Touch
  • About Me
    • Privacy Policy

Power Platform

Super Simple flow to get more than 5000 SharePoint Items

December 9, 2020 by Paulie 1 Comment

I’ve done a series of blog posts on how to get more than 5000 items from a SharePoint list using Power Automate. While helping someone else out I figured out a way to make it much easier.

If you’re not interested in knowing how it works, just download the flow and reconfigure it for your environment.

Overview

In this example the flow is triggered by PowerApps, but the same method could be used for any trigger. Let’s take a look at the complete flow and then I will explain each step in detail:

Super easy flow to get more than 5,000 items from a SharePoint list

Step by Step Explanation

Here is a explanation of every step of the flow in the order they appear in the screenshot above

  1. Trigger Action
    Is the flow trigger action. This could be PowerApps, the HTTP connector or anything that suits your situation.
  2. Compose Action ‘querySettings’
    This is a compose action which specifies the query settings for the flow.
    Image of a Compose Action which specifies the query settings for the SharePoint API Query
    It has two properties:
    • listName – Specifies the name of the SharePoint list that you want to retrieve items from.
    • fields – Is a comma separated list of fields that will be retrieved from the SharePoint list.
  3. Send an HTTP Request to SharePoint ‘getLowestID’
    This step gets the ID of the first record in the SharePoint List.
  4. Send an HTTP Request to SharePoint ‘getHighestID’
    Gets the ID of the last record in the SharePoint List.
  5. Compose Action ‘threads’
    This flow is going to execute multiple simultaneous queries to SharePoint. Each of which will return a maximum of 5,000 records. Specify the number of threads you will need to cover the number of records that you have in total.
    For example, if you have 9,000 records you will need 2 threads. If you have 23,000 you will need 5 threads. For example:
    [ 0, 1, 2, 3, 4 ]
  6. Compose Action ‘itemsPerThread’
    This action calculates the number of items to fetch in each thread. If you have 9,000 items it will get 4,500 items per thread. The expression is:
    add(div(sub(body('getHighestID')?['value'][0]['ID'],body('getLowestID')?['value'][0]['ID']), length(variables('threads'))),1 )
  7. Apply to each
    The apply to each action is where the SharePoint queries take place. It is important to configure the concurrency options on this step to ensure it is set to at least the number of threads you have configured:
    Image showing how to specify apply to each concurrency
    Within the apply to each the following actions take place:
    1. Compose action ‘lowID’
      Calculates the lowest ID to find in the current iteration:
      add(body('getLowestID')?['value'][0]['ID'], mul(outputs('itemsPerThread'), item()))
    2. Compose action ‘highID’
      Calculates the highest ID to find in the current iteration:
      add(body('getLowestID')?['value'][0]['ID'], mul(outputs('itemsPerThread'), add(item(), 1))
    3. Send an HTTP Request to SharePoint
      This is where the actual SharePoint query takes place. Using the inputs from the previous steps. The expression in the Uri field is:
      _api/Web/Lists/GetByTitle('@{outputs('querySettings')['listName']}')/[email protected]{outputs('querySettings')['fields']}&$filter=ID ge @{outputs('lowID')} and ID le @{outputs('highID')}&$top=5000
      Image of SharePoint HTTP Action being used to collect 5000 records from SharePoint
  8. Compose Action ‘Sample Output’
    This action isn’t required for the flow to run. It is used to output two records from the first iteration of the SharePoint API query. This can be used to generate the required JSON Schema in the final step.
    take(outputs('querySharepoint')[0]['body']['value'],2)
  9. Response Action
    This is used to send the data back to PowerApps. You need to modify the Response Body JSON Schema. If you run the flow once you can use the output of the “Sample Data” step and use the “Generate from Sample” function.

    The body content unions all of the data that is returned from SharePoint into a single array. This will need to be adjusted according to the number of threads that you have configured:
union(
outputs('querySharepoint')[0]['body']['value'],
outputs('querySharepoint')[1]['body']['value'],
outputs('querySharepoint')[2]['body']['value'],
outputs('querySharepoint')[3]['body']['value']
)

Conclusion

This is an incredibly easy way to get more than 5000 items from a SharePoint list. It is also very fast. I was able to get 17,000 rows in 5 seconds and someone I am working with was able to get 5,000 rows in two seconds!

Download the Flow

This flow is simple enough that you don’t really need to understand how it works, you can just import it into your environment and reconfigure to your needs.

Download the flow here and import it into your environment.

Filed Under: Power Platform Tagged With: Power Apps, Power Automate, SharePoint Online

How to create Proper Case Strings in Power Automate

December 2, 2020 by Paulie Leave a Comment

Sometimes strings in Power Automate need to be reformatted so that the first letter of each word is capitalized. This is know as “Proper Case”. There is no function built in to perform this common task. PowerApps does have this function so I expect it will arrive on Power Automate eventually.

In the mean time, here is an example flow and the required code to convert a string to Proper Case. The goal is to transform the sample string “this is a TEST” to “This Is A Test”

Example Proper Case Flow

Proper case is simple to achieve, take a look at this sample flow:

Image of a Flow in Power Automate that converts a string to Proper Case (Capitalizes each word).

Explanation:

  • The phrase to be transformed is added to a compose step.
  • An apply to each is started and the input is a split expression which splits the phrase into an array.
  • Each word is then passed into an expression which makes the first letter of the word uppercase and the remainder lower case.
  • The final step of the flow uses a join expression to put the phrase back together again with spaces between each word.

The expression in the flow step “Capitalize each word” is:

if
(
  equals(length(item()),1),
  toupper(item()),
  concat(first(toupper(item())),toLower(substring(item(),1,sub(length(item()),1))))  
)

Explanation of the expression:

  • If the length of the word is only 1 letter, just output the uppercase representation of the word.
  • If the word is longer than 1 character then uppercase the first letter of the word and lowercase the remainder.

I decided to write this up after someone asked how this could be done on the Power Automate community forums. He was following a blog post written by Elaiza Benitez that performs the same function.

My flow is mostly the same, but also works when one of the words has only one letter.

Hope this helps you out until the Power Automate team add a Proper Case function.

Filed Under: Power Platform Tagged With: Power Automate

Publish SharePoint data on a public website with Power Automate

November 25, 2020 by Paulie Leave a Comment

In this post I am going to demonstrate how to collect data from a SharePoint list using Power Automate and publish it in a jQuery grid on any website. Many businesses store data in data sources that are easily accessible by Power Automate that might be useful to share externally. For example, stock levels, upcoming events, price lists etc.

This is easy to do with a small amount of code and saves you having to create guest access to your Office 365 environment. Power Automate can compile a JSON array as a data source to a jQuery grid.

Take a look at the following SharePoint list:

Image of data in a SharePoint list that is going to be exposed to public web

Using the above data, the parts list is going to be exposed to the public web, but the cost and margin columns will be excluded, and the list will be filtered so that only items where “Show on Web” is set to yes.

This data source could be anything that is accessible to Flow:

  • Excel Data
  • SQL Server
  • CDS
  • Oracle
  • MySQL
  • etc

The table below is a live working example of what this flow will demonstrate, as you can see:

  • It’s very fast and easy to browse.
  • Searching is virtually instant.
  • It’s possible to group items easily.
  • Filtering is simple.

How to build the flow

The flow is really easy to build and requires only a few steps, the trigger action is “When a HTTP request is received”.

Filter the data

The first action in the flow is the “Get Items” from SharePoint. This has an oData filter query so that it only retrieves items where “Show on Web?” is set to yes:

Image of a Get Items from SharePoint Power Automate action filtering on a Yes/No field.

Reduce the JSON data from Get Items

The JSON returned by SharePoint is quite heavy, some of the the columns are not required and none of the metadata is needed for the grid. So a select action is used to reshape the data and create new column names:

You can really see the value of this when you compare one record from the “Get Items” action to one record from the select.

A single record from the select action looks like this:

  {
    "Part": "716/27600",
    "Description": "FUEL SENDER UNIT - P12",
    "Price": 67.75,
    "Stock": 25,
    "Product Group": "Standard Product"
  }

As you can see, it contains only the information required to build a grid in the browser.

Send the JSON response to the browser

The final step is just a HTTP response configured in the most basic way. It simply sends the output of the select action.

The entire flow consists of four steps and looks like this (Click to Zoom):

Power Automate flow that returns a JSON array to a browser

Build the Browser page for the grid

The table above was built using the DevExpress DataGrid, but the method is very similar for many jQuery based datagrid components. The amount of code is minimal.

The html for the grid consists only of a couple of DIVs:

<div class="demo-container">
  <div id="gridContainer"></div>
</div>

Everything else is done from jQuery. Three lines in the head section:

<link rel="stylesheet" href="https://cdn3.devexpress.com/jslib/20.2.3/css/dx.common.css">
<link rel="stylesheet" href="https://cdn3.devexpress.com/jslib/20.2.3/css/dx.light.css">
<script type="text/javascript" src="https://cdn3.devexpress.com/jslib/20.2.3/js/dx.all.js"></script>

And one script on the page that pulls the data from Power Automate:

<script>
jQuery(function(){
    jQuery("#gridContainer").dxDataGrid({
        dataSource: "https://prod-03.westeurope.logic.azure.com:443/workflows/...",
        columns: ["Part", "Description", "Price", "Stock", "Product Group"],
        showBorders: true,
		paging: {
            pageSize: 10
        },
        pager: {
            showPageSizeSelector: true,
            allowedPageSizes: [10, 25, 50, 100, 200]
        },
        remoteOperations: false,
        searchPanel: {
            visible: true,
            highlightCaseSensitive: true
        },
        groupPanel: { visible: true },
        grouping: {
            autoExpandAll: false
        },
		filterRow: { visible: true },
        allowColumnReordering: true,
        rowAlternationEnabled: true
    });
});
</script>

The datagrid component has many more options that I did not configure, and could have been setup with even less code than I used.

This is an easy to implement, but quite powerful use for Power Automate. If you only want to provide a small subset of data to users outside of your organisation then you don’t need to setup guest access to your environment, so it really is an easy solution.

Something to consider is that if you are going to do this on a webpage that gets many visitors per day then you are going to go through flow runs quite quickly. An alternative would be to run the flow on a schedule and output the JSON to a file which is publicly reachable. This will produce a faster response to the browser and save your flow usage.

If you would like to copy the source code, go to this demo page and view source in your browser.

Filed Under: Power Platform Tagged With: Power Automate

Add st, nd, rd and th (ordinal) suffix to a number in Power Automate

November 22, 2020 by Paulie Leave a Comment

Sometimes you need to create an ordinal number for use in an email or similar situation. Power Apps does not have any support for this type of number formatting but it can be created quite easily with an expression.

The rules are as follows:

  • st is used for numbers ending in 1.
  • nd is used for numbers ending in 2
  • rd is used for numbers ending in 3
  • all other numbers end in th except…
  • Numbers ending in 11, 12, or 13 use th.

In Power Automate you can generate an ordinal number with the following expression:

if(endsWith(string(outputs('NumbeToConvert')),'12'),formatNumber(outputs('NumbeToConvert'), '#th'),
if(endsWith(string(outputs('NumbeToConvert')),'13'),formatNumber(outputs('NumbeToConvert'), '#th'),
if(endsWith(string(outputs('NumbeToConvert')),'1'),formatNumber(outputs('NumbeToConvert'), '#st'),
if(endsWith(string(outputs('NumbeToConvert')),'2'),formatNumber(outputs('NumbeToConvert'), '#nd'),
if(endsWith(string(outputs('NumbeToConvert')),'3'),formatNumber(outputs('NumbeToConvert'), '#rd'),
formatNumber(outputs('NumbeToConvert'), '#th')))))))

Where outputs(‘NumbeToConvert’) is the value you want to replace. An example flow using the above:

Image of a Power Automate Flow that creates an ordinal number.

The result is obviously 102nd:

Result of a Power Automate Expression that creates an ordinal number.

The flow simply uses the endswith function to determine the correct ordinal reference.

Hope this helps.

Filed Under: Power Platform Tagged With: Power Automate

Working with locked documents in Power Automate Flows

November 21, 2020 by Paulie Leave a Comment

If you are updating the status of a document in a Microsoft Power Automate flow that someone is already using, you will get this error:

The file "filename.docx" is locked for shared use by [email protected]

and it will return a HTTP Status code of 400:

Image showing a Microsoft Power Automate Error condition when trying to update a locked file.

Below is a method that waits for the file to be free and then moves to the next step:

Wait for the file to be unlocked

Here is an example flow which updates the status of a file stored in SharePoint. If the file is available it will work normally. If it is locked it will loop for an increasing amount of time before attempting the operating again:

Image of a Power Automate Flow that deals with a file that is locked by another user.

Here is how it works:

  • A Boolean variable is created called “documentLocked” and its value is set to True. This variable is used to control the loop.
  • An integer variable called delaySeconds is created and its value is set to 1. This variable defines how long the loop will wait for before attempting to access the file again.
  • A do until loop is then started which will continue until the value of documentLocked is equal to false.
  • The “Update File Properties” action is executed as normal.
  • The next action is configured to run if “Update File Properties” was successful, or if it failed:
    Image of a set variable action that determines if a file is locked during a Power Automate flow
    An expression is used to determine the value of the variable based on the locked status of the document.
  • An increment variable action is then used to increase the delay time on the subsequent loop by 60 seconds. Each iteration of the loop will create a longer delay.

The expression used in the “Set Variable” step is:

if
(
  and
  (
    equals(outputs('Update_file_properties')?['body']?['status'],400), 
	contains(outputs('Update_file_properties')?['body']?['message'], 'locked')
  )
  ,true
  ,false
)

The condition checks to see if the output of the “Update File Properties” resulted in an error code 400 and the error message contained the word “locked”. If both of those conditions are true then documentLocked is set to true, otherwise it is set to false.

Both the status code and message are checked because HTTP Status code 400 is vague and the error might not actually be related to a file lock.

The expression in the delay action is:

if(variables('documentLocked'),variables('delaySeconds'),1)

This condition checks if the document is locked:

  • If it is locked, delay seconds is set to the value of the variable delaySeconds.
  • If it is free, then it is set to 1.

I’ve used this technique a number of times and it’s worked really well, and is simple to implement.

Filed Under: Power Platform Tagged With: Power Automate

  • Go to page 1
  • Go to page 2
  • Go to page 3
  • Interim pages omitted …
  • Go to page 5
  • Go to Next Page »

Primary Sidebar

Link to my LinkedIn Profile
Go to mobile version