I have a database with several thousand records and the following structure:

 

Three fields in PARENT table:

id: 1

parent_name: Produce

list_of_children: apple,orange,grape,banana

 

Three fields in CHILDREN table:

id:

fk_id_parent:

child_name:

 

What is the most efficient way to convert the comma separated text in the list_of_children field of the PARENT table into separate related records in the CHILDREN table? I assume that using a looping script is the best way. How would you set up the relationship and how would you loop through the creation of each child?

Basically, it works like this:

Go to Layout ( PARENTS ( Parents ) ]
Go to Record/Request [ first ]
Loop
  # outer loop to walk through parent records
  If [ not IsEmpty ( PARENT::list_of_children ) ]
    Set Variable [ $listOfChildrenNames ; Substitute ( PARENT::list_of_children ; "," ; ¶ ) ]
    Set Variable [ $countChildren ; ValueCount ( $listOfChildrenNames ) ]
    Set Variable [ $parentID ; PARENT::id ]
    Go to Layout [ CHILDREN ( Children ) ]
    Set Variable [ $childrenCounter ; 0 ]
    Loop
    # inner loop to create children by parent
      Exit Loop If [ Let ( $childrenCounter = $childrenCounter + 1 ; $childrenCounter > $countChildren ) ]
      New Record/Request
      Set Field [ CHILDREN::fk_id_parent ; $parentID ]
      Set Field [ CHILDREN::child_name ; GetValue ( $listOfChildrenNames ; $childrenCounter ) ]
    End Loop
    Go to Layout [ original ]
  End If
  Go to Record/Request ( next ; exit after last )
End Loop

How would you set up the relationship

 

This works without an existing relationship; you could also use an existing relationship via PARENT::id = CHILDREN::fk_id_parent and use a portal for record creation, in which case you wouldn't have to set the foreign key.

 

You can make processing the entire PARENT found set more efficient by avoiding the layout switches; with FM 13, you could build two lists of the fields id and list_of_children by using the “List of“ Summary field; then maintain a parent counter in the outer loop to pull the children list and id for each parent record to use in the inner loop.

 

Make sure you're only processing parents with a children list, or the lists will get out of sync …

Thanks eos.