SQL queries

[Bryan - Gendev]

I have asked a number of questions about a new membership database I have been building.

I have just received a request for further features.. as you do!


The user will want to be able to send emails to selected groups of members from within the application.

My first idea was to create SQL queries to get the members the user wishes to set SELECT = .t. for - at the moment 6 are specified.

Whilst I can write the queries into say a table and run them as required it is likely he will think of other queries he would like to make .. as they do.

Is there a 'universal' query form that the user can select fields from the members table and construct a query.

I think this is called QBE ?

Thanks

GenDev

 

[Olaf Doschke]

QBE - Query by example, you have to create something like that, but it's not important:

AFIELDS() gives you a list of fields, let the user choose from that. You might want to add meta data for user friendly names instead of the technical field names, depends on your naming conventions. It would also be ok to have user friendly field names in the first place.

Once you have the field list you can put together a filter/where clause. Assumed in the end you have a comma delimited list in a variable lcFieldlist and a where clause in lcWhere finally use macro substitution:

SELECT &lcFieldlist FROM members WHERE &lcWhere

You might also want to add joins in other situations and in the end could also end up with the total SQL put into a variable lcSQL you execute via &lcSQL.

In the latter case, debugging get's harder, of course. The less concrete the code you compile, the more runtime problems you might face, if putting things together dynamically. Also watch out for sql injection.

Bye, Olaf.

 

[Olaf Doschke]

it's not important

I wanted to write: It's not impossible.

 

[Mike Lewis]

GenDev,

If it's just a question of letting the user pick the fields, that's fairly easy.

As Olaf says, you can use AFIELDS() to get the field names. You could then put the field names in a listbox, and let the user multi-select them. Or put them in a grid, with a checkbox to indicate which ones the user wants to select. Or you might use a listbox with RowSourceType of 8.

If the table is part of a database (DBC), you can use the field's Caption to store a user-friendly name for the field, then use DBGETPROP() to retrieve it.

As I say, that part is fairly easy. What's a lot more difficult is to let the user specify the WHERE clause. In effect, you will have to create builder, where the user can pick fields, and also operators (such as "=" and "<"), and then type in constants, etc. In other words, you must give the user a way of building expressions, and then you must validate the expression in some way.

The difficulty here is not so much the programming, as doing it in a way that the user will understand. This is especially difficult when it comes to logical operators like AND and OR, which some users find very difficult to get their heads round.

What I'm saying is this: Think carefully before you embark on this idea. I can see the value of it, but it is notoriously difficult to do this sort of thing in a way that users will find intuitive.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Olaf Doschke]

In regard of the intuitiveness our company uses a simplification, which makes it less dynamic but still dynamic enough.

We offer a textbox for every field and in the simplest case we create a where condition "field=userinput" for each field and concatenate that with AND, no choice for that to the user, but that's more generally, what they need.

To offer OR within the same field, the user might type in values with comma, eg "smith,miller", which then is processed to field='smith' OR field='miller' by a where clause generator.

Also you can enter <, <=, >, >= into the text box, which then is used instead of the default = or use joker * or ?, at which time we create a LIKE operation (converting to the less known % or _ joker characters for SQL LIKE).

Users knowledgable enough to press F1 can read about this, others might just use the minimum

Other dynmic cases are individually done, eg choice of values via combobox or a multiple listbox, which selections are then put into a cursor to INNER JOIN with etc. etc.

This way we don't have the full blown perfect expression generator, but an interface the beginner can easily use and the professional user can filter more precise. In the end you can do a less stringer filter faster and have a too large result you can pick from afterwards, which is much faster done than perfecting the filter.

Eg we often do a two staged filtering with a filter as said above, listing results for selection, offering selection through click or move into a secondary list of choices and then finally processing, eg opening detail forms for all the choices, printing reports for them, etc.

Bye, Olaf.

 

[Olaf Doschke]

less stringer filter -> less stringent filter

Bye, Olaf.

 

[Dan Freeman]

I wouldn't roll my own for something like this, unless you're certain the requirements will always be limited.

Someone has already done the work:

http://www.stonefieldquery.com

 

[JRB-Bldr]

If it's just a question of letting the user pick the fields, that's fairly easy.

It is easy, IF the users actually know the field names (especially with their 10 character naming convention) that they need to include for the results that they want.
They also need to know those fields represent, and what table(s) the various fields can be found in.

Then, IF they do know the previous IF's - if the fields are in different tables they have to also know how one table can be 'connected' to another via a SQL Query.

Then IF they have a thorough understanding of how Boolean logic works to develop the SQL Query selection criteria - most especially if the query selection criteria is complex.

Those are a lot of IF's for the typical user to understand.

If you have one or more VERY knowledgeable users, then fine.

But if not, they will not be able to successfully create their own SQL Queries using the Query Wizard.

So either plan on ensuring that your users have (or will be getting) a LOT of application data architecture and Boolean logic training or you will end up creating the SQL Queries for them using your own thorough understanding of the data architecture.

Good Luck,
JRB-Bldr

 

[Bryan - Gendev]

Firstly - thank you all for your comments.

To answer some of the points mentioned above.

There will only be ONE user of the system at a time - the current Membership Secretary.

There is only one table - members - that will need to be queried.

The queries will be very simple - just to put a tick in a record's select box to enable the email output system to send just those selected.

Thanks
GenDev

 

[Mike Lewis]

The queries will be very simple - just to put a tick in a record's select box to enable the email output system to send just those selected.

Then I would suggest you don't need a query builder. Just capture the recipients' names, and add them to the "To" list (or Recipients collection, or whatever) when creating the email.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Olaf Doschke]

stonefield query is a nice tool on it's own. Here of course, as we talk of one table, it would be overdone. Also in my cases of filter forms. I don't offer the user to put together any result querying any table.

In most case one of the predefiend fields of my filter forms is used for one main criteria and the option to use operators or comma dellimited list isn't needed. Data is organised in projects, that's a typical main choice giving the list of records of the project and then let the user choose.

In this case, gendev. Why a query at all? How many members do you have? Two lists and the option to move a member from one to the other list via (double) click may be enough. It will make the selection of members a seperate list you can much easier view than scattered checked records in just one list. VFP wizards also often offer this kind of interface done by two listboxes or grids, called mover.

Bye, Olaf.

Bye, Olaf.