• Skip to primary navigation
  • Skip to main content

Tachytelic.net

  • Get in Touch
  • About Me

Use Power Automate to modernise Legacy System Processes and Output

November 11, 2020 by Paulie Leave a Comment

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.

Preview of PDF created by Power Automate using the print output of a legacy Unix system.
Preview of PDF Produced by Power Automate Word Connector

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

Image of documents processed by Power Automate stored in a SharePoint list

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.

Image showing data from a legacy system being inserted into a SQL Database by Power Automate.

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:

Image of a flow that transforms plaint text printer output into a PDF

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.

Filed Under: Power Platform Tagged With: Power Automate

Reader Interactions

Leave a Reply Cancel reply

Go to mobile version