Search Project Mgmt
Search FileMaker Blogs

Thank you for visiting the FileMaker Thoughts blog. I recently moved this content over from my blogger account. Hope you like it! When you get a chance, check out the centralized search feature for all the FileMaker blogs found along the right side panel. It is quite handy!


Saturday
Nov202010

Brief Introduction To FileMaker CrossTab Reports

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

A crosstab report is a report that summarizes values in both rows and columns. For example, I just added a crosstab report for the revision of InBizness SOHO that I’m working on. This report can be used my the marketing department to get an overall idea of client activity ranging from campaigns, proposals, quotes, sales orders, invoices and invoice totals.

Here you can see a small slice of example data in this new crosstab report.

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

Crosstab reports can be very handy and it is something you will want to have in your overall FileMaker reporting skills portfolio.

=
More info about the author and FileMaker in general, contact me at info@dwaynewright.com.

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



Saturday
Nov202010

WAREHOUSE TWEAK: Nightly Updates For New Data (3)

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

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.



Saturday
Nov202010

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.

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.



Saturday
Nov202010

WAREHOUSE TWEAK: Clients By Group ID (part 1)

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

In a previous post (Warehousing Data Lightly Explored), I mentioned how I used data warehousing for a client. This was the type of report that presents year to year activity comparisons for sales and purchases. I showed the results to my client and they were quite happy. I was relieved as well because their database was NOT one that I created. The database is very code dense and it is challenging to see how extensive the coding prevails on the most rudimentary of tasks. My solution added a new warehouse file to the mix and has minimal interactions with the core coding of the solution.

One of those year by year comparisons was for products a client purchased. I'm showing this information in a dedicated tab panel called Comparisons. I included four sub tabs to allow the user the ability to see the data in different ways. Two of the tabs were supplier based, one showing the totals by A-Z and one by the greatest amount purchased. I can do this easily because my grand totals are all static value. I have those same to sub-tabs for products purchased.

Here you can see the tabs I'm mentioning. My client is a wine distributor, so the tabs say Winery instead of Supplier but you get the drift.

GROUP ID TWIST TO THE MIX
Now my client had a twist in mind (don't they always?) and this had to do with a way some of their client records are grouped. In their clients table, they have a group id field. This allows them to attach multiple client records together in order to form a grouping. The main reason for this is that some client records are different locations for the same overall client. So they wanted my breakdown report to show overall grouping totals for the records that need them.

SEEING WHAT SCHEMA / DATA HAND I WAS DEALT
I went ahead to see if the previous developer was capturing the group id for each sales order line item record. Sure enough, the group ID field did reside there and I did a quick search for non-empties. The group ID did have data for about 70% of the overall records, so we are pretty good to go.

GET THE NEW WAREHOUSE TABLE IN ORDER
In my current version of their data warehouse, I have a table (with data) for every unique combination of a client / product. I use the combination of the Customer ID and the Product ID fields in a relationship to get my annual total breakdowns into calculation fields using aggregate functions for Sum and Count. I guess there may even be a future need of a Average calculation.

So the table mentioned above, has a structure and data set) I need! In fact, it has a touch too much. Like removing the sculpture from a large piece of granite, I need to duplicate this table and extract the the things I do not need.

GETTING THE GROUP ID DATA
My current warehoused data does not include the group ID data. I'm going to start things off with getting the group id data added to my customer / product warehouse table. I simply add the field to the table and have it auto enter the group id data from the sales order line item table. Then I was inspired to add some more background logic. I'll add the group ID related fields from both product and customer tables. Because I'm wondering, what if a company is assigned a group id after that company already has orders in the system. Did the previous developer take this into account?

BRIEF OFF TOPIC
I do want to make sure I check on this for the client's behalf, so I quickly cruise over to basecamphq.com for add that action item for their project. I’ve been using this service for about a month now and it is working out nicely. I pull up their project and I add a new To Do category called Development Questions. I haven't actually give the customer access to the basecamphq account I set up for them. I'm going to spring the idea of them using it as a tool to manage their database needs. This will be a great opportunity to show off how we can communicate and schedule database construction activities.

(to be continued)

 



Saturday
Nov202010

Warehousing Data Explored (lightly)

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

There are two types of database implementations but FileMaker succeeds so well at one, the other is often ignored. I’m speaking of operational database and the more reclusive warehouse databases.

Operational databases are used day in and day out to provide up to the minute reliable interactions with information. FileMaker is one of the best databases in the world for this, particularly in small to average size workgroups. Another form of database is the analytical or warehouse database, these types are often used to store large amounts of historical and static data sets. Warehoused data, unlike operational data, is locked and doesn’t undergo editing by users in any way.


Now I recently picked up a client that switched developers and they had a reporting task for me. The previous developer setup a complex report to show year by year sales data for customers and products. The report had a rather complex and intense setup process where the customer would outline what customers to report on, what products to report on and what four possible years they would want to display on the report.

Basically, it was a tab panel based data entry assistant. You make choices on the first tab and then navigate to the next tab. Based upon your first tab selections, you get a filtered set of options on the next tab. This process continues until you reach the last tab and there you can execute the script that runs the report.

This customers comparison report would use a subsummary by customer name, then by supplier and then by the products sold by a supplier. In the body of the report, would be the product id, product name, year 1 sales totals, year 2 sales totals, year 3 sales totals, year 4 sales totals and a grand total for the four select years. The final result was a classic crosstab report that the client found very useful. They wanted a couple more of these reports that showed data in different combinations and for some otherwise ignored business units.

The developers implementation of this data display was brilliant but flawed. I’ve seen this quite a bit lately in the developer community and have written about this before. The developer used a hunt and gather routine in combination with variable arrays to build the data sets. The reports took forever to run but the coding under the hood is quite remarkable. Because the coding under the hood was also largely undocumented and it used multiple subscripts, tweaking this report for my clients needs was about as flexible as a steel girder.

Sometimes the most simple implementation is the best and sometimes asking the client an extra question or two makes all the difference in the world.

These new clients are so nice and I spent a few hours trying to merge my mind into the programming created by the previous developer. I finally reached the conclusion that I would need to call the client and break some bad news to them. Due to the implementation of the previous developer, these tweaks to the report was going to be very expensive. I thought it was a shame because this could probably be a data warehouse implementation. Then I thought, “you dummy” make one of the new reports a warehouse implementation and show it to them!

I called them up and asked them one quick question, “Do your sales for previous years ever change?”. They said no, in fact, they would like for a method to lock older sales records so they cannot be edited by mistake.

So I created a new file for my data warehouse and then needed to make a first pass at populating it with data. I imported a set of records from the sales order line item table that had every unique combination of a client and a product. I then used that data to form a set of relationships back to the sales order line item table to get aggregate sum totals of each combination. Basically, I wanted to get all the totals of each year combination from 2000 to 2009. Then I created a static field and used the Replace command to put the calculated results into a field that could be indexed.

So I would have a record that had a customer id, a product id and static totals of each years matching sales activity.

The final result was a new tab in the customer file that shows a year by year breakdown of every product they have ever purchased and it all uses non-calculated static data. I have one problem with 2009 data. So I run a script from their server that updates the static 2009 totals and the customer is happy with a one day lag on their information. A customer can do standard searches for the data they want and then perform constrain finds on the found set for just about any combination they can think of and the results are immediate.

So warehousing your report data can be a very big boon for your database projects with large sets of historical data!
=
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.