Filtering xBrowse Arrays - SOLVED!

[fraxzi]

Dear All,

I have two xbrowse (all arrays)

oXbrArray1 has column 1 same with oXbrArray2 column 1. In short, its a Parent-child relationship.

My question is.. How can I filter oXbrArray2 with elements equal to element (current row) of oXbrArray1 based on column 1?

like this:

Array1
123 | BMW i8
345 | AMG

Array2
123 | Windshield
123 | door panel
345 | headlights
345 | Tires


If I select 'BMW i8' from the first array(1), then the array(2) should display (filtered) only rows with '123' column.. like that.

I can't find a working sample using ":bFilterExp" of xBrowse or similar.


I really appreciate your inputs.

Thanks,
Frances

[James Bott]

Frances,

There is a database class you can use with arrays--perhaps it supports filters or relations.

It was a fairly recent discussion in this section. Search for it.

James

[TimStone]

James,

Did you change email addresses ? I sent you one yesterday but didn't hear back. It relates to another thread on here

Tim

[James Bott]

Frances,

Here is the link to the array RDD:
viewtopic.php?f=3&t=31054&hilit=array+database

[James Bott]

Tim,

I replied to your email shortly after you sent it. I upgraded from Win8.1 to 10 yesterday so it is possible something is messed up. I will check my online email account to see if the sent mail is there (I can't see any sent mail in the Win 10 mail app).

james

[nageswaragunupudi]

I am suggesting two methods.

1. Using built-in Array filtering capabilities of XBrowse.
2. Splitting the child array and attaching to the Parent array.

Both work well. My preference is the 2nd method. There can be many other approaches too. You may use what you like.

For testing, I created parent and child arrays from states.dbf and customer.dbf in \fwh\samples folder. You may copy this program to \fwh\samples folder and build it.

Code: Select all  Expand view  RUN

#include "fivewin.ch"

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

function Main()

   UseXBrowseFilter()
   UseSplitArrays()

return nil

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

static function UseXBrowseFilter()

   local aParent, aChild
   local oDlg, oFont, oParent, oChild

   USE STATES
   aParent  := FW_DbfToArray( "CODE,NAME" )
   CLOSE STATES

   USE CUSTOMER
   aChild   := FW_DbfToArray( "STATE,FIRST,CITY,SALARY" )
   CLOSE CUSTOMER

   DEFINE FONT oFont NAME "TAHOMA" SIZE 0,-15
   DEFINE DIALOG oDlg SIZE 700,400 PIXEL TRUEPIXEL FONT oFont ;
      TITLE "XBROWSE ARRAY FILTERING CAPABILITIES"

   @ 20,20 XBROWSE oParent SIZE 200,-20 PIXEL OF oDlg ;
      DATASOURCE aParent AUTOCOLS HEADERS "Code", "State" ;
      CELL LINES NOBORDER

   WITH OBJECT oParent
      :SetGroupHeader( "STATES" )
      :bChange    := { || oChild:Seek( "" ), oChild:Seek( oParent:Code:Value ) } // Not necessary but desirable to use
      //
      :CreateFromCode()
   END

   @ 20,220 XBROWSE oChild SIZE -20,-20 PIXEL OF oDlg ;
      DATASOURCE aChild AUTOCOLS ;
      HEADERS "St", "FirstName", "City", "Salary" ;
      AUTOSORT ;
      COLSIZES 60,120,130 ;
      CELL LINES NOBORDER

   WITH OBJECT oChild
      :SetGroupHeader( "CUSTOMERS" )
      :lIncrFilter   := .t.
      :bFilterExp    := { |cSeek,aRow| aRow[ 1 ] = cSeek }
      :bKeyChar      := { |k| If( k == VK_BACK .or. k > 31, 0, nil ) }
      //
      :CreateFromCode()
   END

   ACTIVATE DIALOG oDlg CENTERED ON INIT oChild:Seek( aParent[ 1, 1 ] )
   RELEASE FONT oFont

return nil

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

static function UseSplitArrays()

   local aParent, aChild, n, nAt
   local oDlg, oFont, oParent, oChild

   USE STATES
   aParent  := FW_DbfToArray( "CODE,NAME,ARRAY(0)" )
   CLOSE STATES

   USE CUSTOMER
   aChild   := FW_DbfToArray( "STATE,FIRST,CITY,SALARY" )
   CLOSE CUSTOMER

   for n := 1 to Len( aChild )
      nAt   := AScan( aParent, { |a| a[ 1 ] == aChild[ n, 1 ] } )
      if nAt > 0
         AAdd( aParent[ nAt, 3 ], aChild[ n ] )
      endif
   next

   DEFINE FONT oFont NAME "TAHOMA" SIZE 0,-15
   DEFINE DIALOG oDlg SIZE 700,400 PIXEL TRUEPIXEL FONT oFont ;
      TITLE "USING SPLIT ARRAYS"

   @ 20,20 XBROWSE oParent SIZE 200,-20 PIXEL OF oDlg ;
      DATASOURCE aParent AUTOCOLS HEADERS "Code", "State" ;
      CELL LINES NOBORDER

   WITH OBJECT oParent
      :SetGroupHeader( "STATES" )
      :bChange    := { || oChild:aArrayData := oParent:aRow[ 3 ], ;
                          oChild:Refresh( .t. ) }
      //
      :CreateFromCode()
   END

   @ 20,220 XBROWSE oChild SIZE -20,-20 PIXEL OF oDlg ;
      DATASOURCE aParent[ 1, 3 ] AUTOCOLS ;
      HEADERS "St", "FirstName", "City", "Salary" ;
      AUTOSORT ;
      COLSIZES 60,120,130 ;
      CELL LINES NOBORDER

   WITH OBJECT oChild
      :SetGroupHeader( "CUSTOMERS" )
      //
      :CreateFromCode()
   END

   ACTIVATE DIALOG oDlg CENTERED
   RELEASE FONT oFont

return nil

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

XBrowse is more than a normal browse. It makes many complex things easy, which otherwise require complex coding. All it requires is to add one line of code to the child browse

Code: Select all  Expand view  RUN

      :lIncrFilter   := .t.
 

and another line of code in parent browse

Code: Select all  Expand view  RUN

      :bChange    := { || oChild:Seek( "" ), oChild:Seek( oParent:Code:Value ) }
 

A word about ArrayRDD. I had to test this recently. I do not advise this approach for performance reasons. First we need to create a table using ArrayRDD. This is unacceptably slow. Next we have to append each row of our array to the table one by one and then use filters. All said and done I am not sure how far this RDD is compatible with XBrowse.

[TimStone]

A very simple approach is to have a function that loads the secondary array. On the first browse, with ON CHANGE have it re-load the second array with values from the database. I use this method ( and have for years ) and it is actually quite fast. In one example, I have a service history. Array 1 shows the invoices. The highlighted invoice has it's details loaded into an array 2. Change 1, and 2 is instantly loaded. It actually goes a step further. Click on a line in array 2 and full detail is shown in a pop up. I've never had a complaint about speed, even across a network.

Tim

[nageswaragunupudi]

My answer was to his question how to make parent child browse from two arrays. He knows the source of the arrays.
How the arrays are to be loaded is not part of his question.

[TimStone]

Yes, you answered his question.
However, when I saw his example, I thought I would also give him an alternative approach.
I've been doing this for 33+ years and I'm still happy to see alternative options.

[fraxzi]

Frances,

There is a database class you can use with arrays--perhaps it supports filters or relations.

It was a fairly recent discussion in this section. Search for it.

James

James,

Thanks for pointing this out.. but I am looking for the gathered arrays rather in rdd level.

[fraxzi]

A very simple approach is to have a function that loads the secondary array. On the first browse, with ON CHANGE have it re-load the second array with values from the database. I use this method ( and have for years ) and it is actually quite fast. In one example, I have a service history. Array 1 shows the invoices. The highlighted invoice has it's details loaded into an array 2. Change 1, and 2 is instantly loaded. It actually goes a step further. Click on a line in array 2 and full detail is shown in a pop up. I've never had a complaint about speed, even across a network.

Tim

Hi Tim,

You are right with this approach. I used it several times. But both the parent array and child are already a result from SQL query.. I need to reduce SQL execution so I put all records in an array (approx. 50 to 100 max element both parent and child) so not that big in terms of records. This is done in one SQL query (one pass) from several tables to combine those info.

Thanks so much for the idea. I really appreciate it.

[fraxzi]

Mr. RAO,

Please give me some time to study and test it. I will reply soon.

Probably this is what I need.

Sorry for the delay response.. Please accept my apology.

Thanks so much.

[fraxzi]

Dear RAO,

Code: Select all  Expand view  RUN

oParent:bChange    := { || oChild:Seek( "" ), oChild:Seek( oParent:Code:Value ) }
...

oChild:lIncrFilter   := .t.
oChild:bFilterExp    := { |cSeek,aRow| aRow[ 1 ] = cSeek }
...
 

This approach is perfect.

I have one more concern... How can I get ONLY the filtered child array?

oChild:aArrayData contains all records not only the filtered elements..

[nageswaragunupudi]

aNew := AClone( oBrw:aArrayData )
ASize( aNew, oBrw:nLen )

[fraxzi]

aNew := AClone( oBrw:aArrayData )
ASize( aNew, oBrw:nLen )

Thanks RAO.

I tested it and this solved my problem..