14 Replies Latest reply on Dec 23, 2014 11:42 AM by philmodjunk

    Stepping through variables

    MarkGores

      Title

      Stepping through variables

      Post

      I am trying to parse some data from an old file into a new table.  There are currently several fields a, b, c, d, e, and f.  each had 17 versions, i.e. a1 - a17

      I want to separate them into separate records so that one record has a1, b1,c1, etc the next a2, b2, c2, etc and so on. 

      I tried setting each field to a variable like $a1, then going to the new table, set $count =1, create a new record, then set a to $a$count.  Thinking I could loop through the 17 variations rather than having 17 New Record steps with each of the variable combinations.

      Is there a way to step through these variable names in a loop?

        • 1. Re: Stepping through variables
          jbante

          Rather than using variable names $a1, $a1, etc. use repeating variables: $a[1], $a[2], etc. This way, you can loop through them with your loop iteration variable: $a[$iteration].

          • 2. Re: Stepping through variables
            Korry

            Consider looping through a variable that's a list. If your original field has several values (return separated lines), you can easily create records for them similar to this sample:

            1. Set Variable [ $list; Value:List ( 0;1;2;3;4;5;6;7;8;9 ) ]
            Loop
                2. Exit Loop If [ ValueCount ( $list ) = 0 ]
                3. New Record/Request
                   Set Field [ NewTable::NewField; GetValue ( $list ; 1 ) ]
                4. Set Variable [ $list; Value:MiddleValues ( $list ; 2 ; 10^9 ) ]
            End Loop

            1. Setup your list variable outside the loop. This could be a field with return separated values, a composited list from various sources, whatever you have. If your values have line breaks within, you could substitute the breaks with a string, run the loop, and substitute the strings with a break to restore the original value.

            2. Exit the loop if the count of the values in your list is zero. We're removing the first value after we create a record for it, so the list continues to get smaller until it's empty.

            3. New record + set the new field with the first value in the list variable.

            4. Redefine the list variable to include the second value thru the end (since 10^9 is much larger than your list, FileMaker will just include every value to the end). I forget the official term for this kind of operation.

             

            • 3. Re: Stepping through variables
              MarkGores

              Figured I'd try attacking from the other way.  Just having one set of variables, $a, $b, $etc

              Then setting their values using the GetField("table::a" & $count), and stepping through the 17 records that way.

              Thanks for that repeating variable idea, Jeremy.  Will have to try that out.

              • 4. Re: Stepping through variables
                jbante

                While working with data in return-delimited lists is an important skill to develop in FileMaker, especially for passing data between scripts in parameters and results, it's a habit worth avoiding for within-script data handling. Retrieving values from return-delimited lists is slow compared to repeating variables, and it gets much slower as the size of the list gets bigger.

                Loop
                    Set Variable [$i; Value:$i + 1]
                    Exit Loop If [IsEmpty ( $a[$i] )]
                    ...
                End Loop

                If you time this with different size lists, you'll find it's roughly linearly proportional to the number of repeating variables/size of your list. It runs in O ( n ) time, in computer science terms. It works out this way because retrieving the value of $a[$i] takes roughly the same amount of time no matter what iteration you're on.

                If you instead loop over a return-delimited list:

                Set Variable [$numberOfValues; Value:ValueCount ( $list )]
                Loop
                    Set Variable [$i; Value:$i + 1]
                    Exit Loop If [$i > $numberOfValues]
                    Set Variable [$value; Value:GetValue ( $list ; $i )]
                    ...
                End Loop

                If you time this, or any variation on iterating over every value in a return-delimited list, you'll find that it's roughly proportional to the square of the size of the list. It runs in O ( n^2 ) time. Each call to GetValue ( $list ; $i ) is proportional to at least $i. It gets run $numberOfValues times, and each time takes slightly longer to run, eventually proportional to $numberOfValues itself. Korry's construction is a little different, but ultimately has the same performance characteristics, because the time to run MiddleValues ( $list ; 2 ; $anythingThatWillWorkHere ) will also be proportional to the size of $list, and run a number of times also proportional to the size of $list.

                • 5. Re: Stepping through variables
                  MarkGores

                  Actually this time I'm not working with a return delimited list, it is a routing sheet with 17 possible entries and 2 possible pages.  Each entry has 3 fields (a, b, c for page 1 and d, e, f for page 2).  So the original file had 102 fields to make up the two possible pages.  My desired result is to have each line from the old file as a separate record with the 3 fields a, b and c with d, e and f from the old file now being another record in the new file wit d=a, e=b and f=c, and having original specification type and part type fields.  So my end result will be a table that I can pull possible tasks up by selecting a specification and part type.

                  So I went -

                  Goto layout Table1

                  Goto Record/First

                  Loop

                  Set var $spec; Table1::spec

                  Set var $parttype; Table1::parttype

                  Set var $count; 1

                  Loop

                  Set var $a; GetField("Table1::a" & $count)

                  Set var $b; GetField("Table1::b" & $count) ** and so on through f

                  Goto Layout Table 2

                  New record/request

                  Set field Table2::spec ; $spec

                  Set field Table2::parttype ; $parttype

                  Set field Table2::a ; $a

                  Set field Table2::b ; $b

                  Set field Table2::c ; $c

                  NewRecord/request

                  Set field Table2::spec ; $spec

                  Set field Table2::parttype ; $parttype

                  Set field Table2::a ; $d

                  Set field Table2::b ; $e

                  Set field Table2::c ; $f

                  Set Var $counter ; $counter +1

                  Exit Loop If $counter = 17

                  End Loop

                  GoTo Layout Table1

                  Goto record/next ; exit after last

                  end loop

                  • 6. Re: Stepping through variables
                    philmodjunk

                    In a loop, you can use the same variables over and over again and use a loop counting variable inside the getField call to refer to a different field with each pass through the loop.

                    So I'd nest the loops and only use a much smaller set of variables.

                    • 7. Re: Stepping through variables
                      MarkGores

                      I don't know how I can get less than 6 variables in the loop for the six fields. 

                      That said, it did seem to work.  I ended up with about 90,000 records.  I set up a calculation field to combine

                      spec&a&b&c and when I do a find on that field I get about 57,000 duplicates.  Since I don't need more than 1 record with that value, I wrote a script that finds duplicates, sorts them, does a find on the combined field of the first record, omit the first record in that found set, then deletes the found records.  It is looping through that now and down to 81, 200 records ...

                      • 8. Re: Stepping through variables
                        philmodjunk

                        Well your example copied data from variable a$ to field a and from Variable d$ to field a. I'd use a$ both times but in different iterations of the same loop with a different loop counter value pointing to a different field from which to get the value the second time around.

                        • 9. Re: Stepping through variables
                          MarkGores

                          The original record has six fields, a through f, from which I am created two records in the new table, one where a, b and c are set to a, b and c and one where a, b and c are set to d, e and f of the old table.  And repeating that 17 times for each original record.

                          I guess I could have used just 3 variables and set 34 iterations of eachin one loop, then gone to the new table and looped through to create 34 records.  Then one big loop to do that for each record from the original table.  Will have to experiment with that ...

                          • 10. Re: Stepping through variables
                            philmodjunk

                            A GetField expression can calculate the letter part of the field name as well as the number. But it's all water under the bridge now...

                            • 11. Re: Stepping through variables
                              MarkGores

                              Will remember that for next time though  smiley

                               

                              It does take a while to perform a find, sort, find on 80,000 records.  Probably have to let this run overnight.

                              • 12. Re: Stepping through variables
                                philmodjunk

                                When it's a one time "batch fix", the time spent getting your script perfectly "optimized" can easily exceed the time saved by doing so. A "quick and dirty" script design that runs "slow but correct" is often the most efficient use of your time as a developer--but only when it's a one time deal.wink

                                • 13. Re: Stepping through variables
                                  MarkGores

                                  Is there a more efficient way to do this?   The script I'm running now is:

                                  Loop

                                  Perform Find [comb = ! ]

                                  Sort records [ comb ]

                                  Goto Record/First

                                  set var $f = Table2::comb

                                  Perform Find [comb = $f ]

                                  Go to Record/First

                                  Omit record

                                  exit loop if [get (foundcount) = 0]

                                  Delete all [found set]

                                  End Loop

                                  • 14. Re: Stepping through variables
                                    philmodjunk

                                    Why "table2" and not "table"?

                                    But I think we are already discussing this elsewhere

                                    Another way to filter out duplicates is to make a copy of the table, set a Unique values | Validate always field option on the "comb" field and then import all of your records into this new copy. The duplicates get filtered out during the import. But beware of returns inside that "comb" field. I would use substitute to replace them with another character such as the | character.