6 Replies Latest reply on Feb 8, 2011 2:11 PM by pdoak_1

    One to many relationship

    pdoak_1

      Title

      One to many relationship

      Post

      Background Info: I am using the following

      1. Filemaker pro Advanced 11
      2. Mac OS X 10.6.6
      3. Single user mode
      4. I have been using Filemaker Pro for a few months

      I have two tables.  One which contains a master list of stocks with a primary key of __kp_SEDOL and the other which contains the fields _kf_SEDOL and the next reporting date for that company and the date that the record was updated.  This table is updated monthly with new records.

      I have created a relationship between the tables based upon the SEDOL common field.  However, when I create a layout from the master list table why can't I see any data for the reporting date field of the other table.  It is just blank!

        • 1. Re: One to many relationship
          philmodjunk

          How did you create this layout? Did you place the date field from the secondary table directly on this Master List based layout or did you use a portal?

          Since you describe this as a one to many relationship, I would expect a portal to be the means to make this work so that you can see all the related records--not just data from the first related record--which is what you'll get if you just put the fields from the secondary table directly on a layout based on the Master List table.

          • 2. Re: One to many relationship
            pdoak_1

            How did you create this layout?

            I placed the field from the secondary table directly on the layout.  I don't not want to use a portal as I wish to display just the latest date from the secondary table.  

            My understanding is that the relationship should pick up the first match and as long as a sort the table in date order, I should get the result that I want.  However, the field in the layout is blank which is what I don't understand.

            • 3. Re: One to many relationship
              philmodjunk

              If you sort the records in descending order by date and this field is indeed of type date, then that should be the case. You'll have to do some investigating to figure out why this is not the case.

              • 4. Re: One to many relationship
                pdoak_1

                I have tried to resolve my problem but am struggling.  I can see that the relationship is indentifying the correct stock based upon the SEDOL relationship.  However, it is using a record that is not the latest and the particular record that it is finding does not have any information.  However, if I sort the table in descending date order, it still does not change the record that it is finding based upon the relationship. I have tried to refresh the window to no avail.

                What else can I check?

                • 5. Re: One to many relationship
                  philmodjunk

                  Make sure you are specifying the sort order in Manage | Database | Relationships and for the correct table occurrence.

                  You'd find the table occurrence from which you are specifying the field from the related field, then find the relationship line that links it to your layout's table occurrence (shown in "Show Records From" in Layout Setup...). Double click it and specify a sort order for the table from which your related field is selected.

                  (Table Occurrences are what we call the "boxes" found in Manage | Database | Relationships)

                  • 6. Re: One to many relationship
                    pdoak_1

                    That solved it.  Thank  you.  I did not realise that I had to specify the sort order in the relationship criteria.