3 Replies Latest reply on Aug 5, 2010 3:13 PM by philmodjunk

    Summary Report using repeating field data



      Summary Report using repeating field data


      I am trying to create a report that will show the purchase order number, name of the company the parts were purchased from, description of the part, and the purchase cost.  I have repeating fields for the purchase code, description and purchase cost.  There could be several items purchased at one time. For each purchase record I have a subtotal that totals the purchase cost for all items purchased against the purchase order on a given day.  So far, so good.  My purchase order summary will list dates of purchase, discription and subtotals.  My problem is, I can't seem to get a total of all subtotals to show how much has been spent with a specific vendor over a period of time.  The balance on my report is correct for some vendors, but not others.  For one vendor the amount on the second line of the repeating field ($7.24) was added into the total on the purchase order summary twice.  It is included in the subtotal for that purchase, but the total of the purchase order summary is $7.24 more than the actual total of the purchase. 

      I would really appreciate some guidance.

        • 1. Re: Summary Report using repeating field data

          First off, repeating fields aren't the best approach for this. To see an example of a better approach, create a copy of the Invoices starter solution that comes with filemaker 11. (Invoices and purchase orders are the same thing except for the direction the money travels during the transaction.)

          Even so, you should be able to get a summary report with sub totals for each vendor from your purchase order table. I can't tell from your original post how you've set this up though.

          How do you compute the total for each PO? Are those totals correct on each PO?

          How are you computing the summary report subtotals? with a summary field?

          What layout parts do you have in your summary report? How are they sorted?

          • 2. Re: Summary Report using repeating field data

            1.  Each PO total is computed with a field called "subtotal" which is a calculation field... sum(Purchase Price).  These totals are all correct.

            2.  Summary Report subtotals are computed using a summary field called "total purchases"  which is a summary of the "subtotal" field.

            3. The summary report is in three parts:


                     Sub-summary sorted by "subtotal"

                     Trailing Grand Summary which contains the "total purchases" field


            • 3. Re: Summary Report using repeating field data

              "sorted by subtotal" doesn't look right. That is what you would use if you wanted to group your PO's so that all POs of the same total value are in one group above or below the subsummary part.

              From your first post, I'd guess you would sort by a vendor field so that PO's to the same vendor would be grouped together and your Sub summary would be used to to show the subtotal for that vendor. In which case, the "sorted by" field in the subsummary part would be a field that identifies the vendor.

              This should be obvious to you, but the records will also need to be sorted in an order that includes the subsummary's "sorted by" field inorder for the sub summary part to be visible.