2 Replies Latest reply on Oct 11, 2012 8:22 PM by paolobkk

    Monthly sales report - How to show empty months?

    paolobkk

      Title

      Monthly sales report - How to show empty months?

      Post

           Hello!

           In FMPA 11, I have 3 tables: customers, invoices, line invoices.

           I've managed to make a layout with monthly total invoices as follow:

      Jan2012

           customer 1 - total invoice jan2012

           customer 2 - total invoice jan2012

      Feb2012

           customer 1 - total invoice feb2012

           ..and so on

           This is helpful but limited. I need the report to show also the customers with zero invoices (who did not buy every month).

           As in the previous example, it should appear like this:

            

      Jan2012

           customer 1 - total invoice jan2012 (eg. 100$)

           customer 2 - total invoice jan2012 (eg. 150$)

      Feb2012

           customer 1 - total invoice feb2012 (eg.120$)

           customer 2 - total invoice feb2012 (eg, 0$)

      Mar2012

            

           customer 1 - total invoice mar2012 (eg.0$)

           customer 2 - total invoice mar2012 (eg, 0$)

           As far as I've got, I've made a new table with all the months (last 2 years, 22 records - autocreate new record (month) every month

           DATE=(current month ; "01" ; current year)).

           In this table there is also a global field related to Customer ID - The DATE field is related to Date Printed from Invoices.

           In Customer layout there is a script trigger "on record load" to set the customer ID inside the global field in Months Table.

           With this system I can place a portal in the customer page (from Months) and list all months in one field and in the second field the Total invoices from a self join relation with invoice. (See picture).

           If the customer doesn't have any invoice for one of the months, the total will show nothing.

           How can I list all the months in a layout with all the customers and all the months? Showing me also who did not buy every month.

           I've been scratching my head for a week trying to find this solution..

           Any suggestion is more than wellcome! 

            

           (I use FMPA 11 running on FMS11 - Macbook pro)

            

            

            

            

      Screen_Shot_2012-10-11_at_17.39.57_.png

        • 1. Re: Monthly sales report - How to show empty months?
          philmodjunk

               This is a classic example of FileMaker's failure to support a 'left join'. Such a listing can now be produced with ExecuteSQL, but only if you have FileMaker 12 and only if you can work with the results being contained in a single text field. (You might actually have to use export field contents to export the data to a text file and then import the data back into a table from which to print a properly formatted report if you have a lot of customers in your database or need formatting not possible from within a single field.)

               Note that your screen shot shows an arrangment that appears needlessly complex for the current form of your report. A single occurrence of your invoices  table is all you need for a summary report to produce what you have now--a report listing all customers that had at least one invoice in a given month.

               The work around needed to get what you want without using ExecuteSQL is pretty cumbersome. You can use a script to create "dummy" zero value invoices for each customer for each month (either all customers or just those for months where they have no sales). You might want to do this by importing the invoice data into a separate table and then add the dummy records there to keep them out of your invoices table.

          • 2. Re: Monthly sales report - How to show empty months?
            paolobkk

                 oww.. that sucks.. sad

                 Actually, I've been thinking about creating dummy invoices but it seemed like a lot of "useless" records, so I was hoping in something more light and dynamic using relationships.

                 Thank you so much for your hint about the needless TO in my screenshot. I've just fixed it and it's perfect.

                 As you suggest, I will try to make a "Dummy invoice" table where it will contain the data from Invoices and will auto create the Dummies.

                 Thanks again!!