How to find a field's maximal value in a subset of records in a related table ?
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.
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).
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.
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?
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.