6 Replies Latest reply on Mar 3, 2009 9:25 AM by BryanBoyd

    Parts sub summary when sorted and grand trailing summary

    BryanBoyd

      Title

      Parts sub summary when sorted and grand trailing summary

      Post

      I have purchase order system that has line item price for each record and then a summary for the purchase order that sums the records in the purchase order. However, when I am putting layouts together to produce reports that utilize records in the purchasing system, I would like to be able to run sub summaries for po by requisitioner and then a grand summary for all requisitioner po's. The summary in the table that summarizes all lines within a po will not give me these other summaries. Do you understand my problem?

        • 1. Re: Parts sub summary when sorted and grand trailing summary
          BryanBoyd
             to be clearer . . . I used a portal between two tables that had a relationship on the PO number. The sum for the purchase order number is being done in the portal table to an extended price field. I want to used a field in the other table that is not a number field to summarize, supplier for example, the extended price total on, both as a sub summary, and a grand total.
          • 2. Re: Parts sub summary when sorted and grand trailing summary
            BryanBoyd
               sorry, but to be even more clear . . . the summary that I am using is giving only the last po dollar amount in the list which finds suppliers, then sorts by requisitioner and sub summaries by requistioner. Am I making any sense at all?
            • 3. Re: Parts sub summary when sorted and grand trailing summary
              TSGal

              BryanBoyd:

               

              Thank you for your posts.

               

              With each successive post, I become more and more confused.  Let me make sure I understand everything.

               

              Your purchase order system has records that link to your requisitioner table and another table (line items in the purchase order?).  The link between purchase order table and requisitioner table is the PO Number.  In your Purchase order table, you have a field that adds up each of your line items, and a summary field that adds up each of your purchase orders.

               

              In your Requisitioner table, you have a field that adds up the contents of each PO, and a summary field to add up each requisition.

               

              If you sort by Requisitioner, what sub-summary amount is displaying?

               

              Can you give me another example with sample data, and maybe this will determine where the problem lies?

               

              TSGal

              FileMaker, Inc.

              • 4. Re: Parts sub summary when sorted and grand trailing summary
                BryanBoyd
                  

                Okay, I have two basic tables, one a purchase order number table (PO #, Supplier, Requisitioner, etc) and a purchase order input table that uses a portal to input line item description, job number, price, etc. for each line of the purchase order. In the input table there is a summary field that will sum all of the line on each po number (the PO # is the relationship between the input table and po# table). I have put together a layout that pulls purchase orders by requisitioner and then sorts by supplier. Each buyer in my company can then look at what they have purchased. I would like to summarize the total purchase by supplier so that the buyer can see what they have purchased with each supplier. For each purchase order in the layout, I used the summary in the input file, it works fine. However, when I try to summarized the po summaries as a part that is sorted by supplier, I can not get a total to work. Does this help?

                • 5. Re: Parts sub summary when sorted and grand trailing summary
                  TSGal

                  BryanBoyd:

                   

                  Thank you for the clarification.

                   

                  In your Purchase Order Number table layout, add a portal into the Purchase Order Input table.  Create a calculation field that sums the extended price (price * quantity) for each line item.  That is,

                   

                  Sum ( extended price )

                   

                  Next, create a summary field, TOTAL PO, that is a total of this calculation field.

                   

                  Now, you can sort by Supplier or Requisitioner, and in your layout, create a sub-summary when sorted by Supplier or Requisitioner and place this summary field in that sub-summary part.

                   

                  TSGal

                  FileMaker, Inc. 

                  • 6. Re: Parts sub summary when sorted and grand trailing summary
                    BryanBoyd
                       thank you, it worked great.