AnsweredAssumed Answered

Using Variables to Select Field / Insert File

Question asked by kayakjunkie on May 23, 2012
Latest reply on May 26, 2012 by kayakjunkie


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