Steve Jorgensen <no****@nospam.nospam> wrote in
news:rj********************************@4ax.com:
Mailing List management is a good example of a case where my
conundrum arises. Say there is a m-m relationship between parties
and groups - anyone can be a member of any combintation of groups.
Now, let's say the user wants to be able to send mailings to
people who have various combinations of membership and
non-membership in those groups.
Here's a medium-complex example: (Knitting Group or Macrame
Group) and Active Contact and Mailing List Option
The first issue is that users really do have criteria this complex
or much more so, and the criteria are simple and make complete
sense to them in their own minds, but they haven't been trained to
think or express themselves in terms of boolean logic expressions,
nested parentheses, etc. They'll want a way to express their
intentions in the program in a way that seems like defining a
mailing list, not like writing code.
The second problem is that, ideally, whatever solution we come up
with to the problem, we hope it'll really simple to write and
maintain, not some monstrosity that takes more effort that can
justify the return.
Has anyone come up with models or metaphors that deal well with
cases like these.
First off, it is my belief that your QBF UI should completely hide
the complexity of the underlying schema from the user.
However that does *not* mean that the UI will therefore be simple,
only that dependencies and hierarchies are irrelevant to the user.
Here's a very simple QBF (not actually complete -- waiting for Phase
II to be approved):
http://www.dfenton.com/DFA/examples/QBF/WTS.gif
If you look at the scheam (which includes lots of legacy fields):
http://www.dfenton.com/DFA/examples/QBF/WTSSchema.gif
you'll see that there are three basic entities, people,
contributions and outreach (which is, more-or-less, events). There's
some kludging in the structure in terms of normalization, but let's
ignore that.
The point is that the UI completely hides anything about the
struture from the end users. Each criterion has an implicit AND
operator, while picking criteria inserts OR between multiple
criteria.
It's not clear from this partially example where it's not
implemented yet, but I implement OR conditions by running multiple
queries. That's because I always write results to a temp table, and
the results are data about a person, since the whole point of
querying is to get a list of people who match the requested
criteria.
So, if you want OR criteria *between* fields, you simply run
multiple queries, which append the new matches to the existing
records. Thus, end users get a mental model for querying data that
makes what we see as separate rows in the Access QBE grid as
multiple successive queries, appended to the same resultset.
That's really what OR at that level is -- the ability to run two
queries at once, since it's actual two different sets of data
returned at the same time.
A much more complex implementation of the same approach, but
basically the same schema, is in this example:
http://www.dfenton.com/DFA/examples/QBF/CFM.gif
This has a lot more capabilities built into it, though it doesn't
actually have the capability to pull multiple queries into the same
resultset. There are actually UI complexities with that approach
that I've run onto in other older apps (I'm not including screen
shots for that one because the UI is very badly designed as well as
being pop-up based, and thus, very hard to illustrate), but the end
result is a dialog something like this:
http://www.dfenton.com/DFA/examples/QBF/WBMerge.gif
Now, obviously, there's a lot more going on in that dialog box than
just deciding between Append and Replace, but the key point is this:
The end users have *never* understood the difference between the
results of the two command buttons, except when I repeatedly explain
it to them, and it's not just because there are actually two
separate temp tables involved.
So, I've never actually fully implemented that functionality in any
of the more "modern" QBF interfaces I've created. And it hasn't
really been an issue, since none of the clients have ever presented
me with queries that required the OR'd resultset.
Getting back to this one:
http://www.dfenton.com/DFA/examples/QBF/CFM.gif
Note that there are a number of areas on the form. The leftmost 2/3s
of the form is devoted to selection criteria. The schema is here:
http://www.dfenton.com/DFA/examples/QBF/CFMSchema.gif
Again, the UI groups criteria according to entity that the criteria
apply to, but it doesn't do anything to expose the underlying
complexity of the data storage structure. From the end user's point
of view, they are querying a denormalized spreadsheet, and that's
who the results are presented (a list of matching people).
On the rightmost 1/3, there are two columns. The first controls
export options, what format to export to, and whether or not to
include barcodes (and which barcode data to use). This QBF is used
to produce data dumps to be sent to mailing houses that then do
largescale mailings, some of which will have barcodes on them to
allow quick lookup when the responses come back to the client.
The 2nd column in this area has limitations and exclusions for the
data. There's a set of default exclusions, and the top-level
checkboxes can be used to turn on/off the whole set of default
limitx/exclusions. These are mostly concerned with a number of
criteria in the donor record, most of which are recorded there in
Booleans fields. The AfterUpdate events of these checkboxes are very
complex, as some of the checks are mutually contradictory, or should
be checked in groups. For instance, if the EXPORT AS choice (see
http://www.dfenton.com/DFA/examples/QBF/CFMExport2.gif ) is E-MAIL,
the checkoxes for email are checked while the ones for snail mail
become *unchecked*. These defaults can be changed, but the idea is
to have common-sense defaults that result in resultsets that match
what's actually needed.
The organization to whom this app belongs does literally hundreds of
thousands of direct mail pieces per year and raises literally
millions of dollars every year, so this form gets a *lot* of use.
They currently have 350-400K donors in their database and queries
against that database (still stored in an MDB file) are amazingly
fast (with a few exceptions -- the searches on the most common
criteria are all fast).
A more complex QBF is this one:
http://www.dfenton.com/DFA/examples/QBF/SA.gif
This drives the entire application, and is used to pull up all
records for people -- it is the prime UI for the application.
And it is extremely complicated and butt-ugly. The schema is here:
http://www.dfenton.com/DFA/examples/QBF/SASchema.gif
It would take pages and pages to explain that schema, but it's
basically an executive recruiting application, so the entities are
people, companies and job searches. Some of the people are
candidates for jobs, some of the people are employees of the
companies conducting job searches. Some of the companies are
employers of candidates and some companies are clients of the
executive recruting firm. Much of that is not obvious from the
schema, because the end-user definitions of the entity are
determined by type fields in the records of the underlying schema.
The QBF UI is a completely unorganized mishmash of ad hoc
functionality, with each section devoted to defining criteria for a
different entity, which is indicated onscreen with rectangles around
a finite set of criteria. For instance, the upper right has all the
employment related information, and basically queries the
EmploymentHistory table (along with the Company table). I can't
figure out why Employment Notes is set off from that group, as it's
stored in the Employment History table. Perhaps I've forgotten the
details of the users' mental model here, and it really does make
sense for them.
Beneath that is the area for Education History criteria. The area
beneath that is rather complicated and I won't discuss it.
In the second column, the top group presents geographical criteria,
which are drawn both from the Companies attached to the Employment
History records and from the address in the main Person record.
That's a case where the data structure could have been improved by
having an Address table, though it would have been inefficient,
nonetheless, because it would have required two different join paths
to get to the person (one thorugh the company->employment->person,
and the other direct to the person). Also, much of this was
implemented as transition from the legacy structure in place from
the old database, and the final restructuing never took place (this
was actually a failed project, with a micromanaging boss who was
impossible to work for and impossible to satisfy; I eventually
walked away from the project after it functioned well enough for the
client to do the work they'd been able to do before I started the
conversion).
Then, below the geographic criteria is one of the unique features of
this applicatoin, which is the section of "minor search fields."
These are fields that don't need to be searched often, but
occasionaly to, and this allows that to be done.
Beneath that is the criterion for limited to a particular job
search, and then below that more of the part I'm not going to
discuss ;) because it's too complicated to explain.
Again, the QBE is implemented to reflect a flat mental model of the
data, and all the work of converting that into criteria is done
behind the scenes.
The way I accomplish that is with a class module for querying which
encapsulates all the functionality needed by the QBF UI. That class
module basically has internal members for all the possible criteria,
and then a subroutine to produce the FROM and WHERE clauses
necessary to get the data. This includes an internal array that
stores the criteria so I can loop through that array to write the
WHERE clause.
However, because of the schema, there are lots of exceptions to
that, so not all the criteria are stored in that array.
So, it's basically pretty ugly.
But it does make implementing a QBF interface very fast. I've
considered packaging up a demo, but have never quite gotten around
to it, as generalizing it would be a lot of work.
I hope some of those examples help you with your thinking on this,
Steve. It's an extremely complex set of issues, and I've never been
entirely happy with any solution I've arrived at.
--
David W. Fenton
http://www.bway.net/~dfenton
dfenton at bway dot net
http://www.bway.net/~dfassoc 
