TDatabase: Relating Tables

TDatabase: Relating Tables

Postby nageswaragunupudi » Fri May 10, 2019 5:32 pm

All of us are familiar with setting relationships between tables, both for one-to-one relationship and master-child relationship.

One-to-one relationship:
SET RELATION TO <exp> INTO <other_table_alias>

One-to-many (parent-child) relationship:
SET RELATION TO <exp> INTO <child_table_alias> SCOPED

All may not be very familiar with setting relationships between two database objects using TDatabase. The intent of this post is to explain this in detail with examples and comparing with RDD level relations.

For the purpose of this discussion, we use \fwh\samples\customer.dbf and \fwh\samples\states.dbf. We know that the field "STATE" in customer.dbf corresponds to the field "CODE" in the states.dbf.

1) ONE-TO-ONE RELATIONSHIP:

Every record in customer.dbf has one corresponding record in states.dbf where customer-state = states->code.

Syntax for RDD & TDatabase
Code: Select all  Expand view  RUN

// RDD
SELECT CUSTOMER
SET RELATION TO STATE INTO STATES

// TDatabase
oCustomers:SetRelation( oStates, "STATE" )
 


This sample shows usage first for RDD and then TDatabase, achieving the same results:
Code: Select all  Expand view  RUN

#include "fivewin.ch"

REQUEST DBFCDX

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

function Main()

   RDDSETDEFAULT( "DBFCDX" )
   SET DELETED ON

   // Make sure the tables are indexed
   FERASE( "STATES.CDX" )
   USE STATES
   FW_CdxCreate()
   CLOSE STATES

   FERASE( "CUSTOMER.CDX" )
   USE CUSTOMER
   FW_CdxCreate()
   CLOSE CUSTOMER
   //

   // Now, the actual code
   RelateRDD_OneToOne()
   RelateTDatabase_OneToOne()

return nil

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

function RelateRDD_OneToOne()

   field STATE

   USE STATES NEW SHARED
   SET ORDER TO TAG CODE

   USE CUSTOMER NEW SHARED
   SET RELATION TO STATE INTO STATES
   GO TOP

   XBROWSER "CUSTOMER" COLUMNS "First AS FirstName", "City", "STATES->NAME AS StateName"

   CLOSE DATA

return nil

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

function RelateTDatabase_OneToOne()

   local oStates, oCustomers

   oStates     := TDataBase():Open( nil, "STATES", "DBFCDX", .t. )
   oStates:SetOrder( "CODE" )

   oCustomers  := TDataBase():Open( nil, "CUSTOMER", "DBFCDX", .t. )
   oCustomers:SetRelation( oStates, "STATE" )
   oCustomers:GoTop()

#if FW_VersionNo < 19040
   oStates:load()
   oCustomers:AddCol( "StateName", { || oStates:Load(), oStates:Name } )
#else
   oCustomers:AddCol( "StateName", { || oStates:Name } ) // Simplified in FWH1904
#endif

   XBROWSER oCustomers COLUMNS "First AS FirstName", "City", "StateName"

   oCustomers:Close()
   oStates:Close()

return nil

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


Image

ONE-TO-MANY RELATIONSHIP
(also called PARENT-CHILD/MASTER-CHILD)

Also called SCOPED RELATIONSHIP in DBF terminology.


In this case, one record in the master table corresponds to one or more records in the child table.

In our example, for every record in the states.dbf there are many records in the customer.dbf with the same state code. When we move from one record to another in the states.dbf, we want to retrieve only and all records in customer.dbf having the state code.

Syntax:
Code: Select all  Expand view  RUN

// RDD
SELECT STATES
SET RELATION TO CODE INTO CUSTOMER SCOPED

// TDatabase
oStates:SetRelation( oCustomers, "CODE", .T. ) // 3rd parameter .T. for scoped
 


This sample shows usage first for RDD and then TDatabase, achieving the same results:
Code: Select all  Expand view  RUN

#include "fivewin.ch"

REQUEST DBFCDX

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

function Main()

   RDDSETDEFAULT( "DBFCDX" )
   SET DELETED ON

   // Make sure the tables are indexed
   FERASE( "STATES.CDX" )
   USE STATES
   FW_CdxCreate()
   CLOSE STATES

   FERASE( "CUSTOMER.CDX" )
   USE CUSTOMER
   FW_CdxCreate()
   CLOSE CUSTOMER
   //

   // Now, the actual code
   RelateRDD_Scoped()
   RelateTDatabase_Scoped()

return nil

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

function RelateRDD_Scoped()

   field CODE

   local oDlg, oFont, oBrwStates, oBrwCust

   USE CUSTOMER NEW SHARED
   SET ORDER TO TAG STATE

   USE STATES NEW SHARED
   SET RELATION TO CODE INTO CUSTOMER SCOPED
   GO TOP

   DEFINE FONT oFont NAME "TAHOMA" SIZE 0,-14
   DEFINE DIALOG oDlg SIZE 800,600 PIXEL TRUEPIXEL FONT oFont

   @ 20,20 XBROWSE oBrwStates SIZE 250,-20 PIXEL OF oDlg ;
      DATASOURCE "STATES" AUTOCOLS ;
      CELL LINES NOBORDER

   WITH OBJECT oBrwStates
      :AutoFit()
      :bChange    := { || oBrwCust:GoTop() }
      //
      :CreateFromCode()
   END

   @ 20,250 XBROWSE oBrwCust SIZE -20,-20 PIXEL OF oDlg ;
      DATASOURCE "CUSTOMER" COLUMNS "FIRST","CITY","STATE" ;
      CELL LINES NOBORDER

   WITH OBJECT oBrwCust
      //
      :CreateFromCode()
   END

   ACTIVATE DIALOG oDlg CENTERED
   RELEASE FONT oFont

   CLOSE DATA

return nil

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

function RelateTDatabase_Scoped()

   local oStates, oCustomers
   local oDlg, oFont, oBrwStates, oBrwCust

   oCustomers  := TDataBase():Open( nil, "CUSTOMER", "DBFCDX", .t. )
   oCustomers:SetOrder( "STATE" )

   oStates     := TDataBase():Open( nil, "STATES", "DBFCDX", .t. )
   oStates:SetRelation( oCustomers, "CODE", .T. )
   oStates:GoTop()
   oCustomers:Load()

   DEFINE FONT oFont NAME "TAHOMA" SIZE 0,-14
   DEFINE DIALOG oDlg SIZE 800,600 PIXEL TRUEPIXEL FONT oFont

   @ 20,20 XBROWSE oBrwStates SIZE 250,-20 PIXEL OF oDlg ;
      DATASOURCE oStates AUTOCOLS ;
      CELL LINES NOBORDER

   WITH OBJECT oBrwStates
      :AutoFit()
      :bChange    := { || oBrwCust:GoTop() }
      //
      :CreateFromCode()
   END

   @ 20,250 XBROWSE oBrwCust SIZE -20,-20 PIXEL OF oDlg ;
      DATASOURCE oCustomers COLUMNS "FIRST","CITY","STATE" ;
      CELL LINES NOBORDER

   WITH OBJECT oBrwCust
      //
      :CreateFromCode()
   END

   ACTIVATE DIALOG oDlg CENTERED
   RELEASE FONT oFont

   oStates:Close()
   oCustomers:Close()

return nil

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


Image
Regards

G. N. Rao.
Hyderabad, India
User avatar
nageswaragunupudi
 
Posts: 10643
Joined: Sun Nov 19, 2006 5:22 am
Location: India

Re: TDatabase: Relating Tables

Postby Silvio.Falconi » Fri May 10, 2019 10:53 pm

THANKSSSSS
Since from 1991/1992 ( fw for clipper Rel. 14.4 - Momos)
I use : FiveWin for Harbour November 2023 - January 2024 - Harbour 3.2.0dev (harbour_bcc770_32_20240309) - Bcc7.70 - xMate ver. 1.15.3 - PellesC - mail: silvio[dot]falconi[at]gmail[dot]com
User avatar
Silvio.Falconi
 
Posts: 7063
Joined: Thu Oct 18, 2012 7:17 pm

Re: TDatabase: Relating Tables

Postby Marc Venken » Sat May 11, 2019 7:22 pm

Thank you very much for this guide !!

I can do thinks much easier if I do it this way...
Marc Venken
Using: FWH 23.04 with Harbour
User avatar
Marc Venken
 
Posts: 1434
Joined: Tue Jun 14, 2016 7:51 am
Location: Belgium

Re: TDatabase: Relating Tables

Postby Marc Venken » Fri May 28, 2021 2:34 pm

Can this also work with multiple dbf's linked ?

I believe that only 1 set relation to... scoped can be active at a time... (Not sure)

I want a one Xbrowse with 4 dbf linked by a product_id (See a line with product ID and prices from 4 different dbf's)

set relation to nofoto->id into ean scoped
set relation to nofoto->id into tododbf scoped

// Will only keep the last live...

Code: Select all  Expand view  RUN

   aVelden2 :=  { ;
   { "ID"       , "Id"      ,nil,  90 }, ;
   { "shoptrad" , "Shop"    ,nil,  35 }, ;
   { "naam"     , "Benaming",nil, 300 }, ;
   { "Prijs"    , "Verkoop",nil,   90 }, ;
   {  { |x| If( x == nil, tododbf->bruto, tododbf->bruto ) },"Lev_Bruto",nil,90 }, ;
   {  { |x| If( x == nil, ean->bruto, ean->bruto ) },"Ean_Bruto",nil,90 }, ;
   { "gewicht"  , "Gewicht" ,nil, 100 }, ;
   { "samenstel", "Samenstelling" ,nil, 150 }, ;
   { "fabrikan" , "Merk"    ,nil, 100 }, ;
   { "pagina"   , "Pag"     ,nil,  50 }, ;
   { "reflev"   , "RefLev"  ,nil,  70 }, ;
   { "memotxt"  , "Memotxt" ,nil, 250 }}


   @ 0,0 XBROWSE oBrw2 size -10,-10 PIXEL OF  oFld:aDialogs[ 2 ] font oFont ;
      DATASOURCE "nofoto";
      COLUMNS aVelden2;
      AUTOSORT CELL LINES NOBORDER FOOTERS

 
Marc Venken
Using: FWH 23.04 with Harbour
User avatar
Marc Venken
 
Posts: 1434
Joined: Tue Jun 14, 2016 7:51 am
Location: Belgium


Return to FiveWin for Harbour/xHarbour

Who is online

Users browsing this forum: No registered users and 69 guests