3 Replies Latest reply on Mar 6, 2013 9:00 AM by philmodjunk

    Summary field issue

    Matty_1

      Title

      Summary field issue

      Post

           Hello,

           I have two summary fields, a minimum and maximum for a date formatted field.  I've used it time and again and it's always worked properly until now .... for some reason this one particular table isn't allowing it to function properly.  Not only does the min and max always show the same date, it is neither the max nor minimum but always some where in the middle.  I'm stumped, I'm not sure how many times I can go back and make sure I've selected the correct summary options.

           Is there some sort of rules that I should be aware that could prevent this summary field from functioning properly?

        • 1. Re: Summary field issue
          philmodjunk

               Summary fields evaluate differently depending on the context in which they are used. Can you describe that context?

               Are you using the field to get the max/min values from a found set?

               Values from a related set of records?

               Inside a filtered portal?

               In a calculation?

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

          • 2. Re: Summary field issue
            Matty_1

                 I have three tables, Assets, Line Item (creating a many to many relationship) and Insurance Policy.

                 From the asset table I have a button going to a layout listing line item records.  This button finds all the line items that have the same Asset ID in the _FKAssetID field as the current asset ID record being browsed.  This of course creates a list of all the insurance policies linked to the one asset.

                 On this report I'd like to see at a quick glance, the nearest insurance renewal date and the furthest.  In order to do this I created a min and max summary field in the insurance policy table and then of course called upon it in the line item table.  Now that I've written all this out I'm thinking that causing the issue??

            • 3. Re: Summary field issue
              philmodjunk

                   If your layout is based on line item and your summary table is defined in Assets, then yes, this is the issue as this matches the info I supplied titled:

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

                   A calculation field can be defined in line items that copies the data from Insurance Policy and then you can define a summary field in Line Item that takes the max or min of this calcultion field. Just be sure to select "Date" as the result type for your calculation field.