8 Replies Latest reply on Jun 4, 2010 3:49 PM by philmodjunk

    Summary Confusion

    mosa

      Title

      Summary Confusion

      Post

      Hello all! Im a new user to FIle Maker Pro 11 running on Mac OS X. Im developing a small database to create and track orders. I have an order form table which works perfectly btu im tryng to create and order summary table that would be able to show all the orders  from a customer and thier contents. I have already created a portal that comes up with the order number the total wauntity of the order and th etotal cost but i cant figure out the best way to summarize the items in the order. Also the order summary table is related via the _kp_customer_id field from customers ( a self generaing unique non modifiable serial number) and then a _kf_customer_id in order summary. The portal shows related records from the order form table whcih is related to customers also via _kp_customer id and to products via a join table called line item through _kp_item_number ( product ---< line item) and _kp_order number ( order form ------< line item). Any help would be much appreciated The foreign key for order_summary is populated by a pop up menu containing the customer id number ( but displaying the customer id) from the Customers table.

        • 1. Re: Summary Confusion
          philmodjunk

          Compare what you've set up to this tutorial--which just so happens to create summary reports from a simplified Invoice-line items system that may be very similar in structure to what you have:


          Creating Filemaker Pro summary reports--Tutorial

          • 2. Re: Summary Confusion
            mosa

            Thanks thats exactly what i needed!

            • 3. Re: Summary Confusion
              mosa

               followed the solution u provided but the line tiem table is generated through a portal on the order from table with items drawn from products via the relationshps i mentoned before. When i make the summary report it only shows one item on the summary instead of all the items that are on that order. I did everything on the tutorial you posted but i cant seem to make it work,any insight would be much appreciated.

              • 4. Re: Summary Confusion
                philmodjunk

                Whether you use a portal to generate your line item records or not the principle works the same.

                 

                It works because you have this relationship: Invoices::InvoiceID = LineItems::InvoiceID.

                 

                You may have different table and field names, but in order for the portal to work, you have to have the same relationship. The "invoices" table is sometimes called the "parent" table and the LineItems is called a "child" table.

                 

                Are you basing your layout on the child table with fields from the parent table placed in a sub summary part or the header?

                 

                Are you using List View or Form view? ( you should use List View for a summary report ).

                • 5. Re: Summary Confusion
                  mosa

                  Im using list view and the table layount is based off of LineItems. The relationships/ tables i have are LineItem, OrderForm, and Order Summary, OrderForm and LineITem are joined by the relationship _kp_order_number ( OrderForm) and _kf_order_number (lineitem) and Order Summaryis joined to ORder form via a relationship with the same names for the keys, are my relationships the problem or am i missing something simple?

                  • 6. Re: Summary Confusion
                    philmodjunk

                    It's hard to say without having a copy of the database file to examine. (You can upload a copy to a file share site and post the link to it here if you like.)

                     

                    How many records are in the current found set for LineItems? The summary report works off of whatever records you pull up in a found set so if you have only one record in your found set, that's all you'll see.

                    • 7. Re: Summary Confusion
                      mosa

                      I realized what was wrong with it, i got it working, thanks for the help. However is it possibl to make the summary field into a kin dof collapsing field, i.e. like having a arrow similar to the drop down arrow that could show or hide the summary field?

                      • 8. Re: Summary Confusion
                        philmodjunk

                        No you can't, but you may be able to achieve a similar response by playing games with the sort order.

                        When a set of records aren't sorted by the "sorted by"  field specified in the sub-summary part, the sub summary part will not be visible, so sometimes you can make different sub totals appear/disappear simply by sorting the records differently.  There's an example of that in the tutorial.

                         

                        You could even place a button on the layout so that clicking it sorts the records in a different order.