System and method for dynamically displaying entering, and updating data from a database

• US 5,455,945 A
• Filed: 05/19/1993
• Issued: 10/03/1995
• Est. Priority Date: 05/19/1993
• Status: Expired due to Term

• Current Assignee: Yardley Benham And Rasch LLC (Intellectual Ventures LLC)
• Original Assignee: Richard Vanderdrift
• Inventors: VanderDrift, Richard
• Primary Examiner(s): Black, Thomas G.
• Assistant Examiner(s): WANG, PETER
• Application Number:
  US08/063,905
• Time in Patent Office: 867 Days
• Field of Search: 395/600
• US Class Current: 1/1
• CPC Class Codes: G06F 16/2428 Query predicate definition using graphical user interfaces, including menus and forms G06F16/2423 takes precedence
• CPC Class Codes: G06F 16/248 Presentation of query results
• CPC Class Codes: Y10S 707/99932 Access augmentation or optimizing
• CPC Class Codes: Y10S 707/99934 Query formulation, input preparation, or translation
• CPC Class Codes: Y10S 707/99937 Sorting
• CPC Class Codes: Y10S 707/99945 Object-oriented database structure processing

Abstract

A system for extracting and dynamically displaying data from a database comprises a central processing unit, an input device, a program memory, a display device, a printer, mass storage, and a network. The program memory comprises management records for retrieving and updating information in the database, and dynamic documents for presenting database information to the user. The information from one or more underlying commercial databases is structured and reorganized into management records. The management records interact with the dynamic documents to reformat the data into the form desired by the user. Both the management records and dynamic documents are also used to execute operations on the data in the database such as sorts, filters, and logical and mathematical functions. The present invention also include a plurality of unique methods for extracting and updating data in the underlying databases. These methods include: a method for defining a point of view for viewing the data, a method for generating the language codes of production databases for extracting the data from the underlying databases, a method for assembling the data extracted from the underlying database into management records, a method for reorganizing the data in management records into any form of dynamic documents, and methods for displaying and printing dynamic documents.

Claims

1. A computer implemented method for dynamically displaying and optionally entering and updating data from a database, the database storing the data as primary records, said method Comprising the steps of:

reading a plurality of primary record types for the database and a plurality of relationships between the primary record types;

defining a management record by selecting a plurality of primary record types for inclusion in the management record;

generating management record pointer families for each management record, each management record pointer family comprising a data structure defining a group of related primary record types within the management record, the management record pointer families generated by performing the substeps of;

1) retrieving a management record;

2) retrieving a unique primary record type of the management record;

3) using the unique primary record type as the current primary record type;

4) creating a family pointer type for the current primary record type;

5) retrieving a set of primary record definitions related to the current primary record type;

6) creating a child record type to the family pointer for each primary record type retrieved in substep

     5) that has a one to many relationship with the current primary record type; and

7) for each primary record type retrieved in substep

     5) that has a many to one relationship with the current primary record type, repeating substeps 4-7 with the primary record type retrieved as the current primary record type;

defining a dynamic document that specifies a display format for data from the database, the display format independent of the structure of the management record;

fetching data from the database and constructing a primary record instance and a management record pointer instance by performing the substeps of;

1) creating a database command to query the database by performing the additional substeps of;

a) reading a leaf level management record pointer family;

b) reading a parent management record pointer family for each leaf level management record pointer family;

c) creating a Data Manipulation Language (DML) query that selects the management record pointer family'"'"'s primary record type;

d) reading any filters for the management record pointer family, each filter specifying a set of data selection criteria;

e) creating a WHERE clause for the DML query using the filters for the management record pointer family; and

f) repeating substeps a-e for each leaf level management record, pointer family;

2) executing the database command; and

3) constructing the management record pointer instance;

creating a dynamic document instance for the management record pointer instance; and

displaying data from the database using the dynamic document instance.

2. The method of claim 1, wherein the step of defining a management record further comprises the step of identifying one of the selected primary record types as the unique identifying primary record for the management record.

3. The method of claim 1, wherein the step of defining a dynamic document includes any of the steps of:

defining a function that uses the data of the database;

defining a filter that operates on the data of the database, the filter specifying a set of data selection criteria; and

defining a sort that operates on the data of the database.

4. The method of claim 1, wherein the steps of executing and constructing, further comprise the steps of:

1) executing the query and fetching a resultant table

2) for each primary record type, grouping the resultant table by a key field of each primary record type into a temporary table;

3) inserting a primary record instance for each primary record type key field grouped by temporary table row;

4) for each management record pointer family, grouping each temporary table by the key field of a lead primary record type; and

5) reformatting the rows of each temporary table into a management record pointer instance.

5. The method of claim 1, further comprising the step of executing a function on the management records.

6. The method of claim 1, wherein the method execute user input sorts on the data in the dynamic documents before the step of displaying the data.

7. The method of claim 1, wherein the step of displaying data from the database the using the dynamic document instance comprises the steps of:

sending the data in an organized format to a screen forms display tool;

placing values into proper fields, and

using display rules entered by the user to control the appearance of a set of display panes, positioning the format of fields within the primary record type, and the type of maintenance that can be performed.

8. The method of claim 1, further comprising the step of executing a filter on the dynamic document, the filter execution corresponding to a data selection, the filter specifying a set of data selection criteria.

9. The method of claim 8, wherein the step of executing a filter on the dynamic documents further comprises the steps of:

reading the dynamic document pointer;

reading the filter from a dynamic document definition;

comparing a filter value to an appropriate field of the dynamic document pointer;

displaying the dynamic document pointer if the filter value matches the appropriate field of the dynamic document pointer;

determining whether the filter is a delete type filter; and

deleting any dynamic document pointer and its children if it is a delete type filter and the filter value matches the appropriate field of the dynamic document pointer.

10. The method of claim 1, further comprising the step of executing a function on the management records.

11. The method of claim 10, wherein the step of executing a function further comprises the steps of:

retrieving the dynamic document definition and a dynamic document instance;

retrieving a lowest level unprocessed display pane hierarchy level for the dynamic document definition;

retrieving a set of functions associated with the display pane;

executing each of the functions;

updating the dynamic document pointers corresponding to the display pane;

updating the parent dynamic document pointer of other display panes that use the function; and

updating a group by accumulator, wherein the group by accumulator stores a result corresponding to a calculation performed upon a group of data fields.

12. The method of claim 1, further comprising the step of executing a filter on the management records, the filter execution corresponding to a data selection, the filter specifying a set of data selection criteria.

13. The method of claim 12, wherein the step of executing a filter further comprises the steps of:

reading the management record pointer instance;

reading the filter from a management record definition;

comparing a filter value to an appropriate field of the management record pointer instance;

displaying the management record pointer instance if the filter value matches the appropriate field of the management record pointer instance;

determining whether the filter is a delete type filter; and

deleting any management record pointer instance and its children if it is a delete type filter and the filter value matches the appropriate field of the management record pointer instance.

14. The method of claim 12, wherein the step of executing a function further comprises the steps of:

retrieving a management record definition and a management record instance;

retrieving a lowest level unprocessed management record pointer family for the management record definition;

retrieving a set of functions associated with the management record pointer family;

executing each function retrieved;

updating the management record pointer instances corresponding to the management record pointer family;

updating the parent management record pointer instances of other management record pointer families that use the function; and

updating a group by accumulator, wherein the group by accumulator Stores a result corresponding to a calculation performed upon a group of data fields.

15. The method of claim 14, wherein the method maintains a pass sequence number that is incremented for each level of function nesting in a management record, and the step of executing the function is performed a plurality of times equal in number to the pass sequence number.

16. The method of claim 14, wherein the method maintains a pass sequence number that is incremented for each level of function nesting in a dynamic document, and the step of executing the function is performed a plurality of times equal in number to the pass sequence number.

17. The method of claim 1, wherein the step of defining a dynamic document comprises the step of defining dynamic document pointers, said dynamic document pointers indicating the primary record types to be displayed.

18. The method of claim 17, the step of defining dynamic document pointers further comprises the steps of:

1) retrieving a dynamic document definition;

2) determining a highest hierarchy level display pane definition not yet processed, the display pane definition defining a display window;

3) reading the primary record type and a corresponding management record pointer family for the pane definition of step 2;

4) determining whether a display pane exists for the primary record type and the corresponding management record pointer family, the display pane corresponding to a display window;

5) if the display pane does not exist, creating a display pane for the primary record type and the corresponding management record pointer family by joining management record pointer instances for this display pane and its parent display pane.

19. The method of claim 18, further comprising the step of executing filters before and after the step of creating a display pane.

20. The method of claim 18, further comprising the steps of:

determining whether any child display panes exist; and

storing a copy of the joining management record pointer instances for each child display pane.

21. The method of claim 1, further comprising the steps of:

accepting user revisions to the data displayed;

determining whether the revision is a data revision;

updating the management record pointer instances if the revision is a data revision;

determining whether the revision affects a function;

maintaining and executing the affected functions if the revision affects a function;

determining whether the revision affects a filter;

maintaining and executing the affected filters if the revision affects a filter, each filter execution corresponding to a data selection according to a set of data selection criteria;

determining whether the revision is a sort revision; and

maintaining and executing the affected sort if the revision is a sort revision,

22. The method of claim 21, wherein the step of maintaining and executing the affected functions if the revision affects a function comprises the steps of:

retrieving user changes to the function;

replacing calculated fields used in the function with their function;

updating primary record types used to point to the changed function;

updating the management record pointer family to point to the changed function;

reading the management record pointer instance

executing the changed function.

23. The method of claim 21, wherein the step of maintaining and executing the affected functions if the revision affects a function comprises the steps of:

retrieving user changes to the function;

replacing calculated fields used in the function with their function;

updating primary record types used to point to the changed function;

updating the dynamic document pointer to point to the changed function;

reading the dynamic document pointer;

executing the changed function.

24. The method of claim 21, wherein the step of maintaining and executing the affected filters if the revision affects a filter comprises the steps of:

retrieving user changes to the filter;

updating the management record type definition to point to the changed filter;

reading the management record pointer instance for the filter'"'"'s management record pointer family;

executing the filter.

25. The method of claim 21, wherein the step of maintaining and executing the affected filters if the revision affects a filter comprises the steps of:

retrieving user changes to the filter;

updating the dynamic document display pane definition to point to the changed filter, the display pane definition defining a display window;

reading a display pane row for a display pane associated with the filter;

executing the filter.

26. The method of claim 21, wherein the display is updated after the step of maintaining and executing the affected sort if the revision is a sort revision.

27. The method of claim 21 wherein the step of updating the management record pointer instances if the revision is a data revision, comprises the substeps of:

determining whether a record has been changed;

updating the management record pointer instances for a changed primary record instance if a record has been changed;

determining whether an record has been added;

updating the management record pointer instances for a new primary record instance if a record has been added;

executing the management record filters;

executing the management record functions;

updating the dynamic documents;

creating transactions to update the database; and

re-displaying the dynamic documents to show the data revision.

28. The method of claim 27, wherein the substep of updating the management record pointer instances for a changed primary record instance comprises the steps of:

determining whether field values for a set of related fields are equal; and

removing a pointer within the management record pointer instance.

29. The method of claim 27, wherein the substep of updating the management record pointer instances for a new primary record instance comprises the steps of:

retrieving the new primary record instance;

determining where the new primary record instance is used by reading the primary record type definition and the management record type definition corresponding to the primary record instance;

determining whether the primary record type is a lead primary record of a management record pointer family;

creating a management record pointer instance and executing functions and filter associated with the management record pointer instance, if the primary record type is a lead primary record;

if the primary record type is not a lead primary record, reading the management record pointer instance for the management record pointer family

determining whether a field related to the primary record instance is a management record pointer instance; and

adding a primary record instance pointer the management record pointer instance.

30. The method of claim 27, wherein the substep of executing the management record functions comprises the steps of:

retrieving the changed primary record instance and its corresponding primary record type;

retrieving the function that uses the changed primary record instance;

retrieving the management record pointer family of the function that uses the changed primary record instance;

retrieving the management record pointer instance for the changed primary record instance;

executing the function; and

executing functions using the management record pointer instance.

31. The method of claim 27, wherein the substep of updating the dynamic documents comprises the steps of:

retrieving changed management record pointer instances and their changed primary record instances;

comparing the changed management record pointer instances and their changed primary record instances to the unchanged management record pointer instances and their changed primary record instances to determine the changed fields, pointers and filters;

identifying the dynamic document pointer instances that use the changed fields, pointers and filters;

determining whether any of the changed fields, pointers and filters are used in other nested calculations, and identifying the dynamic document pointer instances that use the other nested calculations;

executing the filters for the identified dynamic document pointers, each filter execution corresponding to a data selection according to a set of data selection criteria;

executing the functions for the identified dynamic document pointers;

re-sorting the rows of dynamic document pointers; and

re-displaying the rows of dynamic document pointers.

32. The method of claim 27, wherein the substep of creating transactions to update the database comprises the steps of:

retrieving update transaction definitions in the management records definition;

retrieving the dynamic document pointer instances for the highest level unprocessed display pane in a display pane hierarchy;

creating an update transaction if the update transaction has not been created and the display pane has changed field to updated to the data base;

updating an update transaction if the update transaction has been created and the display pane has a changed field to be updated to the data base;

creating an update transaction for each child display pane if an update transaction has not been created for the child display pane and the child display pane has a changed field to updated to the data base; and

executing the update transactions.

33. The method of claim 32, wherein the substep of creating transactions to update the database further comprises the following steps before the step of executing the update transactions:

determining whether the update transaction are to be aggregated; and

summarizing the update transactions on the first field.