5 Replies Latest reply on Jul 11, 2014 1:22 PM by philmodjunk

    Import from a local filemaker file to server

    JohnSmith_3

      Title

      Import from a local filemaker file to server

      Post

           Hi,

           I've got a filemaker databse hosted by server, of which I have an identical copy on an ipad using filemaker Go.

           I have written a script to import data from the local database to the main server copy. I'm using the import records function, as detailed in http://help.filemaker.com/app/answers/detail/a_id/7787/~/importing-to-and-from-a-transferred-file, part 2.

           It works just fine, using the matching records to update my fields.

           I`d like to however make it that it only updates a certain field IF it's empty, but that otherwise it does not overwrite data which is already on the server.

           Any way to do this?

            

           Thanks,

        • 1. Re: Import from a local filemaker file to server
          philmodjunk

               Option 1: Import data into a different table and use a script to selectively update the data in this field.

               Option 2: You may be able to map the field to a global field instead of directly to the same field, then set up an auto-enter calculation that copies over the contents of the global field only if the target table's field is empty. This, however, requires enabling auto-enter options--which affects all auto-enter options such as serial numbers, not just the options for this particular group of fields so it may not be possible.

          • 2. Re: Import from a local filemaker file to server
            JohnSmith_3

                 Hi Phil,

                 Thank you, I followed option 1 and I managed to make it work.

                 I'd like to add one more check however:

                 For any given matching record (between iPad DB and server DB), only update the server DB with the iPad DB if the modification time is more recent.

                  

                 There are several people working on the database, some online, others offline, and I want to ensure that if any modifications are done on the server, that they don`t get erased by the iPads syncing.

                  

                 Any hints?

                  

                 Thanks,

            • 3. Re: Import from a local filemaker file to server
              philmodjunk

                   You should use a TimeSTAMP field that auto-enters a modification time stamp so that you are comparing both the date and time of the modification rather than just the time. (Or a change made at 2 pm yesterday will seem more recent than one made at 8 am today...)

                   and you can perform a find for all records where this modification timestamp is greater than a timestamp value logged as the date/time of the previous "synch".

                   If you haven't done much in the way of scripted finds of that type, you may find these examples helpful: Scripted Find Examples

              • 4. Re: Import from a local filemaker file to server
                JohnSmith_3

                     Hi Phil,

                     Thanks, that's exactly what I was trying to do.

                     I had trouble doing the Perform Find, adding ModifiedTime > LastSync as a criteria; so I added a new calculation field that does just that, and sets 1 for a record to be imported, and 0 otherwise.

                     I sync my modifiedTime on my local file to the server, and the calculation works great. The records that should be imported are marked as such.

                     However, when I try to select just those records for an import, somehow it does not do anything, as if my perform find was yielding nothing?

                     My script works as such:

                      

                     Import Records (matching a unique field, and importing only the LOCAL modified time)

                     Go to Layout (layout in question)

                     Show all records (thought maybe it was performing a find on a partial record)

                     Set Error Capture [On]

                     Perform Find (Where ImportRecord field = 1)

                     Import Records (matching, importing everything but modification times)

                      

                     When I go check the results, the record that should be modified, isn't. However, my ImportRecord field does show 1, and my Local modified time is the right one, and greater than the last sync/modified time.

                     Any idea what's going wrong?

                     Use cases:

                     In all cases, the two databases are synced before any changes.

                     - If i create a new record on my local file, it adds it just fine on my server.

                     - If I modify an existing record on the server, and sync the local to the server, the changes are kept (despite the local not having these changes)       This is what's wanted, and didn't used to work until I used my perform find with import record boolean)

                     - If I modify the record locally, it doesn't propagate to the server, despite the ImportRecord boolean being set to 1 after the first import.

                     It should be propagating the change to the server. Why isn't it?

                • 5. Re: Import from a local filemaker file to server
                  philmodjunk
                       

                            I had trouble doing the Perform Find, adding ModifiedTime > LastSync as a criteria; so I added a new calculation field that does just that, and sets 1 for a record to be imported, and 0 otherwise.

                       While that works, it's not necessary and needlessly complicates your system. If you examine the find examples in the thread that I recommended, you'll see a way to use set field to set up such find criteria. This may require setting a variable to a value from a field, entering find mode and then using set field to transfer the value combined with the inequality operator to the appropriate field, but this avoids having to add an extra calculation field to your system.

                       

                            the two databases are synced before any changes.

                       Huh? Isn't this the actual synching script? Why would you synch first and then run this script? I must be missing something here.

                       The first thing to check with an "Import matching" script is to make sure that you do a show all records on the target table occurrence before importing into with the "match found set" option. Otherwise, a record could fail to match because the matching record is not in the found set.

                       The next possible source of confusion is the fact that while your import records dialog asks you to select source and target tables in order to map fields for the import, you are really selecting Tutorial: What are Table Occurrences? from the drop downs. Each table occurrence has its own found set, so if your script goes to a layout for Table Occurrence A and performs a find, this will not necessarily work if you have selected a different table occurrence with the same source table as it may have a different found set.