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

• US 5,293,615 A
• Filed: 11/16/1990
• Issued: 03/08/1994
• Est. Priority Date: 11/16/1990
• Status: Expired due to Term

• Current Assignee: Carlos A. Amada
• Original Assignee: Carlos A. Amada
• Inventors: Amada, Carlos A.
• Primary Examiner(s): Lee, Thomas C.
• Assistant Examiner(s): Harrity, Paul
• Application Number:
  US07/614,907
• Time in Patent Office: 1,208 Days
• Field of Search: 364/419, 364/222.81, 364/200, 364/900, 364/401, 364/419, 395/600, 395/800, 395/157, 395/161, 395/118, 395/700, 340/709
• US Class Current: 1/1
• CPC Class Codes: G06F 40/18 of spreadsheets form-filling G06F40/174
• CPC Class Codes: Y10S 707/99934 Query formulation, input preparation, or translation

Abstract

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.

Claims

1. An apparatus for carrying out spreadsheet calculations using data imported from a database via a point and shoot user interface to select a database record for loading into a specified range of the spreadsheet, comprising:

a computer having a display and a memory having one or more storage locations;

a spreadsheet program, having a spreadsheet data format, in execution on said computer for displaying a plurality of cells which define a spreadsheet, each of said cells having a unique identification code indicative of the location of said cell on said display and each said cell being associated with one or more of said storage locations in said memory, and wherein any one or more of said cells can be selected by a user as part of a selected range using a cursor, and wherein said spreadsheet program includes program code to control said computer to receive data from said user and to store said data in the storage locations associated with user designated cells wherein said data can be text, numbers, or formulas including mathematical or logical operators relating contents of the storage locations associated with one or more of said cells to the contents of the storage locations of one or more other ones of said cells mathematically or logically, and wherein said computer is controlled by said spreadsheet program to display on said display the contents of said storage locations associated with each said cell, except where the contents of the storage locations associated with a particular one of said cells are a formula, in which case the computer is caused to display the results of the mathematical or logical operations defined by said formula at the corresponding location for said cell on said display, and wherein said spreadsheet program includes a recalculation routine for controlling said computer so as to perform a recalculation upon receipt of a recalculation command where all formulas associated with all cells of said spreadsheet are recalculated using whatever the current values are for each said cell stored in the associated storage locations in said memory at the time the recalculation command is received and any new results stemming from recalculation of said formulas where new contents since a previous recalculation exist in the storage locations associated with said cells mathematically or logically related by said formulas are displayed on said display at the location of the corresponding cells;

a database file stored in said memory and containing one or more records, each said record is comprised of one or more fields, each said field contains data;

first means in execution on said computer for controlling said computer so as to display on said display one or more of said records of said database file within a window while said spreadsheet program is in execution;

second means in execution on said computer and coupled to said recalculation routine and to said database file and to said first means, for controlling said computer to display a cursor in said window in which said records from said database file are displayed, said cursor being movable within said window under control of said user and controlling said computer to recognize a select command entered by said user such that any said data record pointed to by said cursor at the time said select command is given by said user is selected, and for controlling said computer so as to automatically load said selected database record into said selected range of said spreadsheet when said recalculation command is given, said loading including automatically reformatting data of said selected database record to said spreadsheet data format prior to the recalculation of said spreadsheet, such that when said recalculation is performed by said spreadsheet program, the contents of the storage locations associated with the cells in said selected range will be set equal to the contents of the selected database record before the recalculation starts thereby forcing the storage locations associated with the cells of said selected range to be loaded with the contents of said fields of said selected database record before the recalculation starts;

database record identifier means for controlling said computer to maintain data pointing to the last database record loaded from said database file into said selected range of said spreadsheet; and

means for controlling said computer to load original contents of said storage locations associated with the cells of said selected range of said spreadsheet back into the database record pointed to by said database record identifier means before the contents of said selected database record are loaded into the cells of said selected range when said recalculation command is issued and before the recalculation is carried out.

2. The apparatus of claim 1 further comprising means coupled to said spreadsheet program and to said second means and database file, for carrying out loading of said cells of said selected range from fields of said selected record from said database file in accordance with a selectable mapping protocol defining which fields of said selected database record are loaded into specific cells of said selected range of said spreadsheet.

3. The apparatus of claim 2 wherein the process of loading said selected database record into the cells of said selected range of said database is accomplished using a one-to-one mapping between the cells of said selected range and said fields of said selected database record, such that when said recalculation is performed by said spreadsheet program, the contents of storage locations associated with the cells of the selected range of said spreadsheet before the recalculation starts will be set equal to the contents of selected fields of the selected database record according to said mapping protocol and wherein any formulas contained in the corresponding fields of said selected database record are stored in the storage locations associated with corresponding cells in said selected range of said spreadsheet, thereby forcing the recalculation process to use corresponding numbers, text and formulas from said database record in the storage locations associated with the cells of said selected range of said spreadsheet during said recalculation.

4. The apparatus of claim 2 further comprising:

intermediate processing means coupled to said database file and said first means for receiving one or more selected database record(s) and for performing a user selectable one or more of a plurality of predetermined processes on said database record and outputting a modified database record; and

wherein said second means is coupled to said intermediate means and includes;

program code for controlling said computer to receive said modified database record from said intermediate processing means and displaying said modified record in said window, and for controlling said computer to display a cursor in said window in which said modified database record is displayed and to recognize a select command entered by a user and to select any said modified record pointed to by said cursor when said select command is received and for loading the contents of said selected modified database record into said storage locations associated with the cells within said selected range of said spreadsheet when said recalculation command is given but prior to the recalculation of said spreadsheet which results from issuance of said recalculation command by said user, such that when said recalculation is performed by said spreadsheet program, the contents of the storage locations associated with said selected range of cells of said spreadsheet will be set equal to the contents of selected fields of said selected modified database record according to said mapping protocol thereby forcing the recalculation process to use selected fields of said modified database record in the cells of said selected range during the recalculation.

5. The apparatus of claim 4 wherein said intermediate processing includes indexing means for either filtering or sorting said selected database records or for changing the mapping between the fields of the database records and the cells of said selected range.

6. The apparatus of claim 4 further comprising means for displaying said modified database records in said window after said intermediate processing and for loading any said modified database record designated by said user by moving said cursor in said window nto said selected range of said spreadsheet.

7. The apparatus of claim 1 further comprising record display means coupled to said second means and said first means for displaying records from said database file in said window, and for records from said database not currently visible in said window, for displaying said previously nonvisible records in said window upon receipt of a predetermined command from said user thereby allowing said user to designate any said record from said database for loading into said selected range of said spreadsheet by moving said cursor in said window regardless of the size of said window.

8. The apparatus of claim 7 wherein said record display means further comprises scrolling means for causing records from said database not currently visible in said window to scroll across said window sequentially upon receipt of a predetermined command from said user and for stopping the scrolling of records across said window upon receipt of a predetermined command from said user.

9. The apparatus of claim 7 wherein said record display means further comprises scrolling means for causing records from said database not currently visible in said window to scroll across said window sequentially and in stepwise fashion upon receipt of a scroll command from said user thereby allowing said user to bring in a predetermined number of new records into said window each time said scroll command is issued.

10. The apparatus of claim 9 wherein said scrolling means includes means for bringing records not currently displayed in said window into said window sequentially and in stepwise fashion whenever said pointer of said second means is moved to the edge of said window displayed by said first means.

11. The apparatus of claim 1 further comprising a plurality of database files, and wherein said first means displays a plurality of windows, each of which has displayed therein one or more records from a corresponding one of said database files, and wherein said second means displays a pointer on said display in a selectable one of said windows which can be moved to select any record displayed in any said window and which can be moved from window to window to select records from other database files for loading into said selected range.

12. The apparatus of claim 1 further comprising means for bringing other records from each database file not currently displayed in the corresponding window into said corresponding window.

13. The apparatus of claim 12 wherein said means for bringing other records into a particular window comprises scroling means for bringing other records from the corresponding database not currently displayed in any particular window into said corresponding window sequentially and in stepwise fashion whenever said pointer is moved to the edge of said corresponding window.

14. The apparatus of claim 1 further comprising ripple recalculation means coupled to said second means, to said recalculation means, to said first means and to said database file for sequentially loading a sequence of said database records, one at a time, into said selected reange of said spreadsheet starting from a predetermined database record, and for automatically setting said database record identifier pointer to point to the database record just loaded anytime between the time said new record is loaded into said selected range and before the next record in the sequence of records to be loaded into said selected range is loaded, and recalculating said spreadsheet, and, thereafter, for loading the resulting recalculated values displayed in said cells of said selected range back into the database record pointed to by said database records identifier pointer, and for loading the next database record in the sequence of database records to be loaded into said selected range of said spreadsheet into said selected range of said spreadsheet and repeating the process for all said database records in said sequence of database records to be loaded into said selected range of said spreadsheet.

15. The apparatus of claim 14 wherein said ripple recalculation means repeats the process of loading records from said database file and causing said spreadsheet program to recalculate until all records in the database file have been so processed.

16. The apparatus of claim 14 further comprising means for marking certain records in said database as restricted, and wherein said ripple recalculation means repeats the process of loading records from said database file and causing said spreadsheet program to recalculate until all records in the database file have been so processed except for those records marked as restricted.

17. The apparatus of claim 1 wherein each cell in said selected range can be designated by the user as either permanent or temporary and wherein there is a one-to-one mapping between each cell in said selected range of said spreadsheet and fields in said selected database record, and wherein fields of said selected database record which are mapped to said permanent cells are not loaded into said permanent cells, such that when said recalculation is performed by said computer under control of said spreadsheet program, the contents of the storage locations in memory associated with cells in said selected range of said spreadsheet before the recalculation starts will be set equal to the contents of selected fields of the database record pointed to by said second means except that said permanent cells will not have had their contents altered by the process of loading data from the database record thereby forcing the recalculation process to use selected fields of said database record in the cells of said selected range of cells during the recalculation.

18. The apparatus of claim 1 wherein said spreadsheet program controls said computer so as to be capable of recognizing and executing a plurality of commands, at least one of said commands for specifying a selectable range of said cells and providing a tool which, when invoked by a user allows the user to define a new command which is effective only for the specified selectable range of cells such that different regions of a single spreadsheet can have different command sets associated therewith.

19. An apparatus as defined in claim 18 wherein said spreadsheet program includes program code for controlling said computer to recognize and execute commands which, when executed, allow a user to define a plurality of new ranges of cells and new sets of commands for said new ranges of cells.

20. An apparatus as defined in claim 19 wherein said program code for controlling the computer to allow a user to define new ranges and environments includes computer code for controlling the computer to recognize and execute commands which when invoked by a user allow the user to specify any one or more of the following criteria for any specified new range of cells:

a new appearance in terms of colors,

new line types,

new fonts etc.

a new set of data validation values against which data entered in the cells of said specified range are checked for validity,

new menus of commands, or

new help screens.

21. An apparatus for carrying out spreadsheet calculations using data imported from a database via a point and shoot user interface to select a database record for loading into a specified range of the spreadsheet, comprising:

a computer having a display and a memory having one or more storage locations;

a spreadsheet program, having a spreadsheet data format, in execution on said computer for displaying a plurality of cells which define a spreadsheet, each of said cells having a unique identification code indicative of the location of said cell on said display and each said cell being associated with one or more of said storage locations in said memory, and wherein any one or more of said cells can be selected by a user as part of a selected range using a cursor, and wherein said spreadsheet program includes program code to control said computer to receive data from said user and to store said data in the storage locations associated with user designated cells wherein said data can be text, numbers, or formulas including mathematical or logical operators relating contents of the storage locations associated with one or more of said cells to the contents of the storage locations of one or more other ones of said cells mathematically or logically, and wherein said computer is controlled by said spreadsheet program to display on said display the contents of said storage locations associated with each said cell, except where the contents of the storage locations associated with a particular one of said cells are a formula, in which case the computer is caused to display the results of the mathematical or logical operations defined by said formula at the corresponding location for said cell on said display, and wherein said spreadsheet program includes a recalculation routine for controlling said computer so as to perform a recalculation upon receipt of a recalculation command where all formulas associated with all cells of said spreadsheet are recalculated using whatever the current values are for each said cell stored in the associated storage locations in said memory at the time the recalculation command is received and any new results stemming from recalculation of said formulas where new contents since a previous recalculation exist in the storage locations associated with said cells mathematically or logically related by said formulas are displayed on said display at the location of the corresponding cells;

a database file stored in said memory and containing one or more records, each said record is comprised of one or more fields, each said field contains data;

first means in execution on said computer for controlling said computer so as to display on said display one or more of said records of said database file within a window while said spreadsheet program is in execution;

second means in execution on said computer and coupled to said recalculation routine and to said database file and to said first means, for controlling said computer to display a cursor in said window in which said records from said database file are displayed, said cursor being movable within said window under control of said user and controlling said computer to recognize a select command entered by said user such that any said data record pointed to by said cursor at the time said select command is given by said user is selected, and for controlling said computer so as to automatically load said selected database record into said selected range of said spreadsheet when said recalculation command is given, said loading including automatically reformatting data of said selected database record to said spreadsheet data format prior to the recalculation of said spreadsheet, such that when said recalculation is performed by said spreadsheet program, the contents of the storage locations associated with the cells in said selected range will be set equal to the contents of the selected database record before the recalculation starts thereby forcing the storage locations associated with the cells of said selected range to be loaded with the contents of said fields of said selected database record before the recalculation starts.