6 Replies Latest reply on Apr 15, 2015 1:58 PM by philmodjunk

    Dailly report Sales/Orders

    younesDEFFOUS

      Title

      Dailly report Sales/Orders

      Post

      Hi every one,

      i'm using the invoice solution sample present in FM. i generated a single repport with daily sales and Daily orders, and face a problem becasue the report is based on Invoices TO , and the days where a product is present in orders and not in sales (invoice Data) , the report don't show this product!!

      ultimate goal is to have on a daily repport displaying :

             
      • Total daily invoices
      •      
      • Total daily orders

      any help to fixe this ... Many thanks in adavance.

      YD

        • 1. Re: Dailly report Sales/Orders
          philmodjunk

          The invoice starter solution does not have separate tables for orders and invoices. It would thus appear that you have changed the design of the database. You'd need to describe your current design before it would be possible to suggest a way to get your report.

          There's one option that would work if both invoices and orders linked to the same line items table. Another option would use the same table for both orders and invoices. Neither option may fit your business model.

          Other more complex options are also possible, but again, we'd need to see the design of your database.

          • 2. Re: Dailly report Sales/Orders
            younesDEFFOUS

            Thank for your answer.

            i just made a new brand invoice DB using the original invoice starter sample, and confirms the use fo separate files for Orders , invoice items and invoices. i posted also the targeted report on the third image.

            Thank you for  help.

             

            • 3. Re: Dailly report Sales/Orders
              younesDEFFOUS
              /files/8cd85a8702/Screen_Shot_2015-04-15_at_22.04.54.png 1027x358
              • 4. Re: Dailly report Sales/Orders
                younesDEFFOUS

                here is a sample of the targeted report.

                 

                • 6. Re: Dailly report Sales/Orders
                  philmodjunk

                  Well first, you do not have separate files for invoices and orders. You have different tables for invoices and orders.

                  Next, I can see that I was misinterpreting what is meant by "order".

                  My answer assumes that you have a date field in Invoices and a Date field in Orders to use inn order to access all Invoices with today's date and all orders with today's date in order to compute the totals that you want on your report. I am also doing this without opening your file so some of my field names may not exactly match what you have in your tables and you'll need to adjust accordingly.

                  Add a new table: DailyStats. It will have one record in it for each day that you either sell or order product and will be used to link to the needed record for your daily sales and orders totals. Define a date field in it to record the date and to match to records in the other two tables.

                  DailyStats::Date = Invoices|Daily::InvoiceDate
                  DailyStats::Date = Orders|Daily::OrderDate

                  With those relationships in place, you can then add two calculation fields to DailyStats:

                  cDailySalesTotal: Sum ( Invoices|Daily::InvoiceTotal )
                  cDailyOrdersTotal: Sum ( Orders|Daily::OrderAmount )

                  So a layout based on Daily stats can use cDailySalesTotal and cDailyOrdersTotal to compute the totals for each day that you do business.