Logical Breaks In SubSummary Report Data - Your Thoughts?

From Dwayne Wright - Certified FileMaker 9 Developer
WEB: www.dwaynewright.com
EMAIL: info@dwaynewright.com
TWITTER: dwaynewright

Had a client that calls me from time to time to chat about different ways to accomplish his goals for his database project. He called me today and I didn’t have a good answer for him but to commiserate with him that FileMaker didn’t have a pure checkbox option for this. I have seen some very heavy tweaks for this need in the past and have avoided them myself. I also have seen some innovative script trigger design in the last few weeks that ... take what used to be an intensive hack ... and boil it down to quite useable and transparent to the user.

I'm so freakishly busy with client work right now and just do not have the time to research the topic properly. I thought I'd toss this thought out into the blogosphere and see what might come back.

The main challenge is a classic one, what is the best way to strategically place sub summary part data breaks based upon the amount of space left on the printed page? If the number of rows that fit into a subsummary part extend to a second page, how do you present that condition to the user printing the report?

Said that I haven't seen anything simple. With FileMaker 10, I imagine there may be a way to do this due to the dynamic reporting feature. Perhaps some field that can detect how much row data you have, detect where a new subsummary part would like occur and then expand the number of carriage returns to accommodate that a subsummary data set would break across a page.

Better yet, have the calculation detect this and create a new subsummary part for the ongoing pages ... which would likely require that records falling into the secondary subsummary part would have the key sort data updated via a replace command (so it would fall nicely into our report needs). This way you would have a fresh subsummary part that would indicate it is from a second page and show the overall subsummary totals and the new “just on this page) summary totals.

If you have any ideas, comments or appropriate links to where we can solve this age old problem, we would love to hear from you!
=
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.

ADVERTISEMENT ==================
Help support this blog by considering a donating to its ongoing growth. For more details, please visit http://www.dwaynewright.com/donate.html

WAREHOUSE TWEAK: Nightly Updates For New Data (3)

From Dwayne Wright - Certified FileMaker 9 Developer
WEB: www.dwaynewright.com
EMAIL: info@dwaynewright.com
TWITTER: dwaynewright

Chapter 6: Warehousing Data

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 even experimented with the technique of writing in all my script step comments and then fill in the blanks with code.


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 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.

ADVERTISEMENT ==================
Help support this blog by considering a donating to its ongoing growth. For more details, please visit http://www.dwaynewright.com/donate.html

WAREHOUSE TWEAK: Clients By Group ID (part 2)

From Dwayne Wright - Certified FileMaker 9 Developer
WEB: www.dwaynewright.com
EMAIL: info@dwaynewright.com
TWITTER: dwaynewright

Chapter 6: Warehousing Data

WAREHOUSE TWEAK: Clients By Group ID (part 2)
This post is part of a series, here are the links to the two previous posts, if you need them ...

Warehousing Data Lightly Explored

WAREHOUSE TWEAK: Clients By Group ID (part 1)

Quick recap, I created a table warehouse for a client to show annual total comparisons for different combinations. One was the combination when a client bought a product and their year by year totals. This is something many wholesalers would be interested in having. My client mentioned that some clients are organized into groups by using a group id. Now they would like to see the totals for various groups.


NEW GROUP DATA GATHERED
I have my group id data now and thought I'd give it a quick test. I did the same search I mentioned earlier for empties. I did a find for when the field has no data and omitted them from the found set. Just like the previous search about 70% of the overall warehouse set had group id data. That is a pretty good sign!

1) I export all the records that have a group id out to the desktop as a FileMaker file.



2) I duplicate my CLIENT_PRODUCT warehouse table, rename it GROUP_PRODUCT and import my recently exported data into the new table. I use the matching field names option to make it quicker to match any fields.

3) I even go back to the layout I designed for the CLIENT_PRODUCT table, duplicate that layout, reset the associated table occurrence to the GROUP_PRODUCT table. It doesn't take that long because FileMaker seems to know that I'm working on a duplicated table. As soon as I select the correct table for each field, it automatically selects the correct field to use.

OOPS ... forgot to mention the only fields I need to import are the key data fields of group id and product id. I'm going to delete all the records and then import just the data I need. In particular, my summary fields.

4) Do a quick find to see how many duplicate group id records I have. This comes up with an amazing (but not unexpected) found set of records (approximately 95%).

5) Need to isolate the original record from the duplicates. A search in FileMaker for duplicates does not omit the original. So you definitely do not want to delete the found set of records.



I tend to use the self relationship method to isolate originals from duplicates. You setup a self relationship ... using the fields you consider make up a duplicate. In this particular instance, it is the group id field.

Here you can see the simple calculation I use for detecting originals.

I do a quick look at the data set and everything looks great. I do a find for the word 'duplicate" in my new field. The search takes quite some time to accomplish but I"m only going to do this one time.

REWIRE MY RELATIONSHIPS THAT COUNT ANNUAL FEES
Next up was the task to setup the relationships I need for my annual calculations. This is going to be a multiple predicate relationship. I'm using three predicate layers here, so the scrolling to find the correct fields for the relationships was getting to be a real drag. I went over to the data file and open the table fields in the Manage Database dialog box. I reordered the Sales Order Line Item table fields so the ones I needed would be at the top. I went back to my warehouse, to see if the new fields order I set worked. Sure enough, this made the setup process much faster.

Even with a few tweaks thrown in that I didn't mention, I've wired up the dynamic calculations within 15 minutes. Took another 5 minutes to update the script that sets the static values and executed the script. Everything worked out just great. I will need to add some logic to capture new groups, new clients, new products, new suppliers and the appropriate combination thereof.

=
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.

ADVERTISEMENT ==================
Help support this blog by considering a donating to its ongoing growth. For more details, please visit http://www.dwaynewright.com/donate.html