Power Automate

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:

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:

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:
  
  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.

When you use the “List rows present in a Table” action in Power Automate to get data from Excel that contains dates, you might be surprised to see that the data in the date column is actually an integer and not a date.

Take a look at this Excel table:

In the example above the both the Date column and the Int Date column contain the same data, but with different display formats. This is because Excel stores dates as an integer. The integer represents the number of days that have elapsed since the 1st January, 1900. In Power Automate, the first record of this table is represented like this in JSON:

{
	"Order Ref": "6075",
	"Value": "11223.16",
	"Date": "44126",
	"Int Date": "44126"
}

In JSON representation, you can clearly see that Date and Int Date hold the same value.

Typically you would want to use that date field to filter, compare or insert data to some other format. So let’s look at an example.

Filter by Excel Date Column

In this example I want to get data from Excel and filter it so that only the rows that match the date 22-10-2020 remain (of which there is only one).

The expression on the left side, which converts the Excel date is:

addDays('1899-12-30', int(item()['Date']), 'dd-MM-yyyy')

Explanation:

The addDays function adds a specified number of days to a given date. It’s second parameter requires an integer, so the int() function is used to convert the JSON string to an int.

The final parameter specifies the date format of the newly formatted date.

You can use exactly the same expression inside of an if condition to only perform operations on records matching a particular date.

If possible, it is better to use a filter before a loop as the performance is much better within a filter compared to a loop.

Validate the Excel data first

One of the problems that can come up when using filtering Excel data based on a date column is that the int function can error easily. Take a look at the following example:

Cells E4 and E6 will generate the following error if they are used in the example expression above:

The template language function 'int' was invoked with a parameter that is not valid. The value cannot be converted to the target type.

This sort of situation can happen easily when you have a data source like Excel. One thing I like to do is add an additional hidden column in Excel that has a formula like the following:

=ISNUMBER([@[Delivery Date]])

Because Excel is actually storing the numbers as dates, this evaluates to either true or false:

The invalid dates can then easily be filtered in Power Automate:

This test isn’t fool proof (for example there could easily be a valid number in there which isn’t a valid date) but it is a good start. Of course you can easily check for blanks etc in Power Automate without using this hidden column technique.

Create an Excel Integer Date from a Power Automate Date

Sometimes you might want to do the opposite function and create an Excel style integer date from a Power Automate date. For example, Excel date 22nd November 2020 is represents as 44157. To create that integer in Power Automate from the same date you can use the expression:

div
(
  sub
  (
    ticks(formatDateTime('2020-11-22', 'yyyy-MM-ddT00:00:00')), 
	599264352000000000), 
  864000000000
)

or for todays date you could use:

div
(
  sub
  (
    ticks(formatDateTime(utcNow(), 'yyyy-MM-ddT00:00:00')), 
	599264352000000000), 
  864000000000
)

A little explanation:

The ticks function returns an integer which represents the number of 100-nanosecond intervals, since January 1, 0001 12:00:00 midnight.

599264352000000000 is the tick value which represents 30th December 1899, which is the start date of Excel Dates.

So the number of ticks in the specified date is subtracted from the Number of ticks for the 30th December 1899 and then divided by 864000000000 which gives you an integer which replicates what Excel would produce!

I hope this helps you to convert your Excel date for use in Power Automate. Good luck!

Many legacy ERP systems produce printed output in plain text format. Over the years I’ve worked with many of these types of systems, and there are still lots of them in use today. Often they are hosted on:

• IBM AIX, pSeries and RS/6000
• IBM AS/400
• Many different flavours of Unix and Linux (Solaris, SCO Openserver, HP-UX, NCR, Tandem)

Mostly they run ERP software based on platforms typical for their day, such as Progress 4GL, Kerridge KCML, Pick Software, IBM Informix, Oracle etc.

The default method of output is quite limiting and as a result, software vendors have produced software that not only enhances the output, but also introduces workflows and document management. Software such as:

• Kerridge / ADP Print Manager
• PlanetPress Suite
• ROC Easyspooler
• Octotools
• Formate eVo

Essentially all of these bits of software are designed to take the output from a legacy system, reformat the data, produce new output and do something with it (Store it/email it etc).

All of these tools have two things in common:

• They are somewhat expensive, although often provide incredible value.
• Require server infrastructure and software to operate.

Given my experience with these types of software and my recent love affair with Power Automate, I’ve been experimenting to see if I could replicate the functionality without using any 3rd party software at all. Ideally, completely serverless.

Objectives

The primary objectives are:

• Transform the incoming print data into JSON
• Create a new document in PDF format
• Store the document with metadata and make it easy to find
• Enrich the legacy system data
• Send the new document via email

In addition to the above, because some legacy systems have limited reporting capability I have also added the data retrieved from the document to an Azure SQL Instance for easy reporting and integration with PowerBI.

Transform the incoming print job

Here is our sample print data, it is:

• 92 Columns Wide.
• 70 Lines (rows) per page.
• 2 Pages Long.

                                                                         Page No:       1
                                                                        Inv no.  00102707


                                                                          Acc no.   0000024
                                                                          Date   30/04/2020

  Invoice to:                                              Deliver to:
  CENTRAL PARTS                                            CENTRAL PARTS                   
  653 ROUTE DE SANDILLON                                   653 ROUTE DE SANDILLON          
  45560 ST DENIS EN VAL                                    45560 ST DENIS EN VAL           
                                                                                           
  FRANCE                                                   FRANCE                          
                                                                                           

                                                           VAT NO:  FR86329130660          

    Order no.                  Order Date                   Our Ref
     ORLEANS                   24/04/2020                   087991    

  Part No.           Description                         Qty    Price     Disc      Value

  02/200073           HOSE*                               1    12.00      0.00      12.00
  02/200147           THERMOSTAT GASKET                   1     0.66      0.00       0.66
  02/202278           GASKET - ROCKER COVER P12           1     4.50      0.00       4.50
  1450/0002           GREASE NIPPLE                      10     0.25      0.00       2.50
  15/920388           SERVO ASSEMBLY                      1   120.00      0.00     120.00
  32/925914           FILTER FUEL *                       1    82.50      0.00      82.50
  32/925950           FUEL FILTER*                        2    16.00      0.00      32.00
  320/07382           FUEL FILTER*                        2    20.00      0.00      40.00
  332/C5605           RETAINER - DOOR HOLD BACK           1    30.00      0.00      30.00
  332/F4780           FILLER CAP*                         1    15.00      0.00      15.00
  595/10027           PISTON HEAD NUT                     1    25.00      0.00      25.00
  701/60014           COLD START SENDER                   1     8.00      0.00       8.00
  701/80298           SWITCH FWD & REV P21 P/SHIFT        1    87.50      0.00      87.50
  716/12400           FUEL TANK SENDER UNIT               1    20.00      0.00      20.00
  716/25800           RELAY FLASHER*                      1     6.00      0.00       6.00
  813/00419           GASKET-EXHAUST FLANGE*              1     0.50      0.00       0.50
  826/01182           DOWEL*                              1     0.80      0.00       0.80
  827/30482           GLASS - RH REAR QTR T.D.*           1    60.00      0.00      60.00
  827/30566           DOOR GLASS UPPER LH LATE P12*       1    38.00      0.00      38.00
  827/80212           GLASS -RH SIDE*                     1    65.00      0.00      65.00
  827/80235           GLASS *                             1    80.00      0.00      80.00
  827/80236           GLASS SLIDING*                      1    45.00      0.00      45.00
  827/80269           SIDE WINDOW - LH P21*               1    40.00      0.00      40.00
  834/00194           WATER HOSE - BOTTOM*                1    15.00      0.00      15.00
  834/00261           WATER HOSE - TOP*                   1     6.00      0.00       6.00
  834/00263           WATER HOSE - BOTTOM*                1    18.00      0.00      18.00
  834/00666           HOSE - ELBOW*                       1     3.00      0.00       3.00
                                                           Carried forward       856.96




















                                                                         Page No:       2
                                                                        Inv no.  00102707


                                                                          Acc no.   0000024
                                                                          Date   30/04/2020

  Invoice to:                                              Deliver to:
  CENTRAL PARTS                                            CENTRAL PARTS                   
  653 ROUTE DE SANDILLON                                   653 ROUTE DE SANDILLON          
  45560 ST DENIS EN VAL                                    45560 ST DENIS EN VAL           
                                                                                           
  FRANCE                                                   FRANCE                          
                                                                                           

                                                           VAT NO:  FR86329130660          

    Order no.                  Order Date                   Our Ref
     ORLEANS                   24/04/2020                   087991    

  Part No.           Description                         Qty    Price     Disc      Value

                                                           Brought forward       856.96
  904/20336           CLAMP SEAL*                         4     4.25      0.00      17.00
  991/00130           SEAL KIT - 100 X 60*                1     7.00      0.00       7.00

























                                                                                   880.96
                                                                  CARRIAGE           0.00

                                                                  VAT                0.00

                                                                                   880.96
 BEAUVAIS TRANSIT F.O.B.                    
 176x36x136cms= 123Kgs W/case               
 82x62x43cms= 23Kgs Pal ctn                 

Using some string manipulation, Power Automate transforms the incoming document to JSON and produces this output:

{
  "name1": "CENTRAL PARTS",
  "address1": "653 ROUTE DE SANDILLON",
  "address2": "45560 ST DENIS EN VAL",
  "address3": "",
  "address4": "FRANCE",
  "address5": "",
  "name2": "CENTRAL PARTS",
  "address2_1": "653 ROUTE DE SANDILLON",
  "address2_2": "45560 ST DENIS EN VAL",
  "address2_3": "",
  "address2_4": "FRANCE",
  "address2_5": "",
  "docRef": "00102707",
  "docDate": "30/04/2020",
  "orderDate": "24/04/2015",
  "customerCode": "0000024",
  "ourRef": "087991",
  "customerRef": "ORLEANS",
  "carrier": "",
  "additionalText1": "BEAUVAIS TRANSIT F.O.B.",
  "additionalText2": "176x36x136cms= 123Kgs W/case",
  "additionalText3": "82x62x43cms= 23Kgs Pal ctn",
  "totalNetValue": "880.96",
  "carriage": "0.00",
  "vat": "0.00",
  "net": "",
  "total": "880.96",
  "lineData": 
  [
    { "partNo": "02/200073", "desc": "HOSE*", "price": "12.00", "dsc": "0.00", "qty": "1", "value": "12.00" },
    { "partNo": "02/200147", "desc": "THERMOSTAT GASKET", "price": "0.66", "dsc": "0.00", "qty": "1", "value": "0.66" },
    { "partNo": "02/202278", "desc": "GASKET - ROCKER COVER P12", "price": "4.50", "dsc": "0.00", "qty": "1", "value": "4.50" },
    { "partNo": "1450/0002", "desc": "GREASE NIPPLE", "price": "0.25", "dsc": "0.00", "qty": "10", "value": "2.50" },
    { "partNo": "15/920388", "desc": "SERVO ASSEMBLY","price": "120.00", "dsc": "0.00", "qty": "1","value": "120.00" },
    { "partNo": "32/925914", "desc": "FILTER FUEL *", "price": "82.50", "dsc": "0.00", "qty": "1", "value": "82.50" },
    { "partNo": "32/925950", "desc": "FUEL FILTER*", "price": "16.00", "dsc": "0.00", "qty": "2", "value": "32.00" },
    { "partNo": "320/07382", "desc": "FUEL FILTER*", "price": "20.00", "dsc": "0.00", "qty": "2", "value": "40.00" },
    { "partNo": "332/C5605", "desc": "RETAINER - DOOR HOLD BACK", "price": "30.00", "dsc": "0.00", "qty": "1", "value": "30.00" },
    { "partNo": "332/F4780", "desc": "FILLER CAP*", "price": "15.00", "dsc": "0.00", "qty": "1", "value": "15.00" },
    { "partNo": "595/10027", "desc": "PISTON HEAD NUT", "price": "25.00", "dsc": "0.00", "qty": "1", "value": "25.00" },
    { "partNo": "701/60014", "desc": "COLD START SENDER", "price": "8.00", "dsc": "0.00", "qty": "1", "value": "8.00" },
    { "partNo": "701/80298", "desc": "SWITCH FWD & REV P21 P/SHIFT", "price": "87.50", "dsc": "0.00", "qty": "1", "value": "87.50" },
    { "partNo": "716/12400", "desc": "FUEL TANK SENDER UNIT", "price": "20.00", "dsc": "0.00", "qty": "1", "value": "20.00" },
    { "partNo": "716/25800", "desc": "RELAY FLASHER*", "price": "6.00", "dsc": "0.00", "qty": "1", "value": "6.00" },
    { "partNo": "813/00419", "desc": "GASKET-EXHAUST FLANGE*", "price": "0.50", "dsc": "0.00", "qty": "1", "value": "0.50" },
    { "partNo": "826/01182", "desc": "DOWEL*", "price": "0.80", "dsc": "0.00", "qty": "1", "value": "0.80" },
    { "partNo": "827/30482", "desc": "GLASS - RH REAR QTR T.D.*", "price": "60.00", "dsc": "0.00", "qty": "1", "value": "60.00" },
    { "partNo": "827/30566", "desc": "DOOR GLASS UPPER LH LATE P12*", "price": "38.00", "dsc": "0.00", "qty": "1", "value": "38.00" },
    { "partNo": "827/80212", "desc": "GLASS -RH SIDE*", "price": "65.00", "dsc": "0.00", "qty": "1", "value": "65.00" },
    { "partNo": "827/80235", "desc": "GLASS *", "price": "80.00", "dsc": "0.00", "qty": "1", "value": "80.00" },
    { "partNo": "827/80236", "desc": "GLASS SLIDING*", "price": "45.00", "dsc": "0.00", "qty": "1", "value": "45.00" },
    { "partNo": "827/80269", "desc": "SIDE WINDOW - LH P21*", "price": "40.00", "dsc": "0.00", "qty": "1", "value": "40.00" },
    { "partNo": "834/00194", "desc": "WATER HOSE - BOTTOM*", "price": "15.00", "dsc": "0.00", "qty": "1", "value": "15.00" },
    { "partNo": "834/00261", "desc": "WATER HOSE - TOP*", "price": "6.00", "dsc": "0.00", "qty": "1", "value": "6.00" },
    { "partNo": "834/00263", "desc": "WATER HOSE - BOTTOM*", "price": "18.00", "dsc": "0.00", "qty": "1", "value": "18.00" },
    { "partNo": "834/00666", "desc": "HOSE - ELBOW*", "price": "3.00", "dsc": "0.00", "qty": "1", "value": "3.00" },
    { "partNo": "904/20336", "desc": "CLAMP SEAL*", "price": "4.25", "dsc": "0.00", "qty": "4", "value": "17.00" },
    { "partNo": "991/00130", "desc": "SEAL KIT - 100 X 60*", "price": "7.00", "dsc": "0.00", "qty": "1", "value": "7.00" }
  ]
}

As you can see from the above, the metadata and all of the line details have been captured.

Create the PDF document with the word connector

The word connector is then used to produce a new document based on the JSON data (Click to download the PDF). The Word document is then transformed to PDF using the action “Convert Word Document to PDF”.

I wrote a separate article on using the Word connector to generate PDF documents if you’d like to read how that works.

Word offers comprehensive formatting options, but the process of designing the document can be quite time consuming.

Store the document and metadata

At this stage in the flow, the process contains:

• The original text based spool/print document.
• An editable word document.
• A PDF of the word document.
• Lots of interesting metadata.

An item is created in a SharePoint list using the metadata obtained from the print file and creates file attachments of .txt .docx and .pdf. The original text document is useful for verification purposes and the Word document is useful if you want to manually edit the final result.

The list and attachments are indexed so document retrieval is very easy.

Here are the documents being presented in Microsoft Lists

Adding the list into Microsoft Teams tabs means that users can instantly access spool documents without even going to the Office 365 portal, directly from their desktop or mobile device.

Enrich the Legacy System Data

Many legacy systems don’t have an email address field, sometimes it is very short.

In this example I have created a separate list to contain customer information. The flow checks if that customer already exists based on their customer code, and if not creates a new entry.

The entry can then have additional details added (email address, mobile number etc). For use in further actions.

Send the document

Next, Power Automate sends the PDF file to its destination based on the email address stored against the customer record, if there is no email address found then it can be sent to an internal recipient or printed.

I use SendGrid dynamic templates for sending email which produce a really nice result on mobile or desktop platforms:

SendGrid is brilliant for sending transactional emails because it enables you to track if it has been delivered or read and handles high volumes easily.

I wrote a post on how to use Power Automate with SendGrid Dynamic Templates here.

Analyse the Data

In addition to the actions above, the customer and line data is inserted into an Azure SQL Database. This won’t always be required or useful, but many legacy systems do not have sophisticated reporting capabilities. So for some, it can really create a lot of extra value.

Here is the invoice data in Azure SQL, which I have left mostly de-normalised for reporting purposes.

How is it done?

Obviously the flow actions for this are highly dependent on the incoming document type, and the capabilities of the originating system. There are many different ways of triggering the process and transmitting the print data to Power Automate. Here is my flow to give you an idea of the steps involved:

Endless Possibilities

Power Automate can work well as a pseudo print server for legacy applications. Microsoft Teams makes it easily accessible and once you have parsed your document data the possibilities are endless. Some ideas:

• Send a text message to a customer when an order is dispatched using Twillo.
• Send an order confirmation email to a customer when an order is created and alert you when they have confirmed.
• Create a to-do task when a pick list is created and assign it to a Warehouse group for completion.
• Stream the data to a Power BI streaming dataset and have a real time sales dashboard.
• Use a payment API when an invoice is created to send a “pay by email” link to a customer and a be notified when the invoice is paid.
• Automatically send Purchase Orders to suppliers and create a payment task for the accounts payable team.

This method lacks some of the features that software dedicated to this purpose has, but on the other hand it has many features and possibilities that they do not.

Power Automate is much less expensive, completely serverless and opens the door to adding completely new functionality and additional value to Legacy systems.

The Create HTML Table action in Power Automate is a useful tool, but lacks any formatting or styling options. In this post I am going to explain how to format the HTML table produced by Power Automate. Here is an example of the default output:

In my opinion:

• The table is just ugly.
• There are no column alignment options.
• If you are using custom columns, it does not allow you to put spaces in the column headers.

The good news is that the Create HTML Table action produces basic HTML that can be easily modified or styled. The HTML source of the table generated above by Power Automate above is:

<table>
   <thead>
      <tr>
         <th>FirstName</th>
         <th>LastName</th>
         <th>JobDescription</th>
         <th>Furloughed</th>
         <th>FurlougedDate</th>
      </tr>
   </thead>
   <tbody>
      <tr>
         <td>Paul</td>
         <td>Murana</td>
         <td>Power Automate Developer</td>
         <td>No</td>
         <td></td>
      </tr>
      <tr>
         <td>Micheal</td>
         <td>Knight</td>
         <td>Lone Ranger</td>
         <td>Yes</td>
         <td>1986-04-04</td>
      </tr>
      <tr>
         <td>Peter</td>
         <td>Venkman</td>
         <td>Ghost Hunter</td>
         <td>No</td>
         <td></td>
      </tr>
      <tr>
         <td>John</td>
         <td>Rambo</td>
         <td>Mercenary</td>
         <td>No</td>
         <td></td>
      </tr>
   </tbody>
</table>

Use CSS to create a much more attractive table

By adding some CSS, you can make the table much more attractive very quickly:

The simple CSS I applied to the above is:

<style>
Table {
  font-family: Arial, Helvetica, sans-serif;
  background-color: #EEEEEE;
  border-collapse: collapse;
  width: 100%;
}

Table td, Table th {
  border: 1px solid #ddd;
  padding: 3px 3px;
}

Table th {
  font-size: 15px;
  font-weight: bold;
  padding-top: 12px;
  padding-bottom: 12px;
  text-align: left;
  background-color: #1C6EA4;
  color: white;
}
</style>

If you aren’t a CSS wizard you can use https://divtable.com/table-styler/ to get you started.

So now you have got the CSS, here is how to add it to the table:

• Create a compose step called CSS Table Style.
• Paste your CSS into that compose action.
• In your Send an email action paste the CSS and then the HTML Table:
  
  

You can put anything else you need in the body, but I would suggest that you put the CSS before anything else.

Add spaces to the column headings

In my example, I used custom columns and I was unable to add spaces to the column headings. So here is my workaround:

• Use a carat ^ symbol wherever you want a space in the column header.
• Create a compose action to replace the carat with a space.
• Select the output of the compose in your email instead of your HTML Table output:
  

The compose expression is:

replace(body('Create_HTML_table'), '^', ' ')

You could of course use any symbol, but make sure to use one that isn’t going to be in use elsewhere in the table. The carats are replaced with spaces:

You can take this much further by not using the Create HTML Table action at all, and constructing your own table directly in Power Automate and looping through an array of records. This is something I do sometimes, but it is a lot more work and I wanted to quickly cover how to improve the look of the standard action.