1 2 Previous Next 15 Replies Latest reply on Jul 9, 2014 11:31 AM by ultranix

    Conditional "Open file" And "Perform script from file" script steps

    ultranix

      Title

      Conditional "Open file" And "Perform script from file" script steps

      Post


           Hello, I have 500 files, which need to run the same Import script, but cannot be merged or imported into one single table/file, so I am making one controlling file with one table and one field - abbreviation, which runs the single controlling script:

           1- Import record (import the list of 500 abbreviations, but in next occurencies numbers may vary)
           2- Go to record/request/page [First]
           3- Loop
           4- Set variable [$Abbr; Value: abbr::abbreviation]
           5- Open file [current folder, (file:calc-analytics & $Abbr)
           6- Perform script ["Import" from file $Abbr)
           7- Close file ("$Abbr")
           8- Go to record/request/page [Next]
           9- End Loop

           But here I encounter 3 challenges:
           1- Open file with $Abbr doesn't work, because it displays message "The file "calc-analytics & $Abbr.fmp12" could not be opened. That means that variables doesn't work here. How can I make them work?
           2- Perform script from other file, that is specified from a variable, and not opened when compiling a script, how can I make this work?
           3- Close the file $Abbr. Same thing as with Open file.


            

        • 1. Re: Conditional "Open file" And "Perform script from file" script steps
          philmodjunk

               1) As you have discovered. Open File is "hard wired" to a specific external data source entry. It cannot use indiect references such as a file name or path in a variable. Try using Open URL to open the file instead.

               2) I haven't tried this myself, but it is my understanding that the URL text used to open a file can include the name of a script and even a script parameter. See this link for more info: FMP URL supports variables but not repeating variables ( $var[N] )

               3) close file might be done from a script in the file you just opened.

               On possible way: Install onTimer in the opened file might use an External Data Source link back to the main file to periodically check the value of a global field in the table referenced via that external data source. When the value changes to a specified value, the script then closes the file.

          • 2. Re: Conditional "Open file" And "Perform script from file" script steps
            ultranix

                 How can I pass $Abbr to the different file that needs to be opened?

                 To state the case clear, after I set variable $Abbr, I then perform a script, which opens file OHLC and first step it asks to define the folder name. I need to remove that Show custom dialog script step and instead use $Abbr variable. Once you go to another file, neither local, nor global variables don't work. How can I make this work?

            • 3. Re: Conditional "Open file" And "Perform script from file" script steps
              ultranix

                   Also, about that FMP URL. I am not able to form my variable according to this one "fmp://$/myFile?script=doThis&param=23&$myVar1=thompson"

                   I tried "fmp://$File?script=Import", but it gave me Error #802.

                   Sad.

                    

              • 4. Re: Conditional "Open file" And "Perform script from file" script steps
                philmodjunk

                     According the the link referenced in the help file, both a script name and a script parameter can be passed via the URL. So, in theory, you can pass the variable's value to the other script as a script parameter. 

                     Looking at your example, I think the problem is that you are passing the name of the variable instead of its value due to putting it inside a quoted string. Try:

                     "fmp://" & $File & "?script=Import"

                     But as I posted, this is something I've read, not yet something that I've tried to do. I'll dog ear this thread by putting it in favorites, test the idea out on one of my files and report back on that.

                     802, BTW, if you look it up in help means "unable to open file".

                • 5. Re: Conditional "Open file" And "Perform script from file" script steps
                  ultranix

                       Again, the same 802 error. No good. If you are able to achieve anything with that, please, let me know.

                       And again, how can I pass $Abbr to the newly opened file?

                  • 6. Re: Conditional "Open file" And "Perform script from file" script steps
                    philmodjunk

                         To repeat, you pass the VALUE of that variable as a script parameter--assuming that we can get this to work.

                         The alternative is to set a global variable to the value of this variable in your control file and use an external data source reference in the controlled file to refer to it.

                         And the "awful icky" alternative that we are trying to avoid is to set up 500 different data source references and a truly awful tree of If-else-if script steps to perform a script in the controlled files. If we have to go that route, you can pass the value of the variable as a script parameter.

                    • 7. Re: Conditional "Open file" And "Perform script from file" script steps
                      ultranix

                           I wouldn't need to go through all that trouble, if I could import records from one table to another (exact same fields). Because now, I use OHLC.fmp12 file to do importing from .txt file and then import that OHLC.fmp12 file to calc.fmp12 file. I assume that this works faster than Loop-Set variable-Set field-Go to next record.

                           Because If I am able to do all the things from 1 file, then I don't need passing variables to another files, as I'm not familiar to script parameters.

                      • 8. Re: Conditional "Open file" And "Perform script from file" script steps
                        philmodjunk
                             

                                  I wouldn't need to go through all that trouble, if I could import records from one table to another (exact same fields).

                             Now I'm confused. Import Records does exactly that and can use a $Path variable to reference the same table, but in different files. So I do not see from what has been posted thus far why you can't just import the data directly from each Filemaker file in turn. There's no real need from what I see here to export to text and then import from the text file.

                        • 9. Re: Conditional "Open file" And "Perform script from file" script steps
                          ultranix

                               Now I remember why I needed that separate file (OHLC.fmp12) for importing stuff from .txt. It is because use script step "Import Records" and see "source fields" with variable $File ("file:" & Get(FileName) & ".fmp12"). And then it doesn't import records. I tried to put it all in one file and import all the records from the same file (ohlc table) to the same file (calc table). Please, don't ask me why I need this importing procedure, I need it. There should be a way to get through this.

                          • 10. Re: Conditional "Open file" And "Perform script from file" script steps
                            ultranix
                                 

                                      The alternative is to set a global variable to the value of this variable in your control file and use an external data source reference in the controlled file to refer to it

                                 Could you explain that?

                            • 11. Re: Conditional "Open file" And "Perform script from file" script steps
                              philmodjunk

                                   What you describe should not require first exporting to text. I suspect that your use of the $path variable may have had an issue. When you use Import Records with a $Path variable, you have to put two file references into the specify file dialog. The upper entry should be the name of your variable without quotes. When this dialog is used to access a file, FileMaker will try it first. The lower, second entry should specify a specific file name that is valid at the time you set up the script.

                                   Here's why. when you use import mapping to specify how the fields will be imported into your target table, FileMaker uses the data in the specify file dialog to access a file and bring up the file information, but since your script isn't executing at that time, the $Path variable is empty. Thus the reference to the $Path variable in that dialog is not a valid reference to a file, but with the second line specifying a reference to a file, FileMaker tries to open the second referenced file when using the $Path variable fails and you are thus able to map fields for your import.

                                   

                                        Could you explain that?

                                   You shouldn't need to use that method, but if you open the source file, go to manage database and add a table occurrence of the table in your target file in which you defined that global field. You can assign a value to that global field in the target file and a script in the source file can use that added table occurrence (which uses the external data source reference to access records in the specified table) make the value in this field accessible to scripts in that file as well. (This is how we used to pass data from file to file back before we had script parameters and each file could only define a single table.)

                              • 12. Re: Conditional "Open file" And "Perform script from file" script steps
                                philmodjunk

                                     PS. I've been unable so far to get this method to work. A closer reading of this help file does not mention using Open URL and refers only to "hosted files" so it may be that you can't use this method in the way that I thought might be possible here.

                                • 13. Re: Conditional "Open file" And "Perform script from file" script steps
                                  ultranix

                                       Ok now. I've been able to complete my controller script via "Copy" and "Paste" commands.

                                       Whole loop script looks like this:

                                       

                                            Loop
                                              Set variable [$Abbr; Value:tick::tick]
                                              Go to Field [tick::tick]
                                              Copy [Select]
                                              Commit Records/Requests [Skip data entry validation; No dialog]
                                              Set variable [$File; Value:"C:\Files|Analytics\Calc_analytics_1stock-" & $Abbr & ".fmp12"]
                                              Open URL [No dialog; $File]
                                              Delete Record/Request [no dialog]
                                              Exit Loop If [Get(foundCount)=0]
                                            End Loop

                                       Everything works like a charm, except for this - when Open URL script step executes, it takes several seconds to open the file. BUt script doesn't stop here, it deletes that records. And here "delete record" logic here is first have list of all  500 abbreviations, and when the Open URL is finished (that opens another file, which has Open script, and after it's done, it closes itself, so it gets back to original file) and ONLY THEN should execute Delete record step. How could this be done?

                                  • 14. Re: Conditional "Open file" And "Perform script from file" script steps
                                    philmodjunk

                                         Why are you using copy/paste instead of set variable/set field?

                                         Copy will replace any data previously copied to the system clipboard. This can be a very undesirable side effect and is very easy to avoid by using set Variable in place of copy.

                                         Both Paste and Copy will silently fail to work if the referenced field is not present and accessible on the current layout--thus a future layout change can break the script and the only way that you know that the script has been broken is when your script now does not produce the expected results--possibly after thrashing large amounts of data in your script.

                                         And Paste is easily replaced with a set field step. (or possibly a set field by name script).

                                         But I still see no reason why you can't just use Import Records to import your data from a table in one FileMaker file into a table in another. And I don't quite follow what just opening the file is supposed to do with your data in this script.

                                         But you could put a pause/resume script that either pauses for a specified time interval or pauses until you click Continue just before the delete records script step to make sure that this step is not performed until preceding operations have completed.

                                         You may find this thread on the $Path variable and how to use it--especially with Import Records, useful: Exploring the use of a $Path Variable in Scripts

                                    1 2 Previous Next