WAREHOUSE TWEAK: Clients By Group ID (part 2)

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

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.

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.

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.