10 Replies Latest reply on Dec 8, 2012 1:40 PM by philmodjunk

    Getting the value of a summary report

    DanielKua

      Title

      Getting the value of a summary report

      Post

           Hi, I have a table named Invoice that I use to enter invoices and an Invoice Data for the invoice line item. Invoice Data is connected to Invoice through the Control No. field which is auto generated. 

           The line items of the invoice are always of the same product type, so when a customer orders different product types, I invoice them according to the items product type. For example, Invoice 1 contains all the items of Type A and Invoice 2 contains all the items of Type B etc. An invoice will never have a mix of different product types.

           After that, I created a layout to get the statement of account of the invoices. What I did was to put the line items in the body of the layout and then create a trailing subsummary report by Invoice No. where I put the net amount of the invoice after discounts. The layout will then list down all the invoice transactions by Customer and then by Invoice Date.

           What I want to do is to get the net amount for all product types of a certain customer on a certain day, how do I do this?

        • 1. Re: Getting the value of a summary report
          philmodjunk

               Presumably, your report layout is based on Invoice Data (What previously was called LineItems).

               You can perform a find on this layout specifying criteria in fields from the related Invoice table. Thus you can specify a customer ID and an invoice date and find all Invoice Data records for that customer and day. This find can be scripted or performed manually. And don't forget to sort your records after performing the find.

          • 2. Re: Getting the value of a summary report
            HughVon987654321

                 Hello Daniel Kua, I was interested in your post because I am having a similar problem, perhaps I can find a solution here.

                 Hello PhilModJunk December 06,2012,  I don't want to interupt here with my problem but it seems to be somewhat similar so I will post this response in this article to see if I can get a correct solution, Thanks again with your response

                 and as I mentioned I am just a begginer with Filemaker, let me clarify from Table A to Table B    is a one to one relation at least with the referencing table graph it would appear like this             TableA                                                             TableB

                                                                                            -kp_TableA----------------------=-----------------kf_TableA

                                                                                             Dougnuts_Cost                                                               

                                                                     Total_Summary_Doughnuts_Cost                                     Total_Summary_Doughnuts_Cost

                  

                 thats it get values out of  Total_Summary field table A plug them into table B we would expect those values to show up when viewed in

                 a Layout for Table A and Table B and we want those values to show up when records are deleted from TableA

                 as for Portals I have been experimenting with them, it doesn't seem to matter.

                 I do think tho PhilModJunk we are making a little progress, let me ask you this since you have so much experience with Filemaker which is a help.

                 perhaps you could explain the Nomenclature used in the Filemaker Pro 12 manual

                 and the thin I need explained is what does Filemaker mean by the function     =Sum( field {;feild...})       ?

                 I have tried every combimation I can think of using 5.1_Bonsai.fmp12   database

                 although very few of the tables shown in the reference graph are actually shown in the Tables list  in manage-database

                 now I have tried  making a calculation Field in the Products Table                          Total_summary_Products =Sum(SellPrice)

                 it returns a value but not of much use, it is some value for the same record.

                 and I have tried    Total_summary_Products=Sum(Products::SellPrice)      it  does return a value but once again not of much use same record

                 and I have tried Total_summary_Products=Sun(1; 2; 3; )        this returns the value of 6 which is very useful but not in my application

                 what I would like is Total_summary_Products=Sum(?)        that actually returns a useful sum of all the SellPrice values, which of itself is not very useful for this table Product but is very useful for my application. your help will be appreciated.

                 and I have tried using Total_summary_Products=Summary=Total of SellPrices  this does return a correct value but it will not transfer over to say the  Customer Table   kind of our like our TableA to TableB problem. and I have used Portals and everything I can think of except, well we won't use a container field.

                 sincerely Hugh

            • 3. Re: Getting the value of a summary report
              philmodjunk

                   You "don't want to interrupt" but then you do exactly that. I recommend starting your own thread for that or reading this one as it progresses to see if a solution for Daniel will work for you.

                   I don't choose to confuse the issue discussing two sets of relationships and tables in one thread so will confine my responses to what is posted by the original poster.

              • 4. Re: Getting the value of a summary report
                DanielKua

                     Hi Phil,

                     What you suggested was one solution I was thinking of but I also need to get the net amount for the other invoices of, let's say, Customer A on Dec.5,2012. Basically, get  the net amounts of the previous invoices since the line items are all the same product type and then put the values on the latest invoice.It should look something like this on the layout:

                     Product Type A = 10000

                     Product Type B = 20000

                     Product Type C = 10000

                     I am able to get the net amount for the item product type of the current invoice but not the previous ones.

                      

                • 5. Re: Getting the value of a summary report
                  philmodjunk

                       You should be able to perform a find for Customer A, Dec 5, 2012. In other words, you specify a customer in a customer ID field and a date in a date field. Even though you are on a layout based on Invoice Data, you can specify find criteria in fields from a related table such as Invoice. This should then bring up all line item (invoice data) records for that customer on that date. You then sort by product type to group the records by type.

                       If you only want one row of data for each product type, Add a sub summary layout part "when sorted by" your product type field and put a summary fieild in that part to compute the subtotal for that type. Then remove the body layout part from the layout. And then you sort your records by product type or the sub summary rows won't show on the layout.

                  • 6. Re: Getting the value of a summary report
                    DanielKua

                         Hi Phil,

                         Sorry for not clarifying, what I wanted to do was get the net amount of the invoices of Customer A on Dec. 5, 2012 per product type. Since one invoice line item contains only one product type, I wanted to put the net amount of all invoices Customer A  has on Dec. 5, 2012 broken per product type on the latest invoice Customer A had on the same date.

                         I also need to print the statement of account of each invoices to give to the customer and each statement of account is divided per product type, that's why the solution you provided would not really fit what I need to do since our accountant wants all the net amount of the invoices the customer has on a particular date on just the last invoice of the customer on that date so that she can immediately see the total amount the customer has to pay our company.

                          

                    • 7. Re: Getting the value of a summary report
                      philmodjunk

                           What I am describing allows you to print a statement of account, exactly like you describe, but not on the same layout used to print the invoice.

                           Does this report have to be on the same page as the invoice?

                           If not, you can use a script that switches to this layout, performs the find and prints it as part of the "print" script for printing out the customer's invoice and statement.

                           If this must be on the same page, then the options become more complex and I need to know whether or not you are using FileMaker 12.

                           With FileMaker 12, you can use the new ExecuteSQL function to produce this result as a "mini report" inside a large sliding text field. Without version 12, you'll need a portal with some fairly complext relationships to pull this off.

                      • 8. Re: Getting the value of a summary report
                        DanielKua

                             Hi Phil,

                             I'll post a screenshot of how I'm planning the layout to appear:

                              

                              

                        • 9. Re: Getting the value of a summary report
                          DanielKua

                               I was planning on putting the values of the totals of product type Confec, Snacks and Beverage on fields TAmount_Confec, TAmount_Snacks and TAmount_Beverage respectively. I'm also planning to use  those 3 summary values in a computation to get the total amount the customer has to pay. Would it be possible to format the summary this way?

                               The Filemaker version I'm using is Filemaker 12 and both subsummaries are sorted by the customer name.

                                

                          • 10. Re: Getting the value of a summary report
                            philmodjunk

                                 Since you have FileMaker 12, I recommend using the ExecuteSQL function to produce these subtotals inside a single calculaiton field: FMP 12 Tip: Summary Recaps (Portal Subtotals)