1 2 Previous Next 20 Replies Latest reply on Nov 20, 2013 7:41 PM by JohnDeBriere

    File Maker Pro Scripting

    JohnDeBriere

      Title

      File Maker Pro Scripting

      Post

           Somewhat new to FM Pro and not that familiar with the architecture of FM so to start I have two questions:

           1. When a user clicks a button in my Client FM Pro application using my FM Pro Server (11.0) data I want to run a script on the FM Pro Server. By "Client" I mean the app is a client using the FM Pro Server data.

           So my question is: Can I run a script on the FM Pro Server from my Client FM Pro app?

           2. The script will pull data from the record currently being viewed by the client app and export the data to a folder on the server as an XML file.

           The question is: Are there any issues with using the "Export Script" to do this?

           The only alternative I have found so far is to upgrade the FM Pro Server to Advanced and use ODBC to extract the data and write the export my self. If there are better alternatives I would appreciate any suggestions.

           Thanks in advance!

        • 1. Re: File Maker Pro Scripting
          philmodjunk

               If the client has "write" access to a folder on the server, a client side script could export this data.

               There is no way in FileMaker currently, that allows a client to run such a server side script on demand. A server schedule or a robot file on the server could be set to run a script periodically. If a script on the client side were set up to create a new records in some kind of "export request" record, such a script could be set to run that checks for new records in this table and that uses data stored in that new records to find the relevant records and export them.

               But server scheduled scripts have significant limitations as to where they can export such a file: http://help.filemaker.com/app/answers/detail/a_id/7035/kw/server%20import%20records

               Thus, a "robot file" a FileMaker file placed on the server to perform a script in the hosted database is sometimes used as it runs from a "client side" perspective and thus avoids these limitations.

          • 2. Re: File Maker Pro Scripting
            JohnDeBriere

                 Thanks Phil!

                 It's easy to open up a shared folder on our server so I think that the client side script should work well.

                 Also thanks for the note on the FM Pro Server scheduled scripts limitations. I had read that article just before sending off the questions.

                 Lastly, while I'm on the subject can you tell me if there are any differences in scripting (either features or functions) between FM Pro and FM Pro Advanced?

                  

                  

                  

            • 3. Re: File Maker Pro Scripting
              philmodjunk

                   The script steps that are available are exactly the same. It's the other features: Script debugger, data viewer, custom menus, custom functions, database design report, etc that make it indispensable for anyone creating database solutions in FileMaker.

              • 4. Re: File Maker Pro Scripting
                JohnDeBriere
                Thanks again Phil for your feedback/help. One last question. I am testing different export options (XML, XLS, and CVS). They all are easy enough to implement and in most parts work well yielding the expected results. That is, when I export using XML or XLS the exported file contains all of the records from my FMP Db and only the fields I selected as I would expect. However, when I export using CVS, the file contains all of the proper fields but only data from the first record is exported to the file and not all records as in the other two cases. So am I missing something or is this a "feature" of CVS exports?
                • 5. Re: File Maker Pro Scripting
                  philmodjunk

                       Check your found set before exporting. Export Records exports the records in your found set. IF you have a found set of just one record, only one record is exported.

                  • 6. Re: File Maker Pro Scripting
                    JohnDeBriere

                         They are not different record sets. That is, in each export option be it XML, XLS or CVS I am using the same Db, same Tables, same records, and same fields so I would expect the results to be the same in each case unless of course CVS has different options and thus an option I am unaware of.

                         Thank you again for your help.

                    • 7. Re: File Maker Pro Scripting
                      philmodjunk

                           I didn't say "Record set", a term that does not apply to FileMaker, but "Found Set". this is something that is dynamically produced by performing either a find or via a script with Go To Related Records.

                           To Make sure that an Export exports all records from a table, do a Show All records just before exporting.

                      • 8. Re: File Maker Pro Scripting
                        JohnDeBriere

                             Aha! Well it sounds like when you export to Excel or XML the "Show All" or equivalent thereof gets executed first. But it looks like that's not the case with CVS.

                             I normally don't need all records exported I was simply pointing out an observation of what appeared to be an inconsistency.

                             Thanks!

                              

                        • 9. Re: File Maker Pro Scripting
                          philmodjunk

                               This is not the case, with any export, the process works the same. Show All Records is both a menu option and a script step so you can include this in a script that exports your data or you can select this option from the Records menu when manually exporting data.

                          • 10. Re: File Maker Pro Scripting
                            JohnDeBriere

                            Well I might disagree as I used the defaults in all cases and as you can see I only used a single script line in each case and defaults otherwise.  I'm using FM Pro 11.0 and the sample Db that comes with it called "The Corporate Travelers Club". Try it yourself. As you can see I chose to export only: First Name Last Name Company Fee Paid Date Paid I have used FM Pro to print each of the Three scripts as follows: Export Records [File Name:“file:FMPData.cvs”; Character Set:“Unicode (UTF-8)”; Field Order:Members::First Name Members::Last Name Members::Company Members::Fee Paid Members::Date Paid] [Format output using current layout] Export Records [File Name:“file:FMPData.xls”;Use field names as column names; Worksheet:"WS"; Title: "People"; Subject: "Info"; Author: "JD"] Export Records [File Name:“file:FMPData.xml”;Grammar: "FMPXMLRESULT"; Character Set:“Unicode (UTF-8)”; Field Order:Members:: First Name Members::Last Name Members::Company Members::Fee Paid Members::Date Paid] So for those reasons I must conclude that the defaults of Exporting as XML or XLS does something different than Exporting with CVS as is evident from the results I received.

                            • 11. Re: File Maker Pro Scripting
                              philmodjunk

                                   I've been using FileMaker since version 2.5. This is a behavior that has not changed for many years. "Using the defaults" does not control what found set is present at the instant you export records as this is something that is easily changed by the user or a script in any number of ways. Unlike a "record set" that you can specify with a SQL query in other systems, a Found Set is very dynamic there are multiple choices in the Records menu and multiple script steps that can change what is in a layout's current found set and such found set's are in turn linked to the Tutorial: What are Table Occurrences? selected for a given layout or that you selected as the "source table" for your export. Select a different table occurrence or a different layout that specifies a different table occurrence and you can have a different found set even though all the records are from the same data source table.

                                   Note that in the attached screen shot, there is a 13 record found set in a table of 539 records. You can see in the overlapping window that export records produced an Excel file with 13 rows of data--corresponding with the 13 records in the found set.

                                    

                              • 12. Re: File Maker Pro Scripting
                                JohnDeBriere

                                     I'm not commenting on your experience with file maker I am only providing my observations based on the results I have received. As a scientist I base my conclusions on observations of test results and I have presented them in an unbiased manner.

                                     Sorry about the formatting however it is the default layout that FM Pro's web site reformatted my text after I posted it and not how I presented it. FYI: I believe this has something to do with the only way FM Pro Forum site allows for "copy - paste" operations. BTW your screen shot is also very difficult to read.

                                     My next question is:

                                     How can I Export only the current record shown in the "Data Entry" layout?

                                     Thank you!

                                      

                                      

                                      

                                      

                                • 13. Re: File Maker Pro Scripting
                                  philmodjunk

                                       What I have been trying to tell you is that the conclusion that you are drawing from your observed results is an incorrect conclusion.

                                       Please note my results. The found set controls what data is exported as shown in the screen shot in my last post. If you only want to export the current record manipulate your found set to have just one record.

                                  • 14. Re: File Maker Pro Scripting
                                    JohnDeBriere

                                         The data I have is what it is and if you refuse to accept and/or review it then that is fine with me. I am beyond that but apparently to are not.

                                         My next question stands as:

                                         How can I Export only the current record shown in the "Data Entry" Layout?

                                         Thank you!

                                          

                                    1 2 Previous Next