8 Replies Latest reply on Jun 27, 2013 10:05 AM by JohnColburn

    Sub Summary or Aggregates or not possible?

    JohnColburn

      Title

      Sub Summary or Aggregates or not possible?

      Post

           I have been attempting this for the past three days. I have looked at numerous posts as well as a couple training videos, so it's not that I'm lazy, I'm just a little dense.

           To simplifiy as much as possible: I have two tables: Trips & Expenses.

           Trips table has these fields: Trip_ID, Trip_Name, Start_date, & End_date

           Expenses table has these fields: Trip_ID, Category, Date, Amount, Total_amount(summary field)

           I would like to have a layout that shows just one Trip and has totals for each category.  Example: Trip_ID is 1001. There are, in addition to other categories, two "Airfare" categories. One for $150 and the other for $200. I would like one of the rows to just show the total of $350. the rest of the rows would be populated with totals of the other categories that have a Total_amount greater than $0.

           Thanks,

           John

        • 1. Re: Sub Summary or Aggregates or not possible?
          ninja

               In your Trip table (joined to Expenses table by Trip_ID) you would have a calc field defined as

               Sum(Expenses::Airfare)  {result is Number}

               and it would show the total of $350.

                

          • 2. Re: Sub Summary or Aggregates or not possible?
            JohnColburn

                 I currently have 30 different catagories, with the probablity of others being added. Is there a way of having this information on the layout without creating a seperate field for every catagory?

            • 3. Re: Sub Summary or Aggregates or not possible?
              ninja

                   Guessing at your structure and usage here...but based on my guess....

                   Joine the Trip table to the Expense table with TripID   AND  the category field

                   Put the category field on your layout

                   Then the sum will only add up the expenses tied to that category...

                   Is this what you're after?

                   For making a summary layout for printing, you may want to base it on your expenses table and do a subsummary report rather than using a whole series of portals.  Using GoToRelatedRecord from your trip table can set you into a found set having only to do with that single trip, but use a layout based on the expenses table in a subsum report.  I picture a button on your Trip layout that says "Print Trip Expense Report" that would GTRR, and print.

              • 4. Re: Sub Summary or Aggregates or not possible?
                philmodjunk

                     A few links that may have some helpful ideas: Summary Reports: Creating Filemaker Pro summary reports--Tutorial

                     A "summary Recap" : FMP 12 Tip: Summary Recaps (Portal Subtotals)

                • 5. Re: Sub Summary or Aggregates or not possible?
                  JohnColburn

                       I appologize for not getting back sooner. I was called out of town and won't be back till the end of next week.

                  • 6. Re: Sub Summary or Aggregates or not possible?
                    JohnColburn

                         Thank you both for the help. Although there are still some aspects of summary fields that I just don't get, I was able to solve my issue. 

                    • 7. Re: Sub Summary or Aggregates or not possible?
                      philmodjunk

                           More info on summary fields that you may find helpful:

                           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.

                      • 8. Re: Sub Summary or Aggregates or not possible?
                        JohnColburn

                             Thanks again.

                             john