KNOWLEDGE BASE
Log In    |    Knowledge Base    |    4D Home
Tech Tip: How to replicate data from a subtable to a a new table
PRODUCT: 4D | VERSION: 6.5 | PLATFORM: Mac & Win
Published On: November 20, 2001
Your current browser may not allow you to download the asset. We recommend Mozilla Firefox or Google Chrome.
Log In

At some point in the evolution of a database, you may want to stop using a subtable field and instead use a related table. At this point, you need to replicate the subtable data to the related table. The following piece of code implements such a change in one pass:

ALL RECORDS([Table_with_Subtable])
For ($i;1;Records in selection([Table_with_Subtable]))
LOAD RECORD([Table_with_Subtable])
ALL SUBRECORDS([Table_with_Subtable]Field3)
For ($j;1;Records in subselection([Table_with_Subtable]Field3))
CREATE RECORD([Target_Table])
`having a key field is required to establish the relation
[Target_Table]Field1:=[Table_with_Subtable]Key_Field
`below is the actual data replication
[Target_Table]Field2:=[Table_with_Subtable]Field3'Field1
[Target_Table]Field3:=[Table_with_Subtable]Field3'Field2
SAVE RECORD([Target_Table])
NEXT SUBRECORD([Table_with_Subtable]Field3)
End for
UNLOAD RECORD([Table_with_Subtable])
NEXT RECORD([Table_with_Subtable])
End for

For each record of the original table, the code loops through all the subrecords and creates a matching record in the target table. Make sure you assign a key field as well in order to establish a relation to the original table.