8 Replies Latest reply on Mar 8, 2011 3:09 PM by billcritten

    Tables / Relationships Question

    billcritten

      Title

      Tables / Relationships Question

      Post

      I inherited a Filemake Pro 11 DB file.   I come from a .Net (VB.Net/C#, MS Access/ Sql Server) background.  So far in fm, I have finished the tutorial that ships with the product.  I still have some questions.  I have attached some images from the Database Manager on Relationships and Tables.  The Tables tab appears to state that there are 2 tables, but the relationships tab seems to be showing more than 2 tables (I'm basing this observations on my experience with the MS Access relationships window).  Based on these images - how tables does this fm DB contain? 

       

       

      chRelationships.JPG

        • 1. Re: Tables / Relationships Question
          billcritten

          It looks like I can only post one image per Thread?  Still learning how to use this forum in addition to learning Filemaker Pro.  I have one other image from the Tables tab that appears to show only 2 tables.

          • 2. Re: Tables / Relationships Question
            philmodjunk

            Yes, it easiest to upload a post to just the first message in the thread. To link files to subsequent posts, you have to upload the file to a file sharing site and then include the link in your message post.

            Take a close look at your tables tab. For each listed table, you'll see a list of "occurrences" of each such table. Those "occurrences" correspond to the boxes you see on the Relationships tab. We call those boxes "Table Occurrences" or TOs for short. The tables created on the Tables tab are also referred to as Data Source Tables to distinguish them from TOs. Any given Data Source table can be referenced by any number of TOs so that you can define a variety of relationships between the same two data source tables.

            The TOs that show <Table Missing> are TOs where its Data Source table has been deleted on the Tables Tab.

            Here's a Tutorial on Table Occurrences you may find helpful. Since you have a background in SQL and programming, you may want to drill down to the tutorial it lists for an even more technical treatment of how Table Occurrences are used in FileMaker.

            Tutorial: What are Table Occurrences?

            • 3. Re: Tables / Relationships Question
              billcritten

              Thanks for this link

              Tutorial: What are Table Occurrences?

              While I study this, I wonder if I could ask -- abut the image above -- how to identify the fields used for each relationship?  I clicked around and did get an empty relationship dialog window.  How to determine which are the link fields?  It looks like Producer is the common field, but I don't want to take that for granted.  Additionally, it seems that the 4 tables related to the Inventory table are redundant (a sin in RDBMS :).  I am thinking -- eventually I will have to merge the content from these 4 anscillary tables into 1 table to reduce the redundancy.

              • 4. Re: Tables / Relationships Question
                philmodjunk

                What version of FileMaker is this?

                When I open my copy of FileMaker 11 and check the Relationships Graph, the relationship lines directly connect the key fields that define the relationship. Often, this can be hard to see as the complexity of the system grows with the same field linking to more than one TO, but you can always double click the line linking two TOs to get a dialog that shows exactly what fields are serving as relational keys and what operator was used in each pair of key fields.

                Many developers use naming conventions that sort key fields to the top and clearly identify them as fields used in a relationship.

                _kf_TableNameID for a foreign key field and _kp_TableNameID for a primary key field, just to give one example.

                And I'd definitely merge the records in tables Inventory 2, Inventory 3, Inventory 4 and Inventory 5 into the inventory table and then eliminate them.

                • 5. Re: Tables / Relationships Question
                  billcritten

                  Yes, since I am code monkey DBA boy I got recruited to fix these redundancies because the app was not functioning correctly.  It was developed by a non-deeloper person and not too bad at that -- except that they needed more functionality which required someone with a little more training.  Right.  They could have read the tutorials just like me.  Now I will be a trained code monkey DBA boy :).  And while I am in training (bananas please) I opened up one of the Inventory# tables and noticed (not to my surprise) that there were no unique fields.  The tutorial showed how to add a serialnumber field, but I noticed that it only starts populating on new rows.  Is there a way to populate a unique field column automatically with like autonum/Identity values (I have about 4000 rows to populat) ?

                  • 6. Re: Tables / Relationships Question
                    philmodjunk

                    Fortunately, adding a primary key field is very easy to do in FileMaker.

                    After defining the new field and setting it up to auto-enter a serial number, replace field contents can be used to update a field for all records in your found set with serial numbers and also to update the auto enter settings at the same time so that the next new record correctly gets the next serial number.

                    Go To a layout based on this table. Choose Show All Records. Put the cursor in the serial number field and select Replace Field Contents from the Records menu.

                    • 7. Re: Tables / Relationships Question
                      billcritten

                      OK.  I went to the layout view of my new layout for Inventory2 table.  I went to Manage Database, Fields and added my new Identity column, then clicked on Options.  I see a Serial Number checkbox, but I don't see Replace Field Contents as a selection here.  Am I in the correct view?

                      • 8. Re: Tables / Relationships Question
                        billcritten

                        Nevermind.  I figured it out.  I was looking at the wrong menu.  Had to look at the top menu (it is so fun learning new stuff :).  And I do wish to extend my thanks and appreciation for all the help/support (I'm sure I will have more questions).