3 Replies Latest reply on Sep 21, 2011 10:43 AM by philmodjunk

    how to create a sales report

    joaol

      Title

      how to create a sales report

      Post

      If I have a 2 sales invoices and need to create a product  sales report. how can convert it from Portal, thank you

      forum.jpg

        • 1. Re: how to create a sales report
          philmodjunk

          You can use a layout based on the portal's table instead of on the invoice. Set this up for a list view type layout. Summary fields defined in the line items table can be used to compute the totals. On this layout you can perform finds for specific products and date ranges or use a sub summary part to create a sub heading with sub totals for each product and then you would sort your records by product  in order to group them for such a report.

          Here's a tutorial on producing such summary reports:  Creating Filemaker Pro summary reports--Tutorial

          Note: this is an old thread. If you have questions about what is presented in it, it's better to post those questions here rather than to that thread as it will no longer pop back up into recent items when you post to it.

          • 2. Re: how to create a sales report
            joaol

            Here is the point .  If I use your way to  enter data , I should enter data like this ,,and I can get same result :

            Invoice#               Date                      ProductID            Description         UnitPrice                          QTY        Total

            0001                       09/20/2011         001                         Apple                    1                              5              5.00

            0001                       09/20/2011         002                         Orange                 1.5                          4              7.50

            0002                       09/21/2011         001                         Apple                    1                              4              4.00

            0002                       09/21/2011         003                         Banana                 .5                            4              2.00

             

            But now only showing fist row if I using portal tools

            Invoice#               Date                      ProductID              Description           UnitPrice                       QTY        Total

            0001                       09/20/2011         001                         Apple                    1                              5              5.00

            0002                       09/21/2011         001                         Apple                    1                              4              4.00

             

            Or I was wrong since from the beginning , I should not use portal tool in layout body, or I should use this way to my table for multi item for one invoice

            Table1: Invoices


            InvoiceNumber (Auto-entered serial number)
            Invoice Date (date field)
            Customer Name (text)

            Table2: LineItems

            InvoiceNumber (number)

            PartNo1 (number)

            PartName1 (text)
            Qty1 (number)
            Category1 (text)

             

            PartNo2 (number)

            PartName2 (text)
            Qty2 (number)
            Category2 (text)

             

             

            PartNo 3(number)

            PartName 3(text)
            Qty3 (number)
            Category3 (text)

             

             

            PartNo4 (number)

            PartName 4(text)
            Qty4(number)
            Category4 (text)

             

             


            Total Qty = sum(Qty1+ Qty2+ Qty3+ Qty4)

             

            p.s I download many sales and purchase order sample from web, 90% will use portal tool in the layout body, but no one create a product detail sale report,

             

            sorry for my poor English…. I try my best to describe what I looking for, thank you

            • 3. Re: how to create a sales report
              philmodjunk

              The difference is in what layout you use for entering the data and what layout you use for your sales report.Each are based on different tables.

              You should have these tables and relationships: (but possibly with different names)

              Invoices---<LineItems>-----Products   (---< means "one to many" )

              Invoices::InvoiceID = LineItems::InvoiceID
              Products::ProductID = LIneItems::ProductID

              You'd use a layout that specifies Invoices in Layout setup | Show Records From for your data entry layout and your portal will work just fine.

              For your summary report, you specify LineItems in Show Records From so that each purchased item is it's own record. Then you can use the summary fields and sub summary parts described in the tutorial to produce your sales report.