9 Replies Latest reply on Jun 22, 2011 11:37 AM by philmodjunk

    Relationship Deffinitions



      Relationship Deffinitions


      I'm learning FMP and converting a large legacy applcation over to it.
      FMP seems to accomplish relationships in a very simmilar fashion but with much different results. I can do it with little effort but I don't understand it.
      My confusion centers aound understanding these definitions:
      1. What is the difference between a "FILE" (as in Recent Files & Favorate Files) and "TABLE"
      2. What is the difference between a "TABLE" and a "TABLE Occurance"
      Any help you can provide will be appreciated.
      Thanks, Jack

        • 1. Re: Relationship Deffinitions

          Depending on the version of Filemaker the defintion a file could vary but in general a file is a collection of Filemaker objects and defintions. (older versions of filemaker only allowed a single table but multiple layouts scripts etc.)

          A table is a collection of fields.

          A table occurance is refrence to a table used in a relationship. Since tables can be join(related) in many different ways each table occurance is used to differentiate those relationships. (the creation of a table will result in a default table occurance being created with the same table name)

          Filemaker doesnt do traditional "joins" as most SQL database do. Filemaker creates static join criteria as relationships. Reference to these relationships is done by selecting a "table occurance"

          If Phil hasnt already posted the tutorial he should be by in a minute and do so.

          • 2. Re: Relationship Deffinitions

            Once upon a time (filemaker 6 and older) you were limited to one table in every file. Thus some literature still appears to use the terms interchangeably.

            In current versions, a file is just that. It's a file of database information just like files created in Microsoft Word are different document files. In current versions a File can contain from 0 to a very large number of tables.

            A table is a list of similar type information that the developer has created. You can think of each table as being something like a spread sheet in Excel, but where each row of data is a record in your table and each column represents a different field in that table.

            Table Occurrences are what we call the "boxes" in Manage | Database | Relationships. They are convenient labels that each refer to a table defined in the tables tab of Manage | Database for this file or some other FileMaker file that the developer has chosen to link this file to. You can have many such table occurrence "boxes" that refer to the same table (called a data source table) so that you can create many different relationships with the same table in different parts of Manage | database | relationships.

            To learn more about table occurrences and how they control the function of FileMaker, see this tutorial. (Drill down to the second tutorial if you're up for a more technical treatment of the subject.)

            Tutorial: What are Table Occurrences?

            • 3. Re: Relationship Deffinitions

              aammondd & Phil,

              Thank you for your responce. I'd been moving along at a ripid rate and had gotten bogged down with this issue.

              Let me check to make sure I undersand this:

              In the Example give we had 2-Tables "Contacts" & "Phone Numbers"  BOTH IN THE SAME FIEL , lests say "CUSTOMERS"

              The relationship is between the 2-TABLES (Contacts & Phone Numbers) inside of the 1-File (Customers).

              End of Story.

              What I'd been familure with was a "Application" with a large number of "Files" each independent of one another unless a "Join" was created (mechanicaly much like the "Relationship" in FMP between the Tables inside a single File).

              In that older Data Base applicaton even after the "Join" was made, the indivdual "Files" were interlocked but maintained their own idendity so that data could be added to them individualy OR in the Join relationship with the same results.

              What I had done was recreated each of the FILES from the Older Original database in FMP (some 70>100) then I thought I would be joining the Files. If I understad you correctly, I can still salvage this work but I will import one recently created FILE as a TABLE into a Master/Parent File  and then I can delete the recently created File (the one that is now a child Table )

              So going back to your example:

              I had Created a "PHONE NUMBER FILE" and also "CUSTOMER FILE" with a "CONTACTS TABLE" in it.

              I will import the "PHONE NMUBER FILE" inot the "CUSTOMER FILE" as a 2nd, child TABLE, put in the key Primary and Key Foreign fields. Then I can Delete to no longer needed "PHONE NUMBER FILE"

              I've just been spending the better part of this last week trying to get the 2-FILES to Relate, aint going to happen.

              If that sounds right, please let me know and I can move ahead.

              Thanks again, you have been very patient.

              PS Massacusetts has a relatively recently law passed requiring proteting employee data such as First Name, Middle, Last Name, Drivers License and SSN. With the separte "FILES" and the use of work place aliases, I thought I'd figure out a fool proof way of protecting that information, now it is back to the think tank to see if separate TABLES will pull of the same protection.

              • 4. Re: Relationship Deffinitions

                You can import the tables from the other files if you like but you can still use multiple file solutions in the current FMP so you dont have to go to the trouble of importing them as new tables.

                In  the relationships tab you can add a table in another file by choosing the + button then choosing "Add filemaker datasource" from the drop down. This lets you see the tables in other files in this one.

                The issues around this have to due with making sure you always have all the files together in a directory structure that stays consistent.

                This creates a table occurance of the table in the other file so you can use it in relationships calculations layouts etc.


                • 5. Re: Relationship Deffinitions

                  One Table, One file structure used to be the only option with FileMaker so it still supports that option just as aammondd describes. If you want to pull together some tables into the same file, you can use Import Records with the new table option selected as the target table or, in FileMaker Advanced only, you can copy and paste the table definition from one file to another.

                  Using multiple files has its uses, but it also complicates the process of securing them all with passwords so you may want to research the pros and cons further before you decide on how you want to go here.

                  • 6. Re: Relationship Deffinitions

                    AAMMONDD & Phil,

                    Got it, Tried it on an adhock model using independten FILES and it woks, looks like my data protection for MASS Personal Info is back on track and going to work well

                    Thanks again,

                    You guys made my week


                    • 7. Re: Relationship Deffinitions

                      Feel free to add a description of your data protection scheme if you'd like. Others may be wrestling with the same issue and your post on the subject may help them out.

                      • 8. Re: Relationship Deffinitions

                         Will do,
                        Need to proof it out 1st
                        It will not be for everyone because it adds a manual file maintenance
                        step that will turn some off but it allows the actual confidential data
                        to reside on a memory stick in a safe somewhere only to be taken out by
                        HR as needed.

                         Mass 201CMR1700 Standards for Protection of Personal Information of
                        Residence of the Commonwealth
                        To me , paralyzing, I think most companies are just waiting to see if
                        they enforce it.

                         I'd spent most of the day going through FileMaker Pro 10 the Missing
                        Manual, FileMaker Pro for Windows, FileMaker Training Series and VTC
                        CDs until I asked you guys.

                         Thanks again for help.

                        • 9. Re: Relationship Deffinitions

                          Make sure that you also research the capabilities of Manage | Security. FileMaker has a pretty decent set of tools for controlling access down to the record level so that different users are permitted access to only specific records, tables and layouts.

                          I suspect that you can also find a plug in or two offered that can encrypt/decript data.

                          All of these are options that may eliminate the need for a memory stick kept in a safe.