Point and shoot interface for linking database records to spreadsheets whereby data of a record is automatically reformatted and loaded upon issuance of a recalculation command

A spreadsheet program providing the combination of spreadsheet and database tools, and enabling users to develop an application environment that can use all resources in the spreadsheet storage area. The spreadsheet program comprises three storage areas: a spreadsheet data structure; a set of database files in a storage area, and a video display system. The spreadsheet data structure is divided into two sets of cells that can be operated through three different environments. The first environment contains the tools of a spreadsheet environment, and the first set of cells can be operated through the first environment. This is most appropriate for scratchpad uses. The second environment contains the tools of a spreadsheet environment and contains a special command that can define commands for the third environment. This is appropriate to develop formulas for an application, and the application's environment of operation. The second environment also contains the tools necessary to define special environments for the integrated operation of spreadsheet and database tools. Said integrated operation coordinates dynamic screen video operations of the information in the spreadsheet and database storage areas. The second set of cells (for the development and use of an application) can be operated through the second or third environments. Several environments for the integrated operation of spreadsheet and database tools can exist in this invention. The software enables users to develop and use full database applications such as integrated accounting packages, within spreadsheet environments.

BACKGROUND OF THE INVENTION 
The invention relates to the field of enhancements for spreadsheet software 
instruments. More specifically the invention relates to an improved 
environment and data interface for spreadsheet and database instruments. 
Heretofore three kinds of instruments had been involved with a similar idea 
as that dealt with in the invention: multitasking environments (A), 
database systems (B) and database tools for spreadsheet instruments (C). 
A. Referring to MULTITASKING ENVIRONMENTS, the DESKVIEW.TM. multitasking 
environment, by Quarterdeck Office Systems, and the MICROSOFT WINDOWS.TM. 
graphical user interface and multitasking environment, by Microsoft 
Corporation, allow applications such as spreadsheets and database programs 
to transfer information from one to another by data exchange services. But 
this communication between processes is slow and limited to information 
displayed in the video screen. 
B. Referring to relational DATABASE SYSTEMS, some of these let the user 
build entry tables that are similar in appearance to spreadsheet tables. 
The FOXBASE+.TM. and the FOXPRO.TM. programmable database file managers, 
by Fox Software Inc., call them browse tables. There are also other 
systems, such as the ADVANCED REVELATION.TM. programmable database file 
manager, by Revelation Technologies Inc., that let the user write small 
programs to perform field calculations that involve formulas and numerical 
operations. The LOTUS/DBMS.TM. spreadsheet/database integrative program, 
by Lotus Development Corporation, is one such relational database systems. 
A publication in the LOTUS.TM. industry-specific magazine, by Lotus 
Development Corporation, April 1988, page 5, says that the LOTUS/DBMS.TM. 
spreadsheet/database integrative program, by Lotus Development 
Corporation, "will share a common menu structure and programming 
capability with Lotus Development's forthcoming graphical spreadsheet" 
(referring to the LOTUS 1-2-3/G.TM. spreadsheet program, by Lotus 
Development Corporation). "This means, for example, that (the user) will 
be able to move data between the database and the spreadsheet without 
reformatting or converting." "[Besides] An add-in product will allow you 
to use LOTUS 1-2-3.TM. Releases 2 and 3 with the LOTUS/DBMS.TM. server. 
This will enable users of Microsoft's DOS 3.0.TM. operating system and 
OS/2.TM. operating system to share information, using the familiar LOTUS 
1-2-3.TM. interface." 
However, browse tables of relational database systems are not spreadsheet 
tables. In a browse table the user may define a formula and place its 
computed results in a field that is added to all the records. Thus, the 
same formula is repeatedly applied to all records. Except for the ADVANCED 
REVELATION.TM. programmable database file manager, by Revelation 
Technologies Inc., the formula's arguments may only be fields of the same 
record that holds the result of the calculation. 
Most relational database instruments provide procedural languages for the 
development of applications. These languages allow the user to perform 
calculations with diverse structures, and write the results back to fields 
located anywhere in the records. But the combination of the procedural 
language and the data interface for indexed files causes the programming 
and interpretation of calculations to be cumbersome. 
The LOTUS/DBMS.TM. spreadsheet/database integrative program, by Lotus 
Development Corporation, is an example of a standalone relational database 
instrument that operates as described above. Compatibility with the LOTUS 
1-2-3.TM. spreadsheet program, by Lotus Development Corporation, is 
limited to menu structure and programming language. The LOTUS/DBMS.TM. 
spreadsheet/database integrative program, by Lotus Development 
Corporation, has offered an add-in product that allows the use of LOTUS 
1-2-3 RELEASE 2.0.TM. and LOTUS 1-2-3 RELEASE 3.0.TM. with the 
LOTUS/DBMS.TM. spreadsheet/database integrative program, by Lotus 
Development Corporation. But it is very probable that this new product 
operates like all database add-ins for the LOTUS 1-2-3.TM. spreadsheet 
program, by Lotus Development Corporation. Database add-ins are very 
similar to the @BASE.TM. spreadsheet add-in for database management, by 
Personics Corporation, a database tool for a spreadsheet program described 
in the following section. 
C. The ideas used by DATABASE TOOLS FOR SPREADSHEET PROGRAMS are the most 
similar prior-art references to the invention. 
The @BASE.TM. spreadsheet add-in for database management, by Personics 
Corporation, may be considered the pioneer among the LOTUS 1-2-3.TM. 
add-in product family. It has kept a leadership position in the market and 
a series of enhanced versions have updated its capabilities. 
The @BASE.TM. spreadsheet add-in for database management is a database 
add-in for the LOTUS 1-2-3.TM. spreadsheet program that mimics most 
commands in the DBASE III.TM. database file manager, by Ashton Tate 
Corporation. The manufacturer refers to it as "a full-feature relational 
database add-in for LOTUS 1-2-3.TM.." Personics advertises the product 
with the slogan: "@BASE.TM. turns LOTUS 1-2-3.TM. into a powerful 
front-end for database applications." 
In the @BASE.TM. spreadsheet add-in for database management it is possible 
to set up dynamic links that import updated information from the DBASE 
III.TM. database file manager records every time a worksheet is 
recalculated. Records can be entered or examined either in a form view, 
where each record occupies a separate screen, or in the usual 
one-record-to-a-line format. 
Besides the indexing feature, the @BASE.TM. spreadsheet add-in for database 
management's OPTION .TM., by Personics Corporation, can perform file 
join operations to create relational databases and operating computed 
fields. Also it may sort a database without rearranging the records on the 
disk, and automatically keeps files in the correct sorting order during 
record entry or updating. 
The @BASE.TM. spreadsheet add-in for database management comprises two sets 
of elements in its operational structure: a functional replicate of the 
DBASE III.TM. database file manager's set of commands, and a set of 
@functions for read and write operations. 
Any DBASE III.TM.-compatible command may be issued from within the LOTUS 
1-2-3.TM. spreadsheet program, by Lotus Development Corporation. But the 
@BASE.TM. spreadsheet add-in for database management and the LOTUS 
1-2-3.TM. spreadsheet program have completely independent video screens 
and communication links between files and data. Thus, operating the 
@BASE.TM. spreadsheet add-in for database management's independent 
compatible format files is functionally equivalent to suspending execution 
of the LOTUS 1-2-3.TM. spreadsheet program, and activating the DBASE 
III.TM. database file manager. The user may go back to the LOTUS 1-2-3.TM. 
spreadsheet program when finished using the DBASE III.TM. tools. This 
clears the video overlay of the add-in application, and allows the user to 
return to the LOTUS 1-2-3.TM.'s previous condition. 
A set of @functions allow the user to read and write the contents of single 
fields in database files and single cells in the LOTUS 1-2-3.TM. 
spreadsheet environment. These functions perform like standard screen 
input and output operations in the DBASE III.TM. database file manager. 
Yet database and spreadsheet instruments operate independently from one 
another. 
With the invention, the user can generate coordinated video operations, to 
look and operate simultaneously at a combination of the spreadsheet and 
the database files contents. This is not possible in the @BASE.TM. 
spreadsheet add-in for database management. 
By means of the invention, the user needs only design the structure and 
contents of database files and spreadsheet formulas. The screen 
simultaneously displays actual spreadsheet information and a dynamic 
database browse view. Yet, the program performs all read and write 
functions automatically, so the user is unaware of them. Thus, it is 
necessary for the user to distinguish between the database and the 
spreadsheet data. 
In the @BASE.TM. spreadsheet add-in for database management, the user has 
to program each individual read and write operation to and from the 
database files. These operations and functions may be directly written in 
data cells or as the LOTUS 1-2-3.TM. spreadsheet program's macro commands. 
Yet an average programmer would consider this an extensive and complicated 
programming task. The execution of these operations would be significantly 
slow, due to the large number of commands and to a sub-optimal execution 
schedule for read and write operations. 
The ORACLE DATABASE ADD-IN FOR 1-2-3.TM. spreadsheet add-in for database 
management, by Oracle Systems Corp., is a product whose basic concept is 
similar to the @BASE.TM. spreadsheet add-in for database management. 
However, this product also allows the user to type SQL statements directly 
into the worksheet. These statements can be stored in LOTUS 1-2-3.TM. 
worksheet cells as LOTUS 1-2-3.TM. @functions. Function @SQL(range) 
executes the SQL statement in "range." These SQL statements may operate 
directly with database tables. 
However, video operations in the environments of LOTUS 1-2-3.TM. and the 
ORACLE DATABASE ADD-IN FOR 1-2-3.TM. spreadsheet add-in are totally 
isolated, as in the @BASE.TM. spreadsheet add-in for database management. 
With the invention, the user can generate coordinated video operations, to 
look and operate simultaneously at a combination of the spreadsheet and 
the database files contents, as was described before. 
SQL statements accelerate programming for the operation of database files, 
but they cannot control those read and write operations that effect 
information transfer between spreadsheet and database instruments. Users 
of the ORACLE DATABASE ADD-IN FOR 1-2-3.TM. spreadsheet add-in would have 
to do extensive programming automatic read/write operations in the 
invention. Therefore, the same limitations described for the @BASE.TM. 
spreadsheet add-in for database management, by Personics Corporation, 
apply to this product. 
The INFORMIX DATASHEET ADD-IN.TM. spreadsheet add-in for database 
management, by Informix Software Inc., develops essentially the same idea 
and data procedures as the @BASE.TM. spreadsheet add-in for database 
management, by Personics Corporation, but is based in a different database 
file format. It uses "query by example" techniques. There are other 
important implementations of database add-in spreadsheet tools, such as 
@DB123.TM. , which are not described here. These present similar 
characteristics and limitations as those products described here. 
As mentioned in preceding paragraphs, the invention presents a spreadsheet 
environment optimally suited for the integrated operation of database 
files. Such an environment cannot be developed using an add-in product 
such as any of those described here. This type of product is designed with 
a different orientation. It provides the user expanded spreadsheet 
operations, by adding @functions and superimposing alternative menus and 
video structures in particular modes of the spreadsheet environment. These 
capabilities in an existent spreadsheet product do not suffice for the 
implementation of the invention. 
Recent spreadsheet programs, like the MICROSOFT EXCEL.TM. spreadsheet 
program, by Microsoft Corporation, the SUPERCALC.TM. spreadsheet program, 
by Computer Associates International Inc., and the LOTUS 1-2-3 release 
2.2.TM. and the LOTUS 1-2-3 release 3.1.TM. spreadsheet programs, by Lotus 
Development Corporation, also contain database features. As an example of 
their characteristics, four products will be discussed here: the QUATTRO 
PRO.TM. spreadsheet program, by Borland International, the VP-PLANNER 
PLUS.TM. spreadsheet program, by Paperback Software International, the 
LOTUS SYMPHONY.TM. integrated spreadsheet/database manager/word processing 
program, by Lotus Development Corporation, and the UNIPLEX SPREADSHEET.TM. 
program by Uniplex Information Systems. 
The QUATTRO PRO.TM. spreadsheet program, by Borland International, is a 
spreadsheet program that implements database instruments similar to those 
described in the previous add-in database instruments. The same 
limitations described for the @BASE.TM. spreadsheet add-in for database 
management, by Personics Corporation, apply to this product. Also, this 
product does not allow the user to write information directly to a 
database file. Besides, the user may read data from a database file, but 
needs to convert it to a spreadsheet format to operate on it. 
The VP-PLANNER PLUS.TM. spreadsheet program, by Paperback Software 
International, is a spreadsheet program that implements the read, write, 
query and sort operations of the previous database instruments. This 
product also implements a multidimensional database option, which lets the 
user create a database with up to five fields. By choosing two of these 
fields to act as vertical and horizontal coordinates, the user can view a 
crosstab table automatically. However, this multidimensional database file 
is really a spreadsheet data storage file of a fixed format. Thus, the 
same limitations described for the @BASE.TM. spreadsheet add-in for 
database management, by Personics Corporation, apply to this product. 
The LOTUS SYMPHONY.TM. integrated spreadsheet/database manager/word 
processing program, by Lotus Development Corporation, integrates 
spreadsheet, database and word processing environments. Yet this product 
stores database information in spreadsheet cells. So, the nature of its 
query, sort and other database operations is very similar to operations in 
LOTUS 1-2-3.TM. and other spreadsheet programs. The same limitations 
described for the @BASE.TM. spreadsheet add-in for database management, by 
Personics Corporation, apply to this product. 
The UNIPLEX SPREADSHEET.TM. program by Uniplex Information Systems, is a 
spreadsheet program based in the UNIX.TM. operating system, by AT&T. It 
supports "embedded calls to the database using SQL," according to the 
commercial documentation. The UNIPLEX SPREADSHEET.TM. program by Uniplex 
Information Systems, is part of the UNIPLEX ADVANCED OFFICE SYSTEM.TM. 
also by Uniplex Information Systems, integrating several application 
programs. However, all these products can only exchange information 
through a compatible format for file storage. Like most integrated 
software systems, the UNIPLEX SPREADSHEET.TM. program only allows video 
integration of independent modules through the window capability of the 
base operating systems. The same limitations described for the @BASE.TM. 
spreadsheet add-in for database management, by Personics Corporation, and 
for multitasking environments such as the DESKVIEW.TM. multitasking 
environment, by Quarterdeck Office Systems, and the MICROSOFT WINDOWS.TM. 
graphical user interface and multitasking environment, by Microsoft 
Corporation, apply to this product. 
Two particular products also merit mention as relevant prior-art references 
to the invention: (D) the REFLEX PLUS.TM. spreadsheet analysis tool, by 
Borland International, and (D) the RONSTADT'S FINANCIALS.TM. financial 
forecasting system, by Lord Publishing Inc. 
D. The REFLEX PLUS.TM. spreadsheet analysis tool, by Borland International, 
is a database program that adds special features for the analysis of 
information. The user may define several ways of displaying the data, or 
database "views." 
For example, one possible "view," called table-mode, can mimic some 
operations in a spreadsheet program. This corresponds to the "browse" mode 
in other database programs. It allows the user to examine all the database 
information as in a spreadsheet table. 
Table-mode operations act only on the structure and contents of a single 
flat database file. In table-mode, any formula is evaluated on all records 
of the underlying database. This is functionally equivalent to the 
operation of computed fields in other database programs. The user 
interface for the development, modification and recalculation of formulas 
in table-mode imitates some characteristics of spreadsheet programs. 
The REFLEX PLUS.TM. spreadsheet analysis tool is a flat database program 
with a powerful customized interface for data analysis. Yet its 
functionality is still limited to that of a database program, which 
naturally lacks the flexibility of spreadsheet operations. Thus the 
restrictions mentioned for relational databases apply to this program as 
well. 
E. The RONSTADT'S FINANCIALS.TM. financial forecasting system, by Lord 
Publishing Inc., is a software tool for developing financial models (by 
Lord Publishing, Inc.; One Apple Hill, Natick, Mass. 01760; 508-651-9955). 
The visual operation and the user interface of the RONSTADT'S 
FINANCIALS.TM. financial forecasting system are similar to the operation 
of the invention's database interface. Also, the RONSTADT'S FINANCIALS.TM. 
financial forecasting system allows the definition of formulas as in a 
spreadsheet, but the user is restricted to write them in a single column. 
This instrument is not a spreadsheet program. 
Formula evaluation in the RONSTADT'S FINANCIALS.TM. financial forecasting 
system parallels a Copy command in a spreadsheet, executed over the entire 
worksheet. The user of the RONSTADT'S FINANCIALS.TM. financial forecasting 
system loads a column of formulas and an entire set of database records 
into memory. The screen displays records as a columns of data. Then the 
column of formulas operates repeatedly on successive data columns. 
As indicated before the RONSTADT'S FINANCIALS.TM. financial forecasting 
system is not a spreadsheet program. Therefore, it lacks the flexibility 
and efficiency of the spreadsheet operations that the invention allows. It 
is restricted by the columnar evaluation of formulas. 
The RONSTADT'S FINANCIALS.TM. financial forecasting system does not allow 
the operation of external database files, because it operates exclusively 
on the database file that is loaded completely into memory. The invention, 
on the other hand, operates directly with database files' contents. 
The RONSTADT'S FINANCIALS.TM. financial forecasting system was designed 
exclusively for the calculation of financial projections. Therefore, it is 
limited to working with data columns that represent single periods in 
time. The invention, being an enhancement to a spreadsheet instrument, 
does not have this restriction, since it can be used to develop all kinds 
of applications. 
F. SPREADSHEET COMPILERS also merit mention as relevant prior-art 
references. Two examples of these are the BALER.TM. spreadsheet compiler, 
by Baler Software Corporation, and the KING JAGUAR.TM. spreadsheet 
compiler, by Sheng Laboratories Inc. 
These instruments allow the user to compile or convert a worksheet into a 
closed (unmodifiable) application. The worksheet may be developed with any 
spreadsheet program, such as the LOTUS 1-2-3.TM. spreadsheet program. The 
result is a standalone program that uses context-sensitive help screens, 
data validation, special macros, and pull-down menus. These instruments do 
not allow the modification of formulas after compilation, nor the creation 
of new formulas. Therefore, the finished application loses the flexibility 
of the spreadsheet. 
The invention allows the user all the features mentioned above WITHIN an 
active modifiable spreadsheet. The invention enables the user to continue 
to work with the spreadsheet, modify formulas and screens, and do 
additional transformations on the data. Also, all existing spreadsheet 
compilers do not contain the capability to access external database files. 
The cited PRIOR-ART references are important because the invention provides 
the benefits of integrating their main characteristics. Like database 
add-ins, the invention is a spreadsheet enhancement. Yet, it is the only 
spreadsheet enhancement that implements options for full environment 
definition and automatic read and write operations between database and 
spreadsheet structures. The RONSTADT'S FINANCIALS.TM. financial 
forecasting system, by Lord Publishing Inc., and the REFLEX PLUS.TM. 
spreadsheet analysis tool, by Borland International, store their 
information in database structures similarly to the invention. Also, the 
RONSTADT'S FINANCIALS.TM. financial forecasting system allows formula 
operation on database structures. Yet, the invention integrates these 
concepts in spreadsheet instruments. Spreadsheet compilers implement 
options for full environment definition, but the invention does so while 
keeping the interactive nature of a full spreadsheet environment. 
Thus, as stated before, the invention allows users to interactively develop 
new environments to program and use finished applications, and to use 
database files for the storage of information operated in the spreadsheet 
program. Prior art references do not implement interactive creation of 
spreadsheet environments. 
Accordingly, the following paragraphs describe several objects and 
advantages of the invention. 
The invention expands the power of spreadsheet programs without placing any 
limitations on their basic operations. All applications developed for 
spreadsheet environments are a primary subject of the invention. Of 
particular interest are applications whose data structures benefit from 
the added support of database files. 
The invention integrates the operation of spreadsheet and database 
instruments in an environment that provides an "automatic" collaborative 
scheme. It allows the coordination of video screens, data storage, 
spreadsheet operations and database features. The combination does not 
confuse the user by presenting a multitude of options. This gives more 
flexibility and power to the spreadsheet than all 3-D and "@BASE-like" 
schemes used in other products. 
The invention enables the user to develop spreadsheet application 
environments. This will enable users to develop small, practical and 
modifiable applications for tasks such as the analysis of financial and 
accounting statements and market reports. Finished applications may 
provide specific database features. Also, they may have data validation 
capabilities, context-sensitive help texts and distinctive screen 
displays. 
The invention's synergistic approach allows users to develop spreadsheet 
applications traditionally executed with database instruments, such as 
integrated accounting packages and inventory analysis. 
By using the invention, users can develop small, practical and modifiable 
applications for the analysis of financial and accounting statements, 
market demand, and other business problems. These types of applications 
will benefit from the invention, because they require relational database 
storage and access techniques, and complex analytical computations. 
For example, a simple financial model can be developed by using only one 
col.sub.-- prog window and one flat database file. In this application, 
each record in the database file corresponds to one period. Also, all 
formulas are developed in the col.sub.-- prog window. The implementation 
of a large-scale financial model may use separate database files to store 
credit information, accounting data, market demand analysis, etc. It would 
take approximately three man-years to implement this model with prior-art 
tools. In the invention's environment, the development of the model takes 
between three and six months. 
Therefore, the invention represents cost reductions in the development of 
many types of large and complicated data-analysis applications. The 
resulting applications are as flexible as spreadsheet programs, and 
modifiable by the user. 
Another example is inventory planning. The invention enables the user to 
design an application containing past history of sales, inventory levels, 
prices and other criteria for each product and/or product group. The user 
may easily classify products according to their sales/inventory level 
indexes. The application allows the user to prepare sales forecasts and 
recommend new orders for each product and/or product group. This is 
accomplished by one or more associated database files. Each file may 
contain, among other data, information regarding individual products, 
product groups, demand forecasts, and future orders for manufacturing 
and/or raw materials. 
Another example is a fully integrated accounting system. The invention 
allows the creation of such a system in a spreadsheet-database 
environment. The scratchpad section allows the user the additional 
opportunity of performing spreadsheet operations on data generated by 
reports. This information is stored in a relational structure of database 
files associated to several col.sub.-- prog windows. 
Database files being used by the invention may contain special operational 
codes. These codes can control the operation of specific database fields 
or spreadsheet cells. In a particular example, operational codes allow the 
results of a financial application to be calculated and expressed in 
different currencies. This may be done without changing the formulas in 
the spreadsheet environment. Thus, operational codes expand the power and 
flexibility of the invention's programmability. 
The invention has the functionality of the combination of a spreadsheet 
compiler and a spreadsheet program. A user can develop applications in a 
spreadsheet format. Then the user may define tools for the application 
(menu options, help screens, data validation routines, etc.) that enable 
other users to operate the application. 
The invention overcomes weaknesses that proved the downfall of spreadsheet 
prepackaged applications. These applications for spreadsheet programs, or 
"templates," reached their sales peak before the arrival of the LOTUS 
1-2-3.TM. spreadsheet program. According to some experts, their popularity 
wanned gradually due to their failure to make the spreadsheet program 
easier to use. (See chapter 3 of: Entrepreneurial Finance--Taking Control 
of Your Financial Decision Making, by Dr. Robert Ronstadt; Lord 
Publishing, Natick, Mass., 1988. Also see: Mark Chussil, "Computer-Based 
Planning: A PC Survival Guide," The Journal of Business Strategy, 
January/February, 1988, pp. 38-42.) 
Users of the invention are able to interactively modify an application 
developed using the invention. The user who developed it can limit the 
number and quality of the modifications available to other users. Thus, 
any other user can build new routines for any application, that conform to 
specific needs. These routines involve adding formulas that cannot damage 
the primitive application. 
The invention can operate as the software equivalent of a hardware parallel 
processor for several Turing machines. Additionally, some tools of the 
invention can make it operate more efficiently than a the parallel Turing 
machines. Any computation or recognition problem for which there is a 
known informal algorithm can be handled by a Turing machine. Therefore, 
the invention can handle and solve all sorts of programming problems. 
Templates developed for spreadsheet programs do not execute as efficiently 
as other programming means currently available. However, spreadsheet 
programming is now popular due to the interactive nature of its operation. 
Also, at a medium range of time, faster mass-memory devices will be 
available. Then, multidimensional database structures coupled with 
spreadsheet program tools will make the invention more attractive. 
Execution of applications programmed with the invention will be more 
efficient. 
This combination of concepts is an important ramification of the long-term 
application of the invention. During the next ten years, a large portion 
of software development and research will focus on the creation of tools 
for power-easy programming and the easy use of applications. Hardware 
storage devices will change. Database files will probably be stored in 
dynamic memory, bubble memory and other hardware structures. This will 
result in faster data operations. 
The storage and processing of the three elements of the invention (database 
structures, spreadsheet structures and video system structures) will be 
important in terms of their relative structures or topologies. The 
invention may represent a future direction for research. The user will 
have more power to develop and operate data structures. Also, the user 
will be able to control the corresponding three levels of the development 
process for products, tools and applications. 
Further objects and advantages of the invention will become apparent in the 
consideration of the drawings and ensuing description. 
SUMMARY OF THE INVENTION 
An enhanced spreadsheet program (a) providing a novel synergistic approach 
to the combination of spreadsheet and database tools, and (b) enabling 
users to develop an application environment that can use all resources in 
the spreadsheet storage area. 
The following paragraphs refer to the structure of the invention. 
The enhanced spreadsheet program comprises three storage areas: 
a spreadsheet data structure, 
a set of database files in a storage area, and 
a video display system. 
The spreadsheet data structure is divided into two sets of cells that can 
be operated through three different environments. 
The first environment contains the tools of a spreadsheet environment, and 
the first set of cells can be operated through the first environment. This 
is most appropriate for scratchpad uses. 
The second environment contains the tools of a spreadsheet environment and 
contains a special command that can define commands for the third 
environment. This is appropriate to develop formulas for an application, 
and the application's environment of operation. 
The second environment also contains the tools necessary to define special 
environments for the integrated operation of spreadsheet and database 
tools. Said integrated operation coordinates dynamic screen video 
operations of the information in the spreadsheet and database storage 
areas. 
The second set of cells (for the development and use of an application) can 
be operated through the second or third environments. 
The following paragraphs refer to the first enhancement, a novel 
synergistic approach to the combination of spreadsheet and database tools. 
A novel aspect according to the teachings of the invention is the provision 
of a browse view in the spreadsheet which shows the contents of a number 
of different records in the active database file. The spreadsheet has a 
portion thereof which has previously been defined as a "col.sub.-- prog" 
range. Cells in this range can receive numbers, text, formulas or any 
other type of data that may be stored in database record fields. These 
cells in the col.sub.-- prog range can be defined as "temporary" or 
"permanent" by the user at any time. Temporary cells are cells that are 
designed to receive data updates from database records. Permanent cells 
are cells that are "write protected" in the sense that when a database 
record is written into the col.sub.-- prog range, the contents of the 
col.sub.-- prog range permanent cells are not changed. Whatever field from 
the database record that would have gone into a permanent cell is not 
used. 
When contents of a database record are written into the col.sub.-- prog 
range, this database record is then called "active record". The fields of 
a database record which are designated to be written into the col.sub.-- 
prog range are defined in the "col.sub.-- prog window". This is a set of 
instructions that defines which spreadsheet cells are part of the 
col.sub.-- prog range, identifies the name of the database file whose 
information will be written into the col.sub.-- prog range, and defines 
which fields of the database record exchange data with which fields of the 
col.sub.-- prog range. 
After the contents of a database record have been written into the 
col.sub.-- prog range, the conventional recalculation function key may be 
pressed and all the formulas in the spreadsheet including the formulas in 
the col.sub.-- prog range will be recalculated. The formulas in any cell 
may reference any other cell in the spreadsheet, i.e., read data or the 
results of formulas from any other cell. Data in the database may thus be 
brought into a spreadsheet one record at a time and may be operated upon 
mathematically by whatever formulas are programmed into the cells anywhere 
on the spreadsheet which reference the cells in the col.sub.-- prog range. 
The "browse view" aspect of the invention allows a screenful of database 
records to be displayed. The particular record which is "active", i.e., 
its contents are currently stored in the col.sub.-- prog range, is 
designed in some visual fashion on the display such as by reverse video, a 
different color, a different color cursor underlining the column, etc. A 
cursor, which the user can move among all the displayed records, 
designates a "current" record. When the user desires to load the contents 
of the "current" database record into the col.sub.-- prog range, he or she 
presses the recalculation function key. This writes the contents of the 
current database record into the col.sub.-- prog range according to the 
instructions in the col.sub.-- prog window. The current contents of the 
col.sub.-- prog range are written back into the database record from which 
the original data entered into the col.sub.-- prog range cells at the last 
recalculation event. In other words, upon each recalculation, the current 
contents of the col.sub.-- prog range are used to update the database 
records from which they originated, and a new database record is loaded 
into the col.sub.-- prog range and is mathematically operated upon by the 
formulas in the spreadsheet. 
For time-based database records, the above process is one step in the 
overall process. Time-based processing works as follows. Assume that the 
report being developed is a collection report to be run monthly linked to 
a database on the spreadsheet where monthly database records exist, one 
field of which is the cash collections for that month. Assume also that 
one line of the report is total collections year-to-date. To process this 
report, the cash collections for the current month need to be added to the 
running total. To do this a "col.sub.-- prev range" is defined in addition 
to the col.sub.-- prog range. In the preferred embodiment, the program 
assumes that the database records have been sorted or entered such that 
records for adjacent periods of time are stored sequentially. In this 
example, assume that the first database record is January collections, the 
second record was February collections and the third record was March 
collections. To process the February record, it would be loaded into the 
col.sub.-- prog range and, the January record would be loaded into the 
col.sub.-- prev range. A formula programmed in a cell of the col.sub.-- 
prog range would then add February collections to the year-to-date 
collections cell contents in the col.sub.-- prev range (January). When the 
March record was to be processed, the cursor would be placed on the March 
record and the recalculation function key would be pressed. This would 
cause the contents of the col.sub.-- prog range to be written to the 
February database record and then the col.sub.-- prog range and the 
col.sub.-- prev range would be loaded. Col.sub.-- prog is loaded with the 
contents of the March record and col.sub.-- prev is loaded with the now 
updated contents of the February database record, and the spreadsheet is 
recalculated. This causes the contents of the March database record for 
March collections to be added to the year-to-date collections data stored 
in the col.sub.-- prev range. 
The "browse view" is a scrolling display and can be used to show all the 
database records by scrolling through them with the arrow or cursor 
control keys. When the cursor is moved to the extreme right side of the 
display, for example, more database records are brought in. Old records 
for which there is insufficient room scroll off the screen to the left. 
Database records shown in the browse view may be sorted in different ways, 
indexed or selected from all database records in a file. 
To summarize this scheme of operation, it can be said that the screen 
simultaneously displays actual spreadsheet information and a dynamic 
database browse view. Read and write functions are performed automatically 
and are transparent to the user. The user does not need to differentiate 
when he is working with the database files or the spreadsheet data. 
The foregoing description of the communication between database and 
spreadsheet data corresponds to one environment for the integrated 
operation of spreadsheet database tools. Several such environments can 
exist in this invention. The operation of these environments can be 
coordinated to operate database files in the manner of a relational 
database system. Thus, said software invention enables users to develop 
and use full database applications such as integrated accounting packages, 
within spreadsheet environments. 
The following paragraphs refer to the second enhancement, the development 
of an application environment that can use all resources in the 
spreadsheet storage area. 
There are at least three novel aspects to the teachings of the invention 
regarding provision of a facility whereby a user can develop new 
applications using the spreadsheet format. An "application" means a 
spreadsheet or collection of related spreadsheets or portions of the same 
spreadsheet which are programmed with formulas. These formulas are 
designed to perform calculations for a particular job such as calculating 
and displaying an income statement or balance sheet for a particular 
business enterprise. 
The invention allows the creation of many different tools for the 
development of new applications using the spreadsheet format. Hence, the 
three novel aspects described here are only particular examples of the 
invention's capabilities. 
The first novel aspect is the provision of layout tools by which the user 
can write his or her own help screens that explain various screens of an 
application that user has developed. This is called "context sensitive 
help". For example, assume that the user is developing an income statement 
report in the range of cells U-5 and Z-200, and assume that the user feels 
that the particular method by which the user is calculating profits needs 
to be explained. At any time while the user is entering text, formulas or 
numbers to the various cells in the report, the user may invoke the help 
option. The user is then presented with effectively a blank screen which 
has associated with it a set of basic word processing commands, e.g. a set 
of commands very similar to the word processing commands of Wordstar.TM. 
3.3. The user may then enter whatever text is necessary to explain any 
methodology, approach, purpose, function, formula or whatever of the 
application screen from which the layout-help option was invoked. 
Alternatively, after a set of reports have been programmed in various 
ranges of a spreadsheet, the user may write help screens for these reports 
(or for each screen of the report if the report takes up more than one 
screen) after they are all done. This is done by invoking the layout-help 
option and specifying the particular range, i.e., report screen, to which 
the help screen pertains. 
The second novel aspect is the provision to the user in a spreadsheet 
environment of a set of layout commands by which the user may program his 
or her own menus and the "look" of the screen. By these commands, the user 
can design a set of custom menus and make them appear as menu options 
anywhere on a screen of his or her design. For example, the user may 
specify the text of each menu option, whether it appears in the center of 
the screen in a window, along the top horizontally or down the side 
vertically etc., the color of the menu box and whether the screen has a 
border etc. For example, the user may wish to develop a series of portions 
of the spreadsheet which contain a series of related financial reports 
regarding the health of a business. Such a series of reports might include 
an income statement, a balance sheet, a statement of changes in financial 
position, a sales forecast, a sales by product line and territory, an 
inventory report and various manufacturing reports. Each of these reports 
can be contained in a specific range of the spreadsheet and can be 
assigned to a menu option on a main menu defined by the user using the 
layout commands. When the user selects a particular report menu option, 
the portion of the spreadsheet containing that report is brought to the 
screen for use. The user who designed the formulas contained in the 
various cells also has the option of "closing" some or all of the formulas 
so that subsequent users cannot modify them. The subsequent uses may go 
back and forth from the various reports and the main menu by pushing the 
escape key. 
A third novel aspect of the invention pertains to "data validation" tools. 
Although it is known in data base programs to define certain fields as 
text, numeric, logical, date etc., this concept is unknown in spreadsheets 
as far as the applicants are aware. Data validation tools are invoked from 
the layout menu in the layout environment. The user then selects a range 
by entering the upper left and lower right corners of the range or by 
pointing to the range corners with a cursor. Then the user presses "enter" 
and the range is accepted. Upon acceptance, a default set of data 
validation options for the range appear. The user may then select one of 
the options such as "text" or "numeric" subsequent users may not enter 
types of data in the cells so "validated" other than the type of data 
originally defined for that cell or range by the procedure described above 
.

DETAILED DESCRIPTION OF THE PREFERRED EMBODIMENT 
A list of all parts identified by reference numerals in FIGS. 1 to 15 
follows: 
41. RAM storage area 
42. Disk storage area (or other massive storage area) 
43. Environment 
44. Spread.sub.-- sa (spreadsheet storage area) 
45. Scratch section (scratchpad section) 
46. Gen.sub.-- prog section (general programmable section) 
47. Database files storage area (db.sub.-- sa) 
48. Video system storage area (video.sub.-- sa) 
49. Col.sub.-- prog window (columnar programmable window) 
50. Col.sub.-- prog range (columnar programmable range) 
51. Col.sub.-- prog range 
52. Active case.sub.-- db file 
53. Active record 
54. Assigned case.sub.-- db file 
55. Video window 
56. Cell's range 
57. Current record 
58. Screen's cursor 
59. Scratch environment 
60. Layout environment 
61. Application environment 
62. Col.sub.-- prog environment 
63. Read and write operations 
Referring to FIG. 2, the invention's entire program storage area is divided 
into three storage areas: 
The video system storage area (48) contains the information that is 
displayed in the computer's video screen. Prior-art references generally 
locate the video system storage area in reserved locations in the computer 
system's main memory; this set of reserved locations is usually called 
display memory. 
The database file storage area (47) contains information in regular 
database files. Prior-art references generally locate the database file 
storage area in any large-scale memory component, usually, a computer hard 
disk. 
The spreadsheet storage area (44) contains information with the same data 
structures as an ordinary spreadsheet program. Prior-art references 
generally locate the spreadsheet storage area in the computer system's 
main memory. 
VIDEO SYSTEM STORAGE AREA 
In this document, the video system storage area will be abbreviated 
video.sub.-- sa. 
The computer's video screen displays the information contained in the 
video.sub.-- sa. In most computer systems, the screen displays the 
modified video.sub.-- sa's contents only an instant after they are 
actually modified. 
Referring to FIG. 9, the video.sub.-- sa (48) contains selected information 
from the spread.sub.-- sa (50) and the db.sub.-- sa (47). The original way 
in which this information is selected and displayed distinguishes this 
invention from all prior-art references. This will be shown in the 
description and operation of the invention. 
DATABASE FILES STORAGE AREA 
In this document, the database files storage area will be abbreviated 
db.sub.-- sa. 
Referring to FIG. 4, the db.sub.-- sa contains information in regular 
database records and files. These files are called case.sub.-- db files 
(52 and 54). Their individual records are called case.sub.-- db records. 
As shown in FIGS. 3 and 9, the invention refers to the integration of the 
video.sub.-- sa (55), db.sub.-- sa (47) and spread.sub.-- sa (44). This is 
managed by operating the information in case.sub.-- db files (52 and 54) 
one record at a time. For example, a single operation may involve reading 
only one case.sub.-- db record (53) into a specified range in the 
spread.sub.-- sa (50). Another operation may involve writing back 
information into one case.sub.-- db record. 
These operations take place between a specified spread.sub.-- sa range (50) 
and a case.sub.-- db record (53). A case.sub.-- db record is a set of 
fields that store numeric or text-based information. Also, the record may 
contain date or time fields. Alternatively, a record field may store a 
formula that is interpreted as a spreadsheet formula, when the field is 
read into a spreadsheet cell. 
In the preferred implementation, each case.sub.-- db record comprises a set 
of fields of the same size (for example, 8 bytes long). Numeric 
information is stored in integer or floating point format, with the 
maximum available precision according to the field's size. Character-based 
information is stored in text format. Formulas are stored in a different 
special format. 
Also, the preferred embodiment implementation allows the definition of 
database files that are not case.sub.-- db files. These database files are 
called desc.sub.-- db files. A desc.sub.-- db file contains textual 
descriptions of the case.sub.-- db records' contents for a particular 
case.sub.-- db file. Each record of a desc.sub.-- db file describes one 
field of a typical case.sub.-- db record. So, the number of records in a 
desc.sub.-- db file equals the number of fields in the case.sub.-- db 
record. If the user wishes to view these descriptions, they are processed 
through the video.sub.-- sa and shown in the video display. 
SPREADSHEET STORAGE AREA 
Throughout this document, the spreadsheet storage area will be abbreviated 
spread.sub.-- sa. In the spread.sub.-- sa, information is organized 
exactly as in an ordinary spreadsheet program. 
Referring to FIGS. 2 and 3, the spread.sub.-- sa is divided into two 
integrated physical sections or sets of cells: 
(a) the scratchpad -non-programmable- section (45), and 
(b) the general programmable section (46). 
The names of these sections are abbreviated as scratch section and 
gen.sub.-- prog section, respectively. 
Referring to FIG. 10, the scratchpad section (45) is a range of cells in 
the spreadsheet structure. It has its own set of tools and commands to let 
the user do ordinary spreadsheet operations (59). 
Referring to FIG. 11, the general programmable section (46) is also a range 
of cells in the spreadsheet structure with its own set of tools and 
commands (60). In addition to allowing ordinary spreadsheet operations, it 
lets the user communicate with database files (47), and develop and use 
new tools and commands (61). 
While working in the spread.sub.-- sa, the user may select the scratch 
section or the gen.sub.-- prog section by pressing the section key. This 
key changes the section where the user is operating to the alternative 
section. The switching operation is similar to the operation of the WINDOW 
Function Key (F6 key) in the LOTUS 1-2-3.TM. spreadsheet program and in 
other similar spreadsheet programs. 
In reference to FIGS. 10 to 13, the user may interact with each section in 
the spread.sub.-- sa (44) through any of three environments (59, 60 and 
61). Each environment defines a set of commands and options available to 
the user for the operation of spread.sub.-- sa information. Also, each 
environment displays the information in the spread.sub.-- sa in a 
particular way. 
As shown in FIG. 10, one of the environments, the scratch environment (59), 
handles the information stored in the scratch section (45). When the user 
is located in the scratch section, the program automatically makes 
available the scratch environment. It executes like an ordinary 
spreadsheet. 
Referring to FIG. 11, the program operates on the information in the 
gen.sub.-- prog section in two different ways, depending on one of two 
environments selected with the environment key: the layout environment 
(60) and the application environment (61). The layout environment may be 
used to develop the application environment. 
Throughout the description of the invention, user is any person who uses 
the invention in any of the three available environments. This person 
becomes a programmer when using the layout environment to develop an 
application. 
A more detailed definition of these two environments is necessary to 
describe the invention: 
The Layout Environment 
This is an enhanced spreadsheet environment that uses a special command set 
(called the layout command set). The programmer may use this command set 
to define the tools available to the user for the operation of the 
application environment. Also, this environment lets the programmer define 
the elements that allow the simultaneous access and operation of 
information in case.sub.-- db files and the gen.sub.-- prog section. 
The Application Environment 
This environment comprises the tools that have been defined in the layout 
environment with the layout command. In the preferred embodiment, these 
are the only tools and operations that the user will be able to use in the 
application environment. 
As shown in FIGS. 12 and 13, an additional environment (62) operates in the 
gen-prog section. It is defined in the following paragraphs. 
Col.sub.-- prog windows, ranges and environments 
In the layout environment, the user may create sets of definitions related 
to the operation on the data in the gen.sub.-- prog section and the 
case.sub.-- db files. Referring to FIG. 3, each one of these sets of 
definitions will be called a columnar programmable window (49), 
abbreviated col.sub.-- prog window. Each col.sub.-- prog window is 
identified by a distinct name, and contains the following definitions: 
a) The location of a specific range of cells within the gen.sub.-- prog 
section; this range of cells is called col.sub.-- prog range (50). 
b) Whether the database records are chained-dependent (as in a time.sub.-- 
based application). 
c) If the database records are chained-dependent, the location of another 
specific range of cells within the gen.sub.-- prog section; this range of 
cells is called col.sub.-- prev range (51). 
d) The names and directories for all database files whose data will be 
accessed; these files are called assigned case.sub.-- db files (54). 
e) The identifier of the assigned case.sub.-- db file that will be linked 
to the gen.sub.-- prog section; this file is called active case.sub.-- db 
file (55). 
f) The identifier of that record in the active case.sub.-- db file that 
will be loaded into the col.sub.-- prog range; this record is called 
active record (53). 
Col.sub.-- prog ranges, time.sub.-- based applications, col.sub.-- prev 
ranges, associate case.sub.-- db files and active case.sub.-- db files 
will be described later in this document. 
The user presses a special key, called window key, to activate a col.sub.-- 
prog window. Through this operation, the col.sub.-- prog window becomes 
the active col.sub.-- prog window. An active col.sub.-- prog window allows 
the simultaneous access and operation on information from the active 
case.sub.-- db file and the col.sub.-- prog and col.sub.-- prev ranges 
contained in its definition. To do this it activates the col.sub.-- prog 
environment. 
Referring to FIGS. 12 and 13, the col.sub.-- prog environment inherits all 
characteristics from the environment in which it is activated (60 or 61). 
In this context, this environment acts as a parent environment, because it 
passes on its properties to the col.sub.-- prog environment. Also, as a 
parent environment, it allows the col.sub.-- prog environment to add its 
own significant characteristics. 
The col.sub.-- prog environment controls and coordinates the display of 
spreadsheet contents and data from the active case.sub.-- db file. 
Referring again to FIG. 3, a col.sub.-- prog range (50) is a range of 
spreadsheet cells that will hold the information in one record of the 
active case.sub.-- db file (52). This record is called the active record 
(53). In the best mode implementation, the col.sub.-- prog range is a 
range of adjacent cells located in a single spreadsheet column. In 
alternative implementations, the col.sub.-- prog range may be an ordered 
set of disjoint spreadsheet cells. 
As shown in FIG. 14, more than one col.sub.-- prog window may be defined in 
the invention. However, in the best mode implementation, col.sub.-- prog 
windows and col.sub.-- prog ranges are in a strict one-to-one 
relationship. That is, there is one col.sub.-- prog range for each 
col.sub.-- prog window, and there is one col.sub.-- prog window for each 
col.sub.-- prog range. 
The col.sub.-- prog environment has several important characteristics. It 
controls the operation of the elements defined in the col.sub.-- prog 
windows. Also, it inherits all the characteristics and basic commands from 
the parent environment. It adds commands and operations for the access and 
operation on information in the case.sub.-- db files and the gen.sub.-- 
prog section. Finally, the col.sub.-- prog environment adds commands for 
generating the video display of that information, i.e., modifying the 
contents of the video.sub.-- sa. 
This invention's originality and power are based in the particular 
operation of the col.sub.-- prog environment. 
All elements described in this summary suffice to be used as a dictionary 
of the terms involved in the claims of this patent application. However, 
the following elements and considerations are also important to the 
operation of this invention. 
As shown in FIGS. 6 and 7, a formula in a spread.sub.-- sa cell may 
reference any other cell in the spread.sub.-- sa. Referenced cells may be 
located in the scratch section or the gen.sub.-- prog section. They may or 
may not be displayed in a col.sub.-- prog window. The possibility of 
formulas that cross-reference the scratch section (45) and the gen.sub.-- 
prog section (46), is due to the fact that both sections effectively 
compose a single spreadsheet. 
Gen.sub.-- prog cells are part of the gen.sub.-- prog section, regardless 
of whether they are in a col.sub.-- prog range (50). They may be accessed 
and operated as ordinary spreadsheet cells, when working in the gen.sub.-- 
prog section. This implies that the programmer may access a col.sub.-- 
prog range cell in the layout environment, without activating the 
col.sub.-- prog window. Also, col.sub.-- prog range cells may store 
formulas that reference other cells or cell ranges in the gen.sub.-- prog 
section and the scratch section. 
The preferred embodiment allows the programmer to configure the invention 
otherwise: access to the col.sub.-- prog range's cells may be effected 
only from within a col.sub.-- prog window. 
Although all gen.sub.-- prog cells are outcome cells initially, the user 
may define some of gen.sub.-- prog cells as data cells. This cell type 
definition is transferred to the application environment. 
Outcome cells are mainly related to the design of the application in the 
gen.sub.-- prog section. The programmer must define the formulas in these 
cells in the layout environment. The user in the application environment 
cannot modify these cells' contents. 
Data cells accept data input from the user in the application environment. 
Data input can be texts, numbers or formulas. 
The user may specify the contents of the col.sub.-- prog range cells as 
"temporary" or "permanent." Permanent cells are not modified when the 
program loads a database record into the col.sub.-- prog range, because it 
does not load record fields into col.sub.-- prog range permanent cells. 
The program only loads those fields that will be placed in temporary 
cells. 
All outcome cells in the col.sub.-- prog range must be permanent cells. All 
data cells in the col.sub.-- prog range are initially set up as temporary 
cells, but the user may specify them as permanent cells. 
The main spreadsheet file stores all texts, numbers or formulas defined in 
outcome cells. This file is identified by the suffix ANS, for ans.sub.-- 
file ("analysis-spreadsheet-file"). All texts, numbers or formulas defined 
in permanent data cells are stored in a separate file, identified by the 
suffix CAP, for cap.sub.-- file ("hat file"). 
According to the cell identification described in the preceding paragraphs, 
the ans.sub.-- file contains the formulas that define the application 
developed in the layout environment. It also enables the program to store, 
in a cap.sub.-- file, information that a particular user wishes to protect 
from other users of the same application. This scheme to store each type 
of information in a different file provides maximum protection to the 
programmer and to each individual user. 
As described previously, if a permanent data cell contains a formula, it is 
stored in a cap.sub.-- file. In the preferred embodiment implementation, 
the file-save command also stores the current results of evaluating this 
formula in the ans.sub.-- file, together with the outcome cells' contents. 
This ensures the integrity of the information contained in the ans.sub.-- 
file. 
In special applications described as "time-based," a col.sub.-- prog range 
works closely with a related idea: the col.sub.-- prev range. As seen in 
FIG. 3, in the best mode implementation, the col.sub.-- prev range (51) is 
a single column of adjacent spreadsheet cells, located immediately to the 
left of the col.sub.-- prog range (50). 
A time-based or record-chained application is an application whose data is 
contained in ordered records. An ordered record depends on the record 
previous to it. In these applications, the records compose a chained 
structure with dependency relationships, so they are called 
chained-dependent records. Chained-dependent records are mostly used for 
time-based applications. Yet, the idea should be envisioned in a broader 
applicability context. 
One example of a time-based application is a financial model. In this type 
of model, the sales level may change for a particular period. This causes 
a variation in the financial statements corresponding to that period and 
subsequent periods. 
Dependency between records is not the case in all applications. For 
example, in an inventory analysis model, each database record contains the 
information corresponding to a single product. Thus, a database record in 
this kind of model represents a completely independent object. 
In all time-based applications, each col.sub.-- prog range has an 
associated col.sub.-- prev range. When a database record is loaded into 
the col.sub.-- prog range, it becomes the active record. Then, the 
database record (previous record) located immediately before the active 
record is loaded into the col.sub.-- prev range. 
Formulas access values in the previous record, which have already been 
loaded into the spreadsheet. Thus, this idea enables the program to 
calculate formulas in the col.sub.-- prog range much faster. 
Only time-based applications employ a col.sub.-- prev range. Therefore, a 
programmer should decide beforehand whether to process the application as 
time-based, and configure program settings accordingly. 
All elements described in this description suffice to be used as a 
dictionary of the terms involved in the claims. 
Referring to FIG. 9, under the control of the col.sub.-- prog environment, 
the video display (48) shows an interactive database browse view of the 
active case.sub.-- db file (52). This browse view is similar to those 
generated by the BROWSE command in prior-art database programs. 
Yet, the invention's browse view has some specific differences. The 
invention's browse view displays all the records' contents that fit in the 
view window, except for those of the active record (53). Instead of the 
information contents of the active record, the video displays those of the 
col.sub.-- prog range (50). The screen displays the contents of the 
col.sub.-- prog range cells. In the special case of formula-containing 
cells, by default the screen displays the results of formula operations, 
as is usual in ordinary spreadsheet programs 
The operations of the col.sub.-- prog environment involve memory elements 
called dynamic links. Dynamic links are storage elements that contain the 
actual value of the identifiers of the active case.sub.-- db records that 
are being accessed by the spread.sub.-- sa. They coordinate the operation 
of the case.sub.-- db files and the col.sub.-- prog window. There are two 
distinct dynamic links, depending on the case.sub.-- db record to which 
they point: 
1) the identifier for the current record, and 
2) the identifier for the active record. 
Referring to FIG. 33, the program reads the contents of the active 
case.sub.-- db file and generates a browse view of its contents, through 
the following steps: 
a) Specifying the col.sub.-- prog range in the spread.sub.-- sa, where the 
contents of individual records from the active case.sub.-- db file may be 
read. 
b) Selecting the active case.sub.-- db file. 
c) Reading selected contents from the current record in the active 
case.sub.-- db file. By this operation, the current record becomes the 
active.sub.-- record. 
d) Generating the video display showing the actual contents of the 
col.sub.-- prog range, and simultaneously showing a browse view of the 
actual contents of all case.sub.-- db records different than the active 
record. Notice, that the video display may show the col.sub.-- prog range 
contents interpreting texts as texts, numbers as numbers, and formulas as 
the results of evaluating said formulas. This is the default mode of 
operation in prior-art spreadsheet programs. 
e) Whenever a spreadsheet calculation is issued during the operation of the 
col.sub.-- prog environment, reading selected contents from the current 
record in the active case.sub.-- db file, where the current record is 
chosen by the screen cursor's position. 
While this operation is performed, the condition of the current record's 
dynamic link is determined by the screen cursor's movements and position. 
The actual value of the current record's dynamic link determines which 
case.sub.-- db record is the current record. Thus, the current record is 
selected according to the condition of the current record's dynamic link. 
When the contents from the current record the case.sub.-- db file are 
loaded to the col.sub.-- prog range (step `c` above), the active record's 
dynamic link is changed to the contents of the current record's dynamic 
link. Thus, the active record's dynamic link can identify again the record 
loaded from the active case.sub.-- db file (that is, it remembers which 
record is the active record). 
This invention's originality and power are based in the operation of the 
col.sub.-- prog environment. The way it controls the operation of the 
video system storage area is particularly important. 
Referring to FIGS. 3 and 12, the col.sub.-- prog environment (62) has 
several important characteristics. It controls the operation of the 
elements defined in the col.sub.-- prog windows (49). Also, it inherits 
all the characteristics and basic commands from the parent environment (60 
and 61). It adds commands and operations for the access and operation of 
information in the case.sub.-- db files (52 and 54) and the gen.sub.-- 
prog section (46). Finally, the col.sub.-- prog environment adds commands 
for generating the video display of that information, i.e., modifying the 
contents of the video.sub.-- sa (47). 
Referring to FIG. 9, under the control of the col.sub.-- prog environment, 
the video display shows--i.e., the video.sub.-- sa (47) stores--an 
interactive database browse view of the active case.sub.-- db file (52)). 
This browse view is similar to any one generated by the BROWSE command, in 
prior-art references such as the FOXBASE+.TM. and the FOXPRO.TM. 
programmable database file managers, by Fox Software Inc., the DBASE 
II.TM. and the DBASE III.TM. database file managers, by Ashton Tate 
Corporation, and others. The cursor key and data input operations involved 
are similar to those used by these programs. 
Yet, the invention's browse view has some specific differences. The 
invention's browse view displays all the records' contents that fit in the 
view window, except for those of the active record (53). In the screen, 
the information contents of the active record are substituted by those of 
the corresponding col.sub.-- prog range (50). The screen displays the 
contents of the col.sub.-- prog range cells. In the special case of 
formula-containing cells, by default the screen displays the results of 
formula operations. 
The col.sub.-- prog environment also implements operations that require a 
dynamic connection between the active case.sub.-- db file and the 
col.sub.-- prog range. Examples of these are the simple.sub.-- calc and 
full.sub.-- recalc operations. 
As shown in FIG. 27, when a simple.sub.-- calc operation is issued, the 
record identified by the screen cursor (current record) is loaded into the 
col.sub.-- prog range. Thus, according to the definition, it immediately 
becomes the active record. Also, if the operation is time.sub.-- based, 
the record before the active record (previous record) is loaded into the 
col.sub.-- prev range. 
FIG. 28 shows a simplified rendering of Procedure Full.sub.-- Recalc. The 
full.sub.-- recalc operation is more complicated. It is a structured 
sequence of simple.sub.-- calc operations that involve memory elements 
called dynamic links. Dynamic links are storage elements that contain the 
actual value of the identifiers of the active case.sub.-- db records that 
are being accessed by the spread.sub.-- sa. They coordinate the operation 
of the case.sub.-- db files and the col.sub.-- prog window. 
Two dynamic links have already been described in the operation of the 
invention's summary. Besides these, a third dynamic link is important to 
the operation of this invention. Hence, there are effectively three 
distinct dynamic links, depending on the record of the active case.sub.-- 
db file to which they point: 
1) the identifier for the current record, 
2) the identifier for the active record, and 
3) the identifier for the previous record. 
The following sections will describe essential elements of this invention's 
operation in the same order in which they are presented in the algorithm 
(see Appendix A). FIGS. 16 and 17 present abbreviated flowcharts of this 
program's execution. 
Environment Set-Up (procedure Initiate) 
Sections and Environments (procedures Select.sub.-- Environment, 
Select.sub.-- Window and Select.sub.-- Section) 
Basic Video Operations (procedure Process.sub.-- Video) 
Formula Editing (procedure Process.sub.-- Formula.sub.-- Edit) 
Database Records Flags (procedure Process.sub.-- Mark.sub.-- Record) 
Simple Recalculation (procedure Calc) 
Full Recalculation (procedure Full.sub.-- Recalc) 
Active and Assigned Case.sub.-- Db Files (procedure Change.sub.-- Case) 
Field Input (procedure Field.sub.-- Input) 
Execution of Parent and Scratch Environments (procedures Execute.sub.-- 
Parent.sub.-- Environment and Execute.sub.-- Scratch.sub.-- Environment) 
Referring to FIGS. 16 and 17, all elements necessary to the operation of 
the invention are defined (initiate). If the Quit.sub.-- key is pressed, 
the program terminates, else, it waits for a keypress. According to the 
key pressed, the program selects a section or does a different action. If 
section &lt;&gt; Gen.sub.-- Prog, Scratch.sub.-- Environment is executed. If the 
Environment.sub.-- Key is pressed, an environment is selected. If the 
Window.sub.-- Key is pressed, the col.sub.-- prog environment and the 
col.sub.-- prog window are activated or deactivated. If the col.sub.-- 
prog window is deactivated, the Parent.sub.-- Environment is executed. 
Finally, if the col.sub.-- prog window is activated, according to the key 
pressed, the invention may execute any of the following processes: 
process.sub.-- video, process.sub.-- formula.sub.-- edit, process.sub.-- 
mark.sub.-- record, process calc, full.sub.-- recalc, change.sub.-- case, 
field.sub.-- input, or execute.sub.-- parent.sub.-- environment. See 
Appendix A, part 2, and FIG. 18. 
The environment will be set up with the specifications that are necessary 
to show the operation of essential elements. It is important to notice 
that the environment could be set up otherwise, for different purposes. 
Referring to FIG. 18, this procedure may be abbreviated as follows: A 
section and environment are selected (by default, the gen.sub.-- prog 
section in the layout environment). Case.sub.-- db files are created. The 
col.sub.-- prog window is defined. Data cells and permanent cells are 
defined. Spreadsheet formulas are defined. 
Procedure Setup also comprises the definition of the Col.sub.-- Prog 
Window, shown in FIG. 19. A summarized description of this procedure 
follows: The col.sub.-- prog window is named and thus, identified. The 
following definitions are enacted: col.sub.-- prog range, time.sub.-- 
based (TRUE or FALSE), col.sub.-- prev range (if time.sub.-- based=TRUE), 
assigned case.sub.-- db files, the identifiers for each assigned 
case.sub.-- db file and the identifier for the active case.sub.-- db file 
(which, in turn, is activated), the active record's identifier. 
A more detailed description of Procedure Setup is presented in the 
following paragraphs. 
The operation of the invention may be described similarly in the layout or 
in the application environment. Therefore, for the sake of simplicity, 
this description will primarily consider the case of the layout 
environment. The differences in the application environment are not 
essential to understanding the invention's operation. 
After the program begins to execute, the user selects the layout 
environment in the gen.sub.-- prog section to define one or more 
col.sub.-- prog windows. (This is represented by the statements 
"Init.sub.-- section(GEN.sub.-- PROG)" and "Init.sub.-- 
environment(LAYOUT)" in procedure Initiate). 
The user may create the necessary case.sub.-- db files (statement 
"Create.sub.-- Dbf"). Yet this is optional, because the user may create 
these files independently of this invention. 
The user must define a col.sub.-- prog window in the layout environment 
(statement "define col.sub.-- prog window"). Although the invention 
contemplates the definition of several col.sub.-- prog windows, this 
description requires only one col.sub.-- prog window definition. 
The definition of a col.sub.-- prog window begins by naming it (statement 
"identify col.sub.-- prog window"). The following items compose the named 
col.sub.-- prog window: 
a) The location of the col.sub.-- prog range (statement "define col.sub.-- 
prog range"); 
b) Whether the database records are chained-dependent (statement 
"time.sub.-- based:=TRUE"); 
c) If the database records are chained-dependent, the location of the 
col.sub.-- prev range (statement "if (time.sub.-- based) then define 
col.sub.-- prev range"); 
d) The names and directories for the assigned case.sub.-- db files 
(statement "assign case.sub.-- dbf"); 
e) The identifier of the assigned case.sub.-- db file that will be the 
active case.sub.-- db file (statement "active case.sub.-- dbf:=assigned 
case.sub.-- dbf[id.sub.-- file]"); 
f) The identifier of the initial active record for the operation of the 
active case.sub.-- db file (statement "define.sub.-- id(active record)"). 
Each case.sub.-- db file assigned to a col.sub.-- prog window has an 
associated identifier. This identifier allows the program to select a 
case.sub.-- db file for activation, without requiring the file's directory 
and name. Each identifier also allows the program to activate case.sub.-- 
db files more efficiently than if they had not been previously assigned to 
the col.sub.-- prog window. 
The active case.sub.-- db file is selected from the assigned case.sub.-- db 
files by using the case.sub.-- db file identifier. This is exactly how the 
SELECT command operates in prior-art database programs. These programs 
allow only one file to be selected at a time. This matches the invention's 
restriction of allowing only one active case.sub.-- db file at any time. 
Each record in the active case.sub.-- db file also has an associated 
identifier. This identifier allows the invention to select a case.sub.-- 
db record to read from or write to it. Thus, record identifiers operate 
similarly to the assigned case.sub.-- db file identifiers. 
Three related entities, called dynamic links, are essential to the 
operation of the invention. Each dynamic link contains the actual value of 
the identifier corresponding, respectively, to the current record, the 
active record and the previous record. These dynamic links also have to be 
defined at this stage of program execution. Finally, to finish the 
environment set-up, the user defines data cells and formulas in the 
gen.sub.-- prog section, and permanent cells in the col.sub.-- prog range 
(statements "define data cells", "define permanent cells" and "define 
formulas"). As mentioned previously, all cells in the gen.sub.-- prog 
section are outcome cells, unless they are defined as data cells. Also, by 
default, data cells in the col.sub.-- prog range are temporary cells, 
unless they are defined as permanent cells. Outcome cells in the 
col.sub.-- prog range are already permanent cells, and the user may not 
modify their status. 
The dimension of the col.sub.-- prog range and the dimension of each record 
in the active case.sub.-- db file may not be the same. The invention may 
handle this problem in several ways. The following paragraphs describe the 
solution envisioned for the preferred embodiment implementation: 
1) If the number of cells in the col.sub.-- prog range is greater than the 
number of fields in the case.sub.-- db file, the program loads the entire 
case.sub.-- db record into the col.sub.-- prog range. In this case it does 
not modify the remaining cells of the col.sub.-- prog range. 
2) If the number of cells in the col.sub.-- prog range is smaller than the 
number of fields in the case.sub.-- db file, the program loads only the 
record fields that fit into the col.sub.-- prog range. 
3) In both cases, the unload operation writes back the same number of 
fields that was loaded into the col.sub.-- prog range. 
SECTIONS AND ENVIRONMENTS 
Procedures Select.sub.-- Section, Select.sub.-- Environment and 
Select.sub.-- Window 
See Appendix A, parts 5, 6, and 7. 
Also see FIGS. 21, 22 and 23. 
The user can select whether to operate in the gen.sub.-- prog section or 
the scratch section. In the scratch section, the scratch environment takes 
control of the user interface. While operating in the gen.sub.-- prog 
section, the user may choose between the layout environment and the 
application environment. Also, in any of these environments, the user can 
activate or deactivate the col.sub.-- prog environment. 
In the preferred embodiment implementation demonstrated in Appendix A, 
parts 5, 6 and 7, the key-activated commands allow the user to make a 
choice. 
Also, in the preferred embodiment implementation, a special key called the 
window.sub.-- key activates or deactivates a col.sub.-- prog window. When 
there is only one col.sub.-- prog window, the window.sub.-- key acts as a 
switch to put the user in the col.sub.-- prog window or take the user out 
of it. When there is more than one col.sub.-- prog window, the 
window.sub.-- key displays a list of options. This list contains all 
defined col.sub.-- prog windows defined, with their respective 
environments, and the layout environment. The user points to the desired 
environment and the program provides immediate access to the selected 
environment. 
BASIC VIDEO OPERATIONS 
Procedure Process.sub.-- Video 
See Appendix A, part 8, and see FIG. 24. 
As already described, when the user is in a col.sub.-- prog environment, 
the video.sub.-- sa generates an interactive database browse view of the 
active case.sub.-- db file. Cursor keys and data input operations are 
similar to those of ordinary database browse operations. 
Referring to FIG. 24, this procedure may be abbreviated as follows: Two 
previous conditions are taken into consideration: the key pressed (any 
cursor key activates this procedure), and the previous state of the video 
screen display. All case.sub.-- db records are shown in a browse view, 
except for the active record, in whose place the col.sub.-- prog range is 
shown. Data indicators, cursors and video indicators are shown as 
required. 
A more detailed description of Procedure Process.sub.-- Video is presented 
in the following paragraphs. 
With the exception of the active record, all the records' information that 
fits in the view window is shown immediately in a browse view. For the 
active record, its own view is substituted by a view of the col.sub.-- 
prog range. This view actually displayed the contents of the cells in the 
col.sub.-- prog range. Formula-containing cells are shown by displaying 
the results of formula operations on the contents of the active record. 
Also, the col.sub.-- prog environment implements operations that require a 
dynamic connection between the active case.sub.-- db file and the 
col.sub.-- prog range. 
Examples of these operations are the simple.sub.-- calc and full.sub.-- 
recalc operations. 
Cursor keys 
The browse view in the col.sub.-- prog environment shows the case.sub.-- db 
records in columnar view. By pressing the right or left cursor keys within 
the col.sub.-- prog window, the user can look at different records of the 
active case.sub.-- db file. Similarly, by pressing the up and down cursor 
keys, the user can look at different fields of a case.sub.-- db record. 
This operation is similar to a browse operation in a database program. 
The span of the information limits the movement of the cursor keys. For 
example, the right cursor key operates until it points to the last record 
in the assigned database file. The size of the col.sub.-- prog range 
limits up and down movements. The col.sub.-- prog range size depends on 
the spreadsheet range defined for a particular screen in the application 
environment. 
In the preferred embodiment implementation for the invention, pressing the 
Ctrl key and a right or left cursor key executes control cursor key 
operations. These operations place the cursor immediately at the 
designated edge of the screen. 
Data and outcome fields 
The type of operation on an individual record field depends on whether it 
corresponds to a data or to an outcome cell in the col.sub.-- prog range. 
Thus, a data field is a record field that would be placed in a data cell, 
if the record that contains it was loaded into the col.sub.-- prog range. 
An outcome field is a record field that would be placed in an outcome 
cell, under similar circumstances. Data fields are the only fields that 
may be modified while the program is operating in the application 
environment. 
The screen cursor 
The screen cursor signals the position where the next data input operation 
will be executed. Thus, it is visible only if the screen displays at least 
one col.sub.-- prog data cell or data field. When it is visible, the 
screen column where it is positioned shows the contents of the current 
record. 
If the screen does not display any data cells or data fields, the column 
cursor is active instead of the screen cursor. The column cursor's 
position always indicates the current record. 
Logical chain of procedural statements 
The program executes, in the following order, the essential ideas described 
for the video operation of the col.sub.-- prog window (see Appendix A, 
part 8 -procedure Process.sub.-- Video): 
1. Initialize all state variables (flags). 
2. Determine the video screen's boundaries. Position the screen and column 
cursors, according to their previous conditions and the cursor key pressed 
by the user (keypress). 
3. Display all records as in a browse view, except the active record which 
is substituted by the col.sub.-- prog range. 
4. Use special indicators to display all col.sub.-- prog range data cells 
and data fields in the col.sub.-- prog window (in most prior-art 
references, data cells and data fields are represented in a different 
color). 
5. Show the screen cursor only if any col.sub.-- prog range data cells or 
data fields are visible in the screen. Show the column cursor if the 
screen cursor is not visible. 
6. Display modified video indicators. Most important among these are: 
mark.sub.-- record flags, need.sub.-- rec.sub.-- calc flags, active column 
indicator, need.sub.-- spreadsheet.sub.-- calc flag, available spreadsheet 
memory, other spreadsheet and database indicators. 
FORMULA EDITING 
Procedure Process.sub.-- Formula.sub.-- Edit 
See Appendix A, part 9, and also see FIG. 25. 
The user presses the formula.sub.-- edit.sub.-- key to modify a col.sub.-- 
prog range formula in the cell where the cursor is. 
Referring to FIG. 25, this procedure may be abbreviated as follows: If 
environment is LAYOUT, then formula.sub.-- edit is accepted and results 
are marked for storage in the ANS file. Else, formula.sub.-- edit is 
accepted and results are marked for storage in the CAP file, only if 
current cell is data cell. Else, formula.sub.-- edit is rejected. 
A more detailed description of Procedure Process.sub.-- Formula.sub.-- Edit 
is presented in the following paragraphs. 
The application environment does not allow formula edition in outcome 
cells. This is possible only in the layout environment. 
In the layout environment, if the cursor is on an outcome cell, the cell 
accepts the action of the formula.sub.-- edit key. This executes the 
formula edit operation. The formula will be saved in the ans.sub.-- file, 
the main spreadsheet file. This file also stores all texts, numbers or 
formulas defined in permanent outcome cells. 
If the cursor is on a data cell, regardless of the environment, the 
formula.sub.-- edit.sub.-- key executes the formula edit operation. This 
operation is similar to formula edit operations in ordinary spreadsheet 
programs. If the data cell is a permanent cell, the formula will be saved 
in the cap.sub.-- file. In the preferred embodiment implementation, the 
value resulting from the evaluation of that formula is also stored in the 
ans.sub.-- file. 
These protection modes allow the programmer and the user to get maximum 
benefit from the program's features. In the layout environment of the 
gen.sub.-- prog section, the programmer can build an application by 
defining or editing formulas in the outcome cells. Then the programmer 
deliberately transforms the layout environment into the application 
environment, to let the user evaluate the formulas on the data. For 
example, in a financial model, the user may want to examine the 
consequences of changes in the firm's financial conditions. To do this, 
the user modifies all data cells in the application environment, even if 
they contain formulas. 
DATABASE RECORDS FLAGS 
Procedure Process.sub.-- Mark.sub.-- Record 
See Appendix A, part 10, and also see FIG. 26. 
The mark.sub.-- record key controls a state variable (flag) associated to 
each record in a case.sub.-- db file. This state variable is the 
mark.sub.-- record flag. The mark.sub.-- record flag variable controls 
whether a database record can be loaded into the col.sub.-- prog range 
when required. 
The mark.sub.-- record key can switch the status of a record to any of 
three possible conditions: 
SP (SPreadsheet), 
DB (DataBase), 
PR (PRotected). 
SP allows a record to be loaded into the col.sub.-- prog range, and to be 
modified. 
DB does not allow a record to be loaded into the col.sub.-- prog range, 
although it may be modified. 
PR is the most restrictive status, because it does not allow the record to 
be modified or loaded into the col.sub.-- prog range. When a record is in 
PR status, the scrolling of the screen is active but all record fields may 
not be modified. The screen cursor has access to these fields only if the 
mark.sub.-- record key switches the record to a different status. 
The user presses the mark.sub.-- record key until the mark.sub.-- record 
flat is set to DB to signal the contents of a record that are not going to 
be modified in the application environment. In a financial application, 
for example, a user prepares an extensive set of records that contain the 
financial history of the firm. The user does not want to modify these 
records while running the model. So, the user sets the mark.sub.-- record 
flag OFF for every period (record) of historical data. 
The operation of mark.sub.-- record flags in simple and full recalculation 
(sections 5 and 6) and field input (section 7) will clarify the functions 
of this key. 
SIMPLE RECALCULATION 
Procedure Calc 
See Appendix A, part 11, and also see FIG. 27. 
Two kinds of recalculation are possible: simple recalculation (CALC or 
recalc, for short) and full recalculation. The calc.sub.-- key activates 
simple recalculation (procedure calc). 
FIG. 27 shows a simplified case, where Mark.sub.-- Record and Need.sub.-- 
Rec.sub.-- Calc flags are not considered. In this figure, the sequence of 
steps followed are: If the current record is also the active record, then 
a spreadsheet calc is executed and the process ends. Else, a spreadsheet 
calc is executed if needed, then the col.sub.-- prog range's contents are 
written to the active record, the current record's contents are read into 
the col.sub.-- prog range. The current record is then identified as the 
active record and, if application is time.sub.-- based, the 
previous.sub.-- record is also loaded into the col.sub.-- prev range. 
Finally, a spreadsheet calc is executed. 
A more detailed description of Procedure Calc is presented in the following 
paragraphs. 
When the user presses the calc.sub.-- key, the program begins procedure 
calc. Procedure calc executes two basic operations: 
a) a read operation of the current record to the col.sub.-- prog range, if 
allowed by the current record's mark.sub.-- record flag, and 
b) a spreadsheet recalc. 
For the first operation, the program identifies the current record as the 
record where the screen (column) cursor is. Then the current record is 
compared to the active record, which is the last record loaded into the 
col.sub.-- prog range. (More properly said, its identifiers are compared. 
Record identifiers are numbers used to point and select specific records 
in the case.sub.-- db file. If two records' identifiers are the same 
number, then these records are the same.) 
If the current record and the active record are the same (i.e., their 
identifiers are the same), the program only performs a spreadsheet recalc. 
A spreadsheet recalc executes all formulas defined in the spreadsheet 
cells. This operation is performed as in the most popular spreadsheet 
programs currently in the market, such as the LOTUS 1-2-3.TM. release 2.2 
spreadsheet program. 
If the current record is not the same record as the active record, then the 
program checks if the current record may be loaded into the col.sub.-- 
prog range. The mark.sub.-- record flag forbids such operation if its 
status is "DB" or "PR." The current record may be loaded into the 
col.sub.-- prog range only if the status of the mark.sub.-- record flag 
for the current record is SP. If the current record may not be loaded, 
procedure calc only executes a spreadsheet recalc. 
Before the current record is loaded into the col.sub.-- prog range, the 
col.sub.-- prog range has to "unload" by writing its contents back to the 
active record (the last record loaded) in the case.sub.-- db file. 
Also, before unloading the contents of the col.sub.-- prog range to the 
active record, the need.sub.-- spreadsheet.sub.-- calc flag has to be 
checked. If the need.sub.-- spreadsheet.sub.-- calc flag is TRUE, then the 
spreadsheet needs to be recalculated, since one or more spreadsheet data 
have been changed with no subsequent spreadsheet recalc. In this case, a 
spreadsheet recalc is executed before the col.sub.-- prog range is 
unloaded. Otherwise, the col.sub.-- prog range's results would not be 
correct when written back to the active record. 
The total sequence of operation in procedure calc is as follows. First, a 
spreadsheet calc is executed (if needed). Then, if the current record is 
going to be read into the col.sub.-- prog range, the col.sub.-- prog range 
"unloads" by writing its entire cells' contents to the active record (this 
is needed in order to save the col.sub.-- prog range's contents). After 
this operation, if mark.sub.-- record's status for current record is SP, 
the current record is loaded into the col.sub.-- prog range. Thus, the 
current record becomes the new active record. Finally, a spreadsheet 
recalc is always executed. 
A special consideration has to be made when the current record is loaded 
(read) from the database file into the col.sub.-- prog range. The 
col.sub.-- prog range may contain permanent data, texts and formulas, and 
this read operation may not modify permanent cells in the col.sub.-- prog 
range. 
Finally, in a time-based application, when the program loads a record into 
the col.sub.-- prog range, it must also read the previous record into the 
col.sub.-- prev range. The col.sub.-- prev range cells may only contain 
data, not formulas. The previous record must be read entirely into the 
col.sub.-- prev range. 
Notice that the mark.sub.-- record flag does not control whether the 
previous record can be loaded into the col.sub.-- prev range. This range 
cannot be modified and is not subject to recalculation, since it contains 
only data. Also, the col.sub.-- prev range does not write information back 
to the database record from which it read. 
FULL RECALCULATION 
Procedure Full.sub.-- Recalc 
See Appendix A, part 12, and also see FIG. 28. 
The full recalculation procedure, or procedure full.sub.-- recalc, is a 
composite calculation procedure that applies only to time-based 
applications. Full recalculation requires the application to be previously 
defined as time-based. 
Referring to FIG. 28, this procedure may be abbreviated as follows: If 
application is not time.sub.-- based, then this process doesn't execute 
any operations. Else, cursor is positioned at first record (that is, the 
first record is made the current record). The next record with Mark.sub.-- 
Record=SP and Need.sub.-- Rec.sub.-- Calc=TRUE is found (if not found, 
Process Full.sub.-- Recalc terminates here). Procedure Calc is executed. 
Cursor is advanced one record and procedure Calc is executed again until 
Mark.sub.-- Record for Current.sub.-- Record &lt;&gt; SP. If current.sub.-- 
record is not last.sub.-- record, the next record with Mark.sub.-- 
Record=SP and Need.sub.-- Rec.sub.-- Calc=TRUE is found and the process 
starts again from that record. 
A more detailed description of Procedure Process.sub.-- Formula.sub.-- Edit 
is presented in the following paragraphs. 
Full recalculation executes procedure calc repeatedly. It stops until it 
has calculated and written back all database records that had to be 
evaluated, due to changes in a data set. In a financial model, for 
example, a sales level change for a given period alters the financial 
statements for all subsequent periods. Therefore, a full recalculation is 
necessary, to update all periods after the change. 
For procedure full.sub.-- recalc to operate adequately, it is necessary to 
define an additional state variable for each case.sub.-- db record: the 
need.sub.-- rec.sub.-- calc flag. A need.sub.-- rec.sub.-- calc flag has 
two possible states: TRUE and FALSE. The need.sub.-- rec.sub.-- calc flag 
is set to TRUE when the user modifies at least one field in the database 
record. It is set to FALSE when the database record contents are loaded 
into the col.sub.-- prog range, calculated, and written back to the same 
database record. 
Procedure full.sub.-- recalc is activated by pressing the full.sub.-- 
recalc key. The operational sequence may be described as follows: 
1. The procedure checks the status of the mark.sub.-- record flag and the 
need.sub.-- rec.sub.-- calc flag in each record of the active case.sub.-- 
db file. 
2. The first record with the following two characteristics is identified: 
a) the mark.sub.-- record flag set to SP, and 
b) the need.sub.-- rec.sub.-- calc flag set to TRUE. 
The screen cursor is placed over this record. 
3. The procedure executes a simple calc operation on the current record. It 
repeats the process on all contiguous and subsequent records that have the 
mark.sub.-- record flag set to SP. Notice that the program does not 
examine the status of subsequent records' need.sub.-- rec.sub.-- calc 
flags after the current record. 
4. This repetitive procedure stops immediately before reaching the first 
record with a mark.sub.-- record flag that is not set to SP. 
5. Procedure full.sub.-- recalc then searches for the next record with 
mark.sub.-- record flag set to SP and need.sub.-- rec.sub.-- calc flag set 
to TRUE. If it finds such a record, the entire procedure is repeated. 
Otherwise, the procedure stops. 
ACTIVE AND ASSIGNED CASE DB FILES 
Procedures Define.sub.-- Col.sub.-- Prog.sub.-- Window and Change.sub.-- 
Case 
See Appendix A, parts 3 and 13, and also see FIG. 29. 
One or more case.sub.-- db files can be assigned to a col.sub.-- prog 
window. The user may define these assignments. This is represented by the 
following statement in the algorithmic procedure "Define.sub.-- Col.sub.-- 
Prog.sub.-- Window": "assign case.sub.-- dbf". (See Appendix A, part 3.) 
Only one assigned case.sub.-- db file can be active at any particular time. 
In the best mode implementation, the user can change the active 
case.sub.-- db file by pressing the change.sub.-- case key. This operation 
activates the assigned case.sub.-- db file whose identifier immediately 
succeeds the identifier of the previous active case.sub.-- db file. (See 
Appendix A, part 13.) 
FIELD INPUT 
Procedures Main and Field.sub.-- Input 
See Appendix A, parts 1 and 14, and also see FIG. 30. 
Referring to FIG. 30, this procedure may be abbreviated as follows: If 
operating in the APPLICATION environment (that is, environment &lt;&gt; LAYOUT) 
and there is no screen cursor, then field.sub.-- input is rejected. Else, 
keyboard input is accepted and written in the col.sub.-- prog range if the 
current.sub.-- record is also the active record. Then, keyboard input is 
written in the current.sub.-- record's field. Set Need.sub.-- Rec.sub.-- 
Calc is set to TRUE for current.sub.-- record. Finally, if the 
current.sub.-- record is also the previous.sub.-- record, keyboard input 
is also written in the previous record. 
The screen cursor is visible only when it is on any col.sub.-- prog data 
cell or database data field on the screen. When the user presses a cursor 
key, the video cursor moves to the next data cell or data field located in 
the direction indicated by the key. If the screen does not display any 
data cells or data fields, the video cursor is not visible. 
A more detailed description of Procedure Process.sub.-- Formula.sub.-- Edit 
is presented in the following paragraphs. 
To modify the information of any case.sub.-- db record field, the user 
places the cursor on the required field and writes the value or text 
desired. 
While operating in the col-prog environment, a field input operation begins 
when the user presses any key of a set of keys defined previously. See 
Appendix A, part 1, procedure Main, statement "if (Keypress in ANY.sub.-- 
INPUT.sub.-- KEY) then Field.sub.-- Input". ANY.sub.-- INPUT.sub.-- KEY 
represents a set of keys that are validated to begin field input. In the 
preferred embodiment implementation, if the first character is a number or 
any of the signs "()+-," the field will be stored in a floating point 
number format. Otherwise, the field will be stored as a text field. 
The layout environment allows the modification of all fields in every 
record and all cells in the col.sub.-- prog range. In the application 
environment, there are several limitations: 
1) only data fields can be modified, and 
2) the records that contain them cannot have mark.sub.-- record flags set 
to PR (protected). 
In a financial model, for example, all historical financial information 
should not be modifiable. The associated records have mark.sub.-- record 
flags set to PR, to prevent the program from loading them into the 
col.sub.-- prog range. Also, the screen does not display any data fields 
belonging to these records. 
Finally, in all situations, any input operation in a case.sub.-- db record 
field will set the need.sub.-- rec.sub.-- calc to TRUE. 
Logical chain of procedural statements 
The program executes, in the following order, the essential ideas just 
described for field input (see Appendix A, part 14-procedure Field.sub.-- 
Input): 
1. Determine if the screen cursor is visible in the current record. If not, 
then end procedure field.sub.-- input. Otherwise, continue. 
2. Clear the input buffer. Accept keyboard input at the input buffer. 
3. If the current record is the same as the active record, then write 
keyboard input only in the corresponding col.sub.-- prog cell. 
4. If the current record is not the active record nor the previous record, 
then write keyboard input in the current record's corresponding field. 
Then set need.sub.-- rec.sub.-- calc to TRUE for current record. 
5. If the current record is the previous record, then write in the 
col.sub.-- prev range. 
EXECUTION OF ENT AND SCRATCH ENVIRONMENTS 
Procedures Main, Execute.sub.-- Parent.sub.-- Environment and 
Execute.sub.-- Scratch.sub.-- Environment 
See Appendix A, parts 1, 15 and 16, and FIG. 31. 
At any given time, the active environment in the gen.sub.-- prog section is 
the environment that controls the operation of the gen.sub.-- prog 
section. The user selects the active environment through the use of a 
special key, called the environment key. As explained before, only the 
layout and the application environments can be the active environment in 
the gen.sub.-- prog section. 
When the user operates in the scratch environment, or in the layout or 
application environment in the gen.sub.-- prog section, the active 
environment controls the operation of the invention. Yet, when the user 
operates in the col.sub.-- prog environment, there are several special 
considerations. 
The col.sub.-- prog environment inherits from the active environment in the 
gen.sub.-- prog section the commands that do not conflict with its 
proprietary commands. As mentioned previously, this is why the layout and 
the application environments may be called parent environments. 
In the col.sub.-- prog environment, if the user presses a key that is not 
defined in its proprietary commands, the program checks the command set of 
the parent environment. See Appendix A, part 1, procedure Main, statement 
"if (Keypress in ANY.sub.-- OTHER.sub.-- KEY) then Execute.sub.-- 
Parent.sub.-- Environment". Statements in procedure Main show all 
proprietary commands of the col.sub.-- prog environment. 
The scratch environment is a spreadsheet environment, while the layout 
environment is an enhanced spreadsheet environment. Consequently, the 
layout environment matches the scratch environment in the commands that do 
not conflict with its proprietary commands. See Appendix A, parts 15 and 
16, procedures Execute.sub.-- Parent.sub.-- Environment and Execute.sub.-- 
Scratch.sub.-- Environment. 
NON-ESSENTIAL OPERATIONS 
There are several operations that are not necessary for the operation of 
this invention. Some of them are described in the following paragraphs. 
These operations are not shown in the pseudocode algorithm of Appendix A. 
Simultaneous video operation of assigned case.sub.-- db files 
In the preferred embodiment implementation, records from all assigned 
case.sub.-- db files may be displayed simultaneously on the col.sub.-- 
prog window video screen. To execute this operation, the sim.sub.-- video 
operation, the user issues an explicit command by pressing the sim.sub.-- 
video key (Alt-F6). 
The sim.sub.-- video operation displays records from assigned case.sub.-- 
db files in the order indicated by a selected field, the index field. The 
computer screen only displays as many records as fit according to its 
dimensions. 
The index fields of several records may have the same value. If this 
happens, the sim.sub.-- video operation displays these records in the 
order in which their case.sub.-- db files were assigned to the col.sub.-- 
prog window. 
While executing an application for the special case of a financial model, 
the sim.sub.-- video operation would follow these steps: 
Each case--db file contains one set of financial forecasts for the firm. 
Several case.sub.-- db files are created, to represent several possible 
scenarios for the future of the firm. 
Each case.sub.-- db record represents the financial information for a 
particular point in time. 
Consecutive records correspond to consecutive periods. 
The date field is the index field in a case.sub.-- db record needed for the 
sim.sub.-- video operation. 
Therefore, the sim.sub.-- video operation displays all records with the 
same date simultaneously and side-to-side. The record that appears 
left-most belongs to the scenario that was assigned first to the 
col.sub.-- prog window. 
Operational Codes 
Database files being used by the invention may contain special operational 
codes. These codes can control the operation of specific database fields 
or spreadsheet cells. In financial applications developed using the 
invention, each case.sub.-- db record may represent financial results for 
a particular period. Each field in a case.sub.-- db record may represent a 
financial concept such as sales or cash flow level. Here, operational 
codes can be used to allow the results of a financial application to be 
calculated and expressed in different currencies, as required by the user. 
These operations do not change formulas in the spreadsheet environment. A 
particular operational code may identify the currency used as the field's 
unit of measure, while another may identify the exchange rate. Specific 
instructions associated with op.sub.-- codes execute all exchange currency 
conversions. 
Uses and applications of operational codes are not limited to the example 
presented above. Operational codes expand the power and flexibility of the 
invention's programmability for all kinds of applications. 
Multi-record Database Operations 
In the preferred embodiment implementation, some formulas in the col.sub.-- 
prog range may access data from different records in the same database 
file. These formulas are expressed in terms of special database read and 
write functions. Some examples are: 
@DB.sub.-- SUM, a read function that calculates the sum of a several record 
fields' contents 
@DB.sub.-- GROW, a write function that calculates an increase over a 
sequence of record fields' contents, according to a constant growth rate 
(used in time-based applications) 
@DB.sub.-- SPREAD, a write function that distributes an original amount 
over a sequence of record fields (where each record corresponds to a 
single time period). 
Relational Databases 
The invention can be extended to operate with relational databases. This 
concept adds powerful advantages to the invention. This is proven by the 
growth of the relational database application market. 
The description in this document considers the definition of only one 
col.sub.-- prog window. Yet, the preferred embodiment implementation can 
define and activate several col.sub.-- prog windows simultaneously in a 
single spreadsheet application. The col.sub.-- prog range in each 
col.sub.-- prog window accesses its corresponding active case.sub.-- db 
file. 
Through these operations, and through the coordination of a central 
program, the col.sub.-- prog windows and the case.sub.-- db files can 
compose a relational database system. (See FIG. 14.) The user develops 
this program in the layout environment of the gen.sub.-- prog section. 
Thus, in this invention, the user can access relational database 
components, view them on a single screen and operate on their contents 
with spreadsheet flexibility. 
Development of the Application Environment 
As already described, the layout environment is an enhanced spreadsheet 
environment. The programmer may use a special command set, called the 
layout command, to define the tools available to the user for the 
operation of the application environment. Thus, the application 
environment comprises the tools that have been defined in the layout 
environment. In the preferred embodiment implementation, these are the 
only tools and operations available to the user in the application 
environment. 
In the preferred embodiment implementation, the layout command can define 
the following tools of the application environment: 
Application screens: The programmer defines all screens by associating them 
to a spreadsheet range. To identify a screen that must operate in the 
col.sub.-- prog environment, a range that is a strict subset of a 
col.sub.-- prog range must be selected. 
Menus: The programmer defines menus by filling the blanks in a special list 
of options in the layout environment. All menu options call other menus or 
application screens (identified by their corresponding cell ranges). 
Programming: Routines from a procedural programming language can be linked 
to application screens. 
Context-sensitive Help Screens: The programmer uses a text editor in the 
layout environment to develop help text. 
Data input validation: Data input validation conditions are associated with 
defined data cells in the gen.sub.-- prog section. These conditions 
include range and type checking of data input. 
Turing Machine Emulation (Automata Theory) 
The invention can operate as the software equivalent of a hardware parallel 
processor for several Turing machines. Additionally, some tools of the 
invention can make it operate more efficiently than parallel Turing 
machines. Since any computation or recognition problem for which there is 
a known informal algorithm can be handled by a Turing machine, therefore, 
the invention can handle and solve all sorts of programming problems. 
In the invention, each field in a case.sub.-- db record may contain data, 
text, or formulas. If a particular field contains formulas in spreadsheet 
format, each read operation will load these formulas in the col.sub.-- 
prog range. The spreadsheet calc operation will produce the expected 
results, given the formulas' syntax. 
For the invention to emulate a set of Turing machines, a minimum of two 
col.sub.-- prog windows may operate simultaneously, and at least one of 
them must read and write formulas in its active case.sub.-- db file. This 
is the software equivalent of a hardware parallel processor for a number 
of Turing machines. Each col.sub.-- prog range's cell corresponds to a 
single processor in the parallel system's hardware. All data and results 
may be processed in a case.sub.-- db file and col.sub.-- prog window 
different from that which is used to process formulas. 
There is considerable evidence that the partial functions computed by 
languages, which are recognized by Turing machines, are exactly those 
recognized by informal effective procedures or algorithms. Thus, as 
mentioned previously, any computation or recognition problem for which 
there is a known informal algorithm can be handled by a Turing machine. 
The problem of efficiency associated to Turing machines can also be solved 
with this invention. (This problem refers to the conclusion that some 
Turing machine computations may require an inordinately large measure of 
tape.) 
The order in which the case.sub.-- db records are read may be controlled by 
a procedural language. Also, it is possible to organize case.sub.-- db 
records by filtering, indexing, and sorting them according to their 
associated indexes. In any of these ways, it is possible to control a 
three-level algorithm, described as follows: 
1) first level, procedural language; 
2) second level, formulas in the case.sub.-- db records; and 
3) third level, formulas in the spreadsheet storage area. 
This produces an efficient instrument for the development and operation of 
any computation or recognition problem. 
Ramifications and Scope of Invention 
While the previous description contains many specificities, these should 
not be construed as limitations on the scope of the invention, but rather 
as an exemplification of one preferred implementation thereof. The 
following paragraphs explain many other alternative schemes for special 
structures that relate to the operation of the col.sub.-- prog window and 
col.sub.-- prog environment with the active case.sub.-- db files. 
Alternative implementation schemes are described for each of the 
structures, identified `A` to `D`. 
A. The col--prog range cells may be organized in two different ways: 
a) Each col--prog range may consist of a single column of spreadsheet 
cells. This interpretation corresponds to the best mode implementation. 
b) Each col--prog range may be an ordered set of disjoint spreadsheet 
cells. 
B. The relationship between the spreadsheet's col--prog range and the 
database files may operate as follows: 
a) Each read and write operation, to and from the col--prog range, is 
performed on one database record at a time. 
This is the best mode implementation, which is adequate for applications 
with a large number of records, each with few fields. An example is an 
inventory analysis program, where each record represents a single product. 
The fields contain past sales volume information, price, inventories, etc. 
This implementation is appropriate for any scenario that requires 
efficient reading and writing to one database record. 
b) The spreadsheet interacts with all records of a database file at a time, 
by accessing the same field in each record. Each read and write operation 
to and from the col--prog range is performed on only one field in every 
record. 
This is suitable for applications with very few records, each with many 
fields. An example is a financial model of a firm, with many accounts 
(fields in a record), and few periods (records) to be simulated and/or 
stored as history. 
C. Information may be transferred back and forth through the link between 
spreadsheet and the database as follows: 
a) One database record at a time; this is the best mode implementation as 
described. 
b) A subset of database file records is substituted into a spreadsheet 
range. Spreadsheet operations are executed one column on one record at a 
time. This is the idea behind some spreadsheet database tools, which 
execute all query operations in the spreadsheet storage area. 
c) Information is transferred back and forth between the database files and 
the col--prog range, through an intermediate storage area. An example is 
the use of main memory as buffer memory for communication. This 
implementation may accelerate the performance of slow computer disk 
drives. 
D. Particular implementations may have the following requirements: 
a) Independent database records. 
b) Each database record contains the information corresponding to a single 
time period. 
This is the case of most financial models: the results corresponding to 
each period depend on the values of the previous period. These 
applications involve an additional set of spreadsheet cells, called 
col--prev range. A col--prev range is associated with each col--prog 
range. A col--prev range contains data from the period previous to the 
col--prog range period. 
E. A separate storage area and a separate environment area may be created 
to store and operate elements similar to those defined in the col--prog 
window. Also, they may be used to implement operations similar to those 
described for the col--prog environment. 
Said separate environment contains commands and operations for the access 
and operation of information in the case--db files and said separate 
information storage area. Said separate environment can integrate the 
operation of data stored in the gen--prog section with data stored in said 
separate storage area by the use of formulas, in a similar manner to 
formulas used in a spreadsheet environment. 
However, said separate environment does not inherit all the characteristics 
and basic commands from the parent environment (layout or application 
environment), since there is not a parent environment to inherit from, nor 
is the data structure compatible with that operated by the layout or 
application environments. 
Accordingly, the scope of the invention should be determined by the 
appended claims and their legal equivalents. 
##SPC1##