1 Reply Latest reply on May 3, 2011 10:10 AM by philmodjunk

    How to find a field's maximal value in a subset of records in a related table ?

    wladdy

      Title

      How to find a field's maximal value in a subset of records in a related table ?

      Post

      Setting
      In a collection of physical books, a page can display one picture or one poem.
      The books, poems and pictures are stored in three different tables.

      Table 'Poems' has the fields: 'fk_Poem_BookID', 'PoemPage' and 'fk_PoemID'.
      Table 'Pictures' is similarly built with fields: 'fk_Pic_BookID', 'PicPage' and 'fk_PicID'.
      Table 'Books' has the field 'pk_BookID' and a bunch other fields providing general info (title, author, year published, etc).

      'fk_Poem_BookID' and 'fk_Pic_BookID' are both linked to 'pk_BookID' in two distinct many-to-one relationships.

      Despite their similar fields, Poems and Pictures are in different tables because 'fk_PoemID' and 'fk_PictureID' refer to the primary keys of two distinct tables not relevant to this discussion but storing the details about individual poems and pictures in fields having very little in common.

      Goal
      When browsing through the poems sorted by book and page, we want to see for every poem the illustration immediately preceding it in the same book.

      In other words, for any one record in 'Poems', we want to know automatically 'fk_PicID' for the one record in 'Pictures' where

      'PicPage' is the greatest WHILE
      'fk_Pic_BookID' = 'fk_Poem_BookID' AND
      'PicPage' < 'Poem

      Approach using a calculated field
      In the relationships graph, I added 'Pictures 2' and linked 'Pictures 2 :: PicPage' to 'PoemPage'. I then added to 'Poems' a field calculating LookupNext ( Pictures 2::PicPage, Lower).

      Problem
      Unfortunately, this method yields wrong results as it fails to take into account the need for the books to be the same. For example, if book B1 has consecutive pictures on pages 10 and 20 and book B2 has a picture on page 12, a poem on page 15 of B1 will erroneous calculate that the picture preceding it in B1 is on page 12 rather than on page 10.

      Questions
      Can LookupNext be used on only a subset of records in a related table ?
      (in this case, the subset would be records such as 'fk_Pic_BookID' = 'fk_Poem_BookID').

      Is there another function that I could use to create a calculated field meeting the same goal?

      Conclusion
      Eventually, I created a totally different solution using a global variable and a script to populate it for each record viewed. It is quite inelegant and clumsy but it works. I will probably start another thread to ask advice on how to improve it. Thank you very much for reading this long post and even more so for taking the time to reply to it – I sincerely appreciate all comments that help me better understand FileMaker.  W.

        • 1. Re: How to find a field's maximal value in a subset of records in a related table ?
          philmodjunk

          LookupNext should work if the relationship to Pictures 2 links by page and also by bookID. This would require two pairs of match fields instead of just one pair in the relationship.

          Poems::fk_Poem_BookID = Pictures 2::fk_Pic_BookID AND
          Poems::PoemPage = Pictures 2::PicPage

          Though I'd probably prefer a different table structure:

          Books---<Pages>----Poems
                            v
                            |
                          Pictures

          With pages serving as a join table linking poems, pictures, and books records, the preceding page simply becomes the preceding record in the pages table.