4 Replies Latest reply on Apr 12, 2011 3:38 PM by Derrenger

    Script to Break Field List into Individual Records

    Derrenger

      Title

      Script to Break Field List into Individual Records

      Post

      I am importing information into FMP 11 (using a mac OS 10.5) from Excel, and have one Excel cell with multiple items in it (each separated by a soft return). I understand that I need to create an intermediary field (Table1::List) to handle this information as a whole (and have gotten this far), but now I would like to create a script that enables me to import each line from this field into its own record in a separate table (Table2::LineItem). I have an ID field (Table1::ID) for each record in the original table, and would like to grab this for reference as well in the new table (Table2::ID), so that I can bring this information back into the original table (Table1) in the form of a portal (which I know how to do).

      Any scripting advice you might have would be greatly appreciated.

      Thank you in advance.

        • 1. Re: Script to Break Field List into Individual Records
          philmodjunk

          Immediately after import, your found set will consist of the records you have just imported. Your script can use a pair of nested loops to step through the found set of records and through each value in the field to create records in the related table.

          Freeze Window
          Loop
             Set Variable [$ID ; value: Table1::ID]
             Set Variable [$List ; value: Table1::List]
             Loop
                Set Variable [$I ; value: $I + 1 ]
                Exit Loop If [$I > valuecount ( $List ) ]
                Go To layout [Table2]
                New Record/Request
                Set Field [Table2::ID ; $ID]
                Set Field [Table2::LineItem ; GetValue ( $List ; $ I ) ]
              End Loop
              Go To Layout [Table1]
              Go To Next Record [next ; exit after last]
          End Loop

          • 2. Re: Script to Break Field List into Individual Records
            Derrenger

            Hey PhilModJunk,

            Thanks so much for the quick response (as usual!)... Per your advice, I have built the following script:

            Freeze Window
            Loop
               Set Variable [$ID ; value: Table1::ID]
               Set Variable [$List ; value: Table1::List]
               Loop
                  Set Variable [$I ; value: $I + 1 ]
                  Exit Loop If [$I > valuecount ( $List ) ]
                  Go To layout [Table2]
                  New Record/Request
                  Set Field [Table2::ID ; $ID]
                  Set Field [Table2::LineItem ; GetValue ( $List ; $ I ) ]
                End Loop
                Go To Layout [Table1]
                 Go To Record/Request/Page [next ; exit after last]   //I couldn't find "go to next record"
             End Loop

            When I ran the script, it imported the ID and the two entries on my List for the first record (just as I wanted), and it also cycled through all of the existing records, but didn't update/import any of the Lists/LineItems after the first record.

            Am I missing a step or detail or something?

            Thanks again!

            • 3. Re: Script to Break Field List into Individual Records
              philmodjunk

              You correctly found the right step and options for Go To record.

              Oops, now I see the error. I didn't set $I back to 1 after the first record. Embarassed

              Insert

              Set Variable [$I ; Value: 0]

              Just above the inner Loop step.

              • 4. Re: Script to Break Field List into Individual Records
                Derrenger

                BINGO!

                Now it works like a charm!

                Thanks again PhilModJunk, you've done it again... I appreciate all of your sage advice.

                cheers!