4 Replies Latest reply on Sep 26, 2012 3:26 PM by jameshoty

    FTS - DB Relationship / ToC




      As a new comer to FM, I have challenges with creating relationship and the concept of Table of Occurrence. The training series on this topic is not adequately covered or lack with exercises. When I study the relationship diagram of the Bonsai Garden, I am struggling to interpret the designer's mind with little help / explanation. 




        • 1. Re: FTS - DB Relationship / ToC

          Not sure what your specific question is.  But the concept of a table occurance is crucial to understanding "context" for both layouts (screens) and scripts.


          Scripts always run within the context of a layout, and a layout is based on Table Occurance (TO).  From that TO you can reach out to related TOs.  Spend some time getting your head around that concept, trying differnent things until it clicks.  Feel free to ask specific questions, we're here to help.

          • 2. Re: FTS - DB Relationship / ToC

            Thanks, I don't have a specific question here. Since this discussion thread is on product improvement, I just wish to feedback if the FTS can give more exercise, hands on on this topic about DB relationship / Table of Occurrence.

            • 3. Re: FTS - DB Relationship / ToC

              Another way that I've found helpful is to think of the T.O. as an "alias" of the actual table.

              If I have Companies as a table, my T.O./alias on the graph can certainly have the _label_ "COMPANIES" (or "companies") and I can view it in a layout, interact with it in scripts, make "joins" on the relationship graph, etc. I can duplicate the T.O. and name it something different if necessary (and this IS necessary for a "self-join"). I can connect T.O. on the graph using various terms that one might find in a SQL query, WHERE clause.


              The Manage Database shows what the Occurrences in Graph are named in the Bonsai db (FTS):



              You can go back and study the Relationships graph, itself, too. If you click on the "connector" (typically a box with an equal sign - although there are others) between any two "alias'", you get another view of the relationship between the two T.O.s.


              This example:


              is somewhat like the SQL

                    WHERE CUSTOMER.__kp_CustomerID = customer_ADDRESS._kf_CustomerID


              or in this case, because it's a JOIN:

              FROM Customers AS CUSTOMER

                    LEFT JOIN Address AS customer_ADDRESS ON

                           CUSTOMER.__kp_CustomerID = customer_ADDRESS._kf_CustomerID


              notice the "alias" that I've assigned to each of the tables and then called them as such in the "ON"?


              I've used "LEFT JOIN" to illustrate that the CUSTOMER is a basis for the fields that might be on a layout, and the layout is in fact tied to the T.O. "CUSTOMER". If I want to show a portal on the layout with (possibly multiple) Addresses, I choose the T.O. "customer_ADDRESS" for the portal and use fields from it. The portal can have 0 or more addresses listed, and they are specific to that customer in that record. The next record, is the next customer, so the addresses would be different than in the previous record.


              This Edit Relationship dialog also has "Allow creation..." under the customer_ADDRESS, so our portal row has a new blank line at the end and we can simply type into it. Once the record is committed (saved) the new address will be tied to the customer with the relationship we defined.


              The Bonsai graph uses what is called "Anchor Buoy" method (or squid). There is a main table/T.O. with several relationships from it. There IS some redundancy, such as the relationship CUSTOMER--<customer_ORDER and another relationship ORDER>--order_CUSTOMER. These are essentially the same relationship, but from a different perspective. It's a way to easily see when you are looking at layout, the related information. One starts from the CUSTOMER T.O., the other from the ORDER T.O. There has been much discussion on  this forum and others to remove the redundancy, but it may be a matter of personal choice and what you need to do from the "layout perspective". But I don't want to confuse you, James, just explain this particular solution.


              I agee, the "Product" FTS maybe could use improving. But it's not meant to "be all". You'll want to study it and all the Product (FileMaker Pro, FileMaker Server, Filemaker Go) documentation and Help topics (from FMI), attend one of the many training courses and read other books, as needed. These forums are for the purpose of answering the questions that one has on the various topics and to get insights into  ways to use the tools set that is "FileMaker Pro".




              • 4. Re: FTS - DB Relationship / ToC

                Hi Beverly,


                Thanks for explaining. It certainly helps.