XBROWSE 15.12 Challenge

[TimStone]

Consider this:

I have an inventory file. I create an index on 6 fields, and using AUTOSORT in XBROWSE I can click on the column headers and reset the order, and do an incremental search.

The inventory file has stocking parts, and non-stocking parts. I would like to switch between showing all parts, or just stocking parts.

For speed, I have created indexes both ways, and do my order selection from a popup box. However, everywhere else in the program, clicking on the header works because no filter is needed.

I tried using the xBrowse commands for filtering, but those are very slow and do not always reset well when I eliminate the filter.

Does anyone have a suggestion how I can use the columns and filter the database quickly, or allow two indexes per column with a runtime condition ?

Your ideas would be appreciated.

[nageswaragunupudi]

Assuming you have a logical field to indicate stocking parts, if you maintain an index on that logical field, even setting a normal filter to that field should give acceptable performance. Particularly ADS client-server version should give good performace with filters even on large tables.

In any case, switching between indexes is definitely faster. Here is an example how to do this. I selected customer.dbf in fwh\samples folder and created a new DBF with 100,000 records. This size should be good enough for us to judge the performance. I have provided both options (1) switching indexes and (2) setting filter. We can also compare the response times.

Note: TDatabase in buffered mode slows down browsing speeds consderably. It is better to use non-buffered mode and the best option is to browse DBF directly.

Code: Select all

#include "fivewin.ch"

REQUEST DBFCDX

static aOrders

//----------------------------------------------------------------------------//

function Main()

   RDDSETDEFAULT( "DBFCDX" )

   OpenDBF()
   BrowseDBF()

return nil

//----------------------------------------------------------------------------//

static function OpenDBF()

   if ! File( "CUST100.DBF" ) .or. ;
      MsgYesNo( "Recreate DBF?" )

      CreateDBF()
   endif

   USE CUST100 NEW ALIAS CUST SHARED VIA "DBFCDX"
   SET ORDER TO TAG FIRST
   GO TOP

   aOrders     := { {}, {} }
   AEval( DbStruct(), { |a| AAdd( aOrders[ 1 ], a[ 1 ] ), ;
                            AAdd( aOrders[ 2 ], a[ 1 ] + 'M' ) } )

return nil

//----------------------------------------------------------------------------//

static function BrowseDBF()

   local oDlg, oFont, oBrw, oChk
   local lMarried    := .f.
   local lUseFilter  := .f.

   DEFINE FONT oFont NAME "TAHOMA" SIZE 0,-12
   DEFINE DIALOG oDlg SIZE ScreenWidth(0)*0.8, 600 PIXEL FONT oFont

   @ 40,10 XBROWSE oBrw SIZE -10,-10 PIXEL OF oDlg ;
      DATASOURCE Alias() AUTOCOLS AUTOSORT ;
      CELL LINES NOBORDER

   WITH OBJECT oBrw
      :cSortOrders   := aOrders[ 1 ]
      :CreateFromCode()
   END

   @ 10, 10 CHECKBOX lMarried PROMPT "Married Only" SIZE 60,10 PIXEL OF oDlg ;
      ON CHANGE ( oDlg:AEvalWhen(), ;
            oBrw:cAlias )->( SetFilter( oBrw, lMarried, lUseFilter ) )

   @ 10, 80 CHECKBOX lUseFilter PROMPT "Use Filters" SIZE 60,10 PIXEL OF oDlg ;
      WHEN ! lMarried ;
      ON CHANGE ( oDlg:AEvalWhen() )

   @ 10,160 SAY oBrw:oSeek PROMPT oBrw:cSeek SIZE 80,10 PIXEL OF oDlg ;
      COLOR CLR_HRED,CLR_YELLOW

   ACTIVATE DIALOG oDlg CENTERED
   RELEASE FONT oFont

return nil

//----------------------------------------------------------------------------//

static function SetFilter( oBrw, lMarried, lUseFilter )

   FIELD MARRIED

   local cOrder   := OrdSetFocus()

   if lUseFilter

      if lMarried
         SET FILTER TO MARRIED
      else
         SET FILTER TO
      endif
      DBSKIP( 0 )

   else

      if lMarried
         oBrw:cSortOrders  := aOrders[ 2 ]
         cOrder      += "M"
         OrdSetFocus( cOrder )
         if ! FIELD->MARRIED
            // Needs repositioning the record
            DBEVAL( { || nil }, nil, { || ! FIELD->MARRIED } )
            if Eof()
               GO BOTTOM
            endif
         endif
      else
         oBrw:cSortOrders  := aOrders[ 1 ]
         cOrder      := Left( cOrder, Len( cOrder ) - 1 )
         OrdSetFocus( cOrder )
      endif

   endif

   oBrw:Refresh()
   oBrw:SetFocus()

return nil

//----------------------------------------------------------------------------//

static function CreateDBF()

   local n

   ? "Will now create DBF"
   USE CUSTOMER
   COPY STRUCTURE TO CUST100
   CLOSE DATA

   USE CUST100 EXCLUSIVE
   for n := 1 to 200
   APPEND FROM CUSTOMER
   next
   ? lastrec(),"records"
   ? "Will now create Index"

   for n := 1 to FCount()
      CreateTag( FieldName( n ), FieldType( n ) )
   next
   CLOSE DATA

return nil

//----------------------------------------------------------------------------//

static function CreateTag( ctag, cType )

   FIELD MARRIED

   PRIVATE cExpr

   if cType == 'C'
      cExpr    := "UPPER(" + Trim( cTag ) + ")"
      INDEX ON &cExpr TAG &cTag
      cTag  += "M"
      INDEX ON &cExpr TAG &cTag FOR MARRIED
   else
      cExpr    := cTag
      INDEX ON &cExpr TAG &ctag
      cTag  += "M"
      INDEX ON &cExpr TAG &cTag FOR MARRIED
   endif

return nil

//----------------------------------------------------------------------------//

 

Please build and run this program in \fwh\samples folder.

[TimStone]

That was not exactly the question.

An Inventory database has 35 fields. It has indexes on 6, including partnumber, UPC code, description, manufacturer, location, and cross reference.

In an xbrowse, I can click on any of those headers and reorder the display based on that index.

The challenge: Keeping that same format, I want to filter the display based on the logical flag that indicates if the part is stocking, or not. ( Non stocking parts are items that were sold but not kept on our shelves ).

In the past, I created 12 indexes ( two for each of those fields I mentioned, 1 including all parts, and 1 including only stocking parts ) and then used a popup to select the one to be used. With ADS that is very fast. However, everywhere else in the program, the client sets the order by clicking on a column head.

I create the browse normally and all parts are displayed, and I click on the field to select the index order. To get the subset of just stocking parts, I use the browse filter ( bFilterExp = "invtyp = .t. " ) and the lFilterFld := .t. and this does work. It's just that it appears to be slow. I'll be testing this with some clients to see if the speed is OK. Initially I was finding a problem that the filter was not accurate, but that may have been resolved.

It's not going to be about an index on invtyp because we do not order the data on that field. We order it on the other 6 fields.

Tim

[nageswaragunupudi]

It's not going to be about an index on invtyp because we do not order the data on that field. We order it on the other 6 fields.

The purpose of creating indexes is not to set order alone. Major purpose is to optimize filters/where clauses.
If there is an index on "invtyp", "SET FILTER TO INVTYP: is optimized and is extremely fast. No body ever need to set order to that index.

However, everywhere else in the program, the client sets the order by clicking on a column head.

In this case also that facility is available. Please note the way the indexes are created and are swapped when the Married flag set on/off. As far as the user is concerned still he can click on the header and get the same effect.

I understand you did not try the sample.

[TimStone]

It has been a very crazy morning. Two people had computers die over the weekend and needed new software installs, and one business had their entire system corrupted by a very powerful virus ... but I will work with this now.

UPDATE: Looking very closely at your code, I think I have a solution now combining your methodology with my current tData code. I'll test it a bit later and post the results.

Tim

[TimStone]

I reviewed your code carefully, but that would require MASSIVE rewriting of a whole module to follow that process. First, I am using a tdata / tdatabase data object format and I really want to stay with it. Please consider the following:

First I have index "pairs". The first is on all parts, and the second is on only stocking parts. These are created without a problem. In my code, I have assigned the following:

Code: Select all

  LOCAL aOrders :=  {{"einnum", "eindes", "einman", "einupc", "eincrs", , , "einloc" },;
         {"einnums", "eindess", "einmans", "einupcs", "eincrss", , , "einlocs" } }

REDEFINE  XBROWSE oLBxin ;
     DATASOURCE oInventory ;
     HEADERS " Part ", " Description ", " Manufacturer ", " UPC ", " Cross Ref. ", " On Hand ", " Charge ", " Location "  ;
     COLUMNS "invnum", "invdes", "invman", "invupc", "invcod", "invstk", "invchg", "invloc" ;
     ID 716 OF oDiw ;
     ON CHANGE ( oPart:load(), oDiw:update() )  ;
     AUTOSORT UPDATE 
    oLBxin:cSortOrders := aOrders[1]

 

The first array are the indexes that have all parts. The second array are the indexes that have only stocking parts. They are in the proper column order. When I implement this, the program works as expected, with all columns responding in the order of the proper index. If I start it using oLBxin:cSortOrders := aOrders[2] it also works correctly, showing only the stocking parts, and allowing me to click on headers to switch indexes.

So, I figured ( from your example ) that I should be able to use a button to toggle to the alternate array. So I created the following code to toggle the two arrays.

Code: Select all

    DEFINE BUTTON oBtn3 OF oBarInv RESOURCE "FILTER" PROMPT "Filter" TOOLTIP "Filter list";
        ACTION ( IIF( lAll = .T., (oLBxin:cSortOrders = aOrders[2], lAll := .F., cIAMsg := "Showing Stocking Parts Only"  ), ;
        (   oLBxin:cSortOrders = aOrders[1], lAll := .t., cIAMsg := "Showing All Parts" ) ), ;
                oLbxin:refresh(), oPart:load( ), oDiw:Update(), oSay13:refresh( ) )

 

So, it does not refresh the browse with the new set of arrays. Apparently there is a method I need to add to push the refresh using the new orders. The logic here: If the setting is showing all records, it substitutes the second order, does a refresh and dialog update and a couple of other things.

Can we make this work ? What am I missing ?

Thanks.

[nageswaragunupudi]

What am I missing ?

In the action clause, you also need to set order to the corresponding new index and re-position the record if necessary.

Note:
You can include sort orders inside the command like this:

Code: Select all

    HEADERS " Part ", " Description ", " Manufacturer ", " UPC ", " Cross Ref. ", " On Hand ", " Charge ", " Location "  ;
     COLUMNS "invnum", "invdes", "invman", "invupc", "invcod", "invstk", "invchg", "invloc" ;
     SORT aOrders[ 1 ] ;
     ID 716 OF oDiw ;
 

[TimStone]

Unfortunately, that does not allow for the column headers to be responsive when clicking them. In other words, it would appear it's not actually substituting the second array using the button control .

I call oInventory:setorder( 2 ) and oInventory:gotop() and that then subs in the first column. Click on another column and it is not using the filtered index.

[nageswaragunupudi]

I shall give you the code in a while that works for tdatabase.

[TimStone]

OK ... it would seem like we could push this with some command that would refresh xbrowse with the new order array. We know both arrays will work, but if we could switch while the control is active, it would be ideal.

I thought:
oLBxin:cSortOrders = aOrders[2]
oLBxin:refresh()

would do it ... but it doesn't

ADS actually has long recommended using indexes rather than filters. I did try filters, but it created a bit of a mess with accessing the parts.

Also, where does cSortOrders come from ? I do not see it at all in xBrowse. That only has cSortOrder as a value within the class.

[reinaldocrespo]

Hello Tim;

I think what Rao is trying to say is that in addition to your 6 existing indexes you should create one extra index. In total you would have 7 indexes. This 7th index is used to create an Advantage Optimized Filter. Like this:

Code: Select all

( cAlias )->( AdsSetAof( "isStockItem == .T." ))

I'm assuming the field name that hols whether an item is stock or not is named "isStockItem" and that it is of type "Logical". When this code is executed and your browse is refreshed, only records with isStockItem on true will be displayed. To turn off the filter execute this code:

Code: Select all

( cAlias )->( AdsClearAof() )

Notice you don't change the controlling index nor do you need to create complex index expressions. BTW- you could use your other indexes to create even more complicated and interesting filters.

Finally; you could use the AOF expression even if an index does not exist, but that would make the browse very slow.

Hope that helps;


Reinaldo.

[TimStone]

Reinaldo,

I'll experiment with that. Even without the filter, at our test site we encountered a number of problems with the indexes. I reverted to a drop down button menu to select the type of filter ... but I would prefer this method if I can get it to work correctly.

The main problem we found was that the filter was not always filtering all records, but then it may be other issues too ....

Tim