Quick recap, I created a warehouse file with a number of tables to show annual total comparisons for different combinations. This means that annual performance for sales and purchase orders history is stored as static data. No need to run a report that calculates totals, this is all done after hours by using script execution on FileMaker Server.
One was the annual combinations we were tracking is when a client bought a product and their year by year totals. So anytime a client purchased a product, all the totals of that combination can be shows in static data fields from 2000 to 2009.
Here you can see the comparisons tab in the Clients module. It includes sub-tabs for supplier, product and group totals. There are two tabs for each of these categories, one has a portal sort alphabetically (A-Z) and one that sorts with the highest total. Here you can see the client / product combination that is sorted by overall totals and has their year by year breakdown. I’ve blanked out some of the key client data but you get the idea. Going from client record to client record, you see their associated totals updates. It is quick nimble, because all these totals are static data.
NOW ABOUT THE NIGHTLY UPDATES
This kind of data display is something many wholesalers would be interested in having. The warehouse works great and it only needs to be updated at night for new 2009 transactions. New transactions include ...
- daily updates for combinations the warehouse knows about
- daily updates for new clients, products, suppliers and combinations thereof
I have a script that I used for updating all the static totals equal to their calculated values. So I just updated this for 2009 data. So everything for the current year is updated by the FileMaker Server for existing combinations. I needed to add logic to create new combinations that the data warehouse doesn't know anything about.
First things first, I add a table occurrence to the relationship graph for my warehouse file for sales order line items and purchase order line item tables from my production database. This is another example of how my warehouse file uses the separation model to do its magic. I didn’t create the clients existing database, so I try to leave their core database untouched and do my programing from the outside ... looking in.
I could not come up with anything that allowed me to find where a relationship should be and isn’t ... without updating their core database. Then I began to think about a reverse GTRR. In my situation, I want a found set of records that I do not have a relationship to. So I'm going to do a GTRR for all related records (found set) and then run a Show Omitted Records Only script step. That gives me a reverse GTRR for the found set of records I desire.
I ended up tweaking this quite a bit but pretty happy with the way it turned out. I was able to duplicate and tweak this script to work for purchase order year by year breakdowns as well!
More info about the author and FileMaker in general, contact me at firstname.lastname@example.org.
© 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.