5 Replies Latest reply on Apr 29, 2017 6:41 AM by beverly

    Multiple Table Occurrences

    leeputnam

      Can someone please help explain multiple table occurrences on the relationships tab?

      What does it mean when one is created?

      Is the data duplicated?

      When is a good time to use them?

      When not to use them? Advice?

       

      Any help would be greatly appreciated!

        • 1. Re: Multiple Table Occurrences
          RickWhitelaw

          The most common reason for duplicating a table odd urgence is to use it in a different relationship or to prevent circular reasoning from happening.

          • 2. Re: Multiple Table Occurrences
            leeputnam

            So Lets say I have this...

             

            Manufacturer <- Accounts (Many to Many) -> Customer -> Quotes -> Line Items

             

            I could create a second occurrence of "Line Items" to have...

             

            Manufacturer -> Line Items

             

            Then I would be able to see all line items for that manufacture with out going through the Accounts -> Customers -> Quotes?

             

            If I create a second occurrence of "Line Items" will both occurrences have the same records? 

            • 3. Re: Multiple Table Occurrences
              ch0c0halic

              All Table Occurrences (TO) on the Graph are "Aliases" of the base table. None of them 'are' the base table.

               

              The Graph cannot be configured to have a circular reference, for example a table occurrence group (TOG) of A<->B<->C<->A where the lines on the graph literally make a circular path.

               

              So to get the right context to create a circular 'data' path you add a new Table Occurrence (TO) of A as A1

               

              So the graph looks like A<->B<->C<->A1

               

              This allows you to get data from A1 based on the path of the TOG.

               

              For example table "People" has many roles, for example: Manager, Project lead, peon.

               

              Case 1) If you want to see, in a portal, all the peon's for a specific manager it's a TOG of People_id to Manager_id, my people_id is in your manager_id field.

              Case 2) If you want to see, in a portal, peon's for a project lead from their manager. You get a TOG of A<->A1<->A2

               

              Each of the TO's represent something different depending on which TO a layout is based on. The layout TO establishes the context within the TOG.

               

              If the Layout is based on A it represents managers because the relationship from A to A1 uses Case 1.

              In Case 2 the Layout TO is still A but the portal is from A2, based on the A1::people_id to the A2::project_manager_id.

               

              You really need to review the Getting Started materials. You will need to understand all these terms to begin to understand how FMP works.

              1 of 1 people found this helpful
              • 4. Re: Multiple Table Occurrences
                philmodjunk

                See this tutorial on the concept:

                 

                Tutorial: What are Table Occurrences?

                • 5. Re: Multiple Table Occurrences
                  beverly

                  This is consistent with the SQL table alias when using the same table in a self join! You must name (alias) the table if used twice (or more).

                   

                  I like to have the same name on the RG (relationship graph) as the base table, but it's not a requirement. So I think, perhaps the confusion comes when the defined table has a name and that same name is on the RG for the TO/table alias.

                   

                  in addition to Phil's tutorials, other things to help:

                  1. start here

                  Key Concepts in FMP7.pdf

                  (Michael Harris' wonderful article written when relationships were new in FMP and he coudn't find much on the subject!)

                  The 'graph' has changed, the concepts have not.

                  2. then learn more and about various "methods" of designing the RG

                  FileMaker Solution Architectures Technical Brief

                  (Data Modeling, File Architectures and the Relationships Graph)
                  beverly