Works Records System
The Works Records System (WRS), was an IBM mainframe based spreadsheet designed by Dr. Robert Mais, then an employee of ICI Mond Division in the UK and developed for the company to monitor chemical plant operations at Runcorn and elsewhere in the group. The design was unusual at that time, since it allowed chemical engineers (who were not programmers) to design and create their own interactive applications using a "kit of components" that incorporated mathematical formulae directly linking input fields to calculated output fields in WYSIWYG fashion.
The WRS was implemented in 1974 by a team that included Ken Dakin, author of several successful CICS debugging products that were used extensively during its development crucially to ensure the highest possible performance by detecting "hot spots" (high execution locations) during code execution. All operations were performed using "double precision" floating point arithmetic and user specified formulae that performed calculations and linked cells together. These cells could be in the same sheet (I/O screen) or in completely separate, "remote" spreadsheets in a different application. Formulae could be entered on multiple lines to aid comprehension and used a syntax similar to Fortran (using the familiar infix notation but with numeric DIR identifiers taking the place of symbolic variable names).
The "Shunting yard algorithm" invented by Edsger Dijkstra, was used to parse these formulae into Reverse Polish notation (RPN). The resulting RPN formulae were converted (compiled) to machine language snippets "on the fly" on first use and then stored for subsequent executions (see Memoization).This technique is now known as Just-in-time compilation (JIT) or, more specifically, "incremental compilation" - but given no label at the time. The instructions were all "built" and executed in CICS dynamic storage - unique for each transaction "thread" (i.e. single user instance of input/processing/output) - to comply fully with the requirements for CICS applications to be quasi-reentrant.
Unlike today's desktop or web-based spreadsheets, multiple input cells could be keyed before committing the central processor to beginning a new task (thread) and performing the more complex validity checks (involving multiple input cells for instance) and calculation or re-calculation. This only ever occurred when the enter key was pressed and only after the modified data was transmitted from the terminal to the mainframe. Thus the overall transaction rate for the CICS transaction processor was significantly reduced, by delays inherent in any manual data entry system, without a corresponding drop in speed of data entry.
If any prerequisite data values were missing (perhaps because a particular tank measurement was unavailable at the time), calculations were postponed until the data was entered. This resulted in a cascade of background calculations that automatically updated (identified) dependent calculated values as soon as that missing input value was keyed. These background calculations were executed asynchronously as CICS "transient data" initiated tasks (therefore only affecting users who might happen to be looking at the particular dependent results at that time).
Historical data accumulated organically to form a database of earlier periods on a shift/day/week/year basis - which in turn fed naturally into cumulative sheets for larger periods. This automatically imbued the spreadsheets with depth. Data including "aged" values was stored using a combination of a BDAM and an Adabas database (described as a “relational like" database in the Wikipedia article about Adabas, although its relational properties were not fundamental to the operation of the system). The user could examine historical data and even enter earlier "missing" data from earlier periods, causing automatic re-calculations in subsequent sheets (to more accurately reflect a chemical plants actual efficiency for instance).
Data that was entered in a particular sheet effectively resided independently of the input sheet it was entered on, as did the formulae (known as "function groups"). The concept of separation of data, input and calculation fully presaged the method used in Lotus Improv - 16 years later and considered at the time (in 1990) a major revelation.
(Similar functionality can be achieved with today's spreadsheets but only by using repetition of rows/columns or sheets together with considerable duplication of formulae for each of the periods to be covered - however "future" periods need to be anticipated or added later - as extra rows/columns or sheets.)
The 3270 workstation did not support full graphic capability but histogram displays of culmulative column data was optionally provided by displaying vertical columns of X's in response to a program function key toggle.
The IBM 3270 workstation chosen for its implementation at the time was a new "breed" of not so dumb terminals which had some basic built-in hardware validity checking such as 'numeric only' input fields. The 3270 hardware also came equipped, as standard, with the ability to "physically" update a small section of the remote screen buffer (including its text colour, background, input behaviour and other attributes) using a "Write" (modified) command, instead of needing to retransmit the entire screen buffer on every change (anticipating Ajax software technology that re-emerged some 22 years later for web 2.0 based applications and now utilized for online spreadsheets and most other recent applications for similar reasons).
The 3270 Model 2 had 24 rows of 80 columns (1920 characters) and, ignoring rows/column headers, the WRS therefore permitted up to around 160 ten digit data values (e.g. 9999999.99) to be displayed simultaneously on the screen (this contrasts with the later Visicalc spreadsheet implementation for the Apple II that had only a 25 x 40 window for displaying values and a single data entry field on the command line). Data could be keyed directly into, in a genuine WYSIWYG manner, or output to, any one or more of these (3270 attribute tabbed) fields.
All of the WRS was programmed in IBM Basic assembly language and the operating environment was initially that of a "standard" Macro level CICS pseudo conversational application running on a typical IBM System/370 MVS operating system. As keyed data was read from the 3270 device, it was routed to a specifically related "DIR record" (essentially a cell variable). After all input was processed for the particular I/O screen, a "function group" (a list of formulae pre-compiled to a single concatenated executable machine code string - using the shunting yard algorithm - as in the example below), was executed to update other dependant DIR records. In this simple illustration, the three relevant 8 byte DIR values (, and ), are considered to be located in contiguous memory locations (and pointed to by general purpose register 1). The example shows first addition then subtraction to create DIR#3. The machine code string was "loaded" dynamically into thread storage (dynamic memory) and "called" using the equivalent of a BALR 14,15 instruction (after setting the address of the start of the string in R15).
Example snippet code (Add and Subtract)
|ADD "=+"...... (c=a+b)||SUBTRACT "=-"...... (c=a-b)|
* Floating point registers 2(/3) and 4(/5) participate in the addition HEX snippet (16 bytes), R15 points to start 68201000 LD 2,0(1) load Op1 (long FPR 2/3) - DIR#1 68401008 LD 4,8(1) load Op2 (long FPR 4/5) - DIR#2 2A24 ADR 2,4 Add double, Op2 to Op1, normalized 60201010 STD 2,16(1) store result from Op1 - DIR#3 07FE BR 14 return
* Floating point registers 2(/3) and 4(/5) participate in the subtraction HEX snippet (16 bytes), R15 points to start 68201000 LD 2,0(1) load Op1 (long FPR 2/3) - DIR#1 68401008 LD 4,8(1) load Op2 (long FPR 4/5) - DIR#2 2B24 SDR 2,4 Subtract double, Op2 from Op1, normalized 60201010 STD 2,16(1) store result from Op1 - DIR#3 07FE BR 14 return
Trigonometric and logical operations
As well as the basic arithmetic operators (+,-,*,/), the WRS also supported trigonometric functions - square root, sin & cosine as well as logical operations. Intermediate results were always held within the floating point registers and the final results only updated at the end of a function group. These pre-assembled concatenated machine code snippets might therefore include loops and conditional tests as appropriate, all built in JIT fashion, "on-the-fly", as the function group is first keyed, compiled and perhaps then altered by the user; then ultimately saved for re-use (memoization).
Because the WRS held its data cells independently of the screen mapped data (identified by a unique integer; its 'DIR' number), there was no theoretical limit to the number of "cells" that could be supported by any one spreadsheet (application). The practical limit was simply the physical file capacity made available for the particular application. Similarly, there was no theoretical limit to the number of "sheets" (I/O screens) that could be linked to forming the complete application.
The output from any WRS application (e.g. a single chemical plant calculation) could be utilized as input to any other WRS application, providing distributed data flow across the connected systems. This is made possible simply because each datum was identifiable by its unique DIR number. Input was alternatively also allowed from pre-existing batch systems via an externally programmed interface that directly updated specific DIR's. Function groups that updated calculated DIR's (rather than directly keyed) were automatically triggered when all the relevant input data was available from the various sources.
Despite the limitations of the 3270 device, the input/output screens (or sheets) could nevertheless be designed interactively by non programmers by using simple "<" and ">" as "field" (cell) delimiters during "the design phase" (building the spreadsheet) as markups. As with modern day word processors, these "tab characters" would not normally be visible during normal usage. The same technique was used to define "on screen" the layouts of printed reports that were not limited to the 80 column screen width of the 3270. Column and row headers were uniquely identified as stored text words (or phrases) and could appear anywhere on the I/O screen.
The system was capable of detecting some illogical operations because of a "units" attribute (such as "kilograms", "ounces", "feet" or "inches") for numeric values (analogous to currency symbol attributes in today's spreadsheets). It was impossible therefore to multiply kilograms by ounces or commit similar logic errors. (By contrast, today's commercial spreadsheets will allow a column of mixed currencies such as pounds & dollars), to be summed or multiplied without even a warning!)
The Works records system, which went "live" in 1974 represented the first known use of:-
- an interactive online spreadsheet,
- a three-dimensional spreadsheet and
- a shared public spreadsheet
since it allowed multiple users to access multiple linked (or "remote") spreadsheets across a private online network covering many remote locations and also used time as a "3rd dimension" to give sheets depth. The System was also an early example of a fully interactive 4GL language - before the term was coined - since a user entered the symbolic language that went on to generate the code to be executed in real time.
- History of spreadsheets by Ken Dakin
- Silverlight demo of Shunting yard algorithm in codeding.com article "expression evaluation"
- IBM System/360 Principles of Operation manual
- Mais, Dr. Robert. Imperial Chemical Industry(ICI),The Works Record System (1974)., 3.1. (hardcopy in The Computer History Museum, CA 94043-1311, Catalogue Accession Number 102746930