Invoice Sales By Fiscal Year Quarter Using FileMaker Pro

From Dwayne Wright PMP
Certified FileMaker Developer

WEB: www.dwaynewright.com
EMAIL: info@dwaynewright.com
TWITTER: dwaynewright
YOUTUBE: FileMakerThoughts

So I decided I want to add some quarterly level report options for InBizness SOHO 2.6. Thought it might be interesting to comment the steps I took to create the report and post it to my FileMaker Reports Explored blog.

ABOUT THE CALCULATIONS

I decided to cruise by the custom function library area at briandunning.com to see what I could find. I quickly found the following ...

FYQ
from briandunning.com (http://www.briandunning.com/cf/759) uploaded by Matt Wills, Virtual Vermont (http://www.VirtualVermont.com/FMP) Returns the fiscal year and quarter of a given date, considering the starting month of the fiscal year and observing the convention that the year is named for the year in which it ends.

This looked very promising, so I added it to the solution. I hadn’t even thought about a fiscal year option for the report! I made a global field in the main table to store the start month value, added this to the general preferences area and attached a value list check box option for values from 1 to 12.

The next step was to add a calculation field into the invoice table to calculate the fiscal year quarter and I named mine FiscalYearQuarterSold. It has the very simple calculation of FYQ ( invoice_m::g_startmonthFiscalYear ; INVOICE::date_ordered ).

ABOUT THE LAYOUT
So I have knocked down the calculation for my general report, the next step was to create a layout. I decided to quickly duplicate an existing report that breaks sales down by year and month. This report has a subsummary by year and then by month. I simply rewired the year subsummary part to use the fiscal year subsummary part and deleted the month part. After reordering my layouts slightly, I was done with the layout portion. Now on to the scripting area.

ABOUT THE SCRIPTING
So you might already know that I organize all my report related scripts in one script folder and then I break them down by module. So I only need to tweak the standard script I use for all my invoice related scripts and break it up by using a Script Parameter. Here again, I duplicated the script steps for my Monthly Sales Report. I changed the script parameter, the referenced layout and the sort order. Now my scripting area is done.

Here you can see my scripting group organization for reports.

Here you can see the specifics of this script.

ABOUT THE CUSTOM MENU
Again, I do almost all the activation of reports from a custom menu. So I copied the new Script Parameter I used for my new script steps and proceeded to find my Reports custom menu. I find my Monthly Sales custom menu report, duplicate it, change the used Script Parameter and I’m good to go.

QUALITY TESTING
I went ahead and ran the report from the custom menu and it worked great. I decided to go back into the preferences area and change my starting fiscal year starting month and run the report again. Once again, everything looked great.

So it really was a quick tweak for an existing report!
=
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.