Larry,
I appreciate all the suggestions I have received about this matter. Jan's suggestion about using a variable radio group seems to have cared for the speed issue. I am having our people try it out today and they will let me know. Since I am doing this remotely I have to depend on their input. I will have to try something though on the normal OKP form because I use it on other tables and eventually the number of records in those tables may slow things down. I want to be able to find the answer to this problem before that happens to those tables. Jim ________________________________ From: [email protected] [mailto:[email protected]] On Behalf Of Lawrence Lustig Sent: Wednesday, January 27, 2010 8:55 AM To: RBASE-L Mailing List Subject: [RBASE-L] - Re: ON KEY PRESS << SET VAR vWhereClause = + ('Control# LIKE'&.vSearchString&'OR ' + + 'Invoice# LIKE'&.vSearchString&'OR ' + + 'OrderNumber LIKE'&.vSearchString&'OR ' + + 'ShipZip LIKE'&.vSearchString) PROPERTY LV_Quotes REFRESHLIST 'TRUE' I have tried using WHERE Control# LIKE and WHERE Control# Contains with the UNION SELECT but to no avail. Since I have never used the UNION SELECT I am sure I am not doing something correctly. >> Start by creating a VIEW as follows: CREATE VIEW SearchView (DisplayColumn, SearchColumn) AS + SELECT DisplayColumn, Control# FROM YourTable + UNION SELECT DisplayColumn, Invoice# FROM YourTable + UNION SELECT DisplayColumn, OrderNumber FROM YourTable + UNION SELECT DisplayColumn, ShipZip FROM YourTable where "DisplayColumn" is the name of the column you want to appear in the drop down control and YourTable is the name of your table. You can either use the word "SearchColumn" to name that second column, or some other name you prefer. I'm assuming here that Control#, Invoice#, and OrderNumber are all TEXT columns. If they are defined with different length, move the SELECT statements around so that widest column is in the first SELECT statement. Now, base your drop down on the view, not the original table. The WHERE clause now becomes a simple "WHERE SearchColumn LIKE 'Value%'". I think that R:Base's query optimizer is smart enough to optimize this query, but there's a chance it won't be able to and the search speed you get from this will be the same as the existing speed. If that proves to be so, you need to get just a tiny bit fancier, basing the drop down on a TEMPORARY view and, instead of recreating the WHERE clause each time, you need to recreate the temporary view with the WHERE conditions built in to each SELECT. If that turns out to be necessary, post back and we can help you do that. -- Larry
