Better than a simple search

Skip to first unread message

Dawn Wolthuis

Aug 9, 2022, 4:07:03 PMAug 9
UV 11.3.1
flavor PICK (for SB+ in some places in the foundation)

Goal: Do at least a slight improvement on a search feature

Existing simple search feature:
Currently, a web front-end gathers a single string from a user and then searches for each separate word (space-delimited) in that string across 5 fields in the database, including a "title" and a "short title."

User types in a phrase, and a lot of entries show up before one that has that exact phrase in one of the title fields. The most obvious choices do not come up first.

User types in 
Better Eating through Chemistry
Resultset could be something like:
Chemistry 101
Eating Well
Better Political Situation in the MIddle East
...and 2 pages later
Better Eating through Chemistry
...before 3 more pages 

I would like to do better than a simple search, without getting into any of the following:
1) front-end selects or sorts
2) add-ins
3) anything that changes what the user enters -- needs to be a response to a Search where they enter a string of what they are looking for
4) anything that detracts too significantly from the performance (it is OK if it is a bit slower, but needs to be a good user experience)

I would like to improve this search, possibly by showing multiple result sets:
Result set #1
Match the set of words entered to any items with one of the fields being matched having all of these words in the same order, but not necessarily adjacent. So a search of Better Chemistry would bring back anything with "Better Chemistry" in it but also "Better Eating through Chemistry" but would not bring back "Why Studying Chemistry is Better than Physics"

Result set #2
DONE. Simple enough to match for all of the tokens with "and" instead of "or" in our current query

Result set #3
DONE. The current query with results where any of the fields being searched have any word matching any token

Any suggestions on how to accomplish Result set #1 with a query and/or basic statements? (I'm green) Are there other ideas for how to improve a simple search so that it is a tad bit better and would show the "Better Eating through Chemistry" item up front if that is what the user enters? For example, I could switch the idea for result set #1 to just find exact matches of the full string within each field, but I was trying to do better than that.

Thanks in advance for any ideas or even query statements or BASIC code for this.   --Dawn

George Gallen

Aug 9, 2022, 4:34:16 PMAug 9
Create a cross reference file with as an id a concat of fields to search with the source @ID as att 1.

Loop through each delimited search word in the cross ref file with …param… if found save @ID*param in a sorted array (can be more then 1)

Loop through this array - giving # of hits and @ID to new array sorted desc by #hits

You now have list of highest probability to lowest of matches

Shouldn’t be too bad on processing as long as the cross reference is updated when data fields change

On Aug 9, 2022, at 4:07 PM, Dawn Wolthuis <> wrote:

You received this message because you are subscribed to
the "Pick and MultiValue Databases" group.
To post, email to:
To unsubscribe, email to:
For more options, visit
You received this message because you are subscribed to the Google Groups "Pick and MultiValue Databases" group.
To unsubscribe from this group and stop receiving emails from it, send an email to
To view this discussion on the web visit

Dawn Wolthuis

Aug 10, 2022, 8:28:28 PMAug 10
Thanks George. 

I'll see what we might want to incorporate. There are some other factors in play here, that I kept out of the info so as not to bias any particular approach. For example, there is already a materialized list of tokens, in the order they are found in the title, stored in the record on save and an I-desc subroutine that builds on that list to make a derived list of keywords.

I appreciate your suggestions.  Thanks.   --Dawn

Reply all
Reply to author
0 new messages