3 Replies Latest reply on Feb 18, 2010 4:10 PM by comment_1

    Cannot get an accurate count of related records

    brianbeshears

      Title

      Cannot get an accurate count of related records

      Post

      I have 2 related tables   1. TITLES and  2. KEYWORDS   These are related via a 3rd table named KEYWORDING.

       

      In the KEYWORD table, I have a field that is a COUNT function of the number of Titles related to a Keyword.  This calculation works and always reports the total number of Title records containing that Keyword.

      I would like however to have it calculate only the number of related records within the Found Set ( on the Titles table ).

      So, if there are 50 total related records and the Titles table is showing ALL records ( all records Found ), then the calculation should report 50.

      If however, in a particular found set there are only 20 related records, then I would like the calculation to report 20.

       

       

      My storage settings are correctly set to not store.

       

      Thanks! 

       

        • 1. Re: Cannot get an accurate count of related records
          dataWolf
             I think you could do this if FM could indicate which records are in Found Set, but it cannot AFAIK. I tried using using a calc of Get(RecordNumber) but it does not work as advertised. That is, looking through a calculation, a record that is not in Found Set still has a Record Number (not ID) though I believe it should not. If it worked (by tweaking reference or something?) then you could count how many had a value for Found Count.
          • 2. Re: Cannot get an accurate count of related records
            philmodjunk
               Since performing a find and defining a relationship are two ways of matching up data, it may be possible to define a relationship that will match only the records that comprise your found set of records. In that case, you can use the count function and it will report the desired counts.
            • 3. Re: Cannot get an accurate count of related records
              comment_1
                

              brianbeshears wrote:

              So, if there are 50 total related records and the Titles table is showing ALL records ( all records Found ), then the calculation should report 50.

              If however, in a particular found set there are only 20 related records, then I would like the calculation to report 20.


              It's not possible, at least not the way you describe - a relationship ignores any found set.

               

              One way to produce the count you want would be to filter the relationship by the same criteria used for the find.

               

              Another option is to collect the found set ID's and use them to filter the results obtained from the relationship. It would probably require running a script after doing the find (I am not altogether clear on the context here).