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.
Leave a Reply