6 Replies Latest reply on Jun 8, 2010 4:29 PM by MaryAnn

    Multiple summary fields in final report

    MaryAnn

      Title

      Multiple summary fields in final report

      Post

      I am using FMP6 with Windows 7.  I am trying to track expenditures by requisition. I have two files, the requisiton file that contains the requisition information such as company, quantity, description, and total amount approved.  The second file is for purchases against the requisitions, with a record for each purchase.  I need to creat a report that shows all requisitions, company name, requisition number, amount approved, total spent and balance. My total spent field is a summary field for "total" based on purchase amount, with running total checked.    I have a sub summary sorted on the requisition number, but the amount spent and the balance for that requisition are not correct, but appears to be a running total from the previous gorup of records above it in the report.  How do I set up the report to show a sub total spent and balance for each requisition and a grand total for everything spent on all requisitions?

        • 1. Re: Multiple summary fields in final report
          philmodjunk

          In which file have you created your summary report? (It should be created in the "purchases against requisition" file.)

           

          Can you describe the layout of this report in more detail?

           

          " the amount spent and the balance for that requisition are not correct, but appears to be a running total from the previous gorup of records above it in the report"

          Did you select the "Print Above" or "Print Below" option for th sub summary part?

          • 2. Re: Multiple summary fields in final report
            MaryAnn

            I have 2 files:  Requisitions and Purchases

            The summary is in the "purchases" file.  The report layout is as follows:

            Company Name      Req. #     Approved Amt      Purchase Code      Total Purchases    Requisition Balance

             

            Relationship is based on req. no. 

            Approved amount comes from the related file as does the purchase code.

            Total Purchases field is     GetSummary( total purchases ,Requisition ID )

            Requisition Balance field is    Requisitions::RequisitionTotal-Purchases::purchase amt

             

            I have a header and a sub-summary sorted on requisition number.  I selected print below on the sub-summary.

            When I unchecked the running total box for the total purchases summary field, the report subtracted some of the "Total Purchases" from the" Amount Approved" correctly, but not others.

            Example:       Found set based on Code 7622

             

            Lightning Electric Co      999-10-0021        300.00         7622           250.00           50.00

            Podunk Plumbing           999-10-0065       2500.00        7622           750.00        2495.00

             

            Of the 15 requisitions in the found set, 5 did not subtract correctly and the number of purchases against the requisition does not seem to make any difference.

             

            I am at a loss and would appreciate any suggestions.

            Thanks for yur time.

             

             

            • 3. Re: Multiple summary fields in final report
              philmodjunk

              I'm puzzled by a few of the details you just posted.

               

              Total Purchases field is     GetSummary( total purchases ,Requisition ID )

               

              This is the definition for a calculation field that uses GetSummary to extract a sub - total from a summary field named as the first parameter in the function. The second parameter, Requisition ID, is the "break" or "sorted by" field. Yet you have the same name as the name of the calculation field and as the name of the summary field. It can't be both. Either Total Purchases is a summary field or it's a calculation field.

               

              Possibly you have two fields, Total Purchases Summary, and Total Purchases with Total Purchases defined as Get Summary ( Total purchases Summary ; Requisition ID) ?

               

              If Total purchases is just to provide a running balance on your report that increases with each item purchased in a given Requisition group, you should simply define it as a summary field and not use Get Summary at all.

               

              Requisition Balance field is    Requisitions::RequisitionTotal - Purchases:: Purchase amt

               

              What kind of field is RequisitionTotal?    A calculation like: Sum ( Purchases:: Purchase amt )  or is it simply a number field?

              • 4. Re: Multiple summary fields in final report
                MaryAnn

                Thanks,

                Based on your confusion I see how I was confused.  I changed the total purchases field to a summary field and it seems to be reporting the right numbers.

                 

                Requisition total is a calculation of  subtotal + shipping

                Subtotal is a claculation of   sum(product estimated amount)

                 

                 

                • 5. Re: Multiple summary fields in final report
                  philmodjunk

                  Just for your information, Sum ( Product estimated amount ) + Shipping and Product Estimated amount + Shipping should give you exactly the same amount unless product estimated amount is a repeating field.

                   

                  Sum normally is used in one of these ways:

                   

                  Sum ( field1; field2; field 3 ) (same as field1 + field2 + field 3) ,

                  Sum ( repeating field ) (adds up the contents of all the field's repetitions)

                  Sum ( RelatedTable::field ) (adds up all the values in field from a group of related records.)

                  • 6. Re: Multiple summary fields in final report
                    MaryAnn

                    Product estimated amount is a repeating field.