5 Replies Latest reply on Oct 1, 2013 3:19 PM by PeterWindle

    Scripted Report using a Layout



      I am trying to generate a report of items sold in [invoices] trying to count the items in the [items] field.


      Let's say I sold 14 cats and 12 dogs in 1 day on multi invoices and some on the same invoice.


      I need my layout report to look like this one: Or like the report at the bottom if at all possible.

      Date Item

      9/30/13 Dogs

      Total 12


      Date Item

      9/30/13 Cats

      Total 14


      My problem is that when i try to do the report on the items field I am only getting the 1st item listed in the 1st row

      So if line item 1 is Dog and Line item 2 is Cat my report is only picking up Dog.

      So if line item 1 is Cat and Line item 2 is Dog my report is only picking up Cat on this invoice.


      Also since ITEM is in the Invoices.Data file my count seems to always be 1.

      If I change it to QTY i get a 2 but both totals are incorrect.


      In my previous software I could do it like this:


      List Invoices ITEM DATE with invdate ="09/30/13" By ITEM COUNT(ITEM)

      I will get the following:

      Cats 9/30/13 14

      Dogs 9/30/13 12


      Can something simular be done in FM?

      Thank you for taking the time to look.

        • 1. Re: Scripted Report using a Layout

          You should have two tables, one for the "Invoice" and another for the "Invoice Items". Within the Invoice Items, you will need to have created some summary fields as well as a report with sub-summary groupings. If you have created an Invoice Items table already, you can create a new layout within this table as a "Report" (using the step by step 'wizard' guide) and this will give you a guide on creating the summary fields and sub-summary groupings required. Normally before showing values on the report, you would perform a find and sort to achevie the groupings you've mentioned.

          • 2. Re: Scripted Report using a Layout

            I did that is how I achieved the above results.

            • 3. Re: Scripted Report using a Layout

              Check the found set of records within the Invoice Items table, if there is only 1 found record within this table, this would explain the problem. Check that the summary fields are within the sub summary part, the position of the fields on the layout are crutial. Check that the records are sorted by the field that is used by the summary part.

              • 4. Re: Scripted Report using a Layout

                I am very new to FM and do not quite understand what you mean by Found Set within Invoice items  as I only have (Invoices) and (Invoice Data)

                • 5. Re: Scripted Report using a Layout

                  ok, so you should have two tables, Invoices and Invoice Items, Invoice table will include the Invoice date, who the invoice is going to etc, then the Invoice items are all the items sold on that invoice. Your reports will be generated using the Invoice Items table. Normally, in order to produce a report, a search is performed within the Invoice Items table on something like the related Invoice date field - searching within Invoice Items on the field from the above parent "Invoice Date" field... check that after the search, the number of found records are meaningful, then normally a sort on this found set is required to show the summary fields within the sub-summary parts on the report... your summary fields should be a Count (to count the number of records within the group) or a Total (to add the sum of the item amount).



                  I hope that helps? The key here is that all items are within the one table, then the search is done within this table to return the results and the sort is performed here for the report to work. You may want to take a look at any information related to creating reports and summary fields. Good luck!