Wondering how I would do a search with a drop down. For example, if someone was searching for a part, but didn't know the part number, only the description, they could search for 18" fan, then choose a drop down to denote if they wanted to search the catalog, mfg part, our part, etc.
Hope that makes sense, thanks.
Are you asking for the SQL query or the application code? I'm a little confused.
Sounds like you'd be using multiple queries to account for the various things a user can select.
A little of both I think. I'm trying to think of a way to search the same field across multiple tables but in a single query, to make it easier for the end user.
Hope that makes sense.
Thanks!
We use, what I believe is referred to as, a Predictive Search. You start typing
18" fan
and a dropdown list of product codes / names is displayed to pick from.
We use JQuery to achieve that (specifically the UI Add-on library), and it uses AJAX to make the query, and display the results (as a dropdown list); using AJAX avoids round-tripping the whole page.
Things to watch out for:
The query is sent to the DB often. We have worked hard on the caching elements of the AJAX Library call - for example if you type
18" fa
and the results (which include 18" fan of course) include ALL possible results for 18" fan, then when the user types the "n" there is no need for a further SQL query - the APP already has, cached, all the values it needs. To achieve this we typically send, say, 20 results when only 5 are displayed.
We also cache the data so that the query is really REALLY fast. We would never include a query like
SELECT ProductCode, ProductName
FROM Products
WHERE ProductName LIKE '%' + @UserSearch + '%'
OR ProductCode = @UserSearch
the frequently repeated query performance would just be horrific. Implementing Predictive Search caused us to create a dictionary (all words in the dictionary, with Aliases and root-words and all that jazz), and then Product Names / Descriptions etc.split into individual words and added to a KEYWORD table, and so on. Free Text Search might work well enough for you, but CLients were telling us they wanted the search to recognise competitors product codes, assoicate Buzz Words in the News / run-up-to-Christmas, etc., with specific products, so we allow the user to aadd their own Alias words to Products and so on.
So, for us, we can just search the KEYWORDS table, and get a link from KEYWORD to ProductCode with a trivial SQL Query. But it was a fair bit of work to get to that point!!
Other optimisations include not sending a query until, say, 3 characters have been typed (but there are always, valid, 2 character searches <sigh!>). Or wait until the user pauses before sending the query (the upshot of that is frustration for the user)
After implementing this in our APPs I have become full of admiration for big websites that have Predictive Search - well, "big websites that have Predictive Search and are NOT brought to their knees by the frequent launching on itty-bitty SQL queries!"
Good insight, thank you.
How close can I get without having to go the AJAX route? I was hoping to have this as a standalone Excel report connecting over ODBC, you think that's possible?
It might be that Excel can get a DropDown via some "function" (which, itself, could call SQL, or anything else 
). Sounds to me like it would need some programmign in VBA.
AJAX only applies (I think?) to web pages and the like.