7 Replies Latest reply on Oct 24, 2011 1:38 PM by LWayne_1

    Complex Relationships (two fields from each table)

    LWayne_1

      Title

      Complex Relationships (two fields from each table)

      Post

      I am new to FileMaker Pro but I am familiar with Microsoft SQL.  When I create a complex relationship (two fields from each table) I would expect the new Table Occurrence to show only the records from both tables where both fields match, like a filtered View in MS SQL.  However, a get all records, is this what should happen?  What would be the purpose of a complex relationship if this is true?

        • 1. Re: Complex Relationships (two fields from each table)
          ninja

           Howdy,

          I wonder if "Perspective" is the issue you are running into...

          When I create a complex relationship (two fields from each table) I would expect the new Table Occurrence to show only the records from both tables where both fields match, like a filtered View in MS SQL. 

          This is true, but only when viewed from the Perspective of the new table.  If you are on the old table occurrence itself, you will see all records.
          Create a layout based on the new TO, then put a portal on that layout, said portal being from the other table.  You'll get only the related records.
          If you get otherwise, make sure your relationship is in order.

           However, a get all records, is this what should happen?

          If you are on the 'old' TO which the records are in, yes.
          If you are on the 'new' TO on the other side of the relationship, no...you'll get records 'filtered' through the relationship.

          Is this what's going on?

          HTH

          • 2. Re: Complex Relationships (two fields from each table)
            LWayne_1
             
            Hi,
             
            Thanks for your response. I am not sure I am doing the right thing so I thought I would explain what I am doing.
             
            1. In the Manage Database - Relationships tab I highlight each table individually and click on the + sign in the lower left hand corner.  I then click on table 1 rename it.  I do the same thing with the second table.
            2. After dragging any field from new table 1 to anyfield on new table 2, I click on the relationship line and match up the two fields from new table 1 to new table 2.
            3. I then go to Manage Layouts and make sure each of the new table occurences are added.
            4. In browse mode under layout of each one, I do see see both filtered and unfiltered records; not just the filtered ones.
            Apparently I am not setting the table occurrence up correctly.  
            • 3. Re: Complex Relationships (two fields from each table)
              philmodjunk

              Each layout in your database is associated with a specific table occurrence. You'll see the name of that occurrence in the "show records from" drop down located in layout setup...

              For any fields you specify for this layout taken from that one table occurrence, the relationships you define have no direct effect on the records shown. You can perform a find to find any group of records in that table matching the criteria you enter in fields from this table occurrence, do a Show all records, show omitted only, etc and the existance of relationships to other tables or occurrences of the same table makes no difference. The relationships you choose to define linking the layout's table occurrence to other tables control what data is visible from these other tables, based on the values in the match fields of the current record in the layout's table occurrence.

              This is what Ninja meant by "perspective". Your "point of view" of the data in other tables, depends what occurrence, what current record in that occurrence and how it is defined to be linked to the other table occurrences in your system.

              For us to specifically help you debug this, we'll need to know on which tabel occurrence your layout is based and what kinds of relationships you've defined linking it to the other occurrences from which you are adding fields on your database. Then Ninja, myself or another helpful individual can spell out in detail how to get what you need to see.

              • 4. Re: Complex Relationships (two fields from each table)
                ninja

                 Seconding Phil,

                Creating a table occurrence does not change the TO that your layout is based upon...it just creates the TO.

                You define the TO that the layout is based on as a separate step.

                This allows you to view records from Table 1 (TO#1a) unfiltered, view records from the same Table 1 (TO#1b) filtered by one set of criteria, and view records from the same table 1 (TO #1c) filtered by another set of criteria.

                It is a handy tool, being able to have more than one occurrence of a table...but you have to pay attention to which TO any given layout is based on.
                Same goes with the fields in the layout...they are TO specific.

                • 5. Re: Complex Relationships (two fields from each table)
                  philmodjunk

                  We seem to have two threads on the same issue from the same poster here:  Filtering a Related many table in a one to many relationship

                  Now clicking over to the other thread to see what's been added to that thread...

                  • 6. Re: Complex Relationships (two fields from each table)
                    LWayne_1

                    All -

                    I think I finally understand what to do.

                    Thanks for all your help!

                    • 7. Re: Complex Relationships (two fields from each table)
                      LWayne_1

                      All -

                      I think I finally understand what to do.

                      Thanks for all your help!