6 Replies Latest reply on Dec 16, 2013 12:43 PM by c.wagner1

    Simple Sales Commission Report

    c.wagner1

      Title

      Simple Sales Commission Report

      Post

           Hi Guys,
           Don't think my brain is working today.  Here is the set up.  Invoices table is related to the Customers and TrackingNumbers tables.  
            
           The relevant fields in customers are:
           Name, ID
            
           In Invoices the relevant fields are:
           invoice number, sold by, invoice total, and invoice total summary
            
           Tracking Numbers contains the fields:
           tracking number, and ship date.
            
           My report needs to list the total sales each customer has made for one particular salesman (sold by field) that has shipped in a particular date range.  Then the report needs to have a grand total at the bottom.  I can't seem to get this to work.  It should be easy.

            

        • 1. Re: Simple Sales Commission Report
          philmodjunk
               

                    My report needs to list the total sales each customer has made for one particular salesman (sold by field) that has shipped in a particular date range.

               That sounds like a report you can produce with a scripted find. See here for examples of scripted finds: Scripted Find Examples You can use three global fields to specify the Salesman and two dates in two different  date fields and then a script performs a find on a layout based on Invoices to find all invoices meeting that criteria The results can be set up on that layout as a list view summary report sorted by Customer. Sub Summary layout parts can be used as a "sub head" to show data from the customer table and compute a subtotal for that one customer.

               

                    Then the report needs to have a grand total at the bottom.

               You should be able to define a summary field in Invoices for computing a total of your Invoice Amount field. Put this summary in the sub summary part and it shows a total for that one customer. Put it in the footer or a trailing grand summary part and it shows a grand total for all the invoice records in your report.

               Here's a tutorial on summary reports that you may find helpful: Creating Filemaker Pro summary reports--Tutorial

          • 2. Re: Simple Sales Commission Report
            c.wagner1

                 This was the approach I took at first and it works until I try to narrow down the ship date range.  When I try to narrow it down to a ship date, the report eliminates every customer who has not had a purchase in that date range.  (Like it should).  The problem is the summary field still shows the lifetime total for the customer.

            • 3. Re: Simple Sales Commission Report
              philmodjunk

                   Sounds like your report layout is based on customer instead of Invoices. If it were based on invoices and the summary field is defined in Invoices, this would not happen.

              • 4. Re: Simple Sales Commission Report
                c.wagner1

                     Yes, the layout is based on customer.  I didn't want every invoice the customer had during the date range listed on the report, just the customer listed once and the sales total for every invoice in the date range.

                • 5. Re: Simple Sales Commission Report
                  philmodjunk

                       And that can be done with a summary report layout based on the Invoice table. You can still have just one row of data per customer by using a sub summary layout part and removing the body layout part. This is covered near the end of the tutorial for which I have already posted a link.

                  • 6. Re: Simple Sales Commission Report
                    c.wagner1

                         Thansk Phil, I knew I was just being dumb today.  Too much fun last weekend I guess.