Update Browse from other database

[Ollie]

Please have a look at this sample code.

I want the "orders" TAB to only show the orders for the selected customer

I have tried many things with no luck. I am not sure what approach to take.
Filtering is too slow.
ORDSCOPE ? DBSETRELATION?
Where in the code do I set this?

Thanks.

Code: Select all  Expand view

#include "FiveWin.ch"

static oWnd
static oDlg
STATIC oDBF_CLIENTS
STATIC oDBF_ORDERS
REQUEST DBFCDX

PROCEDURE main()

   DEFINE WINDOW oWnd TITLE "TEST FOLDER" ;
      MENU BuildMenu()

   ACTIVATE wINDOW oWnd MAXIMIZED
return


function BuildMenu()

   local oMenu

   MENU oMenu
   MENUITEM "Run" ACTION MyFolder()
   MENUITEM "Exit" ACTION oWnd:End()
   ENDMENU

return oMenu

function MyFolder()

   local oDlg1, oDlg2, oFld

   LOCAL oBtn1,oBtn2

   create_data()

   DBSELECTAREA("ORDERS")
   DATABASE oDBF_ORDERS
   oDBF_ORDERS:bEoF = nil
   oDBF_ORDERS:Gotop()
   oDBF_ORDERS:Load()

   DBSELECTAREA("CLIENTS")
   DATABASE oDBF_CLIENTS
   oDBF_CLIENTS:bEoF = nil
   oDBF_CLIENTS:Gotop()
   oDBF_CLIENTS:Load()

   DEFINE DIALOG oDlg of oWnd SIZE 500,300
   @ 0,1 BUTTON oBtn1 PROMPT "PREV" OF oDlg ACTION SKIPREC(-1)
   @ 0,15 BUTTON oBtn2 PROMPT "NEXT" OF oDlg ACTION SKIPREC(1)

   @ 1,1 FOLDER oFld  OF oDlg ;
      PROMPTS "Client Info", "Orders";
      DIALOGS "oDlg1", "oDlg2" ;
      SIZE 400,200

   @ 1,1 GET oDBF_CLIENTS:NUMBER OF oFld:aDialogs[ 1 ] UPDATE WHEN .F.
   @ 1,5 GET oDBF_CLIENTS:COMPANY OF oFld:aDialogs[ 1 ] UPDATE

   @ 1,1 LISTBOX FIELDS ALIAS "ORDERS" of oFld:aDialogs[ 2 ] UPDATE

   ACTIVATE DIALOG oDlg  VALID (DBCLOSEALL(),.T.)

return nil

STATIC PROCEDURE SKIPREC(nRECS)
   oDBF_CLIENTS:SKIP(nRECS)
   IF oDBF_CLIENTS:Eof()
      oDBF_CLIENTS:GoBottom()
   ENDIF
   IF oDBF_CLIENTS:Bof()
      oDBF_CLIENTS:GoTop()
   ENDIF
   oDlg:Update()
RETURN



STATIC PROCEDURE create_data()
   RddSetDefault( "DBFCDX" )

   DBCREATE('CLIENTS.DBF',{{"NUMBER",    "C", 3, 0},{"COMPANY",     "C", 30, 0}} )
   USE CLIENTS ALIAS CLIENTS EXCLUSIVE NEW
   APPEND BLANK
   CLIENTS->NUMBER:='002'
   CLIENTS->COMPANY:='DEF'
   APPEND BLANK
   CLIENTS->NUMBER:='001'
   CLIENTS->COMPANY:='ABC'
   APPEND BLANK
   CLIENTS->NUMBER:='003'
   CLIENTS->COMPANY:='GHI'
   APPEND BLANK
   CLIENTS->NUMBER:='004'
   CLIENTS->COMPANY:='JKL'
   INDEX ON CLIENTS->COMPANY TAG "COMPANY"

   DBCREATE('ORDERS.DBF',{{"NUMBER",    "C", 3, 0},{"GOODS",     "C", 30, 0}} )
   USE ORDERS ALIAS ORDERS EXCLUSIVE NEW
   APPEND BLANK
   ORDERS->NUMBER:='002'
   ORDERS->GOODS:='DEF ITEM 1'
   APPEND BLANK
   ORDERS->NUMBER:='001'
   ORDERS->GOODS:='ABC ITEM 1'
   APPEND BLANK
   ORDERS->NUMBER:='003'
   ORDERS->GOODS:='GHI ITEM 1'
   APPEND BLANK
   ORDERS->NUMBER:='002'
   ORDERS->GOODS:='DEF ITEM 2'
   APPEND BLANK
   ORDERS->NUMBER:='002'
   ORDERS->GOODS:='DEF ITEM 3'
   APPEND BLANK
   ORDERS->NUMBER:='003'
   ORDERS->GOODS:='GHI ITEM 2'
   INDEX ON ORDERS->NUMBER TAG "NUMBER"
   RETURN


[James Bott]

You are going to have to have a "CUSTNO" field in the orders file. Note that I would not use the fieldname "NUMBER" in the customer file (or the order file), use something specific to the file, such as CUSTNO and ORDNO. Now you can have both ORDNO and CUSTNO fields in the ORDER.DBF file.

OK, index the orders file on CUSTNO. Then when the user selects a customer from the customer file, set the scope of the orders file to match.

(oOrders:cAlias)->(setScope(0, oCusts:custno))
(oOrders:cAlias)->(setScope(1, oCusts:custno))
oOrders:gotop()

James

[Ollie]

Thanks, I didn't realise I had to specify an end to the scope (I thought it would stop when it detects a change in the index key.)


I added :

(oDBF_Orders:cAlias)->(OrdScope(0, oDBF_CLIENTS:NUMBER))
(oDBF_Orders:cAlias)->(OrdScope(1, oDBF_CLIENTS:NUMBER))
oDBF_Orders:gotop()

as you suggested and is works.

[areang]

Mr. James Bott

I used oBrw:SetFilter()

James Wrote

Code: Select all  Expand view

(oDBF_Orders:cAlias)->(OrdScope(0, oDBF_CLIENTS:NUMBER))
(oDBF_Orders:cAlias)->(OrdScope(1, oDBF_CLIENTS:NUMBER))
oDBF_Orders:gotop()


Which one better for filter record ?

Can you give me some samples ?

Regards
Areang

[James Bott]

For anything but a very small file, setFilter() will be way too slow--it reads every record in the file.

OrdScope() requires that the data be indexed on the field being scoped. It will read only those records within the scope so it is very fast.

The sample is the code you quoted in your message.

James