FILEMAKER: Basic Reporting Topics To Keep In Mind

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

After you have created a number of reports, you might notice a problem or two cropping up from time to time. Many times these fall into the main areas of ...

- some data entry that is goobering up your reports
- some interface elements that are goobering up your reports
- some of the scripting for your reports is goobered up

DATA ENTRY PROBLEMS
The most typical are missing data where it needs to be or data that needs to be consistent and it isn’t.

For example, your report might count a number of records and then calculates a percentage total of the overall records. In a case like this, empty or duplicate records might throw your actual reporting values off.

In the case of empty values in sub-summary reports that sort upon that field, those records will be grouped together. So you will have a hole in your report that can be open to interpretation by your report audience.

In regards to data entry that should be consistent but is not ... consider a report that sorts by a state field to give sub summary totals broken down by state. If one user decides to enter in Washington instead of WA, the totals that should be in the same grouping are not. This can happen when users are copying and pasting information instead of typing it in correctly.

INTERFACE ELEMENT PROBLEMS
The most typical problem is when a layout object isn’t fully in one layout part. If a field, for example, should reside in a sub-summary part but a small bit has strayed into another layout part, your report will be goobered up.

Another new problem can arise when a developer copies layout fields from one layout and pastes them into another. It is possible those fields have FileMaker 9 object anchors that move those objects in ways you didn’t intend them to on the new layout.

PROGRAMMING PROBLEMS WITH YOUR SCRIPT
The most typical problems come from ...

- a search that doesn’t match the report needs
- a sort that doesn’t match the report needs (break fields)
- the script is on the wrong layout at a key moment
- the script isn’t in preview mode (for sub summaries viewing)

In most cases, these problems happen with you do a last minute tweak to the report or the script without testing. Generally, you can find the problem quickly by using the Script Debugger feature in FileMaker Advanced.

=
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.
====================== ADVERTISEMENT ==============================
For more information on the InBizness SOHO and other quality FileMaker framework solutions, please visit http://www.dwaynewright.com/solutions.html

FILEMAKER: About The Summary Family Of Functions

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

This family of functions actually contains only one function and it is often misunderstood or completely missed by FileMaker designers. However, it is quite powerful and we have hopes that we may help bring some of the mysteries of this function to light.

GetSummary( summaryField ; breakField)
FMP Version: All Current Versions Of FileMaker

This function will give you the information in a summary field for a found set of records when sorted by a break field. It is not dependent on a layout part and can be used within other calculations. The function has the two parameters of summaryfield and breakfield. The summary field parameter obviously refers to the summary field you want to capture the data from. The breakfield is the field that must be sorted to obtain the sub summary total in a grouped value report for the summary field.

Here is a picture of the new layout assistant settings when you want to create a grouped report. The GetSummary function can be setup to mimic what you would see in a sub summary totals.

=
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.
====================== ADVERTISEMENT ==============================
Click Here To See The FileMaker Book (via a blog) homepage!
===================================================================

FILEMAKER: Summary Field Options Explored

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

Here is a closer look at the available options for summary fields.

TOTAL OF - Returns the total of a selected numeric field (or calculation field with a numeric result) for all the records in the current found set or sub-summary grouping. This would be used to give you a total of sales in a sales report. There is a check box option called "Running Totals" that will summarize the current total as to move from one record to the next . This is the equivalent of a spreadsheet sum function where 2 + 2 + 2 = 6.

AVERAGE OF - Returns the average amount of a numeric field (or calculation field with a numeric result) for the current found set. This can be used to give you the average sale in a sales report. Say you are doing a report on product sales for the month of July. You have a multiple structured pricing scheme based upon the quantity purchased on one invoice. The average summary field would be great in figuring out what your average profit is per unit by subtracting... well ... should I say it.... the average cost of the product for the month. There is a check box option that is labeled "Weighted Average." This will weigh the calculated result based upon a field that you choose in the scrollable list to the right. Average is the equivalent of (2 + 3 + 4) / 3 = 3

COUNT OF - Counts the number of valid, populated entries in an indicated field. It is important to know that the Count function is intelligent enough to ignore empty fields and will not count them. One popular method of using the Count option is in the sub summary part of a monthly sales report. Here you can get a count of the number of invoices for each month in the found set and using the Total option, you can get the total sales amount. For example for June 1999 (54 sales - $5,000) and for May 1999 (49 sales - $4800).

MINIMUM OF - The smallest value (number, date or time) of a particular field in the found set of records or for the set of records in a sub summary part. Using the same monthly sales report example discussed above where June 1999 (54 sales - $5,000 Smallest Sale Was $10.00) and for May 1999 (49 sales - $4800 Smallest Sale Was $20.00).

MAXIMUM OF - The largest value (number, date or time) of a particular field in the found set of records or for the set of records in a sub summary part. Using the same monthly sales report example discussed above... June 1999 (54 sales - $5,000 Largest Sale Was $500) and for May 1999 (49 sales - $4800 Largest Sale Was $300).

STANDARD DEVIATION FUNCTION - A statistical element that shows how much a field value deviates from one record to another. It has a checkbox labeled "By population" that changes it from a statistic to a population element. A good example of how Standard Deviation might be used is if comparing test scores for different schools, the standard deviation will tell you how diverse the test scores are for each school.

FRACTION OF TOTAL OF - Returns a fractional representation of a value you specify against all the values in a numerical field (or calculation field with a numeric result) for a found set of records.

=
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.
====================== ADVERTISEMENT ==============================
For more information on the Virtual One On One Training, please visit http://www.dwaynewright.com/training.html
===================================================================

FILEMAKER: Summary Fields

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

Summary fields are normally used to return a numerical result for records in a current found set or that fall within a sub summary part of a layout. Some examples would be monthly sales, averages of profit per product and other such numerical decision making aids. Summary fields are one of the eight possible choices in the Define Fields dialog box. Just like calculation fields, you cannot enter data into a summary field while in browse mode.

Do not be misled into thinking that summary fields have to be about numbers only. You can summarize numerical data from text, date, time and even container fields with the COUNT OF, MAXIMUM OF or MINIMUM OF options. The summarized total can be affected at any moment and shown live. Typically, it's not a good idea to put a summary field on a data entry screen because the data entry screen may be tied up doing a recalculation on a summary field. You then have to wait to do your work until the calculation is complete. This isn't too bad on moderately sized databases but as the database grows in overall size of records, the delay in recalculating the summary field will grow also.

One thing which is often confusing to new users is a Summary field that calculates on on the current found set or on the set that applies to a sub summary part. This means you have to take into consideration the "found set" when you have summary fields in reports that run from scripts. Say, for instance, you have a summary field for sales named "Sum of Sales." It's the sum of a field called Subtotal. We used the Subtotal field because we don't want tax or shipping amounts to show in our sales number. If you perform a find for sales in 1998, this field will show the sum total for those records. If you search for June of 1998, it will only result in the total for those records.

Working with three sub summary layout parts on a layout, the first shows a sub summary when sorted by year, the second by month and the third by salesperson. When you put the same summary field, in this example, "Sum of Sales" in each part, you get three different answers (after you do the correct sort operation, of course). The first time the summary fields appears (in the part sorted by year), it will give you the annual totals, the second time by each month and finally by each sales person for that month.

There are seven different types of summary fields, which are:

Total Of
Average Of
Count Of
Minimum Of
Maximum Of
Standard Deviation Of
Fraction of Total

=
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 (Virtual One On One Training Services)
If you are interested in a one on one virtual session, please click here or send me an email at info@dwaynewright.com and we will schedule an appointment.

FILEMAKER: Sub-summary Part Options In The Part Definition Dialog Box

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

When you drag a layout part from the status area in FileMaker, you will be presented with the layout part definition dialog box. This is used to pick the layout part you want to use and to define any options you may want for that layout part. One amazing aspect of this dialog box is its validation intelligence. It can see where in the layout you placed the layout part you want to use. It will then dim the options that are not available to you. For example, if you put a new layout part above the layout body part in a layout, the Footer option will be dimmed and unavailable to you.

Normally, you select a part option and click the OK button. In the case of the sub-summary part, you need to choose the break field. When you sort by the break field, the individual totals for that month can be viewed in print preview or on a print out.

I have a step by step discussion on creating a sub-summary report at...
Creating A Sub-Summary Report For InBizness SOHO

We also should take a moment to discuss the check box options in the lower portion of the part definition dialog box. These options can be used to add some very powerful options to your reports and other printouts. It can be a little confusing because the way the check box options are indented under the Title Footer area. They are not Title Footer specific but each option is “selectively” available from each of the sub-summary part types.

Page break before each occurrence is used when you want to start a new page just before the occurrence of the indicated layout part. Is can be used in conjunction with the Body, Sub-Summary and Trailing Grand Summary layout parts.

Page break after each ( x number of ) occurrences is used when you want to start a new page after the occurrence of the indicated layout part. Is can be used in conjunction with the Body, Sub-Summary and Trailing Grand Summary layout parts.

Restart page numbering after each occurrence is used to restart the page number information on a layout. This is kind of like having a chapter in your reports.

Allow part to break across page boundaries is used to allow a field to be broken up between two physical pages on those really large layouts. Without the option being checked, a field that covers two pages would start on the second page, leaving the remainder of the first page blank. Discard remainder of part before new page is used to disregard information that is within a layout part but does not fit on one page.

Alternate background fill option bewildered me for a moment because I don’t remember noticing it before. That is a nice benefit about writing FileMaker technical content, you discover little things you had glossed over previously in the heat of the battle of getting projects out before deadlines expire. It seems to only work with the body layout part and can be used in association when you have a body layout part in conjunction with sub-summary parts. Alternate background fills can be a bit annoying if not tastefully done, so channel your inner Martha Stewart in applying this option.
=
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.