I'm about to post a demo of my end user filter / query generator to the binaries. If you unzip it and dump it in a directory somewhere it should run (well I tested it on two different PCs here - Vista & 7 and it worked).

Its written using my own components. Quick run through:

Page control - 2 pages first for filters second for queries.

Filters:

Top of page an nlhDBGrid showing the companies table, bottom has the query generator and a button to toggle the filter.

The filter generator starts showing one row. Click into Field and you get a list of the fields available, Test is (surprisingly) a list of tests and Criteria is the data that will be used in the filter. ( ) are just what they say, so is Not, Link is AND/OR/nothing. A=a if ticked means case insensitive.

The Criteria column is field datatype and test sensitive. you will either be able to type something in (eg for the Name column) or pick from a lookup table (eg OrgType) or pick a date (eg Created).

Since this is just a cut down version of the real thing I think, but I'm not positive I've included all the necessary tables for lookups.

Queries is very similar (same component but a different switch) but there are three more grids. If you don't put anything in the Select List then it selects all fields.

The grid inplace editor has been a nightmare. I think (as I have several times) that its now working.

I'd appreciate some of you having a play and giving feedback.

Whilst I remember: its written in D2006, there is no attempt at internationalisation, its non-unicode and languages go left to right only

Roy Lambert

Roy,

I briefly had a look.

First Tab:
When I chose "Name" I could test for "contains".
When I chose "Website" I could not test for "contains"

2nd Tab:
I could not figure out how to generate an output set. No matter what I did it never generated any display?
I was looking a "button" similar to the first tab but didn't see one.

Michael Riley
GySgt USMC (Ret)
www.zilchworks.com

Michael


Thank you for being the first to have a look.

>I briefly had a look.
>
>First Tab:
>When I chose "Name" I could test for "contains".
>When I chose "Website" I could not test for "contains"

Basically programmer's (ie me) choice. The available tests for any given column can be specified by the developer. So I'm the one who decided that it was sensible to search for words (which is what contains does" in a company name but not in a website. The full list of potential tests is set up in the units initialisation:

initialization

{
G = general
R = range (including IN)
B = boolean
D = date
N = numeric
A = alpha
M = memo (special case of Alpha and not as many tests allowed)
}

euqgTests := TStringList.Create;
euqgTestNames := TStringList.Create;
euqgTestClass := TStringList.Create;
euqgEdits := TStringList.Create;
euqgExtras := TStringList.Create;

{Group, user display, SQL test, Extras:multi select Y/N,TextSearch Y/N}

PrepTest('G', 'is empty', 'IS NULL', 'N');
PrepTest('G', 'is not empty', 'IS NOT NULL', 'N');
PrepTest('G', 'is exactly', '=', 'Y');
PrepTest('G', 'is not', '<>', 'Y');

PrepTest('R', 'is in list', 'IN', 'Y');
PrepTest('R', 'is not in list', 'NOT IN', 'Y');
PrepTest('R', 'is between', 'BETWEEN', 'N');
PrepTest('R', 'is not between', 'NOT BETWEEN', 'N');

PrepTest('B', 'is True', '= True', 'N');
PrepTest('B', 'is False', '= False', 'N');
PrepTest('B', 'is not True', '<> True', 'N');
PrepTest('B', 'is not False', '<> False', 'N');

PrepTest('D', 'is after', '>', 'N');
PrepTest('D', 'is on or after', '>=', 'N');
PrepTest('D', 'is before', '<', 'N');
PrepTest('D', 'is on or before', '<=', 'N');

PrepTest('N', 'greater than', '>', 'N');
PrepTest('N', 'greater than or equals', '>=', 'N');
PrepTest('N', 'less than', '<', 'N');
PrepTest('N', 'less than or equal to', '<=', 'N');

PrepTest('A', 'starts with', 'LIKE' + likeSep + '?%', 'N');
PrepTest('A', 'does not start with', 'NOT LIKE' + likeSep + '?%', 'N');
PrepTest('A', 'ends with', 'LIKE' + likeSep + '%?', 'N');
PrepTest('A', 'not ending with', 'NOT LIKE' + likeSep + '%?', 'N');

PrepTest('M', 'contains the string', 'LIKE' + likeSep + '%?%', 'N');
PrepTest('M', 'does not contain the string', 'NOT LIKE' + likeSep + '%?%', 'N');
PrepTest('M', 'has all', 'CONTAINS', 'N');
PrepTest('M', 'doesn''t have all', 'DOES NOT CONTAIN', 'N');
PrepTest('M', 'has some', 'CONTAINS ANY', 'N');
PrepTest('M', 'has none', 'DOES NOT CONTAIN ANY', 'N');
PrepTest('M', 'contains the phrase', 'LIKE' + likeSep + '%?%', 'N');
PrepTest('M', 'does not contain the phrase', 'NOT LIKE' + likeSep + '%?%', 'N');

Basically when the developer sets up the definitions to be used for the query generator in this instance (I store mine in a table and load in depending on what's being queried) he / she can decide just which of these tests can be applied. The groups are the defaults that are automatically selected and then tests can be added / deleted from the available list for the column.


>2nd Tab:
>I could not figure out how to generate an output set. No matter what I did it never generated any display?
>I was looking a "button" similar to the first tab but didn't see one.

It is there. Basically I didn't want to steal screen real estate from the query generator component by putting a button at the side so I put a full width button with "Create query" on it above the query generator.

Whilst it does look as though there is a blank space between the bottom grids this is all part of the query generator component and dropping a button on there may / may not work all the time

Roy Lambert wrote:

>2nd Tab:
>I could not figure out how to generate an output set. No matter what I did it never generated any display?
>I was looking a "button" similar to the first tab but didn't see one.

>>It is there. Basically I didn't want to steal screen real estate from the query generator component by putting a button at the side so I put a full width button with "Create query" on it above the query generator.

Whilst it does look as though there is a blank space between the bottom grids this is all part of the query generator component and dropping a button on there may / may not work all the time <

I didn't see a button. I'll send you a screen shot when I get home.

Michael Riley
GySgt USMC (Ret)
www.zilchworks.com

Roy Lambert wrote:

> Whilst it does look as though there is a blank space between the
> bottom grids this is all part of the query generator component and
> dropping a button on there may / may not work all the time

OK found the button on Tab 2. I thought it was a "Title Panel" and not
a button.

--
Michael Riley
GySgt USMC (Ret)
www.zilchworks.com

Michael


>> Whilst it does look as though there is a blank space between the
>> bottom grids this is all part of the query generator component and
>> dropping a button on there may / may not work all the time
>
>OK found the button on Tab 2. I thought it was a "Title Panel" and not
>a button.

Woops - never thought of that interpretation

Roy

I've had a quick look at this as well Roy

It works and offers pretty clear alternatives to dense SQL for non-coders.

The GUI should be more interactive though. i.e. There is a "Filter" button. When this is clicked it should change (i.e. turn red, caption change to "filter now active" or something. These types of user-cue are critical to getting a GUI understood.

Also, I always try to lay out the elements the user should use in a linear order in the GUI. i.e. if the user is expected to fill out Grid A then add something to field B then click Button C I try to put these elements in that order in a panel, and give some indication that this panel requires "Users you do stuff here"

i.e. it is nice to have a clear GUI separation between the "you input here" and "results show here". In the case of your app this would be helped (for me) by a greater difference in the presentation of the user-data-entry grid and the results grid.

Maybe the GUI is not the main thing you wanted comments on ... the other aspects of operation seem pretty problem free though, well done.

Adam

>It works and offers pretty clear alternatives to dense SQL for non-coders.

That is its only function in life. Most of the generators I looked at before first starting on this (iteration 1 probably about 12 years ago) were basically was to save you having to type and ignored the fact that most users could just about understand compare a with b and show if the same but freaked out with JOIN. Essentially I wanted a simple spreadsheet like approach that they's be happy with.

>The GUI should be more interactive though. i.e. There is a "Filter" button. When this is clicked it should change (i.e. turn red, caption change to "filter now active" or something. These types of user-cue are critical to getting a GUI understood.

I gave NO thought to the layout and interaction of the demo. With yourself and Michael commenting I'll adjust and repost.

>Also, I always try to lay out the elements the user should use in a linear order in the GUI. i.e. if the user is expected to fill out Grid A then add something to field B then click Button C I try to put these elements in that order in a panel, and give some indication that this panel requires "Users you do stuff here"
>
>i.e. it is nice to have a clear GUI separation between the "you input here" and "results show here". In the case of your app this would be helped (for me) by a greater difference in the presentation of the user-data-entry grid and the results grid.

In the live app, for the filter generator, its a pop-up form with query generator and two buttons. Query generator is at the top. The buttons at the bottom. Left aligned button is "Set Filter", right aligned one is "No Filter". When either is pressed the form disappears and the filter is applied or not. Is that the sort of thing?

If you have any suggestions for the query generator UI itself I'd be very interested.

>Maybe the GUI is not the main thing you wanted comments on ... the other aspects of operation seem pretty problem free though, well done.

Well, on the basis, the demo app took about an hour to create and much of that was down to extracting the data from current tables. The query generator, dbgrid, buttons page control,, drop down menu replacement and ESPECIALLY the inplace editing for the dbgrid probably adds up to around half a man year, I'm more interested in comments on the actual components

Roy

Adam / Michael / Anyone else who want's to comment


Here you go a nice new demo


Roy Lambert

Adam


As a ps it was worth doing a better demo because I found a couple more bugs.

Roy Lambert