Efficiently navigating a workbook linked to a database

Details Efficiently navigating a workbook linked to a database Efficiently navigating a workbook linked to a database

1999-05-10

2003-11-18

Hong, Stephen S. (Department: 2178)

Data processing: presentation processing of document, operator i

Presentation processing of document

Layout

C715S252000

Reexamination Certificate

active

06651216

ABSTRACT:

REFERENCE TO CD-ROM APPENDIX
Part of this patent disclosure is the source code used in Suvan College Office 2000 (hereinafter referred to as College Office), a grade-book program that was being distributed by Microsoft Corporation of Redmond, Washington and Suvan LLC of Corvallis, Oregon at the time that this patent application was filed. The applicants transferred the source code to a compact disk which is attached as Appendix A. It contains only one file named: CollegeOfficeSource-Code—ASCII.txt. This file is 663 kilobytes in size and was created on Apr. 13, 2003. It contains an exact copy of the material originally filed on paper as an attachment to the patent application.
BACKGROUND-FIELD OF INVENTION
This invention relates to user interface design in spreadsheet programs that link with database systems.
BACKGROUND OF THE INVENTION
A spreadsheet program is a computer application program commonly used for recording and analyzing data, budgeting, forecasting, and many other tasks that involve calculating results. In a spreadsheet program, data and formulas are entered in ledger-like forms (herein called worksheets) for analysis, tracking, planning, or “what-if” evaluations. Each worksheet uses rows and columns of cells; each cell can hold text or numeric data or a formula that uses values in other cells to calculate a desired result. Most spreadsheet programs label each worksheet's columns with letters (A, B, C, and so on); and rows are given numbers (1, 2, 3, and so on). Because real-world problems tend to have more than two dimensions, modem spreadsheet programs organize worksheets into a workbook, which is a collection of named worksheets. Most often the names of a workbook's worksheets appear on page tabs that are visible along the workbook's bottom edge. Thus, a particular cell in a workbook might be named Student Information!D7, where the exclamation point serves as a delimiter between the worksheet name and the column/row designator.
Spreadsheet programs are among the most widely used computer programs because they let people to build numerical models quickly and easily. It only takes a few minutes to enter labels and formulas to build a simple budget model. For similar ease-of-use reasons, many—if not most—instructors enter scores about their students' performance into a grading worksheet that assigns one row to each student and has a total column to calculate how many points were earned by each student in the class overall.
The row-and-column ledger-like format of a spreadsheet program provides an understandable way to view and interact with data. This has led people to explore the construction of larger and larger spreadsheet models that make extensive use of workbook pages. For example, if we return to the example of using a spreadsheet to record scores and calculate grades, an instructor's workbook might have a Class Totals page to summarize information on various Activity pages, where each Activity page has detailed scores and comments for one assignment or exam in the class.
While workbooks with many pages of data are fairly easy to construct, maintaining the integrity of the data that they contain can be a major problem. For example, assume an instructor with 20 students builds a workbook with a Class Totals page and nine Activity pages (similar to Class Totals and Treasure Hunt pages shown in FIGS.
5
and
7
). If the instructor sorts the rows on the Class Totals page (perhaps to list students in descending order based on their performance on the Treasure Hunt activity), then formulas that link cells between the Class Totals page and the Activity pages will get jumbled—unless all nine Activity pages are also sorted in an identical manner. This could cause a formula for one student on the Class Totals page to retrieve data about another student from an Activity page—resulting in incorrect values appearing on the Class Totals page. Similar problems can occur if the instructor deletes a row on the Class Totals page (perhaps because a student withdrew from the class) or adds a new row to the Class Totals page—unless extreme care is taken to make sure corresponding changes are made to all related pages in the workbook.
A database system (database) is a set of computer programs used to store and retrieve data. Such data is often limited to text (letters, numerals, symbols, and other characters), but may include any data that may be stored by a computer. Most databases store data in tables. A table is a series of rows, also called records. Each row contains data about a particular thing, such as a merchandise order. The rows are divided into columns, and the intersection of a row and column is referred to as a field.
A database can store many tables and can store relationships among tables indicating how the tables are logically related to each other. It can use these relationships to make sure all data remains well organized as new rows are created, existing rows are deleted, or data is sorted or otherwise manipulated. For example, a database for storing student information might have tables for Students, Classes, Classlists, Courses, Activities, Subactivities, and Scores similar to FIG.
3
.
In
FIG. 3
, the Students table is linked to the Classlist table with a one-to-many relationship based on the StudentID fields of both tables. This means each row in the Students table could be logically related to multiple rows in the Classlist table (another way of saying this is “one student could be enrolled in several classes”). Because this relationship exists independent of the order of records in the Students and Classlist tables, sorting the Classlist table without sorting the Students table will not result in jumbled or incorrect data about which students are enrolled in which classes. Thus, because tables can be linked with predefined relationships, a database can automatically maintain the integrity of data among the tables when requests are made to sort, add, or delete records. This represents an important advantage of using database techniques to organize data in comparison with the usual methods for storing data in multiple pages of a workbook.
In comparison with a spreadsheet program, most people find a database system hard to use. While a computer scientist might find the relationships in
FIG. 3
understandable, the typical instructor would not know how to contend with such an abstract representation of data. This has led software developers to search for ways to link a spreadsheet program to a database in an attempt to combine the spreadsheet's ease-of-use and understandability with the database's power and data integrity.
Perhaps the most difficult aspect of linking a spreadsheet with a database has been the fact that a database can store MANY tables with different kinds of data, and collectively these tables often have more dimensions than will easily fit in a workbook's three-dimensional row, column, and page format. What has been needed is a method of linking a workbook to a database that retains as much of the database's richness and relationships as possible while making the process of imposing these relationships inside a workbook absolutely transparent-so that the computer user doesn't need to know anything about database structure to use the linked workbook.
SUMMARY
As the inventors, we devised a way to link a workbook so that it retrieves data from, calculates totals, and posts data back to a database. The method hides the complexity associated with a traditional database management system from the computer user while allowing the user to tap into the workbook's ability to accept formulas and calculate custom results.
The invention uses a Totals page as a table of contents to organize a variable number of Detail pages inside a workbook whose cells are linked to fields in a database. The Totals page might also be called a summary page or a contents page because its job is to organize and provide access to all Detail pages. A feature is to let the user give a single command to add or delet

Affiliated with

Also associated with

Rating

Efficiently navigating a workbook linked to a database does not yet have a rating. At this time, there are no reviews or comments for this patent.

If you have personal experience with Efficiently navigating a workbook linked to a database, we encourage you to share that experience with our LandOfFree.com community. Your opinion is very important and Efficiently navigating a workbook linked to a database will most certainly appreciate the feedback.

Rate now

Comments { 0 }

Profile ID: LFUS-PAI-O-3168737