Forum Moderators: open
I'm trying to come up with an easy to use interface for extracting data. This would allow users to select data and generate a report. The problem is that the data variables are huge and the options numerous.
Currently I use restricted form which is big but restricted.
e.g.
Date: mm/dd/yyyy to mm/dd/yyyy
State: drop down list
Price: 0 to 999999
Style: Square [X] Round [X] Oval [X]
(50 other options here)
Month: Jan to Dec
[Run Report]
This is the basic form which has around 60 form options. It is usable but obviously the searching is restricted.
What I am trying to do is to open up the hundreds of database fields and options.
I had thought of just teaching users the basics of SQL and let them write their own queeries:
Enter your search term: [ .... ]
e.g. State = 'MA' and price between 1000 and 20000 and name like 'Ellison'
That would be really useful for the geeky users as they know what they want to generate and can get the info quickly. The more novice user would find it daunting though and I would expect a zillion support emails asking why there is an error or no data returned.
The next option is to create a series of input boxes each one seperated by an AND or an OR. The input boxes would be based on the database columns and the corresponding format offered.
e.g.
Choose a field [drop down box listing every database column] (date, state, price ... month]
Enter value [either a drop down box for fixed values or text box depending on field type]
[Add another option] [AND or OR]
[Run Report]
This will allow every field in the database to be searched on and allow the use of more complex and / or values. This is better than the basic form with just 60 variables and is not as difficult as direct entering of sql statements.
How best to create this form? How to deal with the different field types (date, numeric, text, checkboxes). What about situations where the names are listed
e.g.
[Salesperson Lastname] [drop down list] (where every salesperson name is listed)
The user has chosen Salesperson Lastname and a drop down box is being displayed. This has 5,000 names so how can quickly find the name he wants (Larry Ellison exact) or (Ellison - all) or (Ell partial match).
How best to perform the and / or'ing so as to not extract the wrong data:
state = 'MA' and style = 'O' or price > 10000
state = 'MA' and (style = 'O' or price > 10000)
(state = 'MA' and style = 'O') or price > 10000
[edited by: Frank_Rizzo at 10:03 am (utc) on Oct. 26, 2007]
A couple observations:
First, I associate a set of field names with the type of search allowed on that field. This takes the form of lists:
textfields = ('fname','lname','company');
datefields = ('lastmod','created','expires');
select_one = ('property_type','sale_type');
define_range = ('price','square_footage','acreage');
Then you have another list that lays out the fields in the order you like. The list field names come from the lists above.
fieldorder = ('fname','lname','company','lastmod','created', 'expires','property_type','sale_type', 'price','square_footage','acreage');
You go through that list and output the appropriate form fields. I'm using a generic-ish syntax to define the logic, it applies for perl, php, asp, whatever:
foreach item (fieldorder) {
fieldprinted = 0;
foreach formtype (textfields) {
if (item = formtype) {
- print a text input field named "item"
fieldprinted = 1
}
}
if (fieldprinted = 0) {
foreach formtype (datefields) {
if (item = formtype) {
- compose and print a series of select lists
named "item-month item-day item-year"
fieldprinted = 1
}
}
}
............
And so on for each form field type. For "tween" lists, such as prices or square footage, I like to output a series of "method" radio buttons and min-max fields. I name them by a system for all fields of that type, for example, if the field to search is "price,"
<input type="radio" name="price_method" value="0" checked> exactly
<input type="radio" name="price_method" value="1" checked> less than
<input type="radio" name="price_method" value="2"> more than
<input type="radio" name="price_method" value="3"> between
Min: <input type="text" name="price_min">
Max: <input type="text" name="price_max">
When processed, you follow the same logic, using your original lists to build your select statements, with special methods to parse out the date fields.
Specific recommendations:
What about situations where the names are listed...[Salesperson Lastname]..(where every salesperson name is listed)
This is a bad idea because if you have a list of 5000 it begins to present more of a difficulty for the user to scroll through than a simplification. Plus it will take your form longer to load. In these cases it would be part of the textfield group, in which the user enters all or part. You could (should) have radio buttons with it to indicate exact or partial matches on the entered text. This presents another if/then in the logic where you build your select string:
if (inputfield = [one of the textfields]) {
if (match_pref = 'exact') { selectStatement += ' field='[input value]' }
else { selectStatement += ' field like '%[input value]%' }
}
state = 'MA' and style = 'O' or price > 10000
state = 'MA' and (style = 'O' or price > 10000)
(state = 'MA' and style = 'O') or price > 10000
In your logic above,
state = 'MA' and style = 'O' or price > 10000
So this will match on state='MA' and style='O' but it will ALSO match on every other record where price is > 10000, whether state is or is not MA or style is or is not 'O'. Potentially you would see two records that match all three criteria - it matches once because state='MA' and style='O', and again because the price > 10000.
state = 'MA' and (style = 'O' or price > 10000)
This will match on every record where state='MA', with style='O' or price > 10000. There wil be no records outside of 'MA'.
(state = 'MA' and style = 'O') or price > 10000
The results of this should be identical to the first, state and style are bound by the and but will add any record with price > 10000 regardless of the first two criteria.
Consider a fourth option,
state = 'MA' and style = 'O' and price > 10000
Which will only display records matching all three criteria. This should display distinct records without duplicates.
This brings a point, a good way to parse out "tweens." in the price form example above, with 0=exact, 1=less than, 2= greater than, 3=between:
if (price_method = 0) { selectStatement += 'field =' [price_min]' }
else if (price_method = 1) { selectStatement += field <= '[price_min]' }
else iif (price_method = 2) { selectStatement += field >= '[price_min]' }
else iif (price_method = 3) { selectStatement += (field <=' [price_min] and field >= 'price_max') }
Given that you use the first field for exact, less, greater
A few other notables,
let them write their own queries:
Choose a field [drop down box listing every database column]
For example, let's say you have a category field that stores the numeric ID of the category, which is faster than text. Most users would want to search on the category title and not know the ID - so you still have to create a join or sub-query on the category table. I found it easiest to 1) if the field is defined, perform a search on just that field, or 2) if the field value is blank (top of the select list = '') simply searh every field in the table for the value, using like='%%' or ='' based on the data type. If it's a join field, you still have to create sub-queries.
Dynamically build your forms based on your database structure, it will be much easier to maintain as your database grows and changes.
I don't want to parse through every database field and display the options - it has to be: only show the user what he selects.
The database has 200 fields of which the user can search for any item. Assuming this is a database of sales of widgets here are some of the search scenario's which users want to perform:
Show widget sales:
where salesperson was Peter Anderson in state MA
where widget cost was less than $1000 between July and August and widget colour was blue
where sales salesperson commission rate within the past 6 months was more than 20% and store percentage of total state sales was more than 15%
As I say, I currently have a form with about 60 variables which can be clicked or typed into but this needs to go to the full 200. The form would be just too crazy and wasteful for 200 options if the user just wants to queery on any 3 out of the 200.
For this reason I'm thinking more of just presenting one criteria box at a time, one input box at a time and then let the user add to the report build or generate the report.
If this was an 80's style text knowledgebase style database it would go like this:
-----------
Q. Choose a category (persons, locations, dates, salesfigures...)
A. Persons
Q. Search for name
A. Anders
Q. Choose from A Anderson, P Anderson, A Andersville
A. P Anderson
Q. Search now or continue to build report
A. Continue
Q. Do you want to AND or OR the queery
A. AND
Q. Choose a category (persons, locations, dates, salesfigures...)
A. Locations
Q. Choose a sub-category (states, counties, cities, towns)
A. States
Q. Choose a state (AL, AK, AS, AZ...WI, WY)
A. MA
Q. Search now or continue to build report
A. Search now
Report. Widget sales for P Anderson in Massachusetts:
1231231
131
---------------
Obviously this can be done more slicker in html and snazzy with ajax. I don't know if it is the most usable for the user / feasible for me to create.
