1 Reply Latest reply on Mar 18, 2015 2:25 PM by philmodjunk

    Line Items Report



      Line Items Report


      Hello all, thanks for reading. I am using FileMaker 11.0.3 with the Business Productivity Solutions version 7 (for this query bps_Sales_Orders.fp7, bps_Inventory.fp7, bps_Contacts.fp7).

      Included with bps is a report generating "Sales Numbers" which gives the sales value total in £ per week, month, quarter, or year. However, I want to generate a report showing total inventory items sold. I have an inventory of 170 different items and need to generate a report that shows, for example:

      October 2013:

      Part Number           Item                         Quantity Sold
      1172-2                      Rock Teeth               1422
      1172                         Wood Teeth              342
      1101                         Tooth Bolts               205
      12m                          Chip Guard              17
      44005                       Drive Belts               38
      etc etc

      While the "Sales Numbers" report just takes a single figure from each Sales Order, this "Line Items Totals" report needs to take one or many Line Items from the portal in each Sales Order.

      My skill with FileMaker is such that I have adapted the bps to more or less suit my needs but not much more than that. By searching for a solution it seems that maybe there is a report wizard to ease the process.

      It would also be helpful if the same could be done by customer, ie a report showing: Customer X, Year Y, bought 17 Widgets, 130 Thingummies, 2 Whatsits etc etc.

      I'm frustrated that this must be a fairly common need yet neither my own efforts nor searching has born fruit. Please help!
      Many thanks


        • 1. Re: Line Items Report

          I am not familiar with the design of this database solution but you probably have these relationships:


          Where a portal to LIneItems on the Invoices layout is used to list each item sold on that invoice.

          If so, you can set up a layout baed on the LineItems table that functions as a summary report producing the totals that you want. Much of what is done to produce the desired report will be done by finding and sorting the records.

          For a tutorial on summary reports--which coincidentally is based on a line items table, see: Creating Filemaker Pro summary reports--Tutorial