7 Replies Latest reply on Apr 3, 2014 12:42 PM by GiovanniAndreani

    Common Forum Relationship and Field Notations Explained

    philmodjunk

      Title

      Common Forum Relationship and Field Notations Explained

      Post

           This post is being made so I can refer others to it when they have trouble understanding commonly used text based notation to describe fields and relationships.

           Compare the following text to the uploaded image from Manage | Database | Relationships:

           Invoices-----<LineItems>-------Products

           Invoices::__pkInvoiceID = LineItems::_fkInvoiceID
           Products::__pkProductID = LineItems::_fkProductID

           Note how ----< stands for a "one to many" relationship and note how it is represented by a similar line in Manage | Database.

           Note how the first line of text matches up with the boxes and lines in Manage | Database and how the following two lines document what fields are matched to which in these relationships.

           Additional notes:

           __pk is a commonly used naming convention to identify Primary Key fields. A primary key uniquely identifies each record in the table where it is defined. In FileMaker, it almost always is an auto-entered serial number field. I've used two underscore characters so that it sorts before all other field names in any alphabetized list of field names--often very useful when working with fields duing database design.

           _fk is a commonly used convention for Foreign Key fields. Values in foreign key fields are not unique. They match to a value from another table--often the other table's Primary Key. Note how the rest of the field's name identifies the table and primary key field to which it matches. A single underscore ensures that the field will sort before all fields except the primary key field. Foreign key fields are not set up to auto-enter a serial number. If they have any auto-enter setting at all, it's a calculation designed to automatically match it to the primary key of the current record in another table.

           Invoices, LineItems and Products shown in this example aren't necessarily the names of actual tables in your file. They are called table occurrences. To learn more about table occurrences, see this thread: Tutorial: What are Table Occurrences?

           Though not shown in these examples, sometimes I need more than one Table Occurrence with the same data source table. In those cases, I use the name of the data source table first, then use | followed by additional text that identifies the key purpose of this additional table occurrence. Thus, if I added another Table Occurrence, Products|Taxable to the example given here, Products would be the name of its data source table and "Taxable" helps me to know that this relationship only matches to taxable items from the Products table.

      SimpleRelationshipMap.PNG

        • 1. Re: Common Forum Relationship and Field Notations Explained

               May I suggest a friendlier method?

               When I create a field for identification purposes I begin it with ID_.

               And when I create a field that links to another file I use LINK_ and paste in the ID_ stuff.

               ID_Serial_Contacts, ID_UUID_Contacts and so on.

               Then in any file linking to Contacts I create

               LINK_ID_Serial_Contacts and ID_UUID_Contacts

                

               I find this easier for me than trying to read thirty characters with or without caps and no spaces.

               I can also create a self relationship link:

               SELFLINK_ID_UUID_Contacts

               or note if the field is a global link:

               GLOBALLINK_ID_UUID_Contacts

               I think my main issue here is using the 8 character limit from DOS and the anxieties that used to cause which where resolved when I bought a Mac and could use many, many letters and which Windows then followed suit in its clone of the Mac interface with very, very, very large file names.

               Since Filemaker uses a secondary internal database for the text names we see, we have the freedom of using many, many characters for the names and can create our own nomenclature. Just avoid certain reserved characters.

                

          • 2. Re: Common Forum Relationship and Field Notations Explained
            philmodjunk

                 To each they're own. A nice thing about naming conventions is that we can adapt a method to suit our own preferences. This thread has been made primarily to help "newbies" to decode some common notation used in many threads throughout this forum rather than to recommend a specific naming convention.

                 

            we have the freedom of using many, many characters for the names and can create our own nomenclature.

                 While that is true to some degree, one current complaint to Filemaker is that some dialogs and drop downs where we try to access a field by name cannot be expanded to handle long names and they then get truncated--making it hard to select the correct field or table occurrence in some places.

            • 3. Re: Common Forum Relationship and Field Notations Explained

                   The fact that Filemaker dialogs are often inadequate to the task at hand, such as fixed dialogs sized 400 x 400, are a reflection of management's failure to create the best product possible while being unwilling to spend a few bucks to update the windows and dialogs we use constantly. 

                   We have scrolling dialogs that could be resized to show all of the options, we have field pickers that don't show the full field name, we have dialogs that forget how we sized them and we have to resize them every time we open them...

                   I would be glad to fly to California at Filemaker's expense and discuss all of the needed updates (at least 8 years past due in some cases) for an agreeable fee and provide the input that would humanize these antiquated dialogs.

                   Maybe we need a rogues gallery of management photographs to show whose responsible for  this faillure to update ancient dialogs?

                   (Send me a picture for my blog!)

              • 4. Re: Common Forum Relationship and Field Notations Explained
                philmodjunk

                     Please remember that I don't work for FileMaker. I'm just another fellow user.

                • 5. Re: Common Forum Relationship and Field Notations Explained
                  janslort

                       Phil:  

                       Very cool.  I just learned to use two brand new tools. 

                       Thanks

                       Jan

                  • 6. Re: Common Forum Relationship and Field Notations Explained
                    bk1

                         Thanks for all your great tips! You say "Note how ----< stands for a "one to many" relationship and note how it is represented by a similar line in Manage | Database." Why is it that any relationships that I create always have the ">----<" symbol? Am I supposed to be able to designate a relationship as One to many / many to many someplace?

                          

                    • 7. Re: Common Forum Relationship and Field Notations Explained
                      GiovanniAndreani

                           In relation to this phrase by  PhilModJunk

                      "This thread has been made primarily to help "newbies" to decode some common notation used in many threads throughout this forum..."

                      Is this a "common" notation just within this forum o is it part of a wider accepted convention? Also, is there an official convention for using such notation? It would be quite interesting and useful to know.