Xbrowse bBargetAction and the filtering function for it

[Marc Venken]

Hello,

In my xbrowser's i use the folowing syntax for my BargetAction, that i use alot.

Code: Select all  Expand view  RUN

// xbrowse setup ...
   :bBarGetAction := {|| ( oBrwSel:cAlias )->( MARC_SETFILTER( oBrwSel ) ) }

FUNCTION MARC_SETFILTER( oBrw )

   LOCAL cFilter := ""
   LOCAL n, oCol, uVal, cType

   FOR n := 1 TO Len( oBrw:aCols )
      oCol  := oBrw:aCols[ n ]
      IF ! Empty( uVal := oCol:uBarGetVal )
         IF !Empty( cFilter )
            cFilter  += " .AND. "
         ENDIF
         cType    := ValType( uVal )
         DO CASE
         CASE cType == 'C'
            uVal     := Upper( AllTrim( uVal ) )
            cFilter += '"' + uVal + '" $ UPPER( ' + oCol:CExpr + " )"
         CASE cType == 'D'
            cFilter  += oCol:cExpr + " = " + ( uVal )
         OTHERWISE
            cFilter  += oCol:cExpr + " == " + cValToChar( uVal )
         ENDCASE
      ENDIF
   NEXT
   IF Empty( cFilter )
      IF ! Empty( dbFilter() )
         dbClearFilter()
         oBrw:Refresh()
      ENDIF
   ELSE
      IF !( dbFilter() == cFilter )
         SET FILTER TO &cFilter
         GO TOP
         oBrw:Refresh()
      ENDIF
   ENDIF
   oBrw:SetFocus()
RETURN NIL

 

1. I wonder if this code is still optimised for the current version of FW. The code is somewhere from the forum, but sure a longer time ago. Changes needed ?

2. If I put data in a field like : Ref123 this code will show also Ref123 and Ref12345 and dataRef123 . I would like a checkbox to set exact or so for only Ref123

3. Does this function work any better (faster) if I set a index of any kind. Now a index is active, but not related to a search column

[James Bott]

Marc,

There have been a number of recent forum discussions about filters vs scopes.

The basics are that with a filter you have to read every record in the database, but with a scope you only read the records within the scope.

For example, you are using a browse and you want to only show customers in California. Let's assume the customer database is 10,000 records and only a hundred of them are in California. With a filter you have to read all 10,000 records.

However, with a scope you only have to read the 100 records that are in California. The index is already sorted by state, so the scope starts at the first record for California and skips through the next 100 records until it runs out of California customers. So, in this case it is 100 times faster than the filter.

[Rick Lipkin]

James

You are correct that scopes are faster for DBFCDX .. filters are even faster in Sql ...

Rick Lipkin

[nageswaragunupudi]

Mr Marc

1. I wonder if this code is still optimised for the current version of FW. The code is somewhere from the forum, but sure a longer time ago. Changes needed ?

Optimization has nothing to do the version of FWH. Optimization of filters is dependent of (x)Harbour RDD. DBFCDX and ADS optimize filters if the filters obey the rules of optimization.
More about optimization later.
But for now, this function works exactly the same way like it did work in earlier versions of FWH

2. If I put data in a field like : Ref123 this code will show also Ref123 and Ref12345 and dataRef123 . I would like a checkbox to set exact or so for only Ref123

This is the result you should expect because the comparison operator used in the filter is '$'

If you want the filter to return all records with the field starting with the value, the filter condition can be:

Code: Select all  Expand view  RUN

uVal     := Upper( AllTrim( uVal ) )
cFilter += "UPPER( " + oCol:cExpr + " ) = '" + uVal + "'"
 

This will return all records with the field value beginning with "Ref123" followed by any characters.

If you want exact comparison:

Code: Select all  Expand view  RUN

uVal     := Upper( AllTrim( uVal ) )
cFilter += "UPPER( ALLTRIM( " + oCol:cExpr + " ) ) == '" + uVal + "'"
 

3. Does this function work any better (faster) if I set a index of any kind. Now a index is active, but not related to a search column

Speed of filters has nothing to do with the active index. We can set index to any order or even natural order.
Speed is determined by optimization of filter expression. By default, DBFCDX and ADS support optimization of filters but we need to understand this feature and follow the optimization rules. More on this subject of optimization in my next post.

This function does not deal with data types "T" and "L". You need to add these types also.

[Marc Venken]

Due to several post, I indeed started to use Scopes a lot more, and they are pretty fast..

In this case, it is the bBargetAction that i'm looking into. Since Mr. Rao made it possible to use a function of ourselves, maybe there I have to changes stuff for better performance/speed.
In this version there is a filter active, but maybe I need to make a other function to test the vBargetAction with scopes.

But before I try this, I wait for the information that Mr. Rao will provide in the next days depending the optimization rules of filters in DBFCDX, since that is what I use. My change from DBFNTX to DBFCDX
some while ago was only in linking the cdx files, create new indexes. I think that Mr. Rao will come up with some more items that are important.... I wait !!

Thanks for all info, like so many times.