7 Replies Latest reply on Jan 18, 2012 6:00 AM by IT_User

    Having multiple databases auto update a 'master' database


      Hi all,

      I am new to the forum and am currently running a trial of File Maker Pro 11.0v4. My question I am sure has been brought up previously but I am currently unable to find it searching within the forum.


      My goal is to have 1 Master database that can be automatically fed information by several other spreadsheets and or databases (Not sure what the correct term would be). I have went through the:

      File>Manage Database

      as well the as File>Manage>External Data Sources menu's and am unable to make this work. My goal would be to have different employees be able to fill out a database per department but then all departments data gets merged into one master database automatically.


      If someone could walk me through how I should set this up that would be greatly appreciated!


      Thanks in advance,


        • 1. Re: Having multiple databases auto update a 'master' database

          Hi Jesse,


          What you're describing is certainly possible, and could be set up to work in a variety of ways. However it's not clear why you would need to do this rather than simply having each department log in (with appropriate privileges and contraints) to the master database, and enter their data directly.


          If your concern, and the problem you're trying to solve, is related to ensuring that each department sees only their own data, then that is something you might take care of using access privileges. It would really only be if you need to provide for much of the data entry to be done offline that you would need to come up with a method of syncing or transfering the data between systems.


          If you can say a bit more about the reasons you are thinking about using a flotilla of separate databases to capture the information, that might make it easier for folks here to provide further advice.





          R J Cologon, Ph.D.

          FileMaker Certified Developer

          Author, FileMaker Pro 10 Bible

          NightWing Enterprises, Melbourne, Australia



          • 2. Re: Having multiple databases auto update a 'master' database

            You can always set up buttons in a FileMaker layout assigned to a script that automatically import data.  The more elegant solution is connecting via ODBC/JDBC to import the data.  Or better yet, if the database is MySQL, M$ SQL, or Oracle, you can use ESS to make live table occurrences of the those database tables right in FileMaker.  That way you don't even have to import, you can use the live data in those other databases for your FileMaker reports without syncing or importing! 


            There are other games you can play like putting a spreadsheet in a folder that FileMaker has a Server Side Script scheduled to look for and if it is in that folder, then it will import that spreadsheet and erase it.  You'll probably need a file utility plugin, but I use the 360Works ScriptMaster usually since the price is right (free license to use). 


            Automatically importing/syncing external data is a strong point for FileMaker and I find and an indespensible tool!

            1 of 1 people found this helpful
            • 3. Re: Having multiple databases auto update a 'master' database

              I have done this sort of thing in quite a few FileMaker databases over the years (many of them virtual timesheet and/or calendars). Based on what you have posted, my impression is that you just need to master the technique of filtering. Once you have that down, the possibilities are limited only by your imagination and patience.


              If you want to get an idea of what you can do with filtering, take a look at Allegro TimeClock - Freelance Edition, which is one of the turnkey solutions that I sell through my website (http://allegrodata.com). This version is intended for a single user on a single computer (basically sole proprietorships who bill clients or contractors by the hour). You can download a demo copy and read through the online help to get an idea of how it works. One of the new features allows the user to create a job estimate and then link it to one or more invoices. Depending on how the job goes, you can either link a single estimate to one or more invoices, or link multiple estimates to a single invoice. Filtering portals is what makes this possible. An even better example is on the Contacts Tab, where filtered and unfiltered portals show a client's estimates and invoices. Once an estimate or an invoice is marked as archived, it is no longer visible in the filtered portal. This makes it easier to find estimates that have yet to be approved or abandoned and invoices that are active or unpaid.


              I am getting ready to release a second version, which is a bit more sophisticated (Allegro Timeclock - Small Businesses Edition). It is intended for companies with multiple employees who will share the solution on a network. When an employee logs in, he sees a timesheet reflecting his own hours. He "punches in" by clicking a button to create a portal row, then selects from a drop down list of open jobs. To "punch out", he clicks a second button that enters his out time. An employee can log hours on any open job. The manager's interface that looks very much like the one in the Freelance Edition. But, when a manager looks at a jobsheet/invoice, he sees all the hours logged on the project by all the employees who worked on it. Managers can add line items for expenses, materials and other adjustments to the invoice. They can also correct hours (for example, if an employee forgets to punch in or out). There is a new Payroll Tab that allows management to view each employee's unpaid hours and make a record of each paycheck as it is issued. Clicking a button creates a portal row in the payroll table that sums all the unpaid hours, multiplies the total by the employee's hourly rate, deducts taxes, and gives a grand total. Then it marks all the hours for that paycheck as paid and removes them from the portal of unpaid hours. If you make a mistake, you can click a button which voids the check in the payroll portal and restores the rows removed from the unpaid hours table. It locates the rows needed by the check number (a key field that is populated by the same script that creates the row for the paycheck).


              Filtering tables by key values that the user can change when he presses a buttons is a very useful task to master. It enables you to temporarily hide records that aren't relevant. I find that clients would much rather do this than a find + an omit + a Replace operation. It's faster and there is less opportunity for error. Filtering drop down lists by a relationship or comparing a value entered to an aggregate of the values previously entered for a field can also be helpful. In my solution, I use these techniques to prevent employees from logging hours on jobs that have been completed and to prevent managers from assigning duplicate check numbers to payroll record, resepectively.


              As for working with external sources. Sure, you can do that with FileMaker. I've done it myself often enough. I've even built solutions where FileMaker is used merely as a front end for a SQL database, or to catalog and browse hosted media files through the web viewer control. But, before you link to something like an excel spreadsheet, you should take a hard look at whether it wouldn't be better to replace the spreadsheet with a table in FileMaker. My rule of thumb is to always try things the simplest way first and to always try to use just the features in FileMaker unless there is a compelling need to do otherwise.

              • 4. Re: Having multiple databases auto update a 'master' database

                TaylorSharpe has a very good idea for databases.


                For FileMaker based databases you were on the right track with "File>Manage Database" and "File>Manage>External Data Sources".

                Whenever you make a new database file (not the master) you go to "Manage Database" then the "Relationships" tab.

                In the bottom left there is the Add Table button, where you can choose the "Data Source" master file.  If the master file is not there you can add a "data source" from the dropdown.

                After the master files is added the tables should appear.

                Add the table that you want that database (or department) to have.

                Then you can make layouts based on those Table Occurences.


                If you need help with any of that let me know.

                • 5. Re: Having multiple databases auto update a 'master' database

                  Thanks for all of the replies I really appreciate it!


                  I had originally had the idea that each department shouldn't see each others data and therefore thought this could only be done by seperating the databases.  After re-examinig this with the ammunicition you guys have luckily provided for me I think it will be best bet for me would be to just have one database with permissions set for each department!


                  Thanks again.

                  Jesse Korosi

                  • 6. Re: Having multiple databases auto update a 'master' database


                    I am new to forum: I'm having hard time how to make relationship for two dabase file.

                    I really appreciate your help and support. Best regards, Rolando

                    • 7. Re: Having multiple databases auto update a 'master' database

                      From the first Database you need to go to File>Manage>External Data Sources.

                      Then Click New -> Then Add File

                      If it is local find it on your computer.  If it Remote, click the Remote button.  Click OK and fill everything (like Name) to accept the new data source.

                      Here's a link to help: http://www.filemaker.com/11help/html/odbc_ess.20.3.html


                      Once you have sucessfully added a data source you can add it to the current database.

                      Go to File>Manage Database and the on the top tab go to Relationships.

                      Then click on the button on the bottom left ot add a table as you normally would.

                      For the Data Source drop down box.  Choose the data source, which then you can add whichever table you want.


                      (You can actually do this step first, and add the data source once you get to the drop down when adding a new table.)


                      Then with the new table you can connect to whatever existing table there already is.