6 Replies Latest reply on Nov 20, 2014 6:35 AM by jjfcpa

    Importing script step with a variable?

    jjfcpa

      I'm attempting to import a file using the Import Recrods script step. I'm also using Base Elements select file function to get the file I want to import from. No errors, but nothing is getting imported. Here are my script steps:

       

       

      Set Variable [ $Importfile; Value:"filemac:" & BE_SelectFile ( "Select CSV file to import transactions from" ) ]

      Import Records [ Source: “$Importfile”; Target: “Visa_Import”; Method: Add; Character Set: “UTF-8”; Field Mapping: Source field 1 import to Visa_Import::Date

      Source field 2 import to Visa_Import::CCNumber Source field 3 import to Visa_Import::Payee Source field 4 import to Visa_Import::Debit Source field 5 import to Visa_Import::Credit ]

       

      The $Importfile is getting properly populated.

       

      Any ideas what I'm doing wrong?

        • 1. Re: Importing script step with a variable?
          PowerSlave

          You can't use Base element's output of the BE_SelectFile as a Filemaker file reference, you will have to convert it.

          Please see the following script on how to do it.

           

          • Set Variable [ $f ; Value:BE_SelectFile ( "Select a file" ) ]


          • Show Custom Dialog [ Title: "Selected"; Message: $f; Default Button: “OK”, Commit: “Yes” ]


          #Convert to FM File URL

           

          Set Variable [ $nf ; Value:Let(

                                                       [                                            

                                                       bslash = Char(92); // Backslash character                                            

                                                       fslash = Char(47) // Forward slash character                                            

                                                       ];    

          Case (              

                    // Determine the platform              

                    Get ( SystemPlatform ) = - 2 ; "filewin:/" ;              

                    Get ( SystemPlatform ) = 1 ; "filemac:/" ;              

                    "file:/"              

                    ) // End Case    

          &    

                    Substitute($f ; bslash ; fslash)                                           

                                                        ) // End let ]


          • Show Custom Dialog [ Title: "Converted to FM file URL"; Message: $nf; Default Button: “OK”, Commit: “Yes” ]

           

          Base elements will always return the native file url of the platform you are using.

          You can also use a custom function or a calc field to do this conversion for you.

           

          Message was edited by: PowerSlave Reason - forgot to add that calcs and CF's can do this too.

          • 2. Re: Importing script step with a variable?
            jjfcpa

            PowerSlave

             

            Thank you for your reply, but even after fixing the URL (path), I'm not getting anything imported.  Here is my revised code.  Is there something else I need to do besides supply the variable to the Import Records script step?

             

             

            Set Variable [ $Importfile; Value:BE_SelectFile ( "Select 5286 CSV file to import transactions from" ) ]

            Show Custom Dialog [ Title: "Import from file?"; Message: "OK to import VISA transactions from the following file?" & "¶" & $ImportFile; Default Button: “Import”, Commit: “Yes”; Button 2: “Cancel”, Commit: “No” ]

            Set Variable [ $Import_File; Value:Let( [

            bslash = Char(92); // Backslash character fslash = Char(47) // Forward slash character ];

            Case (
            // Determine the platform

            Get ( SystemPlatform ) = - 2 ; "filewin:/" ; Get ( SystemPlatform ) = 1 ; "filemac:/" ; "file:/"
            ) // End Case

            &
            Substitute($ImportFile ; bslash ; fslash)

             

             

            ]

            ) // End let ]

             

             

            Show Custom Dialog [ Title: "Import from file?"; Message: "OK to import VISA transactions from the following file?" & "¶" & $Import_File; Default Button: “Import”, Commit: “Yes”; Button 2: “Cancel”, Commit: “No” ]

            If [ Get ( LastMessageChoice ) = 1 ]

            Import Records [ Source: “$Import_File”; Target: “Visa_Import”; Method: Add; Character Set: “UTF-8”; Field Mapping: Source field 1 import to Visa_Import::Date

            Source field 2 import to Visa_Import::CCNumber Source field 3 import to Visa_Import::Payee Source field 4 import to Visa_Import::Debit Source field 5 import to Visa_Import::Credit ]

            [ No dialog ]

             

            • 3. Re: Importing script step with a variable?
              PowerSlave

              Try unticking the perform without dialogue option in the import script step to see what is going on. Also it helps if you first 'train' the import script step with an absolute file first (manually select the file to import , do the field mappings and save the script). Once that works, replace the absolute filepath with your variable and it should work.

              • 4. Re: Importing script step with a variable?
                jjfcpa

                PowerSlave

                 

                I figured out the problem.  The file spec was coming out like this: 

                 

                filemac://Users/John/Documents/5296.csv  (that's not exact but pretty close)

                 

                What it needed to be is this:

                 

                filemac://Macintosh HD/Users/John/Documents/5296.csv  (the volume was missing)

                 

                Once I added the volume in the Let() function that you gave me, it worked.  Is this normal for the Base Element SelectFile() function not to return the volume?  I can build this in so if it doesn't find it to add it, but I found it kind of strange. 

                • 5. Re: Importing script step with a variable?
                  user19752

                  It is normal since OSX use file path like

                  /Users/John

                  for boot disk, and

                  /Volumes/volumename/folder/file

                  for mounted volumes.

                   

                  FM uses odd format.

                  • 6. Re: Importing script step with a variable?
                    jjfcpa

                    Just wanted to thank everyone who provided insight into this.  I have it work properly and very happy with the results.