topic-40839

Posts: 1195

Joined: Mon Oct 17, 2005 05:41 AM

1-to-many like Foxpro
Posted: 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

nageswaragunupudi

Posts: 10733

Joined: Sun Nov 19, 2006 05:22 AM

Re: 1-to-many loke Foxpro
Posted: Thu Sep 16, 2021 02:10 PM

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

Regards

G. N. Rao.

Hyderabad, India

Marc Vanzegbroeck

Posts: 1195

Joined: Mon Oct 17, 2005 05:41 AM

Re: 1-to-many loke Foxpro
Posted: Thu Sep 16, 2021 02: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...

James Bott

Posts: 4840

Joined: Fri Nov 18, 2005 04:52 PM

Re: 1-to-many loke Foxpro
Posted: Thu Sep 16, 2021 07: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

Marc Vanzegbroeck

Posts: 1195

Joined: Mon Oct 17, 2005 05:41 AM

Re: 1-to-many loke Foxpro
Posted: Thu Sep 16, 2021 08: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...

James Bott

Posts: 4840

Joined: Fri Nov 18, 2005 04:52 PM

Re: 1-to-many loke Foxpro
Posted: 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

Marc Vanzegbroeck

Posts: 1195

Joined: Mon Oct 17, 2005 05:41 AM

Re: 1-to-many loke Foxpro
Posted: Fri Sep 17, 2021 06: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...

nageswaragunupudi

Posts: 10733

Joined: Sun Nov 19, 2006 05:22 AM

Re: 1-to-many loke Foxpro
Posted: Fri Sep 17, 2021 07: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:

Code (fw): Select all Collapse

#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

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

Regards

G. N. Rao.

Hyderabad, India

Marc Vanzegbroeck

Posts: 1195

Joined: Mon Oct 17, 2005 05:41 AM

Re: 1-to-many loke Foxpro
Posted: Fri Sep 17, 2021 09:29 AM

This is a very,very simple example.

This is the configuration to document.

This is the main database with the inputs

This is the child database with all the connection

This is the result I want to generate

They want to see to what output an input is connected.

Marc Venken

Posts: 1487

Joined: Tue Jun 14, 2016 07:51 AM

Re: 1-to-many loke Foxpro
Posted: Fri Sep 17, 2021 09: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.08 with Harbour

Marc Vanzegbroeck

Posts: 1195

Joined: Mon Oct 17, 2005 05:41 AM

Re: 1-to-many loke Foxpro
Posted: 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 ?

nageswaragunupudi

Posts: 10733

Joined: Sun Nov 19, 2006 05:22 AM

Re: 1-to-many like Foxpro
Posted: Fri Sep 17, 2021 10:36 AM

Code (fw): Select all Collapse

#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

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

Regards

G. N. Rao.

Hyderabad, India

nageswaragunupudi

Posts: 10733

Joined: Sun Nov 19, 2006 05:22 AM

Re: 1-to-many like Foxpro
Posted: Fri Sep 17, 2021 03:29 PM

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

Result:

Regards

G. N. Rao.

Hyderabad, India

Marc Vanzegbroeck

Posts: 1195

Joined: Mon Oct 17, 2005 05:41 AM

Re: 1-to-many like Foxpro
Posted: Fri Sep 17, 2021 09: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

nageswaragunupudi

Posts: 10733

Joined: Sun Nov 19, 2006 05:22 AM

Re: 1-to-many like Foxpro
Posted: Fri Sep 17, 2021 11:00 PM

Please try this:

Code (fw): Select all Collapse

#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

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

Regards

G. N. Rao.

Hyderabad, India

FiveTech Support Forums

Continue the discussion