When ever I type a single value into the QuickSearch, it will find 7 records. But when I want to narrow my result set, I add another search term separated by a space. Using SQL in another software tool, I know that the result set should reduce from 7 records to 3 records, once I search for both the first and second term. However, SC8.1 shows me no records in the result set.
The single value QuckSearch works ok now as it is. However…
What should happen when two or more QuickSearch values are entered, is this:
- Each term has it’s own set of “WHERE” clause conditions.
- The WHERE clause conditions will be generated from all the fields listed in the “Advanced Search” fields view
- The condition for all the fields will revert to CONTAINING (regardless of what is set for operators in the “Advanced Search” fields view
- The logic operator between all the fields will be OR
Then…
- EACH search term’s WHERE clause conditions set will be wrapped in ( and )
- All the WHERE clause condition sets will have the AND operator put between them
- Now all the WHERE clause condition sets are ready to go. I put an extra ( and ) around the whole thing just because it feels good
Add a single 'WHERE" at the top, and you’re done. Execute.
Example:
Search terms entered: [SIZE=14px]Mic son ing[/SIZE]
WHERE
(
(
FST_NM CONTAINING ‘Mic’ OR
LST_NM CONTAINING ‘Mic’ OR
OCCUPATION CONTAINING ‘Mic’
)
AND
(
FST_NM CONTAINING ‘son’ OR
LST_NM CONTAINING ‘son’ OR
OCCUPATION CONTAINING ‘son’
)
AND
(
FST_NM CONTAINING ‘ing’ OR
LST_NM CONTAINING ‘ing’ OR
OCCUPATION CONTAINING ‘ing’
)
);
[B]Result set: Count: 1 record:
FirstName LastName Occupation
Michael JACKSON Singer[/B]
This will enable the SC Quick Search to behave more like Google and my users will be happy, that they don’t have to go into the Advanced Search every time they want to narrow their search down from 3,000 records to 5 records or even 1 record.