Shoe Inventory In A FileMaker Crosstab Report

From Dwayne Wright PMP, PMI-ACP
Certified FileMaker Developer

WEB: www.dwaynewright.com
EMAIL: info@dwaynewright.com
TWITTER: dwaynewright
YOUTUBE: FileMakerThoughts

One of my favorite things that I can offer clients is the purchase of one hour of design time. This allows someone to send me a request that they have been struggling with for a few days and I can try to quickly pound out a solution for them. This example file was born out of one of those requests.

FROM MY NEW CLIENT
I am currently working on a point of sale system that is almost complete. I continue to stumble over the development of the inventory matrix. I’m trying to generate what I thought would be a simple inventory report. The system is a shoe sale system whereby everything is done by shoe STYLE #.  In the inventory matrix I’m trying to generate I would be searching for obviously, inventory and then generating a report based on style #, width and size.

ABOUT CROSSTAB REPORTS
This is a classic example of a crosstab report need. A crosstab report is a report that summarizes values in both rows and columns. There is no built in assistant within FileMaker to build crosstab reports but there are multiple ways in which to design them using your skills as a FileMaker developer. Each method will have its advantages and disadvantages in key areas such as ...

- the amount of time to create the report
- how complex the report is to create
- how flexible the report is for recycling for other needs
- how large of a schema footprint it leaves (extra fields, layouts, scripts)
- how well it can handle extremely large sets of data


SO WHAT I DID
I entered in the shoe data the client provided. It ended up being three different shoe styles, seven different width options, which ended up given us nine unique combination's of a shoe style / width. In the screen shot he sent me, there were 21 different sizes of shoes for the report. Adding this all together, we ended up with 96 records specifying each unique combination of shoe style, width and size (again based upon the screen shot he sent).

I then created the report layout to have one subsummary layout part that is sorted by a concatenated value of the shoe style and width. This gives us the ability to create one unique row for each style width combination and then show the on hand totals for each size horizontally across the row. At the end of the row, we have a total field to tell us just how many sizes we have for a particular shoe / width combination.

Finally, there is a very simple script that does a find for any shoe style width combination that has at least size in stock, sorts the list and brings up the report in print preview mode in a new window.

Here is a look at the final result for the report.

An example file can be downloaded by clicking (here)
=
More info about the author and FileMaker in general, contact me at info@dwaynewright.com.

© 2009 Dwayne Wright - dwaynewright.com

The material on this document is offered AS IS. There is NO REPRESENTATION OR WARRANTY, expressed or implied, nor does any other contributor to this document. WARRANTIES OF MERCHANT ABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE EXPRESSLY DISCLAIMED. Consequential and incidental damages are expressly excluded. FileMaker Pro is the registered trademark of FileMaker Inc.