6 Replies Latest reply on May 9, 2011 9:26 PM by JamesRoland

    Newbie:  Plz help me find how to learn linking databases (not tables)

    Nikki59

      Title

      Newbie:  Plz help me find how to learn linking databases (not tables)

      Post

      Hello folks,

      I'm thinking to develop three databases that I can link together for my double-breasted business.  Not convinced yet of building multiple tables for reasons listed below.

      I have two distinct sets of staff with turnover so don't want to invest too much on training.

      Thought to create one database for the resort, one for the restaurant and train staff how to use a simple input layout for each respective business.  I assume I can lock them out of anything important on these.  I would then create a 'Master Database' for me to reference the other two to generate invoices and reports.  

      My logic is that three databases are good in case the resort and restaurant parted ways.  Each database / layout set could go along as stand-alone data and templates with office value.  But that's just an inexperienced assumption.

      Is this the best way to accomplish this ?   Would really appreciate your sage advice.  One thing I have learned so far is that initial planning in creating a database is an important and delicate time and would love to hear if I'm going down a rocky road.

      I don't expect so much help as to walk me through this complicated work, but can you help direct my research ?  

      What would I be attempting to do in "FilemakerSpeak" so I can look it up ?  

      I'll get some education and ask better questions later !

      Thanks a million...

        • 1. Re: Newbie:  Plz help me find how to learn linking databases (not tables)
          philmodjunk

          First, you can really only link tables in FileMaker not "databases" a term in "FileMaker speak" that gets a bit vague as "database" can refer to a single file or a group of files that together comprise a complete solution for what you need to accomplish with FileMaker.

          When you link two tables, the two tables can be in the same file or in separate files, but FileMaker treats the link much the same once you have defined the link. Look up External Data Sources and how to use "Add FileMaker Data Source" to set up a table occurrence "box" in Manage | Database | Relationships that refers to a data-source table in another file instead of a data-source table in the same file.

          When it comes to whether this is a good idea, there's no hard and fast rule that will make the decision for you. You'll need to weigh the trade-offs and decide for yourself.

          "Thought to create one database for the resort, one for the restaurant and train staff how to use a simple input layout for each respective business.  I assume I can lock them out of anything important on these.  I would then create a 'Master Database' for me to reference the other two to generate invoices and reports."

          In reality, this can all be done with a single combined file of all tables and all layouts for both businesses. You can use Manage | Security to set up accounts for each user that limit their access to specific parts of the system and even to specific records within the given tables so you don't have to separate your system into different files to accomplish this.

          "My logic is that three databases are good in case the resort and restaurant parted ways."

          Hmmm, that's a slightly better reason for having separate system. It's not absolutely necessary, however. Should such a split occur, you could save copies of your database and purge records from each copy as needed so that each business can be given a copy of the data specific to their needs. This would take some effort to accomplish, however, and you'd need to keep this possibility in mind while designing the system so that you can easily perform finds to locate the records you need to purge from each copy of your divided database.

          Questions to ask yourself that may help you decide:

          How much "overlap" is there between these two businesses, both now and how much is likely in the future?

          If you have employees that work at both businesses or who might do so in the future; If you have business contacts (Customers, vendors) that may engage in business with both; if you have any common resources/expenses that are common to both...

          The more such overlap, the more duplication of effort and duplication of data you can avoid by keeping your system "unified".

          Do you need "overall view" reports that combine data from both businesses?

          If so, these reports are often much easier to create when the same type of data can be drawn from a common table where a field in each record is used to "label" the record in order to identify the business for which it records data.

          • 2. Re: Newbie:  Plz help me find how to learn linking databases (not tables)
            Nikki59

            When you link two tables, the two tables can be in the same file or in separate files, but FileMaker treats the link much the same once you have defined the link.

            Look up External Data Sources and how to use "Add FileMaker Data Source" to set up a table occurrence "box" in Manage | Database | Relationships that refers to a data-source table in another file instead of a data-source table in the same file.

            So I'm guessing this is my solution, whatever it means exactly (smile).  I'll try to figure it out better.  Never realized Databases had such a complex language !

            I do hve a strong desire to offer a seperate database file my housekeeper can click on and see maybe four layouts for data entry with fields like: (date, client name, room number, housekeeping charges, massage, laundry) as well as do things like check her schedule.  Same for my Restaurant with fields like: (Date, Client name, room number, date and tab) as well as additional layouts for schedule, stock orders needed, etc.

            My database would be the tough one, linking these files of Housekeeping with Restaurant so I could have layouts with fields like:  Client Room Charge (formula), Client Restaurant Charge (formula), Stock Order Request etc. in whatever layout I decide to build for loveliness sake. 

            Right now (as I understand) a 'one file solution' would have 20-30 layouts or more on the pull-down menu.  True ?  If so I don't want my employees having to deal with that whether they were locked out of most of them or not.  

            Would rather have a .fm7 file renamed "Housekeeping.fm7" or "Restaurant.fm7" sitting on the iMac desktop for their data specific entries.  

            Am I on the righ track here ?  Again, just looking for sage advice as to whether this solution would be resonable or difficult to build the architecture for.  

            With your advice I'll choose a road and learn where the curves and hills are.

            • 3. Re: Newbie:  Plz help me find how to learn linking databases (not tables)
              Nikki59

              Hey, just had another thought, hope its not frivoulus.

              Can I have three or more database files and generate REPORTS using data from all three ?  Seems that would be an easy way to link what I need while keeping a certain independence.

              I'm in the Paper Phase of all this (I read the books) and still imagining what is possible.   My head is hurting from reading manuals.

              Thanks for explaining just what these programs can do and can't. 

              • 4. Re: Newbie:  Plz help me find how to learn linking databases (not tables)
                FrankFetzko

                Nikki, I'm learning too and I think I just saw one of your lightbulbs go off.  When Phil said it's 'tables' not databases just replace the word database in your sentence:  Can I have three or more database files and generate REPORTS using data from all three?  

                The answer is YES you can have 3 or more TABLES ... then generate reports using data from all three. 

                (Phil stop me if I'm wrong).  I think we tend to overcomplicate.  I definitely overthink this stuff ... 

                • 5. Re: Newbie:  Plz help me find how to learn linking databases (not tables)
                  Nikki59

                  I just got this from the Filemaker Help:

                  Accessing external data sources

                  You can connect to and work with data in external data sources in much the same way that you work with data in the current, active FileMaker file. For example, you can:

                   •

                  create tables in the relationships graph for external FileMaker files and ODBC data sources

                   •

                  work with data from external files in FileMaker fields and layouts

                  With some effort I can make this happen, mostly I'm glad to have gotten help ferreting out what Filemaker Speak called these things.  I will fasttrack my learning in the direction of the 'relationship graph'.  There's so much to learn I'm trying to segment what is most important at the earliest phases of design.  Right now the most basic question:  One database (fm7) file or three ?

                  Still wondering though if I'm going down a dangerous path and is partly why I started this thread. 

                  Just because someone can (or might) be able to do something, not certain if they should do so as far as maintenance commitment or 'bug hunting'.  For example it also references OBDC or something, with troubleshooting help.  Probably don't want to go there.

                  • 6. Re: Newbie:  Plz help me find how to learn linking databases (not tables)
                    JamesRoland

                    Howdy,

                    While you are in the planning phase, yet another "FileMakerSpeak" item you should strongly consider, is the "Separation Model". As the name suggests, this separates the data from the presentation (screen and print, etc.) By the sounds of what you have in mind, this could help. A search through the Knowledge base will turn up numerous items as will a web search.

                    I am using this model for the solution that I am now developing. The data (all the tables) resides in one .fp7 file and that is all that is in there (basically). I then have "separate" .fp7 files for DesktopLayouts, iPadLayouts, iPhoneLayouts, IWP(Instant Web Publishing)Layouts and Reports; each of these .fp7 files has NO TABLES; they are linked to the Data.fp7 file. Use the method described in the above posts to access "external" FMP data sources (the Data.fp7 file in my case). A bit more work to set up initially but well worth the effort down the road in maintenance, updating and modifying.

                    Yet more to read! :)

                    Cheers!
                    Lyle