I'm creating a database to track customer orders for a retail business. In one FileMaker file each record represents a single item being ordered by a customer. I have fields for the unique item number as well as the unique manufacturer for that item in each record. Here's my problem, the same item can be repeated any number of times in the database (different customers ordering the same product). I need to display a summary of how many times each item appears in the database. I need to have these broken into reports by specific supplier.

I located a thread about creating a summary field and then creating a report with grouped data, but I've been unable to get this to work properly. There are about 50 suppliers and 30,000 product selections for this database, so it's a little different from the explanation I located. Only about 500 products would actually be in the order database at any one time.

Any ideas or example files?

Thank You.

Create a Summary field "ItemCount" = Count of Item.

Create a sorting script which sorts by Supplier, then by item

Create a layout with 2 sub-summary parts. The top one for when sorted by Supplier and the one beneath it for when sorted by Item. Remove the Body part of the layout. In the Header, add some titles.

In the top sub-summary part, place the Supplier field and the ItemCount field. In the next sub-summary part, place the Item field and the ItemCount field.

Then run this script to see the result:

Allow User Abort [Off]

Go To Layout [Report Layout]

Perform Script [Your New Sorting Script]

Enter Preview Mode [Pause]

Enter Browse Mode

Go To Layout [Original Layout]

You should see something like this...

Supplier.........Item............Quantity

ACME.......................................40

.....................Bolts............16

.....................Nuts............24

WIDGET Inc...............................48

.....................Washers.......15

.....................Pliers...........21

.....................Hammers......12