1-to-many like Foxpro

1-to-many like Foxpro

Postby Marc Vanzegbroeck » Thu Sep 16, 2021 12:34 pm

Hi,

Does the exist a command like 'SET SKIP TO ' like in foxpro?

If we browse in the main database, the record stays the same in the main database, and only the child database is skipping for the number of records that match the relation with the main database.

I saw something like SCOPED relation, but that is not the same as the 1-to-many command in Foxpro
http://forums.fivetechsupport.com/viewtopic.php?p=221800&sid=3c2b29d525eb35873bcdbb3792cc56dc&sid=25c46c31fe1e767d08ae1588e662fc02#p221800
Last edited by Marc Vanzegbroeck on Fri Sep 17, 2021 10:27 am, edited 1 time in total.
Regards,
Marc

FWH32+xHarbour | FWH64+Harbour | BCC | DBF | ADO+MySQL | ADO+MariaDB | ADO+SQLite
Marc Vanzegbroeck
 
Posts: 1159
Joined: Mon Oct 17, 2005 5:41 am
Location: Belgium

Re: 1-to-many loke Foxpro

Postby nageswaragunupudi » Thu Sep 16, 2021 2:10 pm

Do you want two or more child browses for one parent browse?
Regards

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

Re: 1-to-many loke Foxpro

Postby Marc Vanzegbroeck » Thu Sep 16, 2021 2:48 pm

Nages,

The purpose is to 'export' an excel with all the colums. So the colums from the parent, en those of the child.
If the parent have 5 child-records, there will 5 lines. Those of the parent will be the same, only the child fields will be different
The problem is the child database have also a 1 child, also that one have a child...
Sometimes 50 childs....

I know that the excel file will be large, but I need it in excel.
The custumer need it for engineering purpose...
Regards,
Marc

FWH32+xHarbour | FWH64+Harbour | BCC | DBF | ADO+MySQL | ADO+MariaDB | ADO+SQLite
Marc Vanzegbroeck
 
Posts: 1159
Joined: Mon Oct 17, 2005 5:41 am
Location: Belgium

Re: 1-to-many loke Foxpro

Postby James Bott » Thu Sep 16, 2021 7:14 pm

Create a temp database in the format you need.

Then write some code to post all the data you need into the temp database.

Then open the temp database and export it to Excel.
FWH 18.05/xHarbour 1.2.3/BCC7/Windows 10
User avatar
James Bott
 
Posts: 4840
Joined: Fri Nov 18, 2005 4:52 pm
Location: San Diego, California, USA

Re: 1-to-many loke Foxpro

Postby Marc Vanzegbroeck » Thu Sep 16, 2021 8:22 pm

James,

That's what I did now, but it's very slow.
I was hoping then it was faster with a 1-to-many relation.
I did it in foxpro, and it was mush faster, but I want it in FWH...
Regards,
Marc

FWH32+xHarbour | FWH64+Harbour | BCC | DBF | ADO+MySQL | ADO+MariaDB | ADO+SQLite
Marc Vanzegbroeck
 
Posts: 1159
Joined: Mon Oct 17, 2005 5:41 am
Location: Belgium

Re: 1-to-many loke Foxpro

Postby James Bott » Thu Sep 16, 2021 11:58 pm

Marc,

Are you saying there could be 50 children of the parent, and each one of those could have 50 children? That would be 50*50 = 2,500 children of the top parent. Then I assume there could be another layer that could also have 50 children. That would be 50 * 2,500 = 125,000, Wow! And that is just for one parent!

Creating an Excel file containing all that data is extremely inefficient. That is why relational databases were born.

If it was my client, the first thing I would be asking is what are you trying to accomplish with this? It seems it would be much easier and more efficient to write a program to provide whatever answer the client needs (without storing hundreds of thousands of records, of mostly redundant data).

Looking at hundreds of thousands of rows in Excel would make my eyes bleed...
FWH 18.05/xHarbour 1.2.3/BCC7/Windows 10
User avatar
James Bott
 
Posts: 4840
Joined: Fri Nov 18, 2005 4:52 pm
Location: San Diego, California, USA

Re: 1-to-many loke Foxpro

Postby Marc Vanzegbroeck » Fri Sep 17, 2021 6:17 am

James,

Not every child have 50 childeren.
There is 1 main database, with 1 child, that child have also 1 child, that one also....

It's a database with connections of function blocks of a process automation system (Honeywell).
In the the child database is a field with the 'From'-block and a filed with a 'To'-block.
The child is always the same table that is opened 50 times. With a relation between the 'from' and the 'to' field. It's an ERDB-type-database.

Depending of the complexity of the control, there can be 50 connections after each other.
Sometimes only 3, like an imput block, a controller block and an output block.
But sometimes there are also interlocks, logic,... in between



James Bott wrote:Marc,

Are you saying there could be 50 children of the parent, and each one of those could have 50 children? That would be 50*50 = 2,500 children of the top parent. Then I assume there could be another layer that could also have 50 children. That would be 50 * 2,500 = 125,000, Wow! And that is just for one parent!

Creating an Excel file containing all that data is extremely inefficient. That is why relational databases were born.

If it was my client, the first thing I would be asking is what are you trying to accomplish with this? It seems it would be much easier and more efficient to write a program to provide whatever answer the client needs (without storing hundreds of thousands of records, of mostly redundant data).

Looking at hundreds of thousands of rows in Excel would make my eyes bleed...
Regards,
Marc

FWH32+xHarbour | FWH64+Harbour | BCC | DBF | ADO+MySQL | ADO+MariaDB | ADO+SQLite
Marc Vanzegbroeck
 
Posts: 1159
Joined: Mon Oct 17, 2005 5:41 am
Location: Belgium

Re: 1-to-many loke Foxpro

Postby nageswaragunupudi » Fri Sep 17, 2021 7:50 am

Unless we are very clear with your requirements, we can not suggest what is appropriate for you.

We can only suggest some approaches.

We have created three tables:
Image

Code: Select all  Expand view
#include "fivewin.ch"

REQUEST DBFCDX

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

function Main()

   field COUNTRY,REGION
   local aData

   CreateTables()

   USE REGION NEW VIA "DBFCDX"
   SET ORDER TO TAG CODE
   GO TOP

   USE COUNTRY NEW VIA "DBFCDX"
   SET ORDER TO TAG CODE
   SET RELATION TO REGION INTO REGION
   GO TOP

   USE CITY NEW VIA "DBFCDX"
   SET ORDER TO TAG COUNTRY
   SET RELATION TO COUNTRY INTO COUNTRY
   GO TOP

   SELECT CITY
   FW_DbfToExcel( { "REGION->REGION AS REGION","COUNTRY->COUNTRY AS CONTRY", "CITY", "CODE AS AIRPORT" } )

return nil

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

static function CreateTables()

   local aRegion  := { { "EU", "Europe" }, { "NA", "North America" } }
   local aCountry := { { "SP", "EU", "Spain" }, { "IT", "EU", "Italy" }, ;
                       { "US", "NA", "USA" }, { "CA", "NA", "Canada" } }

   local aCity    := { { "MAD", "SP", "Madrid" }, { "AGP", "SP", "Málaga" }, ;
                       { "TRN", "IT", "Turin"  }, { "VCE", "IT", "Venice" }, ;
                       { "JFK", "US", "New York"},{ "BOS", "US", "Boston" }, ;
                       { "YOW", "CA", "Ottawa" }, { "YUL", "CA", "Montriel" } }


   DBCREATE( "REGION.DBF", {{"CODE","C",2,0},{"REGION","C",15,0}}, "DBFCDX", .T., "TMP" )
   FW_ArrayToDbf( aRegion )
   FW_CdxCreate()
   CLOSE TMP

   DBCREATE( "COUNTRY.DBF", {{"CODE","C",2,0},{"REGION","C",2,0},{"COUNTRY","C",15,0}}, "DBFCDX", .T., "TMP" )
   FW_ArrayToDbf( aCountry )
   FW_CdxCreate()
   CLOSE TMP

   DBCREATE( "CITY.DBF", {{"CODE","C",3,0},{"COUNTRY","C",2,0},{"CITY","C",15,0}}, "DBFCDX", .T., "TMP" )
   FW_ArrayToDbf( aCity )
   FW_CdxCreate()
   CLOSE TMP

return nil

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


Image
Regards

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

Re: 1-to-many loke Foxpro

Postby Marc Vanzegbroeck » Fri Sep 17, 2021 9:29 am

This is a very,very simple example.

This is the configuration to document.
Image

This is the main database with the inputs
Image

This is the child database with all the connection
Image

This is the result I want to generate
Image

They want to see to what output an input is connected.
Regards,
Marc

FWH32+xHarbour | FWH64+Harbour | BCC | DBF | ADO+MySQL | ADO+MariaDB | ADO+SQLite
Marc Vanzegbroeck
 
Posts: 1159
Joined: Mon Oct 17, 2005 5:41 am
Location: Belgium

Re: 1-to-many loke Foxpro

Postby Marc Venken » Fri Sep 17, 2021 9:37 am

At this point (Current version) it is still so that we can only have 1 parent to 1 child relation and not 1 parent to 2/3 childs ?
Marc Venken
Using: FWH 23.04 with Harbour
User avatar
Marc Venken
 
Posts: 1425
Joined: Tue Jun 14, 2016 7:51 am
Location: Belgium

Re: 1-to-many loke Foxpro

Postby Marc Vanzegbroeck » Fri Sep 17, 2021 10:26 am

You have to add ADDITIVE for the relation to the second child

Marc Venken wrote:At this point (Current version) it is still so that we can only have 1 parent to 1 child relation and not 1 parent to 2/3 childs ?
Regards,
Marc

FWH32+xHarbour | FWH64+Harbour | BCC | DBF | ADO+MySQL | ADO+MariaDB | ADO+SQLite
Marc Vanzegbroeck
 
Posts: 1159
Joined: Mon Oct 17, 2005 5:41 am
Location: Belgium

Re: 1-to-many like Foxpro

Postby nageswaragunupudi » Fri Sep 17, 2021 10:36 am

Code: Select all  Expand view
#include "fivewin.ch"

REQUEST DBFCDX

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

function Main()

   field OUT,FTO
   local aData    := {}
   local aRow

   CreateTables()

   USE MAIN NEW SHARED VIA "DBFCDX"
   SET ORDER TO TAG NAME
   GO TOP

   do while !MAIN->( Eof() )
      aRow  := { MAIN->NAME, MAIN->OUT }
      GetConnections( aData, aRow )
      MAIN->( DBSKIP( 1 ) )
   enddo

   XBROWSER aData

return nil

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

static function GetConnections( aData, aRow )

   local cSeek    := ATail( aRow )
   local a
   local cAlias

   USE CHILD NEW ALIAS ( cAlias := cGetNewAlias( "CH" ) ) SHARED VIA "DBFCDX"
   SET ORDER TO TAG FROM
   if ( cAlias )->( DBSEEK( cSeek ) )
      do while ( cAlias )->FROM == cSeek
         a  := AClone( aRow )
         AAdd( a, ( cAlias )->FTO )
         GetConnections( aData, a )
         ( cAlias )->( DBSKIP( 1  ))
      enddo
   else
      AAdd( aData, aRow )
   endif
   ( cAlias )->( DBCLOSEAREA() )

return aRow

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

static function CreateTables()

   local aMain := {{"INPUT1","ORA"},{"INPUT1","NANDA"},{"INPUT2","ORA"},{"INPUT3","NANDA"},{"INPUT4","NANDA"},{"INPUT5","ONDELAYA"}}
   local aChild:= {{"ORA","OUT1"},{"ORA","XORA"},{"NANDA","XORA"},{"ONDELAYA","RTRIGA"},{"XORA","OUT2"},{"RTRIGA","OUT3"}}

   DBCREATE( "MAIN.DBF", {{"NAME","C",10,0},{"OUT","C",10,0}}, "DBFCDX", .T., "TMP" )
   FW_ArrayToDbf( aMain )
   FW_CdxCreate()
   CLOSE TMP

   DBCREATE( "CHILD.DBF", {{"FROM","C",10,0},{"FTO","C",10,0}}, "DBFCDX", .T., "TMP" )
   FW_ArrayToDbf( aChild )
   FW_CdxCreate()
   CLOSE TMP

//   XBROWSER "MAIN.DBF"
//   XBROWSER "CHILD.DBF"

   CLOSE DATA

return nil

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


Image
Regards

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

Re: 1-to-many like Foxpro

Postby nageswaragunupudi » Fri Sep 17, 2021 3:29 pm

Now the same program works for more levels.
Let us change the child table as
Image

Result:
Image
Regards

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

Re: 1-to-many like Foxpro

Postby Marc Vanzegbroeck » Fri Sep 17, 2021 9:50 pm

It' working fine.

The only problem is when there is a backward connection. Then it stays in a loop.
There must be a seek if than block is already connected before in that line, and stop by adding ex. (LOOP) into the record. If there is the text (LOOP), the seek doesn't find any connection like that

Image
Regards,
Marc

FWH32+xHarbour | FWH64+Harbour | BCC | DBF | ADO+MySQL | ADO+MariaDB | ADO+SQLite
Marc Vanzegbroeck
 
Posts: 1159
Joined: Mon Oct 17, 2005 5:41 am
Location: Belgium

Re: 1-to-many like Foxpro

Postby nageswaragunupudi » Fri Sep 17, 2021 11:00 pm

Please try this:
Code: Select all  Expand view
#include "fivewin.ch"

REQUEST DBFCDX

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

function Main()

   field OUT,FTO
   local aData    := {}
   local aRow

   CreateTables()

   USE MAIN NEW SHARED VIA "DBFCDX"
   SET ORDER TO TAG NAME
   GO TOP

   do while !MAIN->( Eof() )
      aRow  := { MAIN->NAME, MAIN->OUT }
      GetConnections( aData, aRow )
      MAIN->( DBSKIP( 1 ) )
   enddo

   XBROWSER aData

return nil

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

static function GetConnections( aData, aRow )

   local cSeek    := ATail( aRow )
   local a
   local cAlias

   USE CHILD NEW ALIAS ( cAlias := cGetNewAlias( "CH" ) ) SHARED VIA "DBFCDX"
   SET ORDER TO TAG FROM
   if ( cAlias )->( DBSEEK( cSeek ) )
      do while ( cAlias )->FROM == cSeek
         a  := AClone( aRow )
         if AScan( a, { |x| x == ( cAlias )->FTO } ) > 0
            AAdd( a, ( cAlias )->FTO )
            AAdd( a, "[LOOP]" )
            AAdd( aData, a )
         else
            AAdd( a, ( cAlias )->FTO )
            GetConnections( aData, a )
         endif
         ( cAlias )->( DBSKIP( 1  ))
      enddo
   else
      AAdd( aData, aRow )
   endif
   ( cAlias )->( DBCLOSEAREA() )

return aRow

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

static function CreateTables()

   local aMain := {{"INPUT1","ORA"},{"INPUT1","NANDA"},{"INPUT2","ORA"},{"INPUT3","NANDA"},{"INPUT4","NANDA"},{"INPUT5","ONDELAYA"}}
   local aChild:= {{"ORA","OUT1"},{"ORA","XORA"},{"NANDA","XORA"},{"ONDELAYA","RTRIGA"},;
                   {"XORA","OUT2"},{"RTRIGA","OUT3"},{"OUT3","OUT4"},{"OUT3","OUT5"},{"OUT5","RTRIGA"}}

   DBCREATE( "MAIN.DBF", {{"NAME","C",10,0},{"OUT","C",10,0}}, "DBFCDX", .T., "TMP" )
   FW_ArrayToDbf( aMain )
   FW_CdxCreate()
   CLOSE TMP

   DBCREATE( "CHILD.DBF", {{"FROM","C",10,0},{"FTO","C",10,0}}, "DBFCDX", .T., "TMP" )
   FW_ArrayToDbf( aChild )
   FW_CdxCreate()
   CLOSE TMP

//   XBROWSER "MAIN.DBF"
   XBROWSER "CHILD.DBF"

   CLOSE DATA

return nil

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

Image
Regards

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


Return to FiveWin for Harbour/xHarbour

Who is online

Users browsing this forum: Google [Bot] and 48 guests