14 Replies Latest reply on Jan 25, 2014 8:03 PM by NicholasFernandez

    Syncing Identical Databases: Complex or Simple?

    NicholasFernandez

      Title

      Syncing Identical Databases: Complex or Simple?

      Post

           Hi All,
            
           Here's my question: If I'm only updating a single-table database with an identical structure, is there an easy (native) way to do so? 
            
           I am trying to develop a work-around for our office because we do not have FileMaker Server. (Remote hosting is not an option due to IT protocols). 
            
           We have a Contacts file that is linked to each Project file to create invoices and purchase orders. The problem is that only one person can have the Contacts file open at a time, and thus live updating of the Contacts file via a portal is not an option.
            
           I've created a script that has the main Contacts file create several copies of itself each time it is opened and closed. Thus, we only open the Main Contacts to change records. We use the other files for our individual projects. 
            
           This almost works, but it still means that we have to leave our projects to update Contacts. And if we want that information in our Contact file we have to close and reopen our current project.
            
           I spent a good part of today trying to work through the filemaker native sync tutorial: http://www.filemaker.com/solutions/ios/docs/fmi_guide_sync_en.pdf
            
           I understand all of the concepts, but I'm not getting far with implementing it. (Filemaker can't locate the host file, even though I can write a script to have it successfully open the same file) 
            
           Is there a simpler way that doesn't involve 3rd party software? I feel like this should be doable, and I'm frustrated at my inability to successfully create the sync script.
            

        • 1. Re: Syncing Identical Databases: Complex or Simple?
          philmodjunk

               What's not clear is why you are not using FIleMaker Pro instead of server to host your file over your local network. That method allows a small number of users to access the same file at the same time. If your needs are modest, that may be sufficient to avoid any need for synching multiple copies of your file.

               The guide you refer to is primarily for synching FM GO on iOS devices such as an iPhone back to a central database file--a much more common scenario than what you describe here as iOS users are much more mobile and thus much more likely to move out of range of a good 4G or WiFi wireless connection.

          • 2. Re: Syncing Identical Databases: Complex or Simple?
            NicholasFernandez

                 We are a small office. There are typically six to eight users at any one time.

                 The lack of filemaker server is a company decision that I am not at the level to change. Sure, we should have it, but we don't and I can't fix that. 

                 Is there anything I can do in scripting to improve my coworkers' setup? 

            • 3. Re: Syncing Identical Databases: Complex or Simple?
              philmodjunk

                   To repeat, you can host your database with FileMaker PRO instead of FileMaker SERVER. You can open your file with FileMaker PRO and up to 9 FileMaker clients can connect to this database at the same time.

                   No fileMaker server required.

              • 4. Re: Syncing Identical Databases: Complex or Simple?
                NicholasFernandez

                     Ah, sorry. That was covered in the: "Remote hosting is not an option due to IT protocols."

                I've talked with the IT department and we cannot create a local network or share files between computers. 

                     We do have the file stored on a server that we all have access to, but I have not been able to determine a way to host the file from this configuration. The "open remote" command seems to work only with a file that is hosted on a computer, and that won't solve our problem. 

                     Any other ideas? 

                • 5. Re: Syncing Identical Databases: Complex or Simple?
                  davidanders

                       Your IT department is tasked with supporting employees with performing their jobs?
                       I have dealt with IT departments many times.  Sometimes, asking the question in very simple terms, and copying the email to all concerned, can start a conversation.

                        

                  • 6. Re: Syncing Identical Databases: Complex or Simple?
                    NicholasFernandez

                         Hi David and Phil,

                         While the IT department isn't willing to open up file sharing at this time, based on our conversations I get the impression that they would be willing to consider moving to filemaker server if we are able to demonstrate that it will fit certain needs, which leads me to this question:

                         the main features that they are looking for are multiple simultaneous users (which i know filemaker server can do) and the ability to look up a particular name across multiple tables. is this possible? the search item would be in the same field in every table, and the tables are similar or identical in format, but the name would not be isolated. would my best set up be to create a separate table with portals displaying the field and then searching the string that I'm looking to find? This seems doable, but I can't quite wrap my head around it and I need to be able sell it to management.

                         thanks for any advice you might have.

                    • 7. Re: Syncing Identical Databases: Complex or Simple?
                      philmodjunk
                           

                                and the ability to look up a particular name across multiple tables. is this possible?

                           It's possible, but the need to search multiple tables raises the question as to whether your data model is correctly set up. Not always, but often such a question indicates that you have data entered into multiple tables that should actually be entered into a unified table.

                           

                                and the tables are similar or identical in format,

                           And that is a pretty strong indication that the data in those tables should be combined into a unified table.

                           This, BTW, has nothing to do with Server, FileMaker Pro can do this. Server makes very little difference in how FileMaker databases function outside offering increases support for multiple users, automatic back ups and the ability to run a script by scheduling it to run at a specific time of day. Thus, if you can set up FileMaker Pro to do it on one computer, in most cases, the database will function the same when hosted from server.

                      • 8. Re: Syncing Identical Databases: Complex or Simple?
                        NicholasFernandez

                             Hi Phil,

                             I've thought that there should be a cleaner way to organize the data, but here's the issue: 

                             Each table is a project that has several different layouts associated with it and will be used for months at a time. We have around 50 projects live at any one point, so moving everything into one table would result in 300 plus layouts to scroll through to find your project/layout. Does that make sense? This might be something that I'm better able to understand as I grow more knowledgable of databases because I'm sure I'm not the first person to have such an issue.

                              

                             Nick

                        • 9. Re: Syncing Identical Databases: Complex or Simple?
                          philmodjunk
                               

                                    Each table is a project that has several different layouts associated with it and will be used for months at a time. We have around 50 projects live at any one point, so moving everything into one table would result in 300 plus layouts to scroll through to find your project/layout. Does that make sense?

                               I don't see why you need so many different layouts for different projects, I would guess that design changes could reduce that to a much smaller number, maybe even just one or two, but even if so many truly are needed, you are still better off merging your data into a single table and your users would not ever need to manually select the correct layout for a given record. Filemaker can make that selection for them.

                          • 10. Re: Syncing Identical Databases: Complex or Simple?
                            NicholasFernandez

                                 Well, we have layouts for different letters to be sent to clients of each project (that draw from the project info). And layouts for internal communications. Etc.

                                 What I'm betting would work is a situation where navigation is done through buttons and scripts so that the users aren't aware of all of the layout changes they are going through, but that's not happening any time soon. (the change would freak people out!)

                                 Thanks, Nick

                            • 11. Re: Syncing Identical Databases: Complex or Simple?
                              philmodjunk

                                   Those all sound like layouts that do not need to be dedicated to specific projects. There are a number of methods, to give one example, where you enter the text of the Letter into a field in a table with "place holder" text that is replaced with data from the project table (or a table related to the project table) to produce a letter customized to the needs of a specific project. This text field in different records of the same table can store completely different letters which can all be selectively displayed for view and editing on the same layout. Other options for this also exist that do not lock you in to specific layouts for specific projects.

                                   And yes, the nav button approach is also what I had in mind, but the visible changes to the user should be fairly simple. They just know that they are selecting a particular project and then the only layouts/records that they see are for that project. The end result for the user can be very close to what they see currently.

                                   And I can see where you may need that "search multiple tables" option as an interim solution while moving towards a unified data model. Here's a method that can be used for that process.

                                   Set up some global fields on a search page where the users can enter or select the criteria for their search. The script then performs a search on a different layout for each table being searched. The layouts can refer to tables from other files if you set up an external data source reference. If any matching records are found on a given table, the script copies the primary key values of the found records into a text field, one such field for each table being searched. These text fields should be set up as match fields in relationships to each of the tables being searched. You can then set up a search results page where portals listed on these relationships can list the records found and a button in the portal rows of each portal can be clicked to pull up a detail view of that specific record on a layout based on that portal's table.

                                   For simple examples of the type of script that can use data in global fields as search criteria in a find, see: Scripted Find Examples

                              • 12. Re: Syncing Identical Databases: Complex or Simple?
                                NicholasFernandez

                                     I'm starting to see what you mean about unifying the database, but I could use some specifics:

                                     1. Regarding your example with the letter, would multiple people in different projects be able to access the letter template at the same time? (Multi user access to the templates is the main thing that I could see holding this back.)

                                     2. How does a user open the file to their project? Would a home screen with a portal to a table of projects be the answer?

                                     and thanks for the multi-table search ideas. I'll start working on that.

                                • 13. Re: Syncing Identical Databases: Complex or Simple?
                                  philmodjunk

                                       1) This should not be a problem. Two people could not modify the same record at the same time, but any number of people can be accessing different records of the same table and many can view or print or PDF from the same record as long as they don't try to edit it at the same time. You don't want two people editing the same record at the same time anyway. That creates a number of major problems and that's why nearly all DB's have some sort of system for locking records such that only one person can edit a given record at a time. (Once a user commits their changes, another user can then open the record for editing it.)

                                       2) There would not be a "file to their project". There would be a record (Or much more likely, a group of records) for their project. There are many different ways that you could set up for enabling users to select a project and be taken to a layout and set of records for the same. A button in a portal of projects would be one way to do that.

                                  • 14. Re: Syncing Identical Databases: Complex or Simple?
                                    NicholasFernandez

                                         Hi Phil,

                                         1. That's great news. I'm starting to envision how this could all come together. I need to do some more reading on related tables, but it appears that a one – database solution is possible.

                                          2. Sorry. I didn't mean "file to their project" as in opening a specific file, but as in how would a user would navigate within the main file to find the interface that would have the records connected to their project. However, your answer addresses what I needed to know.

                                         Thanks!