WO2013049906A1

WO2013049906A1 - Dynamically generated ui for a relational database and method thereof

Info

Publication number: WO2013049906A1
Application number: PCT/CA2011/001100
Authority: WO
Inventors: Brien R. GIVENS
Original assignee: Givens Brien R
Priority date: 2011-10-05
Filing date: 2011-10-05
Publication date: 2013-04-11

Abstract

A system and method that is used to create a graphical user interface to a relational database. In particular, various embodiments described herein introduce concepts of a relational interface and a relational interface.

Description

DYNAMICALLY GENERATED Ul FOR A RELATIONAL DATABASE

AND METHOD THEREOF

FIELD OF INVENTION

[0001] The invention relates generally to the field of user interface (GUI). In particular, the invention relates to systems and methods for providing and dynamically creating a graphical user interface (GUI) to a relational database.

BACKGROUND OF INVENTION

[0002] In relational database theory, a relationship between two tables is created by including a column (field) within the "child" table to store the primary key (or other candidate key) of its "parent" table. This column is referred to as the foreign key of the parent table. In each row, the stored value of the primary key is referred to as the primary id of the row and the stored values of any foreign keys are the row's foreign ids.

SUMMARY OF INVENTION

[0003] In one aspect, a method is provided to create a graphical user interface to a relational database as a database interface system.

[0004] According to an aspect of the invention, there is provided a method of generating and providing a computer implemented user interface for a relational database, the relational database containing data tables comprising records. The user interface includes one or more page frames, each being constructed for a list of tables of the relational database by:

(a) constructing a page of the page frame for each table in the list of tables, the page to be filled with data of the corresponding table,

(b) identifying one page from the list of pages as an active page and the corresponding table as an active table,

(c) retrieving data of the active table for displaying in a table data section of the page frame,

(d) constructing a table selection section of the page frame with one or more page selectors, each page selector corresponding to a respective page in the list of pages of the page frame, selecting one of which causes a corresponding page displayed in the table data section, and

(e) the table data section being configured to be responsive to user selection of a record displayed therein and upon the user selection of the record, constructing a new page frame as the child page frame of the table containing the user selected record.

[0005] The method includes the steps of:

(a) initializing a list of top level tables,

(b) constructing a page frame for the list of top level tables as a topmost page frame for displaying a list of corresponding top level pages,

(c) displaying the topmost page frame with its table data section filled with data of a top level active table retrieved from the database and its table selection section configured responsive to user selection of one of the top level pages to display the selected top level page in the topmost page frame, and

(d) constructing a new page frame as a child page frame of the top level active table upon user selection of a record of the top level active table in the table data section of the topmost page frame.

[0006] According to a feature of this aspect of the invention, the step of initializing a list of top level tables further includes the substeps of selecting a plurality of initial tables of the relational database, analyzing and categorizing relationships of the plurality of initial tables according to a set of pre-defined relationship types, and determining the list of top level tables from the plurality of initial tables according to the categorized relationships.

[0007] The step of determining the list of top level tables from the plurality of initial tables may include creating an initial list from the plurality of initial tables after removing therefrom any tables which a user does not have access permission, creating a list of S1 and S2 relationship child tables for each table of the plurality of initial tables; and compiling the list of top level tables by combining the initial list and the list of S1 and S2 relationship child tables.

[0008] The step of determining the list of top level tables from the plurality of initial tables may include creating an initial list from the plurality of initial tables after removing therefrom any tables to which a user cannot access, creating a clone of every table of the plurality of initial tables that is referenced by other tables in the initial list and compiling a list of cloned tables, and compiling the list of top level tables by combining the initial list and the list cloned tables.

[0009] The step of initializing the list of top level tables may include retrieving a list of all tables of the relational database, retrieving a list of tables without parent tables, retrieving a list of user selected tables, modifying lists of tables to obtain a consolidated list, and a combination thereof.

[0010] The table data section may have an inline data portion and a drilldown data portion, the inline data portion being configured to be responsive to user selection of a record displayed in the inline data portion. Each page frame is constructed by further performing the steps of compiling a list of supplemental tables based on relationships of tables in the database with the active table, adding the list of supplemental tables to the active page for displaying in the drilldown data portion, and wherein the step of displaying the topmost page frame comprises displaying inline data of the retrieved data of the top level active table in the inline data portion, the method further comprising, upon user selection of a record displayed in the inline data portion: if the list of supplemental tables includes one or more supplemental tables, retrieving data of the one or more supplemental tables, and displaying the data of the one or more supplemental tables in the drilldown data portion.

[001 1 ] Furthermore, the step of compiling the list of supplemental tables may include adding to the list at least one or more uncommitted ancestor tables, adding to the list one or more tables related to the top level active table according to a user specified set of relationship types, or a combination thereof; it may also include creating a clone table for each table referenced by tables in the list of supplemental tables; and adding all clone tables to the list of supplemental tables; or alternatively or in addition, it may include creating a further list of all uncommitted ancestor tables of each table already included in the list of supplemental tables, adding to the further list all tables relating to any table in the further list according to the user specified set of relationship types; and adding to the list of supplemental tables all tables in the further list; or as a further alternative or in addition, it may include creating a further list of uncommitted tables each of which relates to a table in the list of supplemental tables as a child in a parent-child relationship, adding to the further list all tables relating to any table in the further list according to the user specified set of relationship types; and adding to the list of supplemental tables all tables in the further list.

[0012] According to a feature of this aspect of the invention, the child page frame is constructed by compiling a list of child tables of the topmost active table, constructing the new page frame for the list of child tables as a child page frame of the topmost active table, retrieving data of an active child table identified from the list of child tables, and displaying data of the active child table in the table data section of the child page frame, wherein the child page frame is configured to be iteratively responsive to further user selection of a record displayed in the table data section of any of the child page frame and its descendent page frames.

[0013] The method may further include the steps of, upon user selection of a record in a table data section of any of the child page frame or descendent page frames, removing any descendent page frames except for the immediate child page frame of the table containing the selected record, compiling a list of immediate child tables if the table containing the selected record has no descendent page frame, constructing the immediate child page frame for the list of immediate child tables if the immediate child page frame does not exist, and displaying the immediate child page frame if the list of immediate child tables contains at least one table.

[0014] As a further feature, the step of compiling the list of child tables or the list of immediate child tables may include compiling a first list of all committed tables of all remaining displayed page frames, analyzing recursively each table of the first list to identify all tables that are descendants of any table in the first list according to a set of pre-specified relationships to generate a second list of tables, and removing from the second list any table that is already included in the first list to obtain the list of child tables or the list of immediate child tables.

[0015] As another feature of this aspect of the invention, the method includes the further steps of providing a filter actuator in at least one of the page frames, the filter actuator being configured to correspond to a subset of tables of the list of tables for which the at least one page frame is constructed, and, upon user selection of the filter actuator, selectively excluding the subset of the tables from being displayed in the at least one page frame. Optionally, a filter selector may be provided to enable a user to modify the subset of excluded tables.

[0016] As yet another feature, the method may include the steps of dividing the page displayed in at least one of the page frames into a plurality of display pages and displaying only one display page of the plurality of display pages at a time in the table data section of the at least one page frame, providing a plurality of page selectors in the at least one page frame, each page selector corresponding to a respective display page of the plurality of display pages, and, upon user selection of a page selector, displaying the corresponding display page in the table data section of the at least one page frame.

[0017] As another feature, the data table section is configured for adding a new record to the active table and the method includes the further step of, upon user addition of a new record to the active table, adding a corresponding new record to all child tables of one or more user-specified relationship types in the child page frames of the active table displayed in the at least one page frame.

[0018] As yet another feature, the method include the further steps of creating a data object of all data from the selected record, corresponding records in all child tables of the selected record satisfying a set of pre-specified parent-child relationship types and corresponding records in all ancestor tables of the selected record, checking the data object for error, and providing an error message for any error found in the error checking. Optionally, a confirmation input from a user is to be received prior to deleting, saving or changing records of the top level active table and any of its child or descendent tables.

[0019] According to another feature, the data table section further includes a reference field portion, the reference field portion including a dialog selector, the method includes the further steps of displaying a representation of one or more fields from a referenced table in the reference field portion; and, upon user selection of the dialog selector, displaying a dialog window to display therein the referenced table.

[0020] In another aspect, a computer implemented user interface to a relational database is provided for displaying data from and providing access to data in the relational database. The user interface includes one or more page frames each being configured to display a plurality of pages of the each page frame, each page of the plurality of pages corresponding to a table in the relational database and having a plurality of participating tables related to the corresponding table according to relationships of tables in the relational database. Each page frames has a table selection section, a table data section, a page selection section and a toolbar section. The table selection section has a plurality of table selectors. Each table selector corresponds to a respective page of the page frame, selection of which designates the respective page as an active page and the corresponding table as an active table. The table data section provides access to and displays records of the active table in the page frame in one or more display pages. Selection of a record of the active table displayed in a page frame causes construction and display of a new page frame, which will be a child page frame of the active page for displaying one or more child tables. Child tables identified for inclusion in the child page frame are related to the active table of the parent page frame according to relationships specified in the relational database. The page selection section has one or more page selectors each corresponding to a respective display page. Selecting a page selector provides access to the corresponding display page, such as displaying it.

[0021] The plurality of participating tables of a page may include a primary table corresponding to the active table and a list of supplemental tables, each of the supplemental tables being related to the primary table according to a user specified subset of relationships of the relationships of tables in the relational database.

[0022] The table data section may include an inline data portion for displaying inline data and a drilldown data portion for displaying drilldown data. Data of supplemental tables may be displayed in the drilldown data portion.

[0023] The child page frame may be configured to be displayed only upon selection of a record of its parent page. Further selection of a record displayed in a child page frame iteratively causes display of additional child page frame(s) at a lower level(s). Child or descendent tables included to participate in additional child page frame(s) of the lower level(s) are related to the active table having the selected record and all active tables of all parent page frames at higher levels according to relationships specified in the relational database.

[0024] A page frame may also include a filter to selectively exclude a subset of tables (and child tables) from the page frame (and its child or descendant page frames). It may also further include a filter selector to allow a user to access and modify the subset of excluded child tables.

[0025] In another aspect, there is provided a non-transitory computer readable storage medium containing computer programming instructions stored thereon. When executed by a computer system, the computer system executes steps of the method according to any aspect of the invention described above or provides the computer implemented user interface, and optionally, in combination with any or all of the features.

[0026] In yet another aspect, a computer implemented method for creating a graphical user interface (GUI) to a relational database is provided. The method comprising: specifying a relationship between at least two tables; dynamically generating the GUI by creating layers of page frames according to the specified relationships; and displaying the GUI.

[0027] According to one feature, the GUI is organized into page frames comprising levels of structural blocks. The GUI may comprise a browser based interface.

[0028] In another case, the database tables and fields are created or updated (modifying the table or field name, field data type, nuliability and/or addition/removal of indexes) for the graphical user interface when a database is rebuilt by or through the embodiments as described in more detail herein.

[0029] In another case, audit columns are automatically added to corresponding tables in the graphical user interface when a database is rebuilt by or through the embodiments as described in more detail herein.

[0030] In another aspect, a non-transitory machine-readable memory is provided. In various embodiments the non-transitory machine-readable memory stores statements and instructions for execution by a processor for performing any of the methods described herein.

[0031] In another aspect, a system is provided to create a graphical user interface to a relational database as a database interface system.

[0032] In another aspect, a system for creating a graphical user interface (GUI) to a relational database is provided. The system comprises: a display; and a processor configured to: specify a relationship between at least two tables; and dynamically generate the GUI by creating layers of page frames according to the specified relationships; and display the GUI on the display.

[0033] The system may further comprise an input device, wherein the relationships between the at least two tables are specified based on input provided to the input device.

[0034] In another case, the display and the processor are distributed in an n-tier architecture.

[0035] In another case, the database tables and fields are created or updated (modifying the table or field name, field data type, nullability and/or addition/removal of indexes) for the GUI when a database is rebuilt by or through the embodiments as described in detail below.

[0036] In another case, audit columns are automatically added to corresponding tables in the GUI when the relational database is rebuilt by or through the embodiments described in detail below.

[0037] In another aspect, a programming language is provided to create a graphical user interface to a relational database as a database interface system.

BRIEF DESCRIPTION OF DRAWINGS

[0038] Embodiments will now be described, by way of example only, with reference to the attached Figures, wherein:

[0039] Figure 1 is a block diagram of a GUI creation system according to an embodiment;

[0040] Figure 2 is a block diagram that illustrates in further detail an exemplary implementation of the software application program executing on a processor as shown in Figure 1 ;

[0041 ] Figure 3 illustrates a GUI that includes a topmost page frame created by the system according to the present invention;

[0042] Figure 4 shows a chart with sixteen types of theoretical relationships in a relational database according to an embodiment;

[0043] Figure 5 illustrates a GUI that includes a topmost page frame and its subsequent child page frames;

[0044] Figure 6 illustrates a page frame that includes a page selector fly-out for a user to enter a specified page;

[0045] Figure 7 illustrates a method of compiling a list of tables for generating a topmost page frame shown in Figure 3 or Figure 5; [0046] Figure 8 illustrates steps of a method of retrieving data dynamically for displaying in a page frame;

[0047] Figure 9 illustrates in a flowchart a method of compiling a list of participating tables for displaying child page frames of a table;

[0048] Figure 10 illustrates in a flowchart a method of generating and displaying a child page frame;

[0049] Figure 11 illustrates in a flowchart a method of updating participating tables of a child page frame;

[0050] Figure 12A shows an example of a page frame displayed in edit mode;

[0051] Figure 12A shows examples of a page frame with inline rows displayed in display mode and in edit mode;

[0052] Figure 13 shows an example of an S4 relationship between participating tables;

[0053] Figure 14 shows a GUI that displays an M1 relationship according to an embodiment;

[0054] Figure 15 illustrates in a flowchart the steps of a validation method;

[0055] Figure 16 shows an example of a field validation message;

[0056] Figure 17 illustrates in a flowchart the steps of a verification method;

[0057] Figure 18 shows an example of a verification warning and a user dialog window;

[0058] Figure 19A Illustrates the use of the reference field's edit button;

[0059] Figure 9B Illustrates the use of the reference field's show/hide button;

[0060] Figure 20 illustrates in a flowchart steps of a process to generate and display a page frame; and

[0061] Figure 21 illustrates in a flowchart an example of an application initialization process; and

[0062] Figure 22 illustrates in a flowchart a method of table cloning.

DETAILED DESCRIPTION

[0063] It will be appreciated that numerous specific details are set forth in order to provide a thorough understanding of the example embodiments described herein. However, it will be understood by those of ordinary skill in the art that the embodiments described herein may be practiced without these specific details. In other instances, well-known methods, procedures and components have not been described in detail so as not to obscure the embodiments described herein. Furthermore, this description is not to be considered as limiting the scope of the embodiments described herein in any way, but rather as merely describing the implementation of the various embodiments described herein.

[0064] The embodiments of the systems and methods described herein may be implemented in hardware or a combination of hardware and software. Some embodiments can be implemented in software. Some embodiments are implemented in computer programs executing on programmable computers each comprising at least one processor, a data storage system (including volatile and non-volatile memory and/or storage elements), at least one input device, and at least one output device. For example and without limitation, the programmable computers may be a personal computer, laptop, personal data assistant, a smartphone, and a cellular telephone. Program code is applied to input data to perform the functions described herein and generate output information. The output information is applied to one or more output devices, in known fashion.

[0065] Each program is preferably implemented in a high level procedural or object oriented programming and/or scripting language to communicate with a computer system. However, the programs can be implemented in assembly or machine language, if desired. In any case, the language may be a compiled or interpreted language. Each such computer program is preferably stored on a storage media or a device (e.g. ROM or magnetic diskette) readable by a general or special purpose programmable computer, for configuring and operating the computer when the storage media or device is read by the computer to perform the procedures described herein. The inventive system may also be implemented as a computer-readable storage medium, configured with a computer program, where the storage medium so configured causes a computer to operate in a specific and predefined manner to perform the functions described herein.

[0066] Furthermore, the system, processes and methods of the described embodiments are capable of being distributed in a computer program product comprising a computer readable medium that bears computer usable instructions for one or more processors. In various embodiments the computer readable medium is a physical computer readable medium. In some embodiments the computer readable medium is a non-transitory computer readable medium. In some embodiments, the medium may be provided in various forms, including one or more diskettes, compact disks, tapes, chips, wireline transmissions, satellite transmissions, internet transmission or downloadings, magnetic and electronic storage media, digital and analog signals, and the like. The computer useable instructions may also be in various forms, including compiled and non-compiled code.

[0067] Some embodiments described herein relate to systems and methods for providing and creating a graphical user interface (GUI) to a relational database. Some embodiments described herein relate to a programming language that is used to create a graphical user interface (GUI) to a relational database. In particular, the programming language introduces concepts of the relational interface and the relational interface development language.

[0068] In a relational interface, the organization of the graphical user interface (GUI) reflects the established relationships within the database. A relational interface development language is a programming language that enables the programmer to create a relational interface.

[0069] Some embodiments relate to a system for creating a GUI to a relational database. An example embodiment is illustrated in Figure 1 , which is a block diagram of GUI creation system 100. GUI creation system 100 comprises a processor 110 programmed by software application program, residing on a computer system or stored on a non-transitory computer readable medium and executing on the processor, a storage medium 120 for storing a computerized database, among others, an output device 130 and an input device 140. In various embodiments, storage medium 120 is a non-transitory storage medium. In various embodiments storage medium 120 is utilized to store data for use in a relational database as described in greater detail below. Output device 130 can be any appropriate output device such as for example, but not limited to, an LCD monitor. Input device 140 can be any appropriate user input device such as for example, but not limited to, a key board. Some embodiments utilize devices that combine input and output functions such as a touch screen. Processor 110 is utilized to execute computer executable instructions, which may be for example stored on storage medium 120, for the creation of a GUI that is displayed on output device 130. In some embodiments, the instructions executed by processor 110 can be stored on a storage medium other than storage medium 120.

[0070] Figure 2 is a block diagram that illustrates in further detail an exemplary implementation of the software application program 200 executing on the processor 110. As illustrated, the software application program 200 may be organized according to components often conveniently residing on the client side (i.e., to the left of the dashed line in Figure 2) and components often conveniently residing on the server side (i.e., to the right of the dashed line in Figure 2).

[0071] On the client side, there is an application definition component 202, for processing and storing application-specific instructions provided or specified by a user (including a programmer). Input handler 204 receives commands from a user via an input device 140 such as keyboard, mouse and any other suitable input devices and passes the user commands to GUI generator 206. GUI generator 206 utilizes the table definition and relational information from the database or from application definition component 202 to create a dynamic graphical user interface (GUI). In addition, GUI generator adapts the GUI to data supplied from the database via data handler 208 and user commands via input handler 204. Front-end data handler 208 formats data for communication to middle layer data handler data handler 210 (conveniently a single text string sent via a JavaScript™ XMLHttpRequest object) and receives formatted data (conveniently in JavaScript) from middle layer data handler data handler 210.

[0072] On the server side, middle layer data handler 210 receives formatted data from front- end data handler 208 (the text string) and sends data conveniently formatted as JavaScript back to front-end data handler 208. SQL generator 212 parses the data received from middle layer data handler 210 and creates commands conveniently formatted in SQL (Structured Query Language) to retrieve, create, update or delete data in the database. The relationships established in the database or in application definition 202 are utilized when creating the SQL statements used in relationship-based page frame participation, as will be described in detail later. Database-specific connection 214 establishes and provides a software connection to the database provided by the database or a third party (e.g., a database vendor) which executes the SQL statements from SQL generator 212 on the database and returns data from the database to middle layer data handler 210.

[0073] It should be understood that in various embodiments, system 100 (or application program 200) is not implemented as a single computing device. In particular, in various embodiments, the elements of system 100 are distributed in a multi-tier (or n-tier) architecture. Accordingly, monitor 130 and input device 140 may be part of computing device that interacts in a client server manner with one or more computing devices that include processor 110 and storage medium 120.

[0074] GUI Organization: The GUI described herein is organized into levels of structural blocks called page frames. Each level consists of one or more page frames, but usually a single page frame. A page frame has one or more pages, each corresponding to a table of the database and to be filled with data from the corresponding table. Tables included in the topmost page frame are in general completely independent and may be, for example, (1) all of the tables in the application, (2) only those tables which do not have parent tables, (3) all the tables from option (2) plus a manual selection of tables, or (4) a manual selection of tables. Tables in subsequent page frames are dependent on the tables of the previous levels. The placement of a table within the GUI is dependent upon the type of relationship with its parent or parents. Tables which are accessible at a page frame's level are organized into the tabs of the page frame. Depending on the type of relationships, a child table may be directly accessible via the tabs of the page frame whereas another child table may be accessible only through their parent record and when the parent record is selected. [0075] In Figure 3, there is illustrated a GUI according to the present invention. The GUI illustrated in Figure 3 shows a topmost page frame 300 with the "Patients" tab 302 selected. The page frame displays in a table data section 304 a first page 306 of data from the "Patients" table in the database. For large data sets, a page may contain many rows. Conveniently, a page may be divided into multiple display pages, each configured to display only a pre-selected number of rows per display page (such as 15 rows) for easy viewing. When there are more rows than is configured to display on one single display page, the page frame is configured to provide a page selector 308 for a user to select additional display pages for displaying more data for the same page or table.

[0076] The GUI illustrated in Figure 3 also includes tab selectors 310 for user to switch to other tables in the database. Each tab 302 corresponds to a table from the database. For example, tables accessible in the example of page frame shown in Figure 3 include "Insurance Companies", "MDs", "Referring MDs", "Attorneys", "Users", "Groups" and "System". For subsequent page frames, i.e., child page frames, tables selected will depend on relationships of tables in the database, as will be described in detail below.

[0077] Figure 4 illustrates 16 different types of relationships which can be established between tables (entities). These types are briefly described below.

[0078] Relationship Types:

(a) Single-Parent Relationships

The child table in an S1 - S8 relation has a single parent table.

(0 S1

Each record in the parent table has 0 or 1 record in the child table.

Tables with this type of relationship can be used to create optional groups of data within records. (ii) S2

Each parent record has 1 record in the child table.

Tables with this type of relationship can be used to create physically separate groups of data within records.

(iii) S3

Each parent record has 0 or more records in the child table.

(iv) S4

Each parent record has 1 or more records in the child table.

(v) S5

Each parent record has between 0 and Y records in the child table, where Y > 1. (vi) S6

Each parent record has between 1 and Y records in the child table, where Y > 1.

(vii) S7

Each parent record has between X and Y records in the child table, where X > 1 and Y >= X. (viii) S8

Each parent record has X or more records in the child table, where X > 1. (b) Multiple-Parent Relationships

The child table in an M1 - M8 relationship has two or more parent tables.

(i) M1

Each unique combination of parent records has 0 or 1 records in the child table.

(ii) M2

Each unique combination of parent records has 1 record in the child table. This type of relationship is not supported in various embodiments.

(iii) M3

Each unique combination of parent records has 0 or more records in the child table.

(iv) M4

Each unique combination of parent records has 1 or more records in the child table. This type of relationship is not supported in various embodiments.

(v) M5

Each unique combination of parent records has between 0 and Y records in the child table, where Y > 1.

(vi) M6

Each unique combination of parent records has between 1 and Y records in the child table, where Y > 1. This type of relationship is not supported in various embodiments. (vii) M7

Each unique combination of parent records has between X and Y records in the child table, where X > 1 and Y >= X. This type of relationship is not supported in various embodiments.

(viii) M8

Each unique combination of parent records has X or more records in the child table, where X > 1. This type of relationship is not supported in various embodiments. [0079] Relationship types S2, S4, S6, S7 and S8 may be supported by ensuring that new parent records are not created without also creating the specified minimum number of required child records.

[0080] In order to support relationship types M2, M4, M6, M7 and M8, any new record created in parent table A would require a new child record (X new child records for cases M7 and M8) in table C for every existing record in parent table B. All the new required C records would have to be created before the new A record could be saved. These theoretical relationships are not considered practical and therefore may not always be supported in some of the embodiments that are described herein.

[0081] Nomenclature: For the purposes of this application, an S1 table is defined as the child table of an S1 relationship, etc. An S1 row or record is defined as the row in the child table where the foreign id matches the primary id of the row in the parent table, etc. A table's S1/S2 child tables include its immediate S1/S2 child tables plus any S1/S2 child tables of the immediate S1/S2 child tables, etc. A foreign table is any parent table of a relationship. A table's relational tree refers to the collection of tables including the table itself and all its ancestor tables (all parent tables and their parent tables, etc.). A table's ancestors (or ancestor tables), also referred to as relational parents, are all the tables in its relational tree except for the table itself.

[0082] Page Frames: Referring to Figure 3 and with further reference to Figure 5, a page frame 300 consists of several distinct sections. These sections are now described in detail.

[0083] Table Selection Section: The table selection section 310 is the part of the page frame interface that enables the user to select one table from amongst a list of tables of the database, or one page from amongst a list of pages of the page frame. Various embodiments described herein implement this section as a group of tabs 302 which look similar to the tabs on a file folder. This section could also be implemented as a group of radio buttons, a select list (drop-down list) or any other type of pick-one list. Each tab or item in the list displays the page's or the corresponding table's caption and represents a page of the page frame. Each page has its own Table Data section to display data of the corresponding table and Page Selection section to enable a user to select a different display page if the table contains a large set of data that cannot fit within the pre-selected number of rows per display page. Clicking or otherwise selecting a tab (item) in the group (list) switches the display of the Table Data and Page Selection section to that of the selected page. The active page of the page frame is the currently selected page.

[0084] Toolbar Section: The toolbar section 312 or toolbar provides access to the high- level functions of the GUI. Each tool consists of a clickable icon (which could also be a button) and optional tooltip text. The tools provide access to a list of pre-selected functions of the GUI such as Create New Record, Copy Existing Record, Save Changes, Cancel Changes and Delete Selected Record. Edit Selected Record could also be included here; in various embodiments described herein the selected record is edited by simply clicking on it. Each tool functions within the context of the active page. The toolbar can also provide access to other functions of the interface. Many of the functions exposed on the toolbar have keyboard shortcuts, for example Ctrl+S is a shortcut to Save Changes.

[0085] Table Data Section: The table data section 304 provides access to the data of one or more tables in the database. The criteria used to determine which tables are included in a particular page will be discussed later. The table data section 304 includes an inline data portion and optionally a drilldown data portion (when required). Each page has one primary table which consists of required inline fields and optional drilldown fields. The data of the inline fields of the page's primary table are displayed in a grid of inline rows and columns. These rows and columns may be displayed in the order in which the developer added them to the interface. For convenience, a field caption row 314 is provided and is displayed above each inline column in the grid. Clicking on an inline field caption 316 sorts the data by that column in ascending order; clicking the column again will resort the data in descending order.

[0086] Referring to Figure 5, if the main table 502 has any drilldown fields, they are displayed in a separate drilldown row 504. Conveniently, a drilldown row 504 is displayed for a selected record, pinned records and for any records which are in edit mode, though this is not necessary. In contrast to the grid format of the inline row, the fields of the drilldown row float within the row container. The fields float from the top-left of the container in the order in which the developer added them to the interface. A record's drilldown row 504 is displayed immediately below its inline row 506. The GUI may be configured to display fields of any supplemental tables (S1 , S2, M1 or Ancestor tables) always as drilldown fields.

[0087] Each page may also have one or more supplemental tables. The selection of supplemental tables for a page will be described later. Each supplemental table may have its own drilldown row which is only displayed for the selected primary table record, pinned records and for any records of the supplemental table which are in edit mode. Unlike the primary table drilldown rows, the table caption of the supplemental table is configured to be displayed to the left side of its drilldown rows 504, as seen in Figure 5. Whether the field value of a field is empty may be visually indicated through the use of visual features of a field caption, such as colour, font, among others. For example, in the display mode of the drilldown row the field caption of each field may be displayed in bold text to the left of the field value. If the field value is empty then its caption may be displayed in a light grey font colour.

[0088] The inline rows may be alternately highlighted, or in different colours such as in translucent yellow and translucent white. Clicking on an inline row selects the row and displays any associated drilldown rows for the record. A selected row (including any drilldown rows) also may be rendered visually differently from other rows, for example, highlighted in translucent red. Clicking on a selected row or pressing the Enter key will switch the record from display mode to edit mode. In display mode none of the row data can be modified. In edit mode each field has its own control for modifying its data. There are several different field types a user or a developer may choose from when developing their application and the edit controls for each field type may be specifically configured for each type of data it stores. The details of the properties and functions of each of these field types are not further discussed here.

[0089] Pinned Records: A user may pin a row of the data table by selecting the record and then actuating a drilldown display pin tool, such as clicking on the Pin Record toolbar button

318. The drilldown rows of a pinned record stay in place when the selected record changes.

The row colouring of a pinned record (including any drilldown rows) follows the alternating translucent yellow / translucent white scheme. A record is unpinned by selecting it and then clicking on the Pin Record toolbar button 318 again. Of course, a record may be pinned and unpinned in any other suitable manner. For example, all the pinned records in a data table can be unpinned simultaneously by right-clicking the Pin Record toolbar button and selecting

Unpin All Records from a context-sensitive menu (not shown).

[0090] Page Selection Section: The number of rows displayed in the table section is a property the developer can modify. A convenient default is 15 rows which will be used for this discussion. The data is sorted by the field(s) the developer specifies or which the user selects. The number of rows available in the dataset is affected by any filters the developer or user may have defined. If there are more than 15 rows of data to display, the data is divided into 15-row units, each for displaying on a separate display page. Thus, the first display page 306 displays the first 15 results; the second display page displays results 16 - 30 and so on. The last display page of data may display less than 15 rows. As shown in Figure 3, the page selection section 308 displays one or more page selectors 320 (clickable numbers) which represent display pages in the dataset. Clicking on the page selector retrieves and displays the data for the specified display page and also highlights the text of the page selector. Clicking on the current (highlighted) page selector will requery the page data and refresh the display page. In large data sets a method is used to reduce the number of page selectors displayed in the page selector section. Whenever this paring method is implemented a page selector fly-out 322 is displayed (see also Figure 6). When clicked, the page selector fly-out 322 displays text entry form 602 such as a textbox in which the user can enter a specific display page to display, as shown in Figure 6.

[0091] Page Frame Participation: A topmost page frame is shown in Figure 3. A GUI that includes a topmost page frame and its subsequent child page frames is shown in Figure 5. The placement of a table within the GUI is dependent upon the type of relationship established with its parent(s) as defined in Figure 4 and such placement is configurable. For example, although in various embodiments described herein, the child page frame is described to be always displayed immediately beneath the parent page frame, the child page frame could also be displayed above the parent page frame or to the left or right of it. In general, the child page frame is only displayed when a record has been selected from the main table of its parent tab in the parent page frame. A user or a developer can choose the tables to include in the topmost page frame or configure the software application program 200 to select what tables to include. This configuration information may be saved in a configuration file in or by application definition 202, or entered using input device 140, for example. The software application program receives this information and generates or retrieves lists of tables to include in the topmost page frame accordingly, and consolidates the lists into one, if necessary. The tables to be included in the topmost page frame may be, for example, (1) all of the tables in the application, (2) only those tables which do not have parent tables, (3) all the tables from option (2) plus a manual selection of tables, or (4) a manual selection of tables. The end-user may also have the ability to change (add or remove from the developer's selection) which tables are included in the topmost page frame. This enables the system to generate a list of tables for inclusion in a page frame (list of participating tables) that is still flexible enough to meet each user's individual need.

[0092] Figure 7 illustrates steps of a method of compiling the lists of tables and displaying a topmost page frame for a consolidated list (if initially there are more than one list). First, a list of tables is compiled or retrieved. Depending on the configuration of the system, one or more of the following steps may be executed but not all are required. As illustrated in Figure 7, any of the following may be executed: compiling and/or retrieving a list of every table (702) stored in a configuration file in the application definition 202 or retrieved by the application definition 202, compiling and/or retrieving a list of only those tables which do not have a parent table (704), compiling and/or retrieving a list of all tables which do not have a parent table (704), retrieving, for example, from the application definition 202 a developer- specified list of manually selected tables (706), and retrieving an end-user specified list of list of manually selected tables (708). If more than one step of steps 702 through 708 is performed, the lists of tables compiled and/or retrieved at these steps are optionally consolidated into one list (710). A page frame is displayed (712) in the GUI as the topmost page frame. One table from the lists of tables (or consolidated list) is selected as an active table and is displayed, with a separate page for each table in the lists of tables (or consolidated list), selectable by clicking a corresponding page selector tab 302.

[0093] Although only one page is made active (and displayed) at a time, data of every participating table may be retrieved dynamically prior to or while displaying the active page. Referring to Figure 8, the definition data (from application definition 202) of every table participating in the page is first sent to the middle layer data handler 210 at step 802 (if the definition data has not already been sent previously). Next, SQL statements are generated by SQL generator 202 and executed to retrieve data from the database 20 at step 804. The data received from the database is formatted (conveniently in JavaScript™) and returned to the GUI at step 806. The GUI is updated to display the retrieved data at step 808, typically only the inline rows.

[0094] Each page of a page frame has its own child page frame; the child page frame is only displayed when a record is selected in the parent page frame's primary table (or active table). In general, only one table of a page frame can be active at any time. Consequently, only the active page's child page frame will be displayed in the child page frame level. The tables included in the child page frame are determined by a method which examines the relationships defined between the application's tables (see Figure 9). If the method does not identify any tables for participation in the table's child page frame then the child page frame is never displayed. The records displayed for the data tables of each page are retrieved by dynamically generated SQL statements which are constrained by the foreign ids established through the relationships involving the parent page frame(s).

[0095] Filtered Tables: The developer may choose to filter any table out of the application. A table may be filtered (1) to test alternate configurations, (2) because the user is not permitted to view the table or (3) for any other reason. There may also be tables in the database which the developer can choose not to include in the application. The application will render the interface as if the filtered tables did not exist.

[0096] Page Frame Participation Method: The page frame participation method determines which tables to include in a child page frame based on the tables of a parent page and their relationships.

[0097] Committed Tables: The committed tables are a list of every participating table in all the parent pages (i.e., including all ancestor pages of the parent page).

[0098] Uncommitted Ancestor Tables: A table's uncommitted ancestor tables are a list of all its ancestor tables which are not already committed. [0099] Primary Tables: Every primary table in the page frame will have its own page in the page frame. The method examines the relational tree of every uncommitted table in the application. To participate in the page frame, at least one of the table's ancestor tables must participate in the parent page (which includes its own primary table and possibly its supplemental tables). If a table has multiple ancestor tables that participate in pages of different page frames (i.e., at more than one level), the table is included for participation only if these ancestor tables participate in consecutive parent pages (i.e., at consecutive levels) starting from the immediate parent page. All the uncommitted ancestor tables of each participating table will also participate as a primary table in the page frame.

[00100] Filtering S1, S2 & M1 Tables: The developer can choose to omit from primary table page frame participation (1) all S1 and S2 tables, (2) all M1 tables, (3) all S1 , S2 and M1 tables, (4) any of (1), (2) or (3) plus a manual selection of S1 , S2 or M1 tables or (5) a manual selection of S1 , S2 or M1 tables. Filtering an S1/S2/M1 table from primary table page frame participation will neither affect its participation as a supplemental table of any page nor will it prevent the page frame participation of any of its uncommitted parents, although any of the uncommitted parents may also be filtered out if they themselves are an S1 , S2 or M1 table which matches the filter criteria. The end-user may also have the ability to change (add or remove from the developer's selection) which S1/S2/M1 tables are filtered from primary table page frame participation. A filter actuator, such as a filter button, may be provided. A subset of tables, such as S1/S2/M1 tables may be filtered upon user actuation of the filter button. Further, a filter selector, for example, in the nature of a selection list presented on a menu, may be provided for a user to select or modify the subset of tables to be selectively excluded, i.e., filtered.

[00101] These methods are now further described in reference to flowcharts that illustrate some exemplary processes. Figure 9 illustrates steps of a method of compiling a list of participating tables for displaying a child page frame of a table. First, at step 902, a first list, namely List A, is compiled to include all committed tables, which comprise tables participating in the active pages of all displayed page frames. Thus, list A tables are basically all tables currently displayed. Next, at step 904, the relationships of each table of List A are analyzed recursively to include tables that are descendant of any table in List A according to a set of pre-specified relationships, such as S3-S5, M1 , M3 and M5. This results in a second list, namely List B. A table is added to List B if any of its ancestor tables appears in List A obtained at step 902. Next, at step 906, the relationships of each table in List B from 904 are analyzed recursively to identify its ancestor tables and every ancestor table which does not already appear in List B is added to List B. Any table that is already included in List A is removed from List B (step 908). If the final list, namely the processed List B, is not empty (step 910), the tables in the final list are displayed in a child page frame, e.g., at a location immediately beneath the previous page frame (step 912). The child page frame includes a separate page for each table in the final list. If the final list is empty, nothing will be displayed. However, to reduce computation cost, an empty page frame is generated and returned to ensure it is not later reprocessed (step 914).

[00102] Figure 10 illustrates a method of generating and displaying a child page frame for the list of participating tables in response to a record being selected by a user in a parent page frame. Each time a record in the parent page frame is selected and a child page frame is displayed, the process illustrated in Figure 10 is executed. For example, the process illustrated in Figure 10 is executed when generating and displaying the child page frame 510 containing "Insurance Companies" when an inline row 506 is selected in the "Patients" table. Referring to Figure 10, the first step is to remove all descendent page frames, if any, of the page in which the record is selected other than the child page frame (step 1002). The child page frame is left in place and will be updated later. Any page frames below the child page frame are removed. Next step 1004 step is to highlight the selected inline row for visual distinction and display its drilldown and supplemental rows. Next step 1006 is to check if a child page frame already exists. If it does, the child page frame is also examined to determine if it has any pages (step 1008). No page frame will be displayed and the process will end ( 010) if the child page frame does not have any pages. If the child page frame has one or more pages, the child page frame is updated and page data for the active page in the child page frame, i.e., child records of the selected inline row, is displayed (1012).

[00103] On the other hand, if a child page frame does not exist as determined at step 1006, a page frame participation method is applied at 1014 to determine all tables that participate in the child page frame. An example of a method of applying a page frame participation method to determine participating tables in a child page frame is provided in Figure 9, as discussed earlier and will not be repeated here.

[00104] If the child page frame does not have any table, i.e., no page to display (1016), the process ends (1010). If the child page frame has one or more pages, the active page in the child page frame, i.e., the first page, is processed to update all of its participating tables (1018) based on relationships of the related tables established in the database and then displayed (1012).

[00105] Page Participation Method: When a page of a page frame first becomes active, a list of supplemental tables is generated according to the page participation method. Figure 1 1 illustrates an example of a page participation method of compiling a list of supplemental tables, which may be used to update the list of participating tables of a page. [00106] Supplemental Tables: As noted, a page includes at least a primary table and may also include one or more supplemental tables. A supplemental table may be an ancestor table, referenced table or tables belonging to certain selected relationships, such as S1 , S2 and M1 tables. Referring to Figure 11 , the first step is to compile a list (1102) that includes the main table (i.e., the table in which the record is selected or, more generally, an active table) and its S1/S2 child tables. In general, when a table is added to the list, child tables of the added table based on certain selection relationships may also be added. In this example, when a table is added, its S1/S2 child tables are also added to the list.

[00107] Ancestor Tables: Next, add to the list all uncommitted ancestor tables of each table already included in the list plus the S1/S2 child tables of each table added to the list (1 104).

[00108] Referenced Tables: Next, create clones of any tables referenced by tables in the list compiled at step 1 102 and add them to the list (1 106). Any cloned table is preferably given a unique name. When cloning a table, its ancestor tables and S1/S2 child tables are also cloned - all relationships of the cloned tables are changed to point to the cloned parent tables. Each cloned ancestor table also has its S1/S2 child tables cloned in the same manner. In this example, a relationship is created between the clone of the referenced table and the reference field's table (one of the tables in the list compiled at 1 102). This will be the only relationship of the cloned table. If an S1/S2 table has S1/S2 child tables, these are also cloned. Each clone has its relationship changed to point to the cloned parent.

[00109] M1 Tables: Finally, add to the list any uncommitted table of some preselected relationship, such as M1 relationship, for which all parent tables already included in the list (1 108). The method examines the relational tree of every uncommitted M1 table in the application. If all of the 1 table's parent tables are committed or participate in the page then the M1 table and its S1/S2 child tables will also participate in the page. The page participation used for this part of the method is dynamic. Initially it includes the primary table, uncommitted ancestor tables and all S1/S2 child tables of the primary table and uncommitted ancestor tables. Each M1 table that is identified for participation in the page is added to the list along with its S1/S2 child tables before examining the next table's relational tree. The tables are examined in the order in which the developer added them to the application. Since the application will not allow a developer to add a child table before its parent table(s) have been added, the method is ensured of pulling into the page participation any M1 tables that are themselves a child of another M1 table (including its S1/S2 child tables). [00110] Although four steps of the page participation method are described, it will be understood that not all are required or necessary and that any suitable combination or combinations of the steps may be used.

[00111] The use of the methods illustrated in Figures 9 through 11 is now further described with reference to Figure 5. Figure 5 illustrates a GUI state with a topmost page frame and four subsequent child page frames. As described earlier, the software application program is configured to display a child page frame when a record is selected in the parent page frame's active table. As shown in Figure 5, initially, a topmost page frame 508 is displayed. Next, an inline row 506 is selected in the active table, in this case the "Patients" table. The selected record also includes drilldown data. Selecting the inline row 506 displays both the drilldown row 504 and the child page frame, which is a second level page frame 510. The child page frame displays therein the "Insurance Companies" page as an active page. Next, an inline row 512 in "Insurance Companies" page of the second level page frame 510 is selected and a third level page frame 51 is displayed. The third level page frame 514 displays an "Insurance Polices" table as its active page. Next, an inline row 516 is selected in the "Insurance Policies" page of the third level page frame 514 and a fourth level page frame 518 is displayed, which displays a "Transactions" page as its active page. Finally, an inline row 520 in the active page "Transactions" of the fourth level page frame 518 is selected and a fifth level page frame 522 is displayed.

[00112] In this example, the "Insurance Companies" table is displayed in the second page frame 510 because of the M1 relationship established in the database between the "Patients", the "Insurance Policies" and the "Patient Policy" tables and because the Insurance Companies" table is parent to the "Insurance Policies" table. The displayed "Patient Policy" row 524 is the M1 child row of the selected "Patient" row 504 and the selected "Insurance Policy" row 516. The displayed "Transactions" rows 520 (only one in this example) are the S3 child rows of the selected "Patient Policy" row 524. The displayed "Disability" row 526 is the S1 child row of the selected "Transaction" row 520. The displayed "Procedures" row 528 (only one in this example) are the S3 child rows of the selected "Transactions" row 520.

[00113] The GUI

[00 14] S1 & S2 Tables: Conveniently and for easy identification, the child record of an S1 or S2 relationship is displayed immediately beneath its parent record. The S1/S2 row only appears when its parent row is selected or is in edit mode. If more than one S1 or S2 relationship exists, the records are displayed in the order the developer added the tables to the application. If a child record does not exist in the database then no row is displayed in display mode. S1/S2 rows are edited in conjunction with their parent row and are always displayed when their parent row is in edit mode. In display mode, an S1 row can be deleted by clicking on the delete icon to the left of the S1 row. In edit mode, any changes to the row can be cleared by clicking on the cancel changes icon which takes the place of the display mode delete icon. The S1 edit row will still be displayed after clicking the cancel changes icon. Since an S2 row is required, no delete or cancel icons are displayed for it.

[00115] Considering a database that contains a "Transactions" table, a "Disability" table and an "Ambulance" table, among others. The "Transactions" table has two child S1 tables, "Disability" and "Ambulance". Tables with these relationships established in the database may be displayed in a state as illustrated in Figure 5, which shows page frame 516 containing a "Transactions" table in display mode. The selected "Transactions" record 520 does not have a child record in the "Ambulance" table, so no row appears for the Ambulance table in the display mode of the row. There is one child record in the "Disability" table, so a single row 526 is displayed in the "Disability" table. To display the "Ambulance" row for editing, the "Transactions" row 520 is first switched to edit mode. This may be achieved by, for example, requiring a user to click on any field of the "Transactions" row 520 or the Disability row 526.

[00116] Figure 12A shows an example with rows in edit mode. In edit mode, the "Ambulance" row 1202 is displayed, as shown in Figure 12A, together with "Disability" row 1204, both in edit mode. As the "Ambulance" table contains no data, it is configured not to be displayed in display mode and an "Ambulance" row 1202 is displayed only when the row is in edit mode. If the user fills out any data for the "Ambulance" row, a corresponding new record will be created in the database when the active page ("Transactions" page) displayed in the fourth level page frame 518 is saved.

[00 17] Figure 12B shows another example with an inline row in edit mode. In this example, the displayed table 208 is not empty. An inline row 1206 in the table 1208 is selected, shown as a highlighted row in the example. The selected row is switched to edit mode upon receiving an edit command from the user, for example, when the user clicks on the selected row 1206, presses the Enter key when the row 1206 is selected, presses an edit button in the toolbar 312, among others, or in any manner provided by the system. In edit mode, a user is able to enter or change the data displayed in the inline row 1210.

[00118] S3 - S8 Tables: Each participating S3 - S8 table will have its own tab of which it is the primary table. Figure 13 shows an example of displaying an S4 relationship between the "Transactions" table 1302 in page frame 1304 and the "Procedures" table 1306 in page frame 1308. [00119] M1 & Ancestor Tables: The GUI representation of M1 and ancestor tables is similar to that of S1 and S2 tables but unlike the S1 and S2 rows, the M1 and Ancestor rows can be edited and deleted independently of their primary row. The ancestor tables will have fields that are defined as inline (when the table is accessed as the primary table of a page) but these inline fields will appear as drilldown fields in the Ancestor row. In display mode, an M1 or Ancestor row can be deleted by clicking on the delete icon to the left of the row. In edit mode, the cancel changes icon takes the place of the display mode delete icon. Clicking on the cancel changes icon will discard any changes made to the row data and switch the row to display mode. Deleting an Ancestor row will also delete its primary row and any other primary row on the page that is a child of that Ancestor row.

[00120] -Table Filter Button: When one or more M tables have an ancestor table participating in the page, the leftmost column of the data grid's header row displays an M- Table filter button 324 (see Figure 3). Different graphics symbols, possibly coupled with a colour scheme, may be used to indicate the states and meanings of the M-Table filter button 324. The states and meanings of the M-Table filter button 324 may be represented graphically using different graphics symbols as illustrated below:

[00121] When there are two or more M-Tables available for filtering, a menu, such as a right-click or context sensitive menu, among others, may be provided to enable a user to select a table for filtering from a selection list provided by the menu. Selecting a table from the selection list will display only those primary rows with an existing row in the participating ancestor table of the selected M-Table. The M-Table filter button then displays a gray checkmark.

[00122] M-Table Status Indicator: An M-Table status indicator 326 (see Figure 3) is displayed in the leftmost column of each primary row when one or more M-Tables have a relational parent participating in the page. Different graphics symbols, possibly coupled with a colour scheme, may be used to indicate the states and meanings of the M-Table status indicator 326. The states and meanings of the M-Table status indicator 326 may be represented graphically using different graphics symbols as illustrated below.

[00123] One can configure to display the M1 status button only when there are one or more participating M1 tables on the page and one or more M1 rows are missing from the primary row. In general, the main purpose of the button is to provide a method for the user to add the missing M1 rows. Clicking on the M-Table status indicator button will display every missing M1 row in edit mode. If all missing M1 rows are displayed in edit mode then clicking on the M-Table status indicator button will hide all of these new rows. If only some of the missing M1 rows are displayed in edit mode then clicking on the M1 status button (to turn the button into a checkmark) will display all the other missing M1 rows in edit mode.

[00124] Figure 14 shows an example of an M1 relationship, where the Patient Policy table 1402 is the M1 child of two tables, the Patients table 1404 and the Insurance Policies table 1406. An M-Table status indicator button 1408 is provided to enable a user to switch every missing M1 row to edit mode by clicking on the M-Table status indicator button 1408.

[00125] M3 & M5 Tables Each participating M3 & M5 table will have its own tab of which it is the primary table. [00126] Order of Appearance: The order in which the parts of a selected row are displayed is as follows:

1 . The inline row of the tab's primary table

2. The drilldown row of the tab's primary table

3. S1 & S2 child rows of the primary table

4. Ancestor rows + their S1 & S2 child rows

5. Referenced rows + their S1 & S2 child rows

6. M1 rows + their S1 & S2 child rows

[00127] Within sections 3, 4 & 5 of the row, the tables appear in the order in which the developer added them to the application. S1 & S2 rows always appear immediately beneath their parent row. If a parent row has more than one S1 or S2 child rows, they all appear immediately beneath their parent row in the order in which the developer added them to the application.

[00128] Editing Multiple Records: Any displayed row on the page (primary, Ancestor, Referenced, S1 , S2 or M1 ) can be edited in conjunction with any other row on the page. S1 and S2 rows are always edited in conjunction with their parent row. The tables of only one page of any of the application's page frames can be edited at any time, unless new primary rows are being added to a table (see below). Regardless of how many rows are being edited, only one row will ever be selected at any time. The tab's child page frame always displays the child records of the selected parent row.

[00129] Adding New Primary Rows to a Table: When new rows are added to a primary table, new rows can also be added to any primary tables participating in the tab's child page frame. This provision extends to all the descendant tables of the initial primary table. Relationship types S4, S6, S7 & S8 require this capacity in that each of these relationships requires one or more child record for each parent record. If the primary table is an S5, S6 or S7 table and the maximum number of allowed records already exist, the user is informed and no new record is added.

[00130] Saving Changes: All the changes made in every open edit row on the page (and its descendant page frames in the case of new records) are saved together by clicking the save tool in the toolbar or pressing Ctrl+S. Before the changes are committed to the database the changes are validated and verified.

[00131] Validations and Verifications: The developer may specify a validation and / or verification function for each field in the application. These functions are called when changes are saved. The naming conventions [table name]_[field name]_validate and [table name]_[field name]_verify are used for the names of these functions.

[00132] Field Message Area: Validation / verification messages are displayed in an area immediately below an inline field and to the right of a drilldown field.

[00133] Each validation / verification function is passed the following five parameters:

[00134] If the developer passes back a non-empty string from a validation / verification function, the text is displayed in the field message area, the field fails validation / verification and the record is not saved. If no validation / verification function exists for the field then it passes validation / verification. If a field fails validation, its value (or possibly the value of another field in the row) must be adjusted before the save will succeed.

[00135] Figure 15 illustrates in a flowchart the steps of a validation method. The first step 702 is to create a data object of all data from the row, its S1/S2 child rows and all ancestor rows. Data from the row, its S1/S2 child rows, all ancestor rows (and their S1/S2 child rows) are collected to create the data object. At step 504, a copy of the data collected at step 1502 is passed to each field's validate data-driven function (if it exists). At step 1506, if a message (non-empty text string) is returned from the validate data-driven function then the message is add to a list of validation errors. The validation errors are displayed at step 1508 and the software application is configured to direct the system not to save any changes when there is a validation error. A field validation message 1602 is shown in Figure 16.

[00136] Figure 17 illustrates in a flowchart the steps of a verification method. The first step 1702 is to create a data object of all data from the row, its S1/S2 child rows and all ancestor rows. At step 1704, a copy of the data collected at step 1702 is passed to each field's verify data-driven function (if it exists). At step 1706, if a message (non-empty text string) is returned from the verify data-driven function then the message is add to a list of verification errors. The verification errors are displayed at step 1708 as verification warnings. A field verification message 1802 is shown in Figure 18. In the case of a verification message, the user may choose to proceed with the save without changing the data or may cancel the save and make changes first. Validation and verification messages may be visually differentiated. For example, validation messages may be displayed in a red block of the GUI while verification messages may be displayed in an orange block. As shown in Figure 18, the software application is configured to provide a dialog window 1804 to enable a user to ignore the warnings and save the changes or to cancel the saving operation.

[00137] The system may be configured to act on a validation or verification error without requiring a user's input. For example, if the user attempts to save a new S4, S6, S7 or S8 parent record without also creating the required number of child records, a validation message is displayed and the save is cancelled. As another example, if the user is adding new S5, S6 or S7 records to an existing parent record, the database is checked to ensure the total number of child records will not exceed the allowed number; if it will, a validation message is displayed and the save is cancelled.

[00138] Deleting Records: The developer may specify a row delete validation function for each table. The naming convention [table name] row_validateDelete is used for the names of these functions. [00139] Each delete validation function is passed the following four parameters:

[00140] If the developer passes back a non-empty string from a row validate delete function, the delete fails validation, the user is informed and the delete is cancelled.

[00141] If the delete passes validation and the number of non-S1/S2 child records is > 0 (parameter 3), the user is informed of the number of child records they are about to delete and they must confirm the delete before it is executed. A delete verification message in a pop-up window may be provided.

[00142] Reference Fields

[00143] In many cases, it is desirable to incorporate data from a foreign table within the child data table itself. A classic example is an order tracking database consisting of three data tables: "orders", "order items" and "products". In this database, "order items" has both "orders" and "products" as foreign tables. It makes sense to follow the description of paragraph entitled "S3 - S8 Tables" and display the order items in the page frame following orders but if only the description of paragraph entitled "M1 & Ancestor Tables" is followed for the "product", then the product name would not be displayed until a row is selected. In this example, the interface would be much more user-friendly if the product name is a field in the inline row. In other scenarios, the foreign information is better suited to placement in the data table's drilldown row. The functionality of including information from one data table within the inline or drilldown row of another data table is provided by the reference field.

[00144] Nomenclature

[00145] A reference field is a field in the data table which displays a string representation of one or more fields from a foreign table within the inline or drilldown row of the reference field's data table. A reference field can be any field in a data table other than the data table's primary key or one of its foreign keys.

[00146] The referenced fields of the reference field refers to the list of fields from the foreign, referenced data table.

[00147] The child data table of a referenced data table is the data table which contains the reference field.

[00148] Adding a Reference Field to the GUI

[00149] A reference field is appended to a data table by its .appendReference or .appendDrilldownReference function. The new reference field will be appended to the data table's inline or drilldown fields, respectively.

[00150] Specification

A reference field is specified by the following parameters:

[00152] Parameter Notes

[00153] Parameter 1 - Physical Field Name: FNFDA refers to a Field Name or Field Definition Array (FDA). A field name is specified by a string whereas a field definition array is specified by an array in the form ["field name","data type",{options}] or [["field name", "old field name"],"data type",{options}]. A field definition array is used when (re)building the database with some of the embodiments described herein. The first form of the array is used when creating a database table, adding a field to an existing database table or changing the data type of a field in an existing database table. The second form of the array is only used when renaming a field in an existing database table. The options available for the third parameter of the FDA are: Default

Name Data Type Description / Notes

Value

The .allowNull setting specifies whether or not the field allowNull Boolean true

accepts NULL values.

If .drop is true, the field will be dropped from the table

Drop Boolean false

on the next database rebuild.

If .indexed is true and the index doesn't already exist, an index will be built for the column on the next indexed Boolean false database rebuild. If .indexed is false and an index exists for the column, the index will be removed on the next database rebuild.

If .update is true, an ALTER will be forced on the field on the next database rebuild. This is not normally update Boolean false necessary when changing a field's data type since various embodiments described herein will detect most data type changes.

[00154] Parameter 2 - Field Caption: A string value or an array of two string values is required. If the specified value (or first parameter of an array) is non-string then the field name will be used as the caption. If the field name is desired as the field caption then undefined should be specified as the field caption. If there is no suitable caption for the field, then an empty string ("") should be specified. The normal format of the field GUI is to display the field caption and then the field value or edit elements in the format Field Caption: [Field Value / Edit Elements]. If the field value and edit elements should be displayed within the field caption, then specify the location of the field value / edit elements with "[— ]" as in "Field Caption Before [— ] Field Caption After".

[00155] The developer may choose to include a field in a grouped collection of fields in either the inline or drilldown portion of a row. An array of two strings should be specified for any field that is part of an inline or drilldown group. The first parameter of the array is used within the GUI of the display and edit rows while the second parameter is used everywhere else when referring to the field. For example, consider the field "hphone" which is part of a group named "Phone Numbers". The proper field caption array for this field would be ["Home'V'Home Phone Number"].

[00156] Parameter 5 - Optional Properties

[00157] The property inheritance model is illustrated below:

[reference element]{properties}

▲

[data table element]. properties.fields.reference{properties}

▲

app.properties.dataTables.fields.reference{properties} [00158] The default value of each property is the initial value set by various embodiments in app.propert^'ies.dataTables.fields.reference{properties}. The developer can override these default values and set new default values for every reference field in the entire application. Each data table in the GUI inherits .properties.fields.reference{properties} from app.properties.dataTables.fields.reference{properties}. The developer can override these inherited values and set new default values for every reference field appended to the specific data table. Each reference field in the GUI inherits .properties from its data table's .properties. fields. reference{properties}. The developer can override these inherited values for each individual reference field.

[00159] .type

[00160] .ignoreCase: Only applies to reference fields of type "quickpop" or type "quickpick". This property specifies whether or not the search of existing items should respect the case of the user-entered text.

[00161] .inputSize: This property specifies the width (in number of characters to display) of the field's edit input. Data Type Acceptable Values Default Value

Integer 5 - 50 10

[00162] .isRequired: If the field's database column does not allow nulls then .isRequired is true, otherwise .isRequired is inherited. If .isRequired is true, the user will be forced to select a value before the record can be saved.

Data Type Acceptable Values Default Value

Boolean true, false false

[00163] .isSortable: If .isSortable is true and the field is inline, the user can sort the data by clicking on the field's header cell. For both inline and drilldown fields, sortable columns are listed in the data table's sort dialog.

Data Type Acceptable Values Default Value

Boolean true, false true

[00164] .isUnique: If .isUnique is true, the field value must be unique to the database table, within the current foreign ids (entire database table if the data table does not have foreign keys); the user will not be able to save the record if another record exists with the same value.

[00165] Reference Field GUI

[00166] The reference field displays a string representing a row selected from another data table. The displayed string is calculated by passing the referenced fields from the referenced table to the field's data-driven format function(s). [00167] When using a reference field with .type = "quickpop" or .type- 'quickpick", one or more data-driven format functions can be used with the field; when .type = "poptable" only one data-driven format function may be used.

[00168] When using only one data-driven format function, the naming convention [data table name]_[field name]_format must be used.

[00169] For multiple data-driven format functions, the functions must be consecutively numbered starting with 0 and following the naming convention [data table name]_[field name]_format[N].

[00170] Multiple data-driven format functions are used for enabling quickpick searches of the referenced table by different orders of the referenced fields. A menu, a selection button or any other suitable means can be used for user to select a desired format from a list of available formats. For example, a button may be provided to enable a user to left-click on the change format button 1908 (see Figure 19A) to cycle through the formats while right-clicking on the button 1908 to display a context menu of all the formats.

[00171] When using multiple data-driven format functions, the displayed value of the field is calculated using the first (N = 0) function. Table 1 lists examples of single and multiple data-driven format functions.

[00172] If no data-driven function exists for the field, the displayed value will be the referenced field values concatenated with a space.

[00173] Clicking the edit button 1910 (see Figure 19A) of the reference field edit GUI will pop up the referenced table in a modal dialog 1912. The user may change the referenced record by choosing a record in the modal dialog 1912 and pressing the Select button 1914.

[00174] The modal dialog contains one page of a page frame without a tab. The tables participating in the modal dialog's page include the referenced table and any S1/S2/ancestor table(s) to the referenced table. The modal dialog's page does not spawn a child page frame even if the referenced data table would normally spawn a child page frame.

[00175] All of the normal table functions including CRUD (create, update and delete) are available to the user for the table(s) in the popup modal dialog. [00176] Reference fields of the tables in the modal dialog may themselves spawn additional modal dialogs. Each spawned modal dialog is organized into a separate layer of the GUI with no access to the layer beneath it. A translucent div element provides the physical boundary between layers enabling the user to see the data of lower levels put preventing access to that data until the modal dialog is closed. [00177] If the developer does not specify a data-driven format function, various embodiments described herein will use a concatenation of the values of the referenced fields with a separator of a single space (" ").

[00178] Reference Field Data-Driven Format Function

[00179] The data-driven functions are developer-created functions which are named following precise naming conventions. The naming convention for the reference field's format data-driven function is [data table name]_[field name]_format or [data table name]_[field name]_format[N].

[00180] The parameters and return value of the format data-driven function are as follows:

Parameter

Data Type Description / Notes

Field's

1 + Field Value The value of the field.

Data Type

Return Data Type Description / Notes

String The formatted value to display.

[00181] Examples:

Table Name Field Name Reference Fields Example

"patient" "rdrmstid" ["lname","fname","mi"] ["Carter","John","T"]

Single Data-Driven Format Function function patient_rdrmstid_format(s,t,u)

"Carter, John T."

{return s + ", " + 1 + (u?" " + u + ".":"");}

Multiple Data-Driven Format Functions function patient_rdrmstid_formatO(s,t,u)

"Carter, John T."

{return s + ", " + 1 + (u?" " + u + ".":"");} function patient_rdrmstid_format1 (s,t,u)

"John T. Carter"

{return t + " " + (u?u + ". ":"") + s;}

TABLE 1 EXAMPLES OF THE REFERENCE FIELD'S DATA-DRIVEN FORMAT FUNCTION

[00182] Relational Effect of Reference Fields

[00183] Each reference field links a field of its data table to the primary key of a foreign table, establishing an S3 relationship from the referenced data table (parent) to the reference field's data table (child).

[00184] Referenced Record as a Supplemental Table of a Page: When included as a supplemental table of a page (as per the paragraph entitled "Referenced Tables"), the GUI of a referenced record follows the paragraph entitled "M1 & Uncommitted Ancestor Tables" with the exception that the record is initially hidden from view. The display of the referenced record 1902 is toggled by clicking on the show / hide record toggle button 1904 (see Figures 19A and 19B) of the related reference field 1916. Any ancestor records to the referenced record are always displayed. Alternate implementations here include (1) referenced records being initially displayed, (2) toggling the display of ancestor records to the referenced record along with the referenced record and (3) both (1 ) and (2). As with the main page frame, selection of a referenced record 1902 in display mode also causes the GUI to display the referenced record in edit mode 1906. [00185] Page Frame Participation: A referenced data table will participate in the page frame participation method of the paragraph entitled "Primary Tables" only if it does not appear in the relational pillar of its child data table. If the referenced data table does participate in the page frame participation method, the referenced data table is treated as an uncommitted relational parent in the method of the paragraph entitled "Primary Tables".

[00186] Relational Pillars: A relational pillar is a group of interrelated data tables in an application. Each application will have one or more relational pillars. The following steps are used to calculate the relational pillars of an application:

1 . Collect a list of relational trees of data tables which do not have any child tables. 2. Combine any items with intersecting relational trees.

3. Each item (group of tables) in the list is a relational pillar of the application.

[00187] Clone-Spawning Referenced Fields: If a referenced data table appears within its child table's relational pillar then the referenced data table is a clone-spawning referenced table and a clone of the relational pillar will be created. The fourth step in the method of the paragraph entitled "Relational Pillars" is to (1) set a flag for each reference field in the data tables of each relational pillar indicating whether or not it is a clone-spawning reference field and (2) set a pointer to its relational pillar.

[00188] Inclusion of Non-Clone-Spawning Reference Fields in the Relational Pillars: The fifth step in the method of the paragraph entitled "Relational Pillars" is to add the relational tree of the referenced table of any non-clone-spawning reference field to its relational pillar. When a relational pillar is cloned, the non-clone spawning reference fields in the cloned relational pillar will reference a table in the cloned relational pillar.

[00189] Clone-Linking Relationship: The sole link between a relational pillar and its clone is an S3 relationship between the reference field's referenced data table and the clone of the reference field's data table from the primary key of the referenced data table to the reference field in the clone of the reference field's data table.

[00190] Self-Referenced Reference Fields: A self-referenced reference field is a reference field which specifies its own data table as its referenced data table. A self- referenced reference field is always a clone-spawning reference field.

[00191] Clone Naming: Each data table in the clone of the relational pillar is assigned a unique table name in the application. [00192] The new name is a concatenation of the original name and an iterative counter. If another data table exists in the application with the same name as the new name then the counter is incremented and the process repeated until a unique name is found.

[00193] Tab Caption: In order to uniquely identify the clone of the reference field's data table to the user, the caption of the cloned data table's page frame tab follows the naming convention "[data table caption]: [reference field caption]

[reference field's formatted display value]". For example, in an "employee" data table (caption "Employees") containing the self-referenced reference field "reportsTo" (caption "Reports To"), the page frame tab caption of the clone of the employee data table is "Employees: Reports To " + [reference field's formatted display value]. The "Employees: Reports To" part of the caption is static while the reference field's formatted display value will differ for each record in the original "employee" data table. If the referenced fields parameter (parameter 4) of the reference field is ["salutation", "firstName","lastName"] with field values ["Dr.","Leonard","McCoy"], respectively in the original "employee" data table, then the page frame tab caption for the clone of "employee" data table will be "Employees: Reports To Dr. Leonard McCoy" for that referenced record. Note that for this example since no data-driven format function is specified, the reference field's formatted display value is simply a space-delimited concatenation of the values of the referenced fields.

[00194] Timing of Clone Creation: When the application is launched, the only data table that is cloned is the reference field's data table. Clones of all the other data tables in the relational pillar are made only if (1 ) the user clicks on the page frame tab of the clone of the reference field's data table or (2) if the clone of the reference field's data table is the first or only primary data table in its page frame, then the copies are made when the user selects a record in the reference field's referenced data table.

[00195] Delaying the bulk of the data table cloning serves to speed up application launch following just-in-time programming practices and also prevents what would otherwise be a race condition with endless propagation of clones of relational pillars.

[00196] Clones' Foreign Keys: Once the cloned table has been assigned a new name, its foreign tables are scanned and each foreign table's name (pointer) is replaced with the name of the foreign table's clone in the cloned relational pillar. The reference fields of the data table are then scanned and the names of the referenced tables are likewise replaced with the name of the referenced table's clone in the cloned relational pillar.

[00197] The cloned data tables are processed in the order in which the developer added them to the application to ensure that a cloned data table will always be renamed before any relational pointers to itself are encountered in the method of the paragraph entitled "Clones' Foreign Keys".

[00198] The method of the paragraph entitled "Clones' Foreign Keys" does not include the clone-linking relationship established in the paragraph entitled "Clone-Linking Relationship". The reference field in the clone of the reference field's data table retains this pointer to the referenced data table in the original relational pillar.

[00199] Multiple layers of cloned relational pillars are possible with the number of clones limited only by how deeply the user navigates the GUI following the steps outlined in the paragraph entitled "Timing of Clone Creation".

[00200] Clone-Linked Boundary: The single S3 relationship established between the reference field's referenced data table and the clone of the reference field's data table by the clone-spawning reference field is the only relationship between these two data tables. This relationship serves as a boundary (clone-linked boundary) between the original relational pillar and its clone.

[00201 ] In the GUI, the clone-linked boundary separates the original relational pillar from its clone. The data tables of the cloned relational pillar only participate in the page frame participation method of the paragraph entitled "Page Frame Participation Method" in descendant page frames of the reference field's referenced data table.

[00202] When some embodiments described herein create SQL statements to retrieve the various data for a page of a page frame involving the cloned relational pillar, the clone-linked boundary is never crossed with the single exception of the one S3 relationship between the original clone-linking data table and the clone of the clone-linking data table. In other words, any foreign ids that have been set by selecting records in the data tables of the original relational pillar do not apply to the SQL statements created for the data tables of the cloned relational pillar with the exception of the foreign id from the clone-spawning reference field in the original clone-linking data table to the primary key of the cloned clone-linking data table.

[00203] The S3 relationship between the reference field's referenced data table and the clone of the reference field's data table serves as a link between a relational pillar and its clone. For the first clone, the link is to the group of original data tables. For the second clone, the link is between the second and first clones of the relational pillar (not to the original relational pillar).

[00204] Reference is now made to Figure 20, which is a flowchart diagram illustrating steps of a process to generate and display a page frame performed by system 100 of Figure 1. At 2002, the applications initializes, which includes retrieving a list of all tables in the database and relationships of the tables as established in the database from the database or from the application definition 202, analysing and categorizing the relationships of the tables, and processing referenced tables that have been removed due to restriction placed on a user, among others. This will be further described below.

[00205] At 2004, page frame participation method is applied to determine the list of participating tables. An example of a method of applying a page frame participation method to determine participating tables for the topmost page frame is provided in Figure 7, as discussed earlier and will not be repeated here. An active page or active table for the topmost page frame is also selected from the list of participating tables. This may be the first table in the list, may be a specified table selected from the database, or may be a table specified by a user, among others.

[00206] Optionally, at step 2006, page participation method is applied to determine the list of participating tables of the topmost page frame for the active top level page, such as the first page of the topmost page frame. Figure 11 illustrates an example of a method of compiling the list of participating tables for a page in a page frame, such as the active top level page. The list of participating table for a page includes a primary table and a list of supplemental tables for the primary table. Details of compiling a the list of participating tables are not repeated here.

[00207] Finally, at step 2008, page data for the first page is displayed in the topmost page frame. An example of a method for displaying data in a page frame is illustrated in Figure 8 and discussed earlier, which will not be repeated here.

[00208] Now, returning to step 2004 and referring to Figure 21 , a detailed example of application initialization is illustrated in Figure 21 and described here. First, at step 2102, application definition file stored in application definition component 202 is parsed. The application definition file contains the programmer's code. The code may be used to specify all the tables in the application and their relationships to one another. The code may also contain the application's data-driven functions (e.g., validate, verify and others).

[00209] Next, at step 2104, the definition of every table and its relationships with other tables created or retrieved at step 2102 are stored in a designated area in a memory device, such as quick access memory (conveniently in JavaScript on the user's machine).

[00210] The application's administrator may choose to limit a user's access to specific tables. At 2106, any tables to which the user does not have access are removed from the table definitions along with their relationships retrieved or created at step 2102. [00211] At step 2108, the relationships of each table are analyzed and the tables are categorized by type, for example according to the relationship types listed in Figure 4.

[00212] For convenience and speed of processing, each table may have a list of its child tables created and stored that belong to a selected set of relationships. For example, each table may have a list of its child S1 and S2 tables created and stored. Optionally, at step 2110, each S1/S2 table is stored in the list of its first non-S1/S2 ancestor table.

[00213] Finally, at 2112, every referenced table in the list obtained at step 2106 is cloned (see Figure 22 for an example and explanation) and a relationship is created between the cloned table and the table of the reference field. This completes the initialization process shown in Figure 21.

[00214] When cloning the referenced tables, a list of cloned tables is generated and provided to the page participation method and all child page frame participation methods. An exemplary process of cloning a reference table is illustrated in Figure 22. First, at 2202, create a clone of every table in the referenced table's relational pillar (except for the referenced table itself). Next, at 2204, relate each cloned table to its parent tables within the clone group. Next, at 2206, create a clone table of every referenced table in the list obtained at step 2106 and relate the clone table to the table containing the reference field that is referencing the referenced table. Finally, at 2208, the list of cloned tables is provided to the page participation method and all child page frame participation methods.

[00215] It will be understood that the invention is not limited to the examples provided above and that variations and combinations are possible. For example, according to an embodiment, a computer implemented user interface is constructed for a relational database. The user interface includes one or more page frames each being configured to display a plurality of pages of a page frame, each page of the plurality of pages corresponding to a table in the relational database and having a plurality of participating tables related to the corresponding table according to relationships of tables in the relational database. Each page frames has a table selection section, a table data section, a page selection section and a toolbar section. The table selection section has a plurality of table selectors. Each table selector corresponds to a respective page of the page frame, selection of which designates the respective page as an active page and the corresponding table as an active table. The table data section provides access to and displays records of the active table in the page frame in one or more display pages. Selection of a record of the active table displayed in a page frame causes construction and display of a new page frame, which will be a child page frame of the active page for displaying one or more child tables. Child tables identified for inclusion in the child page frame are related to the active table of the parent page frame according to relationships specified in the relational database. The page selection section has one or more page selectors each corresponding to a respective display page. Selecting a page selector provides access to the corresponding display page, such as displaying it.

[00216] The plurality of participating tables of a page may include a primary table corresponding to the active table and a list of supplemental tables, each of the supplemental tables being related to the primary table according to a user specified subset of relationships of the relationships of tables in the relational database.

[00217] The table data section may include an inline data portion for displaying inline data and a drilldown data portion for displaying drilldown data. Data of supplemental tables may be displayed in the drilldown data portion.

[00218] The child page frame may be configured to be displayed only upon selection of a record of its parent page. Further selection of a record displayed in a child page frame iteratively causes display of additional child page frame(s) at a lower level(s). Child or descendent tables included to participate in additional child page frame(s) of the lower level(s) are related to the active table having the selected record and all active tables of all parent page frames at higher levels according to relationships specified in the relational database.

[00219] A page frame may also include a filter to selectively exclude a subset of tables (and child tables) from the page frame (and its child or descendant page frames). It may also further include a filter selector to allow a user to access and modify the subset of excluded child tables.

[00220] The page selectors may be a single page selector and at least a page range selector, selecting of which causes a corresponding display page to be displayed or a page range selector, selecting of which causes multiple page selectors to be displayed for a user to select display pages in a corresponding page range.

[00221 ] The toolbar section is configured to include several tool selectors for selecting pre-defined tool functions, such as creating new records, copying existing records, editing selected records, saving changes, cancelling changes and deleting selected records. Tool selectors may also include a drilldown display selector, actuating the drilldown display selector causes the drilldown data portion to alternate between a display mode and a non- displayed mode.

[00222] The data table section may be configured for adding a new row, addition of the new row also adding a new record to the active table and a corresponding new record to all child tables of one or more user-specified relationship types in the child page frames of the active table. The table data section may also be configured to initially display only the inline data portion, and display any associated drilldown data or data of the supplemental tables in the drilldown data portion only upon user selecting a row in the displayed inline data portion.

[00223] The data table section may further include a reference field portion. The reference field portion includes at least a reference field for displaying a representation of one or more fields from a foreign table and may include tool selectors for selecting predefined tool functions. The reference field portion may be placed within the inline data portion or the drilldown data portion. The tool selectors may include a dialog selector. Selection of a dialog selector causes a dialog window to appear to display therein a foreign (referenced) table. The dialog window may include additional tables selected according to relationships between the foreign table and other tables in the relational database. Selection of a record in the dialog window iteratively causes a new dialog window to appear to display therein the one additional data table. Further, the new dialog window may include more additional data tables selected according to relationships between the one additional data table and data tables in the relational database.

[00224] A record may be displayed in edit mode or display mode and may be switched between edit mode and display mode through a pre-selected key actuation, actuation of an edit selector defined in the toolbar section and a combination thereof.

[00225] It should be understood that in various embodiments, the creation of the graphical user interface is dynamic. Accordingly, as relationships are specified the GUI is updated displayed dynamically.

[00226] The above-described embodiments are intended to be examples only. Those of skill in the art can effect alterations, modifications and variations without departing from the scope.

[00227] Although this disclosure has described and illustrated certain embodiments, it is also to be understood that the system, apparatus and method described is not restricted to these particular embodiments. Rather, it is understood that all embodiments which are functional or mechanical equivalents of the specific embodiments and features that have been described and illustrated herein are included.

[00228] Further, it will be understood that, although various features have been described with respect to one or another of the embodiments, the various features and embodiments may be combined or used in conjunction with other features and embodiments as described and illustrated herein and that numerous modifications, adaptations and variations may be made to the examples. Therefore, the invention is not to be limited to those details but only by the appended claims.

Claims

What is claimed is:

1. A method of generating and providing a computer implemented user interface for a relational database, the relational database containing data tables comprising records, the user interface comprising one or more page frames, each being constructed for a list of tables of the relational database by:

constructing a page of the page frame for each table in the list of tables, the page to be filled with data of the corresponding table,

identifying one page from the list of pages as an active page and the corresponding table as an active table,

retrieving data of the active table for displaying in a table data section of the page frame,

constructing a table selection section of the page frame with one or more page selectors, each page selector corresponding to a respective page in the list of pages of the page frame, selecting one of which causes a corresponding page displayed in the table data section, and

the table data section being configured to be responsive to user selection of a record displayed therein and upon the user selection of the record, constructing a new page frame as the child page frame of the table containing the user selected record,

the method comprising:

initializing a list of top level tables,

constructing a page frame for the list of top level tables as a topmost page frame for displaying a list of corresponding top level pages,

displaying the topmost page frame with its table data section filled with data of a top level active table retrieved from the database and its table selection section configured responsive to user selection of one of the top level pages to display the selected top level page in the topmost page frame, and

constructing a new page frame as a child page frame of the top level active table upon user selection of a record of the top level active table in the table data section of the topmost page frame.

2. The method of claim 1 , wherein the step of initializing a list of top level tables comprises:

selecting a plurality of initial tables of the relational database, analyzing and categorizing relationships of the plurality of initial tables according to a set of pre-defined relationship types, and

determining the list of top level tables from the plurality of initial tables according to the categorized relationships.

The method of claim 2, wherein the step of determining the list of top level tables from the plurality of initial tables comprises:

creating an initial list from the plurality of initial tables after removing therefrom any tables which a user does not have access permission,

creating a list of S1 and S2 relationship child tables for each table of the plurality of initial tables; and

compiling the list of top level tables by combining the initial list and the list of S1 and S2 relationship child tables.

creating an initial list from the plurality of initial tables after removing therefrom any tables to which a user cannot access,

creating a clone of every table of the plurality of initial tables that is referenced by other tables in the initial list and compiling a list of cloned tables, and compiling the list of top level tables by combining the initial list and the list cloned tables.

The method of claim 2, wherein the step of initializing the list of top level tables includes retrieving a list of all tables of the relational database, retrieving a list of tables without parent tables, retrieving a list of user selected tables, modifying lists of tables to obtain a consolidated list, and a combination thereof.

The method of claim 1 , wherein the table data section comprises an inline data portion and a drilldown data portion, the inline data portion being configured to be responsive to user selection of a record displayed in the inline data portion, each page frame being constructed by further performing the steps of:

compiling a list of supplemental tables based on relationships of tables in the database with the active table,

adding the list of supplemental tables to the active page for displaying in the drilldown data portion, and wherein the step of displaying the topmost page frame comprises displaying inline data of the retrieved data of the top level active table in the inline data portion, the method further comprising, upon user selection of a record displayed in the inline data portion:

if the list of supplemental tables includes one or more supplemental tables,

retrieving data of the one or more supplemental tables, and

displaying the data of the one or more supplemental tables in the drilldown data portion.

7. The method of claim 6, further comprising:

displaying drilldown data of the retrieved data of the top level active table in the drilldown data portion if the drilldown data exists.

8. The method of claim 6, wherein the step of compiling the list of supplemental tables comprises adding to the list at least one or more uncommitted ancestor tables, adding to the list one or more tables related to the top level active table according to a user specified set of relationship types, or a combination thereof. 9. The method of claim 8, wherein the step of compiling the list of supplemental tables further comprises:

creating a clone table for each table referenced by tables in the list of supplemental tables; and

adding all clone tables to the list of supplemental tables. 10. The method of claim 8, wherein the step of compiling the list of supplemental tables further comprises:

creating a further list of all uncommitted ancestor tables of each table already included in the list of supplemental tables,

adding to the further list all tables relating to any table in the further list according to the user specified set of relationship types; and

adding to the list of supplemental tables all tables in the further list.

1 1. The method of claim 8, wherein the step of compiling the list of supplemental tables further comprises:

creating a further list of uncommitted tables each of which relates to a table in the list of supplemental tables as a child in a parent-child relationship, adding to the further list all tables relating to any table in the further list according to the user specified set of relationship types; and

adding to the list of supplemental tables all tables in the further list.

12. The method of claim 1 , wherein the table data section comprises an inline data portion and a drilldown data portion, the inline data portion being configured to be responsive to user selection of a record displayed in the inline data portion, the method further comprising, upon user selection of a record in the inline data portion:

identifying drilldown data associated with the user selected record, and

displaying the associated drilldown data in a drilldown data portion.

13. The method of claim 12, further comprising:

providing a drilldown display selector, and

toggling the drilldown data portion to alternate between a display mode and a non- displayed mode upon user actuating the drilldown display selector.

14. The method of claim 13, further comprising:

providing a drilldown display pin tool, and

displaying the drilldown data portion to pin the record in the display mode upon user actuating the drilldown pin tool. 15. The method of claim 1 , wherein the step of constructing the child page frame further comprises:

compiling a list of child tables of the topmost active table,

constructing the new page frame for the list of child tables as a child page frame of the topmost active table,

retrieving data of an active child table identified from the list of child tables, and displaying data of the active child table in the table data section of the child page frame,

wherein the child page frame is configured to be iteratively responsive to further user selection of a record displayed in the table data section of any of the child page frame and its descendent page frames.

16. The method of claim 15, the method further comprising, upon user selection of a record in a table data section of any of the child page frame or descendent page frames,

removing any descendent page frames except for the immediate child page frame of the table containing the selected record,

compiling a list of immediate child tables if the table containing the selected record has no descendent page frame,

constructing the immediate child page frame for the list of immediate child tables if the immediate child page frame does not exist, and displaying the immediate child page frame if the list of immediate child tables contains at least one table.

17. The method of claim 16, wherein the step of compiling the list of child tables or the list of immediate child tables further comprises:

compiling a first list of all committed tables of all remaining displayed page frames; analyzing recursively each table of the first list to identify all tables that are descendants of any table in the first list according to a set of pre-specified relationships to generate a second list of tables; and

removing from the second list any table that is already included in the first list to obtain the list of child tables or the list of immediate child tables.

18. The method of any one of claims 1 to 17, further comprising:

providing a filter actuator in at least one of the page frames, the filter actuator being configured to correspond to a subset of tables of the list of tables for which the at least one page frame is constructed; and

upon user selection of the filter actuator, selectively excluding the subset of the tables from being displayed in the at least one page frame.

19. The method of claim 18, further comprising:

providing a filter selector to enable a user to modify the subset of excluded tables.

20. The method of any one of claims 1 to 19, further comprising:

dividing the page displayed in at least one of the page frames into a plurality of display pages and displaying only one display page of the plurality of display pages at a time in the table data section of the at least one page frame, providing a plurality of page selectors in the at least one page frame, each page selector corresponding to a respective display page of the plurality of display pages, and

upon user selection of a page selector, displaying the corresponding display page in the table data section of the at least one page frame.

21. The method of claim 20, wherein the one or more page selectors comprise at least a single page selector and at least a page range selector, further comprising

upon user selecting a single page selector, displaying a corresponding display page, and upon user selecting a page range selector, displaying multiple page selectors corresponding to display pages in a corresponding page range for the user to select a display page therefrom.

22. The method of claim 1 , further comprising:

upon user selection of any record displayed in the table data section, displaying the selected record in edit mode to enable editing thereof.

23. The method of claim 1 to 22, wherein the data table section of at least one page frame is configured for adding a new record to the active table, further comprising:

upon user addition of a new record to the active table, adding a corresponding new record to all child tables of one or more user-specified relationship types in the child page frames of the active table displayed in the at least one page frame.

24. The method of claim 22, further comprising:

creating a data object of all data from the selected record, corresponding records in all child tables of the selected record satisfying a set of pre-specified parent- child relationship types and corresponding records in all ancestor tables of the selected record,

checking the data object for error, and

providing an error message for any error found in the error checking. 25. The method of claim 24, further comprising:

receiving a confirmation input from a user prior to deleting, saving or changing records of the top level active table and any of its child or descendent tables.

26. The method of claim 1 , wherein the data table section further comprises a referenced field portion, the referenced field portion including a dialog selector, the method further comprising:

displaying a representation of one or more fields from a referenced table in the referenced field portion; and

upon user selection of the dialog selector, displaying a dialog window to display therein the referenced table.

27. The method of claim 26, further comprising:

selecting additional data tables according to relationships between the referenced table and data tables in the relational database; and displaying the additional data tables in the dialog window together with the referenced table.

28. The method of claim 27, further comprising:

upon user selection of a record of one of the additional data tables in the dialog window, iteratively displaying a new dialog window to display therein the one additional data table,

wherein the new dialog window includes more additional data tables selected according to relationships between the one additional data table and data tables in the relational database. 29. A non-transitory computer readable storage medium containing computer programming instructions stored thereon, when executed by a computer system, causing the computer system to execute steps of the method of any one of claims 1 to 28.

30. A computer implemented user interface for a relational database, the relational database containing data tables comprising records, the user interface comprising:

one or more page frames each being configured to display a plurality of pages of the each page frame, each page of the plurality of pages corresponding to a table in the relational database and having a plurality of participating tables related to the corresponding table according to relationships of tables in the relational database, each of the one or more page frames comprising:

a table selection section, the table selection section comprising a plurality of table selectors, each table selector corresponding to a respective page of the plurality of pages of the each page frame, selection of which designates the respective page as an active page and the corresponding table as an active table;

a table data section for providing access to and displaying records of the active table, the records being organized for displaying in one or more display pages;

selection of a record of the active table displayed in the table data section of a page frame causing construction and display of a new page frame, the new page frame being a child page frame of the active page for displaying one or more child tables identified from the tables of the relational database and the page frame being a parent page frame of the child page frame, the one or more child tables identified for inclusion in the child page frame being related to the active table of the parent page frame according to relationships specified in the relational database; and

a page selection section, the page selection section comprising one or more page selectors each corresponding to a respective display page of the one or more display pages, selecting a page selector of the one or more page selectors providing access to the corresponding respective display page.

31. The computer implemented user interface of claim 30, wherein the table data section includes an inline data portion for displaying inline data and a drilldown data portion for displaying drilldown data.

32. The computer implemented user interface of claim 30, wherein the plurality of participating tables of a page includes a primary table corresponding to the active table and a list of supplemental tables, each of the supplemental tables being related to the primary table according to a user specified subset of relationships of the relationships of tables in the relational database.

33. The computer implemented user interface of claim 32, wherein the table data section includes an inline data portion for displaying inline data of the primary table and a drilldown data portion for displaying optional drilldown data of the primary table and data of the supplemental tables. 34. The computer implemented user interface of claim 30, wherein the child page frame is displayed upon selection of a record from the active table in the parent page frame of the child page frame.

35. The computer implemented user interface of claim 30, wherein selection of record displayed in a child page frame iteratively causes display of one other new page frame, the one other new page frame being a child page frame at a lower level of the child page frame displaying the selected record, child tables included to participate in the one other new page frame of the lower level being related to the active tables of the child page frame displaying the selected record and all active tables of all parent page frames at higher levels according to relationships specified in the relational database. 36. The computer implemented user interface of claim 35, wherein at least one of the relationships is a parent-child relationship between a parent table and a child table and the child table in the parent-child relationship is included for participation in the child page frame at a level immediately below the level of the page frame in which the parent table in the parent-child relationship participates.

37. The computer implemented user interface of claim 36, wherein the one or more child tables included in the child page frame of a page frame are immediate child tables of the tables in the page frame, each of the included child table having at least one parent data table in its relation tree participating in at least one of the parent pages.

38. The computer implemented user interface according to any one of claim 30 to 37, wherein each child page frame includes at least one active child table accessible from the each child page frame. 39. The computer implemented user interface according of claim 30, further comprising a filter to selectively exclude a subset of the child tables from the child page frame.

40. The computer implemented user interface according of claim 39, further comprising a filter selector, actuating said filter selector allowing user access to and modification of the subset of excluded child tables. 41. The computer implemented user interface of claim 30, wherein tables corresponding to pages of a topmost page frame that is not a child page frame are selected from the group consisting of:

(a) all data tables of the relational database,

(b) all data tables of the relational database that have no parent tables,

(c) a user specified collection of data tables of the relational database, and

(d) a combination thereof.

42. The computer implemented user interface of claim 30, wherein the one or more page selectors comprise at least a single page selector and at least a page range selector, selecting a single page selector causing a corresponding display page to be displayed and selecting a page range selector causing multiple page selectors to be displayed for a user to select display pages in a corresponding page range.

43. The computer implemented user interface of claim 30, wherein selecting a page selector retrieves data to be displayed in the corresponding page and displays the retrieved data in a corresponding display page of the corresponding page. 44. The computer implemented user interface of claim 30, wherein selecting a page selector corresponding to the currently displayed display page causes retrieval of displayed data and refreshing of the currently displayed display page.

45. The computer implemented user interface of claim 31 or claim 33, wherein the inline data portion is partitioned into a plurality of rows for displaying inline data of records of the active table.

46. The computer implemented user interface of claim 45, wherein selection of any row of the plurality of rows enables editing of any data fields in the selected row.

47. The computer implemented user interface of claim 45, wherein the data table section is configured for adding a new row, addition of the new row also adding a new record to the active table and a corresponding new record to all child tables of one or more user- specified relationship types in the child page frames of the active table. 48. The computer implemented user interface of claim 45, further comprising a verification message window, said verification window being displayed and confirmation input required from a user prior to deleting, saving or changing records of the active table and any of its child tales in the child page frames.

The computer implemented user interface of claim 45, wherein the table data section initially displays the inline data portion, selecting a row in the displayed inline data portion causing the drilldown data portion to be displayed and any associated drilldown data to be displayed in the displayed drilldown data portion.

50. The computer implemented user interface of claim 30, further comprising a toolbar section, the toolbar section including tool selectors for selecting pre-defined tool functions.

51. The computer implemented user interface of claim 50, wherein the tool selectors includes a drilldown display selector, actuating the drilldown display selector causes the drilldown data section to alternate between a display mode and a non-displayed mode.

52. The computer implemented user interface of claim 50, wherein the pre-defined tool functions includes at least one of creating new records, copying existing records, editing selected records, saving changes, cancelling changes and deleting selected records.

53. The computer implemented user interface of claim 30, wherein one of a pre-selected key actuation, actuation of an edit selector defined in the toolbar section and a combination thereof causes the table data section to switch between an edit mode and a display mode.

54. The computer implemented user interface of claim 30, the data table section further comprising a reference field portion, the reference field portion including at least a reference field for displaying a representation of one or more fields from a foreign table, the reference field portion including tool selectors for selecting pre-defined tool functions. 55. The computer implemented user interface of claim 54, wherein the reference field portion is placed within the inline data portion.

56. The computer implemented user interface of claim 54, wherein the reference field section is placed within the drilldown data portion.

57. The computer implemented user interface of any one of claims 54 to 56, wherein the tool selectors include a dialog selector, selection of the dialog selector causing a dialog window to appear to display therein the foreign table.

58. The computer implemented user interface of claim 57, wherein the dialog selector is an edit selector.

59. The computer implemented user interface of claim 57, the dialog window includes additional tables selected according to relationships between the foreign table and other tables in the relational database.

60. The computer implemented user interface of claim 59, wherein selection of a record of one of the additional table in the dialog window iteratively causes a new dialog window to appear to display therein the one additional data table. 61. The computer implemented user interface of claim 60, wherein the new dialog window includes more additional data tables selected according to relationships between the one additional data table and data tables in the relational database.