3 Replies Latest reply on Jun 18, 2015 12:03 PM by philmodjunk

    Report with Fields from 2 Related Tables shows incorrect Summaries

    ScottM_2

      Title

      Report with Fields from 2 Related Tables shows incorrect Summaries

      Post

      I’m sure the answer to this is simple, but I can’t seem to figure it out:

      I have two tables CLAIMANT and CLAIM, which are related using 2 fields, MATTER and CLAIMNUMBER.  A single claimant can only be in 1 claim, but 1 claim can have multiple claimants. 

      The CLAIMANT table contains the field for FullName (which is the only field I am interested in from this table for this report)

      The CLAIM table contains fields for

             
      • Claim Number (Number Field)
      •      
      • TotalNumberOfClaims (Summary Field)
      •      
      • TotalPaidOutOnClaim (Number Field)
      •      
      • TotalofTotalPaidOutOnClaim (Summary Field)
      •      
      • TotalClaimants (Number Field)
      •      
      • TotalofTotalClaimants (Summary Field)

       

      If I run a report showing records from the CLAIM table (subsummary when sorted by Claim Number, Trailing Grand Summary with TotalofTotalClaimants, TotalofTotalPaidOutOnClaim, Total number of Claims), I get the correct information for all the CLAIM table fields, but it only displays 1 claimant leaving the others off the report. 

      If I run a report showing records from the CLAIMANT table, I get all of the Claimants listed, but none of the Trailing Grand Summary CLAIM fields are correct.

      What can I do to get the correct information from both tables in the same Report?

        • 1. Re: Report with Fields from 2 Related Tables shows incorrect Summaries
          philmodjunk

          I'm not sure that I understand the relationship. It appears to be this:

          Claimant::Matter = Claim::ClaimNumber

          Is that correct? Seems odd that a Claimant can never have more than one claim given how litigious people are these days, but I'll take your word for that....

          Option 1: Base the report on Claim, but use a portal to list the Claimants. This will require using a portal with a very large number of portal rows (has to be more than you will ever need for a given claim) that you then set to "slide up" and to "resize enclosing part. This, when printing or previewing, will shrink the portal to just the number of claimants for a given claim.

          Option2: Base the report on Claim but change how you set up your grand totals in order to get the correct results. This would require something such as:

          calculation fields with ExecuteSQL, or
          filtered portals to a different occurrence of Claims using a cartesian join instead of matching by claim number. The filtering would need to be set up to match any find criteria if your report is used for listing data on only certain records in Claims.

          • 2. Re: Report with Fields from 2 Related Tables shows incorrect Summaries
            ScottM_2

            The relationship between the tables is:

            CLAIMANT::matter_fk = CLAIM::Matter

            AND

            CLAIMANT::claim_fk = CLAIM::Claim

             

            I should also clarify that while a particular claimant can be in multiple claims, in this database, it would require the claimant information to be entered again along with a unique Claim and Matter number.  This is how I was asked to design it, which may come back to bite them in the future.  

            • 3. Re: Report with Fields from 2 Related Tables shows incorrect Summaries
              philmodjunk

              Sorry, but your relationship makes no sense to me.

              I see no purpose to matching by anything more than the claim id.

              In any case, this information does not modify my previous suggestions.

              Here's more on how summary fields evaluate, which can explain why your previous attempt to base your report on the claimant table failed to produce the expected summary field values from fields in the claims table:

              A "non Running" Summary field produces an aggregate value (a value from more than one field in one record). The value returned is determined by the context in which it is used/displayed:

              Summary field is referenced on a layout based on the table in which it was defined:

              A group within a FoundSet

              If you place the summary field in a subsummary part that specifies the "break" field that grouped the records when the found set was sorted, you get a subtotal--the total for that group.

              In a calculation, you can use the getSummary function to access the same group based sub total.

              All the records in a FoundSet

              If you put that summary field in a layout part other than the sub summary part, you get the total for all the records in the current found set.

              If you refer to a summary field in a calculation field defined in the same table as the summary field, it will also return a total for the current found set. (Which is why we have the GetSummary function to get sub totals in calculations.)

              Summary field is referenced on a layout based on a table related to the table in which it was defined:

              Not in a Filtered Portal

              If you place the summary field on a layout based on a related table or refer to it in a calculation defined in a related table, the relationship controls the value that is computed. It will be based on all the records in the summary field's table that are related to the current record in this table.

              Think of it this way, if you put an unfiltered portal on this layout to the summary field's table, you'd see all the records in this portal that are used to compute the summary field's value in this context.

              In a Filtered Portal (FileMaker 11 and newer only)

              If you place that summary field inside a portal with a filter, you no longer get a value based on all the related records. Instead, you see a value based on all related records for which the filter expression evaluates as True.

              This is a special case use of a summary field that is often implemented by putting a single row copy of a filtered portal below it with the summary field inside so that the user sees a value based on just the records visible in the larger portal.

              This is a "Display Only" trick as you cannot refer to the value of this field in a calculation and get the same value shown on the layout--you get the result described in "Not in a Filtered Portal" above.

              Note that this does not just apply to "total" summary fields, Average, Count, Maximum, standard deviation, etc all follow these same rules.