3 Replies Latest reply on Feb 7, 2012 12:00 PM by philmodjunk

    Generating a report based on a query



      Generating a report based on a query


      Hi, I have multiple tables: Clients, Invoices, Buyings. A client can have multiple invoices, each invoice can contain multiple buyings, each one has a specific day.

      I'd like to create a report that groups all the buyings for a specific invoice (and so a specific client) grouped by the day they have been done. I'd like to have a structure like this to be printed:

      ClientName Invoice Number


                        buying1 att1 att2 att3

                        buying2 att1 att2 att3

                        buying3 att1 att2 att3


                        buying4 att1 att2 att3

                        buying5 att1 att2 att3


      My problem is that I cannot achieve this layout when I try to create a simple report and sometimes it show more than the records I need, how can I just query my sistem for a specific invoice? Do I have to create some script? I'd also like to query some Buyings in order not to make them all appear in the report

        • 1. Re: Generating a report based on a query

          First, you need a layout with this design:

          ---Sub summary when sorted by Client------
          Clients::ClientName  Invoices::InvoiceNumber

          ----Sub summary when sorted by purchase date------
          Date Field

                Fields from Buying table go here

          The layout would be based on the Buyings table.

          This layout should be viewed in list view after sorting first by the client field specified in the first sub summary part and then also sorted by the Purchase date. Without correct sorting the sub summary parts will not be visible.

          To limit the report to a specific set of records ( a given customer, a given date or range of dates, etc.) you perform a find and then sort the resulting records as I have already described.

          • 2. Re: Generating a report based on a query

            Thanks as always PhilModJunk, I have a problem with the search part, when I enter search mode, it appears like the fields in the sub summary sections are not visible and so I cannot search for those values

            • 3. Re: Generating a report based on a query

              You can use a different layout based on the same table occurrence to perform your search or you can use a script to perform the find.