Creating multiple child records from a list

Problem
You want to create child records from a list of values in a set of parent records

Solution
Create a loop-within-a-loop FileMaker script; use the power of indexed variables

Download LoopInLoop.zip

Example
//create child records from lists of values example
//by Ernest Koe, The Proof Group LLC, April 18, 2007

Go to Layout [ “Parent” (Parent) ]
Go to Record/Request/Page [ First ]

//Loop through our parent records, store the parentid in a variable

Loop

    //store the parentid in a variable while looping through each parent record
    Set Variable [ $parentid; Value:Parent::parentid ]

    //$n is just a counter to help us loop through child values
    Set Variable [ $n; Value:1 ]

    //$itemcount is the total number of child values
    Set Variable [ $itemCount; Value:ValueCount ( Parent::childvalues ) ]

    //While we are INSIDE the parent loop
    //do a loop through the child values, storing each item the $childid variable

//the inner loop to store the child values

    Loop

        //the trick is to store each child id in a repetition of the variable!
        Set Variable [ $childid[$n]; Value:GetValue ( Parent::childvalues ; $n ) ]

        Set Variable [ $n; Value:$n + 1 ]
        Exit Loop If [ $n  > $itemCount ]
    End Loop

Go to Layout [ “Child” (Child) ]

//inner loop that creates a new record for each value in childid[]

//$i is another counter
Set Variable [ $i; Value:1 ]

    Loop
        New Record/Request
        Set Field [ Child::parentid; $parentid ]
        Set Field [ Child::childid; $childid[$i] ]
        Set Variable [ $i; Value:$i+1 ]
        Exit Loop If [ $i   ≥   $n ]
    End Loop

    Go to Layout [ “Parent” (Parent) ]
    Go to Record/Request/Page [ Next; Exit after last ]
End Loop

Discussion
The crux of this technique to is realize that you can store and access data in variable repetitions; we can set childID[1] = "a", childID[2] to "b", childID[3] to "c" and so forth. The beauty of variables is that you can set their indexes (the repetition numbers of the variable) with the value of another variable, such as a counter that is incremented while in a loop.

Comments

[Søren Dyhr]

I tried to make the swiching back and forth between the two layouts, one single time - guess what there is no change in speed at all, we are made to believe that the metaphor actually works mechanicly - but it doesn't!

This point is what we all know, it's not as much the algorithmic measures that count as it's the naked creation of a record. Oldfasion imports between bypasses the things going on when a record is created - are by all means the fastes method for this task, so as long as the solution isn't shared with IWP or CWP would I use the import method.

By this must I conclude that the repeating feature of the variable, more is a measure to keep the developers mind straight, than a powerfull tool in it self ...they are clumsy to set since Extend( doesn't work with them ...yet????

--sd

[ErnestKoe]

No recursion needed I think but instead of storing each childvalue in variable repetitions, I store it them as an old-fashion ¶ separated list. So, on the aggregate pass through it would just be a matter of writing parentid to $parentid[$n] and childvalues to $childvalues[$n] for however many [$n] records there are.

After collecting these records, we create a record for each $parentid[$n], GetValue($childvalues[$n] ; $i) for all $i items in $childvalues[$n].

-ek

[Søren Dyhr]

I tested my own suggestion, and no there were no significant difference to notice. But when you write you aggregate before flipping the childside, does it include the use of some custom functions of the recursive type. But here are we're on shaky ground ...since the larger the found set is, the worse is the thru-put!!!! List( which might prove a little better than recursive GetNth('s can unfortunately only be used over a relation.

I tried out with 4000 records which is dupes of your 3 original ones, but nothing beats splitting your list with:

GetValue ( Extend ( childvalues ) ; Get ( CalculationRepetitionNumber ) )

...in a calc'field and then import between tables, it takes only 11 seconds, while you original suggestion accomplish the same in a little beyond 3 and a half minute on my dozy old iBook G4.

--sd

[ErnestKoe]

Interesting question. Let me know if you see any performance improvements. There are other ways to optimize this but I think the biggest drag is the layout-flipping. In production work, I actually aggregate everything first, then switch layouts (context) once to write everything back out.

[Søren Dyhr]

I wondered if it wouldn't be a tad, faster if you made the foreign key an autoenter, instead of using a script to do so - since you use a local variable will it only live as long as the script lasts?

--sd