1-to-many like Foxpro

[Marc Vanzegbroeck]

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]

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

[Marc Vanzegbroeck]

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]

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.

[Marc Vanzegbroeck]

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]

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...

[Marc Vanzegbroeck]

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

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]

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: Select all | Expand

#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

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

[Marc Vanzegbroeck]

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]

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 Vanzegbroeck]

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

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]

Code: Select all | Expand

#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

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

[nageswaragunupudi]

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


Result:

[Marc Vanzegbroeck]

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]

Please try this:

Code: Select all | Expand

#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

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