14 Replies Latest reply on Jul 27, 2011 10:40 AM by AdrianWilliams

    FM Pro 11... Report, Summary: Total of: Balances

    AdrianWilliams

      Title

      FM Pro 11... Report, Summary: Total of: Balances

      Post

      I have a Sub Summary Group Sorted by Product name (DES Direct, DES Franchise etc).

      Body contains columns for fields:  

      Client   SaleID   Item   DR_Income   DR_Expenditure1  DR_Expenditure2    DR_Balance  (...FR_Expenditure etc displays off-screen).

      I need to keep a Total of the DR_Balance for each product, below the Body (on right side of Report).

      I've created a Summary field 'DR_Total' and set it to hold Total of: DR_Balance: When Sorted by: Product.

      DR_Total currently shows Zero, but should be showing 35000 for the DES Direct product. Is this the correct way to set this up?

      I found another Post on this Forum discussing a very similar issue (FM Pro 2) , but it seems a complex way to achieve it, so I was hoping things would be easier in FM Pro 11?...

      http://help.filemaker.com/app/answers/detail/a_id/4302/kw/total%20%24

      des1.jpg

        • 1. Re: FM Pro 11... Report, Summary: Total of: Balances
          philmodjunk

          Open your field options for this summary field and try removing the running total options. These are intended for when you put this field in the body and you want to see the total change with each successive record in the group. Since you are putting the summary field in a sub summary layout part, you don't need these settings and they may be a factor in why you aren't seeing the correct totals.

          • 2. Re: FM Pro 11... Report, Summary: Total of: Balances
            AdrianWilliams

            Have read about Summary Parts and Fields until my eyes hurt but still cannot produce a single number in a Summary field!  I'm not yet at first base. I want only two totals…  'Total of Self' in a Sub Summary Part and 'Total of Amounts Paid' in a Trailing Grand Summary Part…
            http://www.clubtype.co.uk/fm_pics/des1.5.jpg

            Total of Self should be 1000
            Total of Amounts Paid should be £35,000

            Here's what I've done so far that has never worked so I have spent 3 weeks doing something fundamentally wrong.

            Each 'Self' field is a different 'caluated value' of 5000, 3750 etc.

            PRODUCT TABLE
            Create Sub Summary Part above when sorted by 'Product' in the blue part - that works. The Product field is in the product Table.


            I would understand better if this issue could be explained in an easy to foillow way like this, below...

            LINE ITEMS TABLE
            The figures I want to total are in a different 'LineItems' Table.
            Create a Sub Summary Part below when sorted by LineItems::ProductID.
            Create a Summary field in LineItems 'sTotal of' Self.
            Place sTotal of Self field inside the Total of Self Sub Summary part.

            Create a Trailing Grand Part below.
            Create a Summary field in LineItems 'sGrand_Total_Amounts_Paid.
            Place sGrand_Total_Amounts_Paid. inside the Trailing Grand Summary part.

            The button script Sort by ProductID does not populate summary fields with results.


            Create aTrailing Grand Part below.
            Create a Summary field in LineItems 'sGrand_Total_Amounts_Paid.
            Place sGrand_Total_Amounts_Paid. inside the Trailing Grand Summary part.

            The button script Sort by ProductID does not populate summary fields with results.

            • 3. Re: FM Pro 11... Report, Summary: Total of: Balances
              philmodjunk

              "The figures I want to total are in a different 'LineItems' Table."

              Then your report layout with summary and sub summary totals should be based on this LineItems table.

              When you refer to a summary field defined in a different table, you get the total/average/count etc. of those records in the other table that are related to the record(s) in the current table and this isn't likely to produce the desired sub totals for you.

              Here's a tutorial on setting up a summary report for a simplified invoicing solution. Note how the summary report is based on the line items table--not the invoices table.

              Creating Filemaker Pro summary reports--Tutorial

              Note that this is an old link that no longer pops into recent issues if you post a message to it, so you are better off posting any questions you may have about it here in this thread instead.

              • 4. Re: FM Pro 11... Report, Summary: Total of: Balances
                AdrianWilliams

                OK. That was REALLY helpful. Now at least I have a total in the Summary field below, but its the total of just one record. Why has it not calculated all records?

                • 5. Re: FM Pro 11... Report, Summary: Total of: Balances
                  philmodjunk

                  Summary fields, if defined in the table referenced in the layout's Show Records From drop down, summarize records found in the current found set--either all the records in the found set if you place it in a footer, header or grand summary part or a group of those records if you sort the records to group them and put the same summary field inside a sub summary part that is set up to refer to this same sort order.

                  Can't really be specific as I don't know exactly what you've set up here, nor exactly how you've structured your data.

                  • 6. Re: FM Pro 11... Report, Summary: Total of: Balances
                    AdrianWilliams

                    I've placed the Summary field in all three Parts at the bottom. Same result. Here's a pic:

                    http://www.clubtype.co.uk/fm_pics/des1.6.jpg

                    PINK Sub Summary sorted by Product ID (5) displaying only one record amount (3750)

                    GREEN Trailing Grand Summary displaying only one record amount (3750)

                    BLUE Footer displaying nothing

                    The field that it is totalling is a calculation field of all amounts on that record DR + FR + AS + AO + IFA + IF6 + IF12 in the InvestUS column.

                    • 8. Re: FM Pro 11... Report, Summary: Total of: Balances
                      philmodjunk

                      How many records are shown on the layout above.

                      At first glance, it looks like each row is a record, but then the layout objects on the right suggest otherwise.

                      What Layout part is the Grey band that shows twice here.

                      Are your records sorted just by product ID or are other fields also part of the sort order? If so, what was the exact sort order at the time you captured this screen shot.

                      If you could place the Product ID in the body as well as in the sub summary part, it would be easier to interpret this screen shot.

                      • 9. Re: FM Pro 11... Report, Summary: Total of: Balances
                        AdrianWilliams

                        Every line between the Grey stripe is in the same record. So the shot shows only two records. Shot was sorted by Product ID (5) now placed in Body too.

                        Sub Summary report + ProductID in Body

                        • 10. Re: FM Pro 11... Report, Summary: Total of: Balances
                          AdrianWilliams

                          Does the last screenshot help to clarify?

                          • 11. Re: FM Pro 11... Report, Summary: Total of: Balances
                            philmodjunk

                            So the bold faced values 3750 and 5000 (Invest US) should be totaled to appear in the sub summary part as 8750?

                            Your found set of records has only two records with Product ID = 5?

                            The field you've placed in thes sub summary and grand summary parts behaves like it is a data or calculation field instead of a field of type summary defined to compute the "total of" another field. I'm not insisting this is the case, but can't ignore the way their values are consistent with that explanation here.

                            What's odd to me is that you have Income, InvestUS, RBP and other text repeated 8 times for the same record but only one shows a value. That has me wondering what other details in the design of your database and layout might be at issue here.

                            Here's a demo file patterned after what I understand about your setup. It works to compute the correct totals and sub totals. Perhaps if you download it and compare it to your own, you'll be able to spot something different in how you set this up:  http://www.4shared.com/file/v1GRwrdl/SummaryReportExample.html

                            • 12. Re: FM Pro 11... Report, Summary: Total of: Balances
                              AdrianWilliams

                              Thanks. The SummaryReportExample file describes near perfectly the setup, except for two things:

                              To simulate what I'm doing, I've moved all original fields to another Layout (that has not affected anything), the pics I've sent are of the Report Layout.  Only cInvestUS field would be shown on the Report in Bold in the InvestUS column to simulate what I'm doing.

                              Example 1 file modified

                              - - - - - - - -

                              So, some progress. I now have a Sub Summary (yellow) part separating records. But only displaying results in the Summary field for the last record. This screenshot shows two records; the Parts below only show a total from the last record. I always Unsort then Sort by ProductID. The issue seems to be that the Sub Summary part is not totalling anything, except for the last record. Does this help?Sub Summary: only last part displays result

                              • 13. Re: FM Pro 11... Report, Summary: Total of: Balances
                                philmodjunk

                                Not really. It doesn't explain why I can get a correct sub total and you can't. Your screen shot looks like you have your calculation field (your version of cInvestUS on the layout instead of the summary field (sTotalInvestUS). (I use a lower case c to identify fields of type calculation and a lower case s for fields of type summary.)

                                Can you post a layout mode view of your layout? A screen shot of the summary field's definition (or that section of Manage | database Fields)?

                                • 14. Re: FM Pro 11... Report, Summary: Total of: Balances
                                  AdrianWilliams

                                  Eureka!  The Layout was based on the Sales Table rather than the LineItems Table where all the figures are kept. I now have Sub Summary parts for ProductIDs with totals. Many thanks for your patient hand-holding.