Define this calculation field in Subjects: Count(BookSubjectLin::_fkSubjectID ) // any never empty field in BookSubjectLink can be used.
Now a list or table view of subjects can include this calculation field to show the number of books linked to each subject. A portal to subjects would also work to show these counts.
Thanks - I was thinking it would have to be something along those lines.
I still can't figure out how to introduce the constrain on the Books::Season field. I tried adding that to the layout but it throws everything out.
Sorry, that detail zipped right by me.
the simplest method is to perform a find on a BookSubjectLinks based layout Specifying the season in the related Books record. Sort this by subject and use sub summary parts to create one "count" subtotal for each subject (Remove the body layout part to get just one row of data per subject).
It's also possible to use relationships. If you define a global text field for season in the BookSubectLink table, you can select a value for it on your subjects layout and use a modified set of relationships with this count calculation to get a count of all books to be released during that season broken down by subject.
BookSubjectLink::gSeason = Books|Season::Season AND
BookSubjectLink::_fkBookID = Books|Season::__pkBookID
Now your count expression defined in Subjects would be:
Count ( Books|Season::__pkBookID )
A third option would be to use ExecuteSQL to produce the same count without Needing to add more table occurrences to your relationship graph like you have to with the second option.
I tried method one, but, although I could apparently search on the season field on the layout, it doesn't actually constrain the totals displayed - they're still the totals for all books.
Your second option is, I'm afraid beyond my ability to understand.
Your third option would be ideal except that I'm on version 11 which doesn't have ExecuteSQL.
Thanks, anyway, for your suggestions. There's already a lot of your ideas in the various databases I have to maintain, and it's much appreciated.
I'm afraid that your problem with method 1 does not make sense to me. I suspect that you are counting records in the Books table when you should be counting records in the Join table. (and there is no getting around the fact that such a report can count the same book multiple times, one for each assigned subject.)