Line Items Sales Report

[captainllama]

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

There is included a report generating "Sales Numbers" which gives the sales value totals 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 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
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 inventory 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, in 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 many thanks, and if this would be better in a different topic please advise thank you.
 

[MaxB.]

There's no ready-made script for this because to do this efficiently you want to exploit your table relationships.

 

Assuming you have a resonable table stucture (i.e. CustomersTable--<SalesTable>--ItemsTable) you can just make a Summary Field in the Sales table that is the total of Quantity (or whatever you named the field that contains the number sold in Sales). Then make a layout using the Sales table that looks like this:

 

Part Number           Item                         Quantity Sold
[ Items::Part# ]         [ Items::Name  ]        [ QtyTotal ]

 

Where the line "[ Items::Part# ]         [ Items::Name  ]        [ QtyTotal ]" is on a Layout Part that is a "Sub-Summary when sorted by" and select Items::Part# as the sort field.

 

Then make a layout that asks for the relevant info and then a script that searches Sales with it (this can actually be really complicated depending on how snazzy you want your interface to be). Then go to the layout I specified above and sort by Items::Part#. You should get a list like how you want.

 

There is a waay to do this using only a script, I've done it, but it's probably the ugliest and most inneficient thing I've ever written.

 

I don't know your skill level so I hope I'm being clear enough. Feel free to ask me to further explain anything.