Method and apparatus for database filter generation by display selection

• US 5,787,411 A
• Filed: 03/20/1996
• Issued: 07/28/1998
• Est. Priority Date: 03/20/1996
• Status: Expired due to Term

• Current Assignee: Microsoft Technology Licensing LLC (Microsoft Corporation)
• Original Assignee: Microsoft Corporation
• Inventors: McKee, Timothy P., Groff, Shane L.
• Primary Examiner(s): AMSBURY, WAYNE P
• Application Number:
  US08/619,018
• Time in Patent Office: 860 Days
• Field of Search: 365/60, 365/61, 365/62, 707/2, 707/4
• US Class Current: 1/1
• CPC Class Codes: G06F 16/2428 Query predicate definition using graphical user interfaces, including menus and forms G06F16/2423 takes precedence
• CPC Class Codes: Y10S 707/99932 Access augmentation or optimizing
• CPC Class Codes: Y10S 707/99934 Query formulation, input preparation, or translation

Abstract

A method for selecting records from a displayed database table by generating an SQL SELECT command for filtering the displayed records in accordance with cell values highlighted by user input. A presently selected set of records from a desired table (generally referred to as a record set or record source) are displayed on the user'"'"'s display screen. The user selects particular values in cells (an intersection of a row and a column of the displayed table) by highlighting the values using the pointer device or keyboard of the computer system. Methods of the present invention then generate an SQL select (filter) command to selectively retrieve those records from the displayed records which match the user'"'"'s highlighted values. A fully highlighted cell indicates exact equality is desired by the user, a beginning portion highlighted indicates that the user wishes to match records whose corresponding column starts with the highlighted value, and ending portion selection matches the ending portion of qualified records, and a middle portion highlighted matches any record containing the highlighted value. Values highlighted in the same row generate logically AND'"'"'d clauses in the SELECT command while the comparison generated for a row are logically OR'"'"'d with the comparisons generated for other rows. The user may indicate that the highlighted values are for selection (inclusion of qualified records) or for exclusion selection (exclusion of qualified records). A new select (filter) command may be logically AND'"'"'d with the prior filter to permit complex selection criteria to be defined by simple graphical user inputs.

Claims

1. A computer operable method for generating a database filter comprising:

selecting data on a computer display screen by highlighting a portion of at least one value on said computer display screen that is indicative of a desired filter in response to user supplied input, wherein said data represents a plurality of data values stored in a database table; and

generating commands to select database records in accordance with said desired filter.

2. The method of claim 1:

wherein the highlighted portion comprises the entirety of said value on said computer display screen; and

wherein the step of generating commands to select includes the step of generating commands to select database records which have a corresponding column value equal to the highlighted portion.

3. The method of claim 1:

wherein the highlighted portion comprises the entirety of said value on said computer display screen; and

wherein the step of generating commands to select includes the step of generating commands to select database records which do not have a corresponding column value equal to the highlighted portion.

4. The method of claim 1:

wherein the highlighted portion includes a starting portion of said value and excludes an ending portion of said value on said computer display screen; and

wherein the step of generating commands to select includes the step of generating commands to select database records which have a corresponding column value with a starting portion equal to the highlighted portion.

5. The method of claim 1:

wherein the highlighted portion includes a starting portion of said value and excludes an ending portion of said value on said computer display screen; and

wherein the step of generating commands to select includes the step of generating commands to select database records which do not have a corresponding column value with a starting portion equal to the highlighted portion.

6. The method of claim 1:

wherein the highlighted portion includes an ending portion of said value and excludes a starting portion of said value on said computer display screen; and

wherein the step of generating commands to select includes the step of generating commands to select database records which have a corresponding column value with an ending portion equal to the highlighted portion.

7. The method of claim 1:

wherein the highlighted portion includes an ending portion of said value and excludes a starting portion of said value on said computer display screen; and

wherein the step of generating commands to select includes the step of generating commands to select database records which do not have a corresponding column value with an ending portion equal to the highlighted portion.

8. The method of claim 1:

wherein the highlighted portion excludes an ending portion of said value and excludes a starting portion of said value on said computer display screen; and

wherein the step of generating commands to select includes the step of generating commands to select database records which have a corresponding column value with any portion equal to the highlighted portion.

9. The method of claim 1:

wherein the highlighted portion excludes an ending portion of said value and excludes a starting portion of said value on said computer display screen; and

wherein the step of generating commands to select includes the step of generating commands to select database records which do not have a corresponding column value with any portion equal to the highlighted portion.

10. The method of claim 1:

wherein the highlighted portion is displayed in a first format different than a second format used to store said data values in said database tables; and

wherein the step of generating commands to select includes the step of generating commands to translate the highlighted portion from said first format to said second format.

11. The method of claim 1:

wherein the highlighted portion is displayed in a first format different than a second format used to store said data values in said database tables; and

wherein the step of generating commands to select includes the step of generating commands to translate the a corresponding column value from said second format to said first format.

12. The method of claim 1:

wherein data not selected by the selecting step represents data stored in a first database table;

wherein data selected by the selecting step represents values stored in a related second database table; and

wherein the step of generating commands to select includes the step of generating commands to join said first database table and said second database table.

13. The method of claim 1:

wherein the step of generating commands to select includes the step of generating commands to logically combine a previously generated command to select records with newly generated commands to select records.

14. A computer operable method for generating a database filter comprising the steps of:

displaying presently selected database records of a database on a computer display screen having a plurality of rows and a plurality of columns, wherein the intersection of each of said plurality of rows with each of said plurality of columns is a cell;

graphically selecting, in response to user supplied input, data displayed in at least one cell by highlighting a portion of a value in said at least one cell wherein said value is indicative of records to be selected by a desired filter;

generating SQL commands to select newly selected database records from said presently selected database records in accordance with said desired filter; and

displaying said newly selected database records on said computer display screen.

15. The method of claim 14:

wherein the selecting step highlights portions in each of a plurality of cells within one of said plurality of rows; and

wherein the step of generating SQL commands to select includes the step of generating commands to select said newly selected database records wherein each of said newly selected database records has a corresponding column value which matches each of the plurality of highlighted portions.

16. The method of claim 14:

wherein the selecting step highlights portions in each of a plurality of cells within one of said plurality of columns; and

wherein the step of generating SQL commands to select includes the step of generating commands to select said newly selected database records wherein each of said newly selected database records has a corresponding column value which matches at least one of the plurality of highlighted portions.

17. A program storage device readable by a computer, tangibly embodying a program or instructions executable by the computer to perform the method steps for generating a database filter, said method steps comprising:

selecting data, that represent data values stored in a database table on a computer display screen indicative of a desired filter in response to user supplied input by highlighting a portion of at least one of said data values on said computer display screen; and

generating commands to select database records in accordance with said desired filter.

18. The program storage device of claim 17:

wherein the highlighted portion comprises the entirety of said value on said computer display screen; and

wherein the step of generating commands to select includes the step of generating commands to select database records which do not have a corresponding column value equal to the highlighted portion.

19. The program storage device of claim 17:

wherein the highlighted portion comprises the entirety of said value on said computer display screen; and

wherein the step of generating commands to select includes the step of generating commands to select database records which have a corresponding column value equal to the highlighted portion.

20. The program storage device of claim 19:

wherein the highlighted portion includes a starting portion of said value and excludes an ending portion of said value on said computer display screen; and

wherein the step of generating commands to select includes the step of generating commands to select database records which have a corresponding column value with a starting portion equal to the highlighted portion.

21. The program storage device of claim 19:

wherein the highlighted portion includes a starting portion of said value and excludes an ending portion of said value on said computer display screen; and

wherein the step of generating commands to select includes the step of generating commands to select database records which do not have a corresponding column value with a starting portion equal to the highlighted portion.

22. The program storage device of claim 19:

wherein the highlighted portion includes an ending portion of said value and excludes a starting portion of said value on said computer display screen; and

wherein the step of generating commands to select includes the step of generating commands to select database records which have a corresponding column value with an ending portion equal to the highlighted portion.

23. The program storage device of claim 19:

wherein the highlighted portion includes an ending portion of said value and excludes a starting portion of said value on said computer display screen; and

wherein the step of generating commands to select includes the step of generating commands to select database records which do not have a corresponding column value with an ending portion equal to the highlighted portion.

24. The program storage device of claim 19:

wherein the highlighted portion excludes an ending portion of said value and excludes a starting portion of said value on said computer display screen; and

wherein the step of generating commands to select includes the step of generating commands to select database records which have a corresponding column value with any portion equal to the highlighted portion.

25. The program storage device of claim 19:

wherein the highlighted portion excludes an ending portion of said value and excludes a starting portion of said value on said computer display screen; and

wherein the step of generating commands to select includes the step of generating commands to select database records which do not have a corresponding column value with any portion equal to the highlighted portion.

26. The program storage device of claim 19:

wherein the highlighted portion is displayed in a first format different than a second format used to store said data values in said database tables; and

wherein the step of generating commands to select includes the step of generating commands to translate the highlighted portion from said first format to said second format.

27. The program storage device of claim 19:

wherein the highlighted portion is displayed in a first format different than a second format used to store said data values in said database tables; and

wherein the step of generating commands to select includes the step of generating commands to translate the a corresponding column value from said second format to said first format.

28. The program storage device of claim 19:

wherein data not selected by the selecting step represents data stored in a first database table;

wherein data selected by the selecting step represents values stored in a related second database table; and

wherein the step of generating commands to select includes the step of generating commands to join said first database table and said second database table.

29. The program storage device of claim 17:

wherein the step of generating commands to select includes the step of generating commands to logically combine a previously generated command to select records with newly generated commands to select records.

30. A program storage device readable by a computer, tangibly embodying a program or instructions executable by the computer to perform the method steps for generating a database filter, said method steps comprising:

displaying presently selected database records of a database on a computer display screen having a plurality of rows and a plurality of columns, wherein the intersection of each of said plurality of rows with each of said plurality of columns is a cell;

graphically selecting, in response to user supplied input, data displayed in at least one cell by highlighting a portion of a value in said at least one cell wherein said value is indicative of records to be selected by a desired filter;

generating SQL commands to select newly selected database records from said presently selected database records in accordance with said desired filter; and

displaying said newly selected database records on said computer display screen.

31. The program storage device of claim 30:

wherein the selecting step highlights portions in each of a plurality of cells within one of said plurality of rows; and

wherein the step of generating SQL commands to select includes the step of generating commands to select said newly selected database records wherein each of said newly selected database records has a corresponding column value which matches each of the plurality of highlighted portions.

32. The program storage device of claim 30:

wherein the selecting step highlights portions in each of a plurality of cells within one of said plurality of columns; and

wherein the step of generating SQL commands to select includes the step of generating commands to select said newly selected database records wherein each of said newly selected database records has a corresponding column value which matches at least one of the plurality of highlighted portions.