"David W. Fenton" <dXXXfenton@bway.net.invalid> wrote in message
news:Xns96E3AFBFCABCEdfentonbwaynetinvali@216.196. 97.142...[color=blue]
> Steve Jorgensen <nospam@nospam.nospam> wrote in
> news:rjkuj1d3q5duo8f97523rcjr11opq93gfk@4ax.com:
>[color=green]
>> 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.[/color]
>
> 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[/color]
Can you save the design of these searches, like you can with saved queries?
