Openedge SDO -> smart data browser - I want to filter the query results

Question

I have an SDO supplying data to a read-only browser. The SDO query joins several tables and has calculated fields as well as natural data fields.

The users now want a search facility so the browser will only show rows where the search word appears in ANY of the text fields.

For example they want to see rows where

customer.name matches "*bob*" OR
customer.address1 matches "*bob*" OR
product.description matches "*bob*" OR 
calc_field_1 matches "*bob*" OR
calc_field_2 matches "*bob*" OR ...

Ideally the answer will filter the SDO output as it is created - but I am also happy to filter the data on the way to the smartbrowser or in the smartbrowser.

Answer 1

The business problem you're trying to solve in fraught with performance issues if you implement it as written. I'd suggest

1. adding another character column to the table or db,
2. putting all the words from the other columns in it,
3. applying a word-index to the new column,
4. doing a search on that column, and then linking back to the source tables.

It'll be much faster and easier to use.

Answer 2

I used a very simple solution in the end. Users can enter a string they are looking for. If the string is in a cell in the browser then the cell is highlighted in yellow.

Before this the users had to scroll up and down trying to spot the cells of interest in hundreds of rows. We did not have the time or budget for anything fancier.

The important bit of code in the smartbrowser is like this...

on row-display of br_table in frame f-main
do:
    if rowObject.field1 matches "*BOB*" then 
       rowObject.field1:BGCOLOR in browse br_table = 14.
    if rowObject.field2 matches "*BOB*" then 
       rowObject.field2:BGCOLOR in browse br_table = 14.
    if rowObject.field3 matches "*BOB*" then 
       rowObject.field3:BGCOLOR in browse br_table = 14.

    ... etc ...

it's not hard-coded to only look for Bob - but you should get the idea.