how to explore many tables

[l3whmg]

Hi friends,
I need your help to understand which is the best way or approach to explore concurrently some tables.

I have this pattern tables (limited to fields that are correlated with each other):
Tab1.FieldN = Id_Code => Primary Key
Tab2.FieldX = Id_Code => Indexed but not Primary Key
Tab3.FieldY = Id_Code => Indexed but not Primary Key
Where Id_Code is the same value: typical example of a relationship of one to many!
one Tab1.Record to many Tab2.Records
one Tab1.Record to many Tab3.Records

With the Xbase language, I think I can use two alternative:
A) DBSETRELATION or B) FILTER

My problem is: how can I scan from the beginning to the end of Tab1 and concurrently scan all related records on both Tab2 and Tab3?

Many thanks in advanced

[dhaine_adp]

Hi Luigi,

In traversing the tables, you should use do while loop and check your primary key against the key of the second table and on the third table and so on. That is a loop within a loop.
But make sure that you would not end up in endless loop. Example:

Code: Select all

function main()

local PrimaryKey, DetailKey, RemKey

use invoicehead alias INVHEAD new
index on InvoiceNbr to Invoice

use invoicedetails alias INVDETL new
index on InvoiceNbr

use remarks alias REMARKS new
index on InvoiceNbr+LineNbr



dbselect("INVHEAD")
dbgotop()

do while !eof()
   PrimaryKey := INVHEAD->InvoiceNbr

   do while !eof() .and. INVDETL->InvoiceNbr == PrimaryKey

       DetailKey := PrimaryKey + LineNbr

       do while !eof() .and. ( ( REMARKS->InvoiceNbr + REMARKS->LineNbr ) == DetailKey )

           dbskip()
       enddo

       dbskip()

    enddo
    
    dbskip()
enddo
dbcloseall()
return

I hope that helps...


Regards,


Danny

[Rathinagiri]

Hi Danny,

Shouldn't select relevant table before giving dbskip()?

[dhaine_adp]

Shouldn't select relevant table before giving dbskip()?

Thanks for pointing out the mistake I made...

Luigi,

Sorry, here is the corrected sample:

Code: Select all

function main()

local PrimaryKey, DetailKey, RemKey
use invoicehead alias INVHEAD new
index on InvoiceNbr to Invoice
use invoicedetails alias INVDETL new
index on InvoiceNbr
use remarks alias REMARKS new
index on InvoiceNbr+LineNbr
dbselect("INVHEAD")
dbgotop()

do while !eof()
   PrimaryKey := INVHEAD->InvoiceNbr

   dbselectarea( "INVDETL")
   dbseek( PrimaryKey )
   do while !eof() .and. INVDETL->InvoiceNbr == PrimaryKey

       DetailKey := PrimaryKey + LineNbr

       dbselectarea( "REMARKS" )
       dbseek( DetailKey )
       do while !eof() .and. ( ( REMARKS->InvoiceNbr + REMARKS->LineNbr ) == DetailKey )

           dbskip()
       enddo

       dbselectarea( "INVDETL" )
       dbskip()

    enddo
    
    dbselectarea( "INVHEAD" )
    dbskip()
enddo
dbcloseall()
return

[l3whmg]

Hi friends,
many thanks for your quickly answer.
Thanks Dany, but what you have proposed is also what I normally use. I thought there was a different solution, another pov. Anyway thank you very much for your interest.

To avoid problems reported by Rathi, I always use the table name before the instructions as "DBGOTOP, DBSKIP" without using "DBSELECTAREA" that I use only in specific situation .

This question is borned because I'm try to develope a simple contact manager, that I want publish and submit to HMG forum, and I want to make it "fairly" well and I have some tables with the same referrer.

Best Regards

[Rathinagiri]

Contact Manager?!

Very good Luigi. Waiting for your utility...

[Czarny_Pijar]

Unfortunately, the code provided by dhaine_adp does'nt fulfill the requirements of l3whmg:

typical example of a relationship of one to many!
one Tab1.Record to many Tab2.Records
one Tab1.Record to many Tab3.Records

Look at the attachment for the correct solution, even in 2 flavours:
1st - straight from the school board, and
2nd - optimezed for speed ( both checked, works well).
----------
Sorry for the delay, the problems with the IDE and the grids has been just resolved

[l3whmg]

Hy Czarny_Pijar,
many thanks for your answer: I'll look at your example!
Best Regards

[nguyenchiduc]

I have to work with multiple tables at once, each table is a different index. Each table has hundreds of thousands or millions of records

Results return to the temporary tables. For example data_view1, data_view2 ... data_view_n

If the search index is used to return results very quickly even though many records, search on multiple tables to retrieve relevant information

Definition of GRID for data_view1, data_view2 ... data_view_n to see the results on screen.

@ x1, y1 GRID Grid_data_view1 ... ROWSOURCE 'data_view1'
@ X2, y2 GRID Grid_data_view2 ... ROWSOURCE 'data_view2'
.................................................. ..
@ xn, yn GRID Grid_data_view_n ... ROWSOURCE 'data_view_n'

============================

For example, I have the table:

Table1: index1
Table2: index1, index2 ...
Table3: index2, index3 ...
..............................
Table_N: index_(n-1), index_n ...

Code: Select all

select data_view1
zap
select data_view2
zap
.........................
select data_view_n
zap

select Table1
seek object_search_1
IF FOUND() ==  .T.
      select Table2
      seek Table1->object_search_1
      IF FOUND() ==  .T.
           select Table3
           seek Table2->object_search_2
           IF FOUND() ==  .T.         
                select Table4
                seek Table3->object_search_3
                IF FOUND() ==  .T.         
                     DO WHILE Table3->object_search_3 == something
                           select data_view
                           append blank    
                            data_view-Field1 := Table3->Field1        
                            data_view-Field2 := Table3->Field2
                             ..............................................
                            data_view->FieldN := Table3->FieldN

                            data_view->( DBCOMIT() )
                            skip
                     ENDDO

                 ELSE
                    /* Else Commands  */
                 ENDIF     

           ELSE
                /* Else Commands  */
           ENDIF

     ELSE
           /* Else Commands  */
     ENDIF

ELSE
    /* Else Commands  */
ENDIF

[nguyenchiduc]

Below is a main table of data (bottom), looking from 4 different tables. Each table has hundreds of thousands to millions of records

The displayed information is always changing depending on the change of the main table.