11 Replies Latest reply on Aug 13, 2017 3:48 PM by philmodjunk

    Import Records

    rkutcher

      I have a database that I make frequent changes to as we identify features we would like to include or modify.  I now have around 50 tables and manually importing all the records each time I want to deploy a new build is getting to be quite the task.  I'm pretty sure this the sort of thing that can be automated with a looping script.

      In all the loop scripts I have the loop moves thru the record with the Go to Record/Request/Page (Next) script step.  I have the basic steps for the Import Records Script for one table but don't understand how to make the loop advance to the next table.  (for example a Go to Table (Next) step)

       

      If anyone could direct me to a basic Import Records (from a remote Filemaker Server) it would be much appreciated.

       

      Richard

        • 1. Re: Import Records
          Philip_Jaffe

          Richard,

           

          While there is no "Go To Table" script step, there is Go To Layout.  You will need to have a layout based on the Tables you wish to navigate to.  Then do your import.

          1 of 1 people found this helpful
          • 2. Re: Import Records
            philmodjunk

            This is a common misconception. For manual imports, you need to be on a layout for the target table in order to import. For scripted imports, you do not need to be on the layout of the target table. There's a drop down menu inside the Import records dialog where you can pick the target table. This menu only allows you to pick the layout's table occurrence or "new" in manual imports, but inside the scripts workspace, it will let you pick any table occurrence in the list.

             

            It can however, be useful to navigate to the appropriate layout before importing via script as you sometimes have to do some "post import processing", such as updating a serial number field's "next value setting".

             

            What you can't do, is do this in a loop. It's a script with a long list of one Import Records step after another.

            • 3. Re: Import Records
              Philip_Jaffe

              Thanks Phil. 

              • 4. Re: Import Records
                rkutcher

                Thanks to you both.

                 

                I had started writing the individual scripts for each table but thought I would ask about the loop.  

                • 5. Re: Import Records
                  rkutcher

                  A couple of things.

                  1.  Each time I deploy a new build I have to change the file name slightly.  I just use the build number.

                  2. As I am writing the script to automate the Import process I have to either put in the actual file path to the source file on the Filemaker Server or I should set up some sort of a Variable - $path.

                  3. If I use the actual file path I would have to change the script, to direct it to the latest version of the database on the server, each time I want to import the new records.  That doesn't sound right to me.

                  4. If I use $path I don't have the option to select a source when I am trying to set up the Import dialogue box.  I think that's because $path isn't really a file path until $path is defined.

                   

                  Any suggestions?

                   

                  I've tried to change the name of the source file on the Filemaker Server but haven't had any luck.  I was thinking that if the script had a file path leading to the source file "Old" I could the source file name to "Old" each time I wanted to import the files.

                  • 6. Re: Import Records
                    rkutcher

                    I think I solved the problem.

                     

                    I set up the dialogue box with the actual file path then once it was set up I changed the file path back to the $path and it seems to be working.

                    • 7. Re: Import Records
                      beverly

                      You can actually leave the full path, just place a return between the $$variable path and the full path:

                      $$filepath

                      file:myfile.fmp12

                       

                      That way you can have something "valid" should you need to change the import dialog.

                      Beverly

                      • 8. Re: Import Records
                        philmodjunk

                        I use a container field and insert file to insert a reference to the file into a container field so that I can  set the path variable to the file path that can be extracted from the container field. The user gets an open file dialog, selects the file from which to import and the script takes it from there.

                        • 9. Re: Import Records
                          fmpdude

                          When you say "automation", JDBC is an obvious candidate for consideration.

                           

                          FMP has a free JDBC driver and you can do basically anything (SELECT, UDPATE, DELETE, INSERT) you need to with it. Although the driver has some yet-to-be-fixed (years' old) annoying bugs with container fields in particular, there are "workarounds". For regular data, that is, not container fields, the JDBC driver is super easy to use.

                           

                          To use the JDBC driver, you just need to:

                           

                          1. Create a connection to your LIVE FMP database. (Have to allow JDBC connections)

                           

                          2. Create a couple of objects. (Statements, etc.)

                           

                          3. Start issuing SQL statements (INSERT, UPDATE, DELETE, etc.). 

                          Note that compiled (for even more speed) Prepared statements are also supported.

                           

                          The JDBC driver also lets you get database metadata so you can figure out table names, field names and types, all programmatically.

                           

                          Thus, using JDBC gives you total control. And, best yet, it's FREE and iterating a JDBC ResultSet (similar to FMP "FoundSet") is much, much faster than "looping" in FMP's slow scripts.

                           

                          Does JDBC sound like it would help in your situation?

                          • 10. Re: Import Records
                            rkutcher

                            Phil,

                             

                            I added a “Build Number” field to the layout that I run the import script from.  Then I copied the portion of the file path that doesn’t change and pasted it into the Set Variable step of the script so the variable is then

                             

                            $path = “portion of file path that doesn’t” & Build Number 

                             

                            Seems to work. 

                             

                            Regards,

                             

                            Richard Kutcher

                            Kutcher Engineering & Inspections Inc.

                            250-885-6844

                            • 11. Re: Import Records
                              philmodjunk

                              Hey, whatever works. I'm just sharing a method that I've found to be very flexible and user friendly.