2 Replies Latest reply on May 26, 2012 11:41 AM by kayakjunkie

    Using Variables to Select Field / Insert File



      Using Variables to Select Field / Insert File


      Is there a way to use a variable to assign the focus to a specific field & a separate variable to insert a file?

      I've looped through a found set of records with paths to files I want to insert into container fields.

      /Users/Jesse/Dropbox/Databases/Portfolio Center/Source Files/Archive/20081002.EXE

      /Users/Jesse/Dropbox/Databases/Portfolio Center/Source Files/Archive/20081002.TRD

      /Users/Jesse/Dropbox/Databases/Portfolio Center/Source Files/Archive/20081002.POS

      Using a sub-script I've assigned each file in the found set to a variable ($$InsertFile1, $$InsertFile2, $$InsertFile3, etc.)

      #Script set-up

      Set Variable [$FoundSet ; Value:Get (FoundCount)]

      Go to Layout ["Paths" (Paths)]

      Set Variable [$k ; Value:$k + 1]

      #Script body

      Go to Record [No dialog ; $k]

      Set Variable [$$Insert1 ; Value:Paths::filePaths]

      If [$k = $FoundSet]

        Exit Script[]

      End If

      Set Variable [$k ; Value:$k + 1]


      Repeat for each record in the Found Set.

      For each record in the set, I get the file extension and assign to a variable.

      Set Variable [ext1; Value:Right ($$Insert1 ; 3)]

      Set Variable [ext2; Value:Right ($$Insert2 ; 3)]


      I then create a path for each record to be used as part of the Insert File [] script step.

      Set Variable [$InsertFile1; Value:Right ($$Insert1 ; Length ($$Insert) - Position ($$Insert1 ; "Source ; 0 ; 1) + 1)]

      This creates a variable ... $InsertFile1 as the string "Source Files/Archive/20081002.EXE"

      Since I don't know what kind of file will end up in each variable, I've written a case statement that assigns a value to a variable

      which indicates which field in the Source Files table should receive the file.


      Go to Layout ["Source Files" (source files)]

      Set Variable [$field1; Value:

      Case (
      $ext1 = "EXE" ; $field1 = "archive" ;
      $ext1 = "ZIP" ; $field1 = "archive" ; 
      $ext1 = "TRD" ; $field1 = "demographics" ;
      $ext1 = "INI" ; $field1 = "tansactions" ;
      $ext1 = "TRN" ; $field1 = "transactions" ;
      $ext1 = "POS" ; $field1 = "positions" ;
      $ext1 = "SEC" ; $field1 = "securities" ;
      $ext1 = "PRI" ; $field1 = "prices" ; 
      $ext1 = "CBL" ; $field1 = "reconciliation"
      This returns $field1 = "archive"

      I want to use this variable to select the field ... Go to Field [Source Files::$field1]
      Can I do this?
      The other alternative that I see would be to Go to Field [] script step, loop through all of the fields and do a search on each
      of the variables ($InsertFile1, $InsertFile2, InsertFile3, etc.) to find a string with the correct extension for the field.
      However, this approach seems to have difficulties of its own.  Perhaps assigning each path to a temp table with the
      second field does a calc based on the file name with a Lookup to pick-up the field name for the path?  Not at all sure how to
      set-up something like that.
      The next step is to insert the file.  Preferably something like
      Insert File [Source Files::$field1; :"&$InsertFile1"]
      The problem here is that I am then presented with a dialog box to select a file even though the dialog options box is left

        • 1. Re: Using Variables to Select Field / Insert File

          If you use the "name" box in the Inspector's position tab to give each field an object name, you can use go to object to put the focus on a specific field present on your layout. Then an insert step that does not specify a target field can insert into that field.

          But your entire process seems needlessly complex.

          Why build a list of file paths in a series of variables? Why not combine the two steps in to one loop?

          In otherwords, get the Filepath, put it where it needs to be, get the next filepath and put it where it needs to be. If you are dealing with two different found sets, you can set them up on different layouts and your script can switch back and forth between them as needed.

          And you can simplify looping through a found set of records with these steps:

          Go To Record/Request/Page [First]


            Go To Record/Request/Page [Next ; exit after last]
          End Loop

          And I suspect you could also simplify this by having just one set of fields in a related table that includes a "label" or "type" field to identify the entry as

          "archive" , "demographics" , "tansactions" , "positions" , "securities" , "prices" , or "reconciliation"
          • 2. Re: Using Variables to Select Field / Insert File

            That guidance was of tremendous help.  I realized that I needed to normalize the table.  At that point I was able to set-up the Lookup and dramatically cut back the amount of code.