14 Replies Latest reply on Mar 19, 2015 12:08 PM by electon

    Linking two Seperate Databases

    Jason_Farnsworth

      Yesterday I posted a question relating to creating a script to close (stop hosting) a database via a client. Given the responses, I have rethought the need and would like to back up and present a more broad question.

       

      I have two database files (1) is a master database and (1) is a inspection database. The master file is accessed via client intranet while the inspection is accessed via a remote in the field connection using a pro client.

       

      The inspection db is purely a data entry system meant to limit the amount of data, tables and fields to improve remote connectivity. While accessing the data via the remote connection I have the file hosted on FM server 13. I have written scripts to look at what needs to be inspected for the week and push a copy of this weeks jobs into the inspection db. The inspector goes out and does his list and returns, then I have another script written to push the data back into the master db creating new inspections for those jobs.

       

      The challenge is I have to kill the hosted connection in order to push and pull the data between databases then re-host when complete. Have I missed something in the way I am using these (2) database that make me stop the host for the transfer.

       

      It would seem like to me the master db should be able to see the inspection db and pull the data hosted or not.

       

      Any idea, suggestions?

       

      Jason Farnsworth

      Midland, TX 

        • 1. Re: Linking two Seperate Databases
          Jason_Farnsworth

          Ha,

           

          I seems like I can open via a remote connection and it sees the hosted file.

           

          Jason Farnsworth

          • 2. Re: Linking two Seperate Databases
            siplus

            I don't quite understand the real problem but one thing is sure: you should never have to kill/rehost. There are many ways to circumvent this. For example you can have an ftp site with 2 directories, one for "queries" and one for "results". The inspection database uploads files into the query directory and reads (imports) results from the "result" directory, the main database does the inverse. Each one deletes files as processed. The main database does the import and deliver based upon a FM Server schedule. The inspector does it on demand by the current user. Just sayin'...

            • 3. Re: Linking two Seperate Databases
              BruceHerbach

              If both files are hosted on FMS,  you should be able to set up a Sync system that pushes data to and pulls data back from the Inspection file.  You can roll your own.  Here is a white paper on the subject:

              FileMaker Sync Guide (for FileMaker 12)

               

              HTH

              • 4. Re: Linking two Seperate Databases
                electon

                Not sure if I understand the problem correctly and why you have to close the db on server to get this done.

                You can setup the two files to look at each other via External Data Source. Create TO's for the needed tables and script the process accordingly.

                Bare in mind that if you're gonna use the export / import routines between the two files on FM Server it works a bit different.

                This is for Perform Script on Server or Scheduled Script !!! Which are way faster than doing this via the client.

                You can't do it via the relationship graph, the records must be first exported to a known path on server, then imported from that file into the other table(s).

                PsOS are tricky to debug. Error catching is a must. I've had situations in testing when the whole thing just hangs.

                Here is a link that explains this.

                Import/Export script on FileMaker Server | FileMaker

                • 5. Re: Linking two Seperate Databases
                  Jason_Farnsworth

                  I have since now connected the two databases via a remote connection and have the ability to pull and push the data freely between them. These databases are connected via a TO and I script the pull and push.

                   

                  You mentioned perform script on server command, Is this a good way to decrease script run times? Can this be done with any script?

                   

                  Jason

                  • 6. Re: Linking two Seperate Databases
                    BruceHerbach

                    There are two options in 13.  The script step perform script on server and the FMS scheduler can run a script on a schedule.

                     

                    When you create the script, you can have script editor show you script commands that are compatible with server.  Look in the lower left corner of the editor screen

                    HTH

                    • 7. Re: Linking two Seperate Databases
                      matthew_odell

                      Hey Jason, I highly suggest looking at some of the sync tools that are already out there like EasySync, GoZync, and MirrorSync (I'm sure I'm missing some as well). All of these third party tools have solved the problems you are slowly going to face over time when it comes to connection an offline file and a hosted file. The sync guide that Bruce mentions above is another great tool to understand the behavior between an offline file and a hosted file. Good luck,

                       

                      Matt

                      • 8. Re: Linking two Seperate Databases
                        Jason_Farnsworth

                        Matt,

                         

                        Thank you for the list of sync tools, I will look into them. That said I am always naturally a little leery of using 3rd party addons as they don't always keep up with the development pace of Filemaker.

                         

                        Jason

                        • 9. Re: Linking two Seperate Databases
                          Jason_Farnsworth

                          Bruce,

                           

                          Thank you for the heads up on this guide it seems like Filemaker has a guide for everything these days.

                           

                          Jason

                          • 10. Re: Linking two Seperate Databases
                            matthew_odell

                            The nice thing about some of these tools is that they are native FileMaker, so it's not like you can't get under the hood and fix things yourself if you do run into issues. EasySync is free and open source, so that might be the best bet for someone who wants to really own what's happening there.

                            • 11. Re: Linking two Seperate Databases
                              BruceHerbach

                              Jason.

                               

                              I have a client that uses MirrorSync.  360Works has given us excellent support when we needed it. 

                               

                               

                              Sent from my mobile device... Please excuse typos.

                              • 12. Re: Linking two Seperate Databases
                                electon
                                You mentioned perform script on server command, Is this a good way to decrease script run times? Can this be done with any script?

                                Depends on what you want to achieve and if the increase in speed justifies the extra effort of implementing it.

                                Increase in speed can be enormous, especially with remote connections. The more data needs to be moved the bigger the gain with doing it on server.

                                The thing is that the client is not involved with moving the data, just with controlling the movement. All because the records involved don't need to make round trips from server to client.

                                You can see it for yourself by trying to delete 1000 records via client and via Perform Script on Server.

                                Remember that with PsOS the context ( found set, etc. ) needs to be recreated on the server as it is not aware of what the client can see. You will have to Go To Layout, Find records, etc. All this can be passed with script parameter.

                                • 13. Re: Linking two Seperate Databases
                                  bigtom

                                  Can you do this with the main file using the data from the inspector file with a direct connection via ESS? I have never tried this exactly, but it makes sense that the main file would need access to the inspector file and have it open, but the remote inspectors would only need to have the inspection file open.

                                   

                                  I am all for a Sync solution if it fits but does that always have to be the answer?

                                   

                                  I have done remote management a similar way in a sense and it works fairly well. There is a lot of refreshing with joined data to keep it up to date on both sides, but it is not so slow. Does require the remote users to always have connection to the server.

                                   

                                  Sync solutions have a great use when the remote clients cannot be connected to a network or the amount of data is large.

                                  • 14. Re: Linking two Seperate Databases
                                    electon

                                    The way I understood the question is that the Inspections file is accessed from the server remotely. That way ( no external sync solutions ) I'd script the data movement on the server between the two hosted files.

                                    If the Inspections file is a stand alone file then syncing is probably the best bet.

                                    Were you actually modifying the Inspections file, then closing it / giving a physical copy to the inspector so he / she can use it in the field? Then after getting the file back, re-hosting it on server?