The following is a reprint of an article by Ian Sandler
published in the March/April 2000 issue of International Spectrum Magazine.

Integrating into the PC World case study: The AGES Group

Overview

Most of you own Pick applications that have been successfully meeting your company’s business data processing needs for a large number of years. Probably, you also have lots of PCs around doing the word processing, spreadsheet, imaging, and World Wide Web thing. Perhaps you have heard conflicting stories about how easy or hard it is to combine these worlds. In all likelihood, you have done some degree of integration yourselves, and are now looking for a more global, easier approach to use the next time. If so, read on. This article describes one approach that easily and seamlessly integrated these two worlds, adding significant value for the company that chose to follow that path.

Who is AGES

The AGES Group (Air Ground Equipment Services), a subsidiary of Volvo, sells surplus and aftermarket parts to the Aerospace industry. It owns, leases and manages commercial jet engines and aircraft, including a fleet of well over 200 small aircraft belonging to the US Army. It also works closely with a number of major players in the Aerospace industry. For example, AGES has a 130,000 sq ft warehouse in Seattle where Boeing stores its surplus inventory for AGES to manage and sell. (For more details see www.ages.com).

Note: As of June 15, 2001, The AGES Group was renamed Volvo Aero Services and their new web site is www.volvoaeroservices.com).

Evolution, not Revolution

AGES installed its original Microdata Reality system in 1983. Since then AGES has grown from approximately $20 million per annum turnover to approximately $450 million turnover in 1999. All of this time using the same custom application, which has of course been heavily enhanced and modified over the years. AGES is now owned by Volvo Aerospace, and has offices in approximately 20 countries around the world.

The original Microdata computer was replaced long ago, first by a multi-processor Unisys system running Universe, and later by a mid sized HP system also running Universe. The applications still provide excellent response times, even though they continue to run the same old PQN Procs and Basic programs, follow the same programming standards, and use the same file structures. All that has changed from this perspective is that the applications now do much more, and some of the files are now very large (multi-millions) of records.

What has changed though is that nowadays, in addition to the main Pick database, AGES also has a dedicated Microsoft Exchange server for handling email, a dedicated NT server running their ages.com web site, a dedicated Novell server for storing business documents, a dedicated NT server for storing scanned documents and other images, and another dedicated NT server for storing archived Pick reports. All of these systems shared the same high-speed network, and all share information with the greatest of ease.

Replacing Terminals with PCs

The dumb terminals are all gone, replaced by PCs running on a high performance local area network, with remote offices connected by a frame-relay/VPN wide area network. Each user has on their desktop one or more telnet terminal emulation sessions (AccuTerm from Accusoft Enterprises) which they use to log directly into the main Universe database, plus Microsoft office, Microsoft Outlook (email) and Microsoft Internet Explorer (www), plus various custom applications written in Visual Basic that interface directly into the existing Pick database. All of these resources are accessible from anywhere in the world, as mobile users can dial directly into the network from their laptops.

Upgrading to Laser/Barcode Printers

The old serial printers are long gone, replaced by two-sided HP LaserJet printers with installed postscript modules. These print all of the various forms directly from the existing Pick applications using a product called crteprnt which merges the Pick output with two-sided templates developed on a PC, and converts the results into postscript files. Copies of these documents are also automatically converted into PDFs at the same time and stored on a dedicated NT server so that they can then be displayed, printed, faxed and emailed using Adobe Acrobat Reader. These same printers also handle the printing for Microsoft Office etc. The various warehouses also make extensive use of barcode labels, which are printed by Zebra barcode printers.

Using RF Guns in the Warehouses

The warehouses use Symbol wireless guns to handle the various movements of the barcoded inventory. These are programmed in Pick Basic using the tools provided by Point Information Systems. Developing programs this way is particularly easy, allowing AGES where necessary to have two versions of the same program, one that works with the guns, and one that works with regular PCs. (Actually notebooks with wireless modems that share the wireless network with the guns).

EDI and WWW

AGES makes extensive use of industry standard EDI to share information, and this is handled by a PC based package. Even though the actual EDI package is not written in Pick, the outgoing transactions are generated by Pick applications, and the incoming transactions are parsed and stored in a relevant Pick file so that they can be processed as part of the normal data stream.

AGES also publishes its entire inventory (approx 2,500,000 part numbers) on its own in-house web site, along with various sets of information that inform customers of the status of their orders, shipments etc.

What is so special about the AGES Environment

What makes AGES unique is the way all of these elements are integrated together. In particular, the way data is shared, the way the terminal emulator works, the way that existing Pick reports are integrated with email, and the way the Pick data integrates with Visual Basic applications or Microsoft Office applications running on PC clients and the web.

Data Sharing

All data sharing is handled using NFS or dbObjects (connectivity software from VantagePoint Software). Although AccuTerm handles file transfers, that particular feature is never used to upload/download or convert data.

Instead, portions of the Pick database are NFS mounted by Novell and NT servers and visa versa. This allows a user on a PC to reference their Q: drive thinking it is either local to their PC or on the Novell server, when in actuality it is part of the HP’s local file system. Pick applications can read and write to this area of the disk either using Basic OPENPATH statements to reference the file space, or by creating file pointers and using normal OPEN statements. It is equally easy for the Pick applications to manipulate the various images stored on any dedicated NT server that supports NFS. For example, the image server’s disk is simply NFS mounted by the HP system, making it really easy for a Pick application to check if a particular image exists.

Foreign data normally passes through the AGES FTP site, which is once again NFS mounted by the HP computer running Universe. This allows files to be automatically uploaded/downloaded each evening to/from any other computer environment that supports the FTP protocol. This has proven to be a highly reliable method of passing large amounts of data to and from other large database systems.

All client/server interaction between Microsoft clients and the Pick Database is handled by dbObjects, which is very fast and allows multiple client applications to share the same Universe license, even when those clients are being used on different PCs.

Of particular importance is the fact that all of this data sharing is available to all processes all of the time. It does not require that you are running any specific software on your PC desktop. It works just as well for phantom Pick processes that are running without any terminal device, or for Internet clients running on an iMac.

Executing PC Applications as TCL commands

AccuTerm scripting combined with a File Display/Edit COM Server from VantagePoint Software allows existing Pick applications to display images, providing the application knows the address of the images. A new TCL verb (DISPLAY-IMAGE) allows you to specify the path to the image as a parameter. When this command is executed, it checks to ensure that it is being executed by AccuTerm, then uses AccuTerm’s VB scripting capability to instantiate the Display/Edit Server that calls the appropriate viewer software to display the image. In the case of a PDF document it calls the Adobe Acrobat viewer to display the image. This method is very robust, and works for almost all existing PC applications, not just Acrobat.

Using ENGLISH to publish information

AGES applications continue to make extensive use of ENGLISH (ACCESS/INFORM/RECALL) to produce reports. The version of ENGLISH used has been greatly enhanced to support a set of custom extensions that allow you to take an existing ENGLISH statement, replace the LIST/SORT keyword with EMAIL, add a couple of new phrases such as SEND-TO “iansandler” SUBJECT “This is a test” and the report arrives in the specified email inbox as an Excel spreadsheet. Adding the phrase “AS RTF” instead delivers the report as a word document. “AS HTML” delivers it as a HTML document, “AS TXT” delivers it as a tab delimited ASCII text file and so on.

EMAIL also allows the output to be posted directly to disk anywhere on the network. Adding a second address to the SEND-TO of the form \\sysa\disk1\reports99 causes the report to also be written to that specific file on that specific computer on the network, creating any required elements of the path if they do not exist. Add the phrase “NAMED testdoc” and the file that is created there is named “testdoc”.

What is actually going on here is that ENGLISH has been modified to produce a tab-delimited text file (a newer version will be using XML) rather than a printed report, and this file is written to a NFS shared disk where it is picked up by an application running on a NT server (Task Dispatch Manager from VantagePoint) that knows how to convert the file format and email it.

Task Dispatch Manager is a system for processing tasks that manage data within a network. A task within the system identifies a set of data, and a set of operations to be performed on that data. These operations include consolidation, conversion, and conveyance. Data from different sources can be consolidated into a packet, converted from one format to another, and transported to one or more destinations. The definition of what data to use, how to structure it, the format it should take, and where it should go is defined as a Task. Data sources can be any disk file. Destinations can be an email item, a public folder item in Exchange, a disk file, or a printer. Data can be converted from or to a number of supported formats. Data formats include, ASCII Text, Comma Separated Text files, Tab Delimited Text files, XML data, HTML tables, RTF files, PDF files, TIF files, Excel spreadsheets, ADO record sets, Dynamic String Array Objects, or Postscript printer files. Any of these data formats can then be stored as a zipped file. Tasks are defined using XML and are processed as a Task Packet. What this all means is that future versions of TDM will eventually allow existing Pick applications to produce and send documents containing multiple dissimilar attachments.

Archiving Reports

As well as sending emails to users, the AGES system also allows copies of reports to be archived in either of two common shared repositories. One option is to post a report to the Microsoft Exchange Public Folders. Once there, the report can be accessed by all users authorized to do so by the Microsoft Exchange administrator. This type of posting is achieved by using a SEND-TO address such as “Public Folders\AGES\End of Months\Dec99”.

The AGES system can also post reports to a custom report engine. Once there, the report can be accessed by all authorized users. The retrieval mechanism in this case uses the standard Microsoft Search Engine over the AGES private network (Intranet). Posting to the report engine is achieved using the “REPORT name” clause with the EMAIL verb. Access to the information in this case is controlled by a Pick file whose key is the name of the report. This record contains a list of users and groups who are allowed to see that file. One very nice feature of this report engine is that it allows the user to also look at archived copies of the same report, which are automatically preserved for a specific amount of time or number of back copies.

Using Pick data from within Visual Basic Applications

AGES makes extensive use of the Power Strings and dbObjects products from VantagePoint Software to handle the sharing of information between the Pick database and Visual Basic applications. dbObjects allows a VB program to call a Pick subroutine, passing and retrieving data. This is similar to working with stored procedures in one of the other relational database systems. One major strength of this approach is that the Visual Basic program does not need to know anything about how the data is stored. The other major strength is that performance is excellent, in some cases being several orders of magnitude better than ODBC. The Power Strings product allows the VB programmer to work with Pick data as dynamic string array data within the VB development environment.

Scanning Documents

This is handled by custom Visual Basic applications that make use of dbObjects to interact with the Pick Database, and store the images as PDFs in the image server. Adding new programs in this environment is very simple, allowing for easy customization. Typically, VB programs are launched from an icon on the user’s desktop. AGES instead uses a custom launcher integrated with the Pick database that presents each user with the set of VB applications that they are allowed to use. The advantage of the custom launcher is that the Pick system determines who is allowed to use which VB application.

Using Pick data from within Excel

Power Strings and dbObjects support VB scripting as well as regular VB. This has allowed AGES to customize their Excel spreadsheets to include an additional option on the main menu bar. This option calls a number of canned subroutines that return sets of data back from the Pick database. For example, it can return the list price, description and manufacturer for a column of part numbers. More importantly, it can also return the cost paid for these parts, but only to users who are authorized to see cost. All other users get a blank column for cost.

One point to note is that although AGES has no reason to do so, it would be almost trivial to interface the AGES database into Word, allowing for customization of user created documents. (Instead, AGES generates this type of document in Pick, which then emails or faxes it as a Word document).

Using Pick data from a Web application

AGES hosts its own Web site (www.ages.com or www.volvoaeroservices.com) that is connected over the local area network to the main Pick database using dbObjects. The advantages of this approach are enormous. Using Active Server Page technology and this type of database object technology, customers can quickly see views of the 30 gigabyte database that are relevant to their needs, based on the company they work for and the products they are interested in. They can ask for quotes and place orders while interacting directly with the same exact database as an AGES salesperson talking to them on the phone.

They can also get to see copies of relevant documents stored on the image server, and can look up the current status of an order, or link from a shipping document directly to the carrier to find out where the goods are. Best of all, these lookups work for all business transactions between that customer and AGES, not just for business done over the Web.

Standard Pick Applications

Development using standard Pick-Basic and standard screens is also a part of the AGES application environment. These applications are accessed using AccuTerm over the network. The advantage of mixing the standard character based Pick type applications with the newer graphical Windows type applications allows AGES to leverage existing programs as well as providing character based applications when a graphical application is not necessary.

The Future

The future should be a continuation of the immediate past. More and more data stored in the Pick database. Most existing business applications remaining in Pick, sometimes getting a facelift with added VB scripted functionality. Some new applications developed in Pick, some in VB and some in Active Server Pages (web) depending on the business requirements. A continued commitment to open standards and a standardized Microsoft desktop. More effort to retain everything and derive added value from the existing information and components.

Questions

If you have any questions about the tools and techniques described in this article, please email them to iansandl@ages.com.