6 Replies Latest reply on Jan 16, 2014 1:48 PM by philmodjunk

    Tutorial: What are Table Occurrences?

    philmodjunk

      Title

      Tutorial: What are Table Occurrences?

      Post

           What are table occurrences and how can I use them?

           This post is a new attempt at helping people new to FileMaker understand table occurrences and how to use them to get more out of their FileMaker solutions. (A past attempt: Table vs. Table Occurrence (Tutorial) proved difficult for some to understand.)

           Let’s start with a simple database to illustrate the concept:

           Define a table, Contacts, with the following fields:

           ContactID (serial number)
           Name (text)
           cFax (Calculation, define it with just “Fax” in quotes as its calculation and set it to return “text” as its return type)

           Define a second table, Phone numbers:

           ContactID (number)
           PhoneType (text)
           PhoneNumber (text)

           At this point you have two “tables” listed on the Tables tab of Manage | Database and two “tables” shown in boxes on the relationships tab. While you’re on the relationships tab, drag from ContactID in contacts to ContactID in PhoneNumbers to link the two so that you have:

           Contacts::ContactID = PhoneNumbers::ContactID

           Double click the line you’ve just made and select “Allow creation of records via this relationship” for the PhoneNumbers table.

           Click OK to dismiss Manage | Database. Enter layout mode and add a portal to PhoneNumbers on your Contacts layout. Add both the PhoneType and PhoneNumber fields to this portal. You can now easily add several phone numbers for a single contact and you can use the PhoneType field to enter phone types such as “Home”, “Office”, “Cell” or “Fax”. Add a few such records and make sure to include one entry where the PhoneType is “Fax”.

           Now let’s say you want to put a contact’s Fax number on the contact layout without listing all the other phone numbers for that contact. To do this, we’ll create a relationship between Contacts and PhoneNumbers that matches to records with the same ContactID and which also has “Fax” in the PhoneType field.

           But now you have a problem. You have a field in contacts, cFax, that could be used in such a relationship, but if you drag from cFax to PhoneType to get this relationship, your existing portal will change to only show phone numbers of type “Fax”.

           Here’s how to get around this issue:  Create a second table occurrence of PhoneNumbers so that you can link it Contacts by both ContactID and cFax = PhoneType without changing the existing relationship required for the portal. I’ll walk you through the process first and explain the terms later.

           Go to Manage Database | Relationships and click PhoneNumbers to select that “table” in the relationship graph. In the bottom left corner of this window, there’s a button with two green plus signs. Click this button and a new “table” labeled PhoneNumbers 2 appears. Drag from Contacts::ContactID to PhoneNumbers 2::ContactID. Then drag again from cFax to PhoneNumbers 2::PhoneType to define a relationship that matches records by contact number and when the value “Fax” is in the PhoneType field. Go back to your Contacts layout and use the field tool to add a field to the layout. Select the PhoneNumber field from PhoneNumbers 2 as the field to be added. Return to browse mode and note that the phone number entered in the portal and labeled as “Fax” now also appears in this new field.

           Now to explain the terms: Each “table” in the relationship graph is actually a “table occurrence” and each time you find a post in this forum that talks about table occurrences (sometimes abbreviated as TO), that’s what the writer should be referring to. Each “table” on the tables tab is a “data source table”. Every time you define a new data source table on the tables tab, FileMaker automatically creates a table occurrence box on the relationships tab with the same name. 

           Table Occurrences are simply convenient labels you can use to link your data-source tables in relationships. You can create as many as you need to create different relationships between the same data source tables. You can see this link between a table occurrence and its data source table by hovering the mouse over the arrow in the upper left corner of the table occurrence box. Hovering over this arrow on both PhoneNumbers and PhoneNumbers 2 reveals that they have the same data source table, PhoneNumbers. Conversely, if you click on the Tables tab, you see that the PhoneNumbers data source table lists two “Occurrences in Graph”:  PhoneNumbers and PhoneNumbers 2.

           In our example, the two data source tables are linked in two different ways so that we can see either all the contact’s phone numbers or just the fax number. The additional table occurrence, PhoneNumbers 2, makes this possible.

      Note to Readers: This is a thread that I point a lot of people to as a way to explain what I mean by the term "table occurrence". So many people get here from another thread. If you have a comment or question about this information, please click the back button and post it in the thread that directed you here. Due to an unfortunate bug in the forum software, threads like this that are more than 3 months old will not pop back up into Recent Items when you post a new comment to it and thus I am very unlikely to see your new comment if you post it here.

            

      TOtutorial2.JPG

        • 1. Re: Tutorial: What are Table Occurrences?
          philmodjunk

          Now here’s a more advanced trick you can do with table occurrences:

          Switch to the PhoneNumbers layout and select table view so that you can see a table of all records and with all fields visible. Enter layout mode and select Duplicate Layout twice from the layouts menu to create two more layouts identical to the PhoneNumbers layout. While you are still on the PhoneNumbers Copy2 layout, select Layout Setup… from the Layouts menu. Change the table occurrence listed in “show records from” to PhoneNumbers 2 instead of PhoneNumbers. Dismiss layout setup… and double click each field on the layout, so that you can re-select the fields to come from PhoneNumbers 2 instead of PhoneNumbers.

          Now return to Browse mode and perform the following experiment. Click on the column header for PhoneType so that it sorts the table by PhoneType in ascending order. Click on a field in one of the rows to make that record the current record. Now select the PhoneNumbers Copy layout. Pull down the column header menu for Phone Type and sort the records in descending order. Finally, click on the same phone number row that you selected in the previous layout and select omit record from the records menu to omit that record from the found set. Switch between the three phone numbers layouts and note how PhoneNumbers and PhoneNumbers copy have the same records visible, sorted in the same order and with the same current record. When you examine PhoneNumbers Copy2, you should see a different current record, found set and sort order.

          This is a simple technique you can use to work with different groups of records from the same data-source table: Each table occurrence on your relationship graph has a separate current record, found set and sort order for each window to the file.

          In one of my recent projects, I used one layout to pull up a list of “team leaders” from a Personnel table. I then used a different layout to search for and process records from the same Personnel data-source table to pull up a list of the team members of a selected Team Leader from the first layout. Even though both layouts ultimately referred to the same data source table, using different table occurrences in Layout Setup… enabled me to perform finds and sorts on lists of team members in the second layout without those actions affecting the list of team leaders in the first layout.

          2 of 2 people found this helpful
          • 2. Re: Tutorial: What are Table Occurrences?
            solvberg_1

            This is a very good explanation. I understood it...

            • 3. Re: Tutorial: What are Table Occurrences?
              kroywen19

                   Hello PhilModJunk,

                   I followed some of your posts and I like the way you explain every detail of the solutions to forum members' database problems.

                   I am a newbie in filemaker (I use FM10 advance) but have problems creating a db.

                   I have created a patients db (flat file). Have patient (px) information (name, address, dob,company. etc)

                   on the backround and created a tabbed layout with the following titles and fields done:

                   - Case History ( chief complaints, medical history. medications fields)

                   - subjective exams ( date of exam, age of px, refraction done by, prescription (eyeglasses data fields etc.)

                   - objective exam (insturmentation fields)

                   - purchases (spectacle frame, contact lenses)

                   - inventory of stocks

                   My problem is that I am unable to conect the relationship of these tables successfully  to the VISIT table I created in the manage/ database dialog box. I am confused where to start connecting which. In every record, I want to create new visit files for the succeeding eye exams of each patient.

                   I'm cracking my head and spent some nights understading how I can make a tabbed interface for my project.

                   Please see the screen shots.

                   THanks

                    

              • 4. Re: Tutorial: What are Table Occurrences?
                MorkAfur

                     Very nice. Extremely clear and easy to follow.

                     Great job.

                     -m

                • 5. Re: Tutorial: What are Table Occurrences?
                  eric

                       Suppose you have two unrelated table occurrence groups:
                       TOG1 with table occurrences  T1 = A1 = B1 and
                       TOG2 with table occurrences  T2 = C2 etc.
                       Without breaking layouts etc. based on TOG1 or TOG2,
                       how do I merge the two occurrences so that T2 = A1 = B1 and T2 = C2 etc.,
                       effectively replacing T1 with T2 in TOG1?

                  • 6. Re: Tutorial: What are Table Occurrences?
                    philmodjunk

                    Note to Readers: This is a thread that I point a lot of people to as a way to explain what I mean by the term "table occurrence". So many people get here from another thread. If you have a comment or question about this information, please click the back button and post it in the thread that directed you here. Due to an unfortunate bug in the forum software, threads like this that are more than 3 months old will not pop back up into Recent Items when you post a new comment to it and thus I am very unlikely to see your new comment if you post it here.