8 Replies Latest reply on Feb 5, 2014 10:27 AM by philmodjunk

    Reoccurring Import



      Reoccurring Import


           Hello, I have a database that I'm trying to have a reoccurring import setup for it but it would be of itself so that I can work on the database offline, make all the changes I need, save out a clone and import all the records from the currently active database but I'm having trouble figuring out how to make the script as automated as possible.  From what I can tell/figure out myself, I know how to make the script go from layout to layout in the target file using the layout number and setting up an import folder and starting at the top .... how can I match the layout in the source file so that I don't have to manually select it each time?


           Also how do I get the serial number from the source file to then use the set next serial script in the target file?

        • 1. Re: Reoccurring Import

               You don't need to select the layouts, just the Table Occurrences that they represent. When you build your script, there's a dialog that opens with Import Records that has a drop down lists for selecting the source and target table occurrences. This is also where you map fields from one table to another though in your case, you can just use "matching field names" most of the time.

               There are two ways to get the next serial value so that you can reset this value in the new file:

               1) GetNextSerial Value is a function that you can use to extract the next serial value setting from a specific field in a specific table in a specific file.

               2) You can import all your records into the table, sort them all in either ascending or descending order by the serial number field and then refer to the first (descending order) or last (ascending order) record's serial number field and set the next serial value to that value plus one.

          • 2. Re: Reoccurring Import

                 Right, occurrences not tables, my bad on using the incorrect terminology.  I wasn't thinking straight ... So sounds like there is no way of fast tracking the selection of source and target tables occurrences correct?


                 So there is a get function for serial numbers .... strange I did a text search for the word serial in the get script list and it did not  return any results so I thought it had to be done some other way.  Thank you for both suggestions Phil!

            • 3. Re: Reoccurring Import

                   What do you mean by "fast tracking"?

              • 4. Re: Reoccurring Import

                     Sorry, should have said: Having a script handle the selection of the source and target.

                • 5. Re: Reoccurring Import

                       I'm rusty as I haven't done this in a while so please be patient with me.

                       How can I automate the getting and setting of serial numbers?  all my serial fields are "__kpnID"  I'm trying to use something along the lines of GetField ( Get (LayoutName) & "::__kpnID" ) but can't seem to get it to work.  Nor can I find a way to automate telling FileMaker which field should be updating it's serial number because I'm forced to select which field name rather then use a calculation.


                  • 6. Re: Reoccurring Import

                         Dynamically selecting source and target would be very problematic as you also have to specify the field mapping between the two tables.

                         Getfield should work as you have described, but since you can't use that level of indirection for the Import Records steps anyway, there's no point. You'll need to set up a series of groups of scripts that:

                         a) Import records from Table A in the old file to table B in the new
                         b) determine the next value for any serial number fields in the table

                         c) reset them.

                         But once you have once such set of steps set up, in FileMaker Advanced, you can copy and paste the block of script steps and then update them to refer to the next pair of tables.

                    • 7. Re: Reoccurring Import

                           I was hoping that having the serial ID fields named the same though out each table would enable me to at the very least have the updating of the serial numbers be a step where I don't have to do any "clicking".  So there's simply no way around it?

                           If I have to select the source and target tables every time that's not a big deal as I have to review the fields and make sure they lineup properly but I having the serials update without prompts would be REALLY nice.

                      • 8. Re: Reoccurring Import

                             Nice but not an option with current versions of the application.