FILEMAKER: Reports By Month and Week

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

I was recently asked to provide a report that broke values down by the month and then by the week. This was a billable timecard report and it would show the total timecard hours, the total of hours flagged as billable and the total billable amount.





The layout parts used in this report include a header, a subsummary sorted by the month of which the timecard entry occurred, a subsummary sorted by the week if the year in which the timecard entry occurred and a title footer.

Now this breakdown is a little off and that is because each week doesn’t fit snugly into a given month. So you can see that Week 36 of year 2008 is included in the September 2008 and the August 2008 totals.

In the case of my clients needs, they thought this was fine. They were mostly looking for how the month progressed by each week. The real value they were interested in was the monthly totals. I struggled with a compromise in this area but couldn’t get past the fact that a week almost never starts on the first of the month or ends on the last day of the month. However, my odds did go up a little bit if no timecards are entered on weekends!

The month and week values were captured using calculation fields using the Month and Week Of Year functions.

The Month function will return the number of the month in a date field or text string formatted in a recognizable date value. This will be a whole number between 1 and 12 because there are only 12 whole months available in a year.

The WeekOfYear function extracts the week of the year from a properly formatted date string in a date field. For example the information of 1/24/98 would return 4 only.
=
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: Showing Open Purchase Order / None Found Subscript

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

I just added a simple report script for the latest release of InBizness and thought I’d quickly share it. The report is a very simple ... find these records and go to list view. It is activated from the Reports custom menu, so it can be activated from just about anywhere in the system.

There are a few different things about this script that some fledgling developers might or might not be aware off. I do have an error capture routine, to branch the script if there isn’t any matching records to report upon. If the find does fail, it activates a subscript, so I can reuse the same code in multiple reports. The sorting feature is a little different as well. It didn’t make any sense to sort the returned records alphabetically by status, so it sorts by the matching value list attached to the status field.

Here you can see the report that is activated by a script parameter passed to it by the Reports custom menu. It does show the report in a new window, which is pretty standard for most of the reports I create.

Here you can see my search is actually a couple omits, assuming that everything that is not delivered or canceled is still open.

Here you can see the contents of the “no records found” subscript.

Here you can see the message shown to users when the “no records found” subscript if fired. Notice how vanilla and multiple purpose it is.

Here you can see that the sort routine is using the associated value list settings.
=
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: Invoice Sales By Fiscal Year Quarter

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

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.

====================== ADVERTISEMENT ==============================
For more information on the InBizness SOHO and other quality FileMaker framework solutions, please visit http://www.dwaynewright.com/solutions.html