5 Replies Latest reply on Dec 18, 2014 3:36 PM by philmodjunk

    Count Related Records satisfying criteria

    AlastairMcInnes

      Title

      Count Related Records satisfying criteria

      Post

      Hello,

      We have a database of book data.

      In the main Books table, we have a field, Season, which holds the season that a book is being launched (for example S15).

      We also have a Subjects table listing 40 or so different subjects. A book can have many subjects and a subject can be attached to many books, so we use another table BookSubjectLink to act as the link table to facilitate the many-to-many link.

      What I would like to see is a list of all subjects, together with the count of the number of books in each subject which have S15 for their season.

      So, I'd see something like:

      World War 1     15
      World War 2     10
      Aviation           5
      ...

      Can anyone tell me how to do this?

      Thanks,

      Alastair

        • 1. Re: Count Related Records satisfying criteria
          philmodjunk

          Books---<BookSubjectLink>-----Subjects

          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.

          • 2. Re: Count Related Records satisfying criteria
            AlastairMcInnes

            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.

            Alastair

            • 3. Re: Count Related Records satisfying criteria
              philmodjunk

              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.

              Subjects-----<BookSubjectLink>---Books|Season

              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.

              • 4. Re: Count Related Records satisfying criteria
                AlastairMcInnes

                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.

                • 5. Re: Count Related Records satisfying criteria
                  philmodjunk

                  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.)