Jump to content

Scripted creation of related records from a list?


HALBURN
 Share

This topic is 2799 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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?

Link to comment
Share on other sites

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 …

Link to comment
Share on other sites

This topic is 2799 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.