FILEMAKER: Show All Records If Not Base Table

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

This is something I did on the fly for my InBizness 2.7 update and thought I’d do a quick reporting blog post on it. What I did was add a couple payment based reports that break the report by the payment type (Check, Cash, Credit Card, PayPal, etc ...) and then by a date range. One of the reports groups the payment totals by month / year and the other shows totals for each individual day that a payment was made.

The thing is, I have a Reports menu that is available from any other module and needed to come up with a method to handle the found set of records to report upon.

What I decided to do was branch the script via a script header that detects if you were in the payments module at the time the report was run. So if the user was in the payments module when the report is executed, use the current found set. If the user is in a different module, then show all payment records.

This way I can do a search in the payments module and run the report based upon that set of records ... or ... make sure all the records are represented if run from another module.

The secret of the show all records branch is the Get(LayoutTableName) function and the use of a header variable.

Since the report isn’t going to take very much time going to a the payment module, I use a script variable to know what module was linked to the activation of the report script. So I set a local variable named $StartingLayout to the Get(LayoutTableName) function. The Get(LayoutTableName) function will return the name of the table occurrence that is linked to the currently viewed layout in the foreground window.

Then later on I decided to show all records or not based upon the fact if my variable $StartingLayout does NOT equal PAYMENT. If it is anything but PAYMENT, I show all 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.


===== UNPAID ADVERTISEMENT FOR A SERVICE I RECOMMEND =====


For more information about the database hosting services from thedroolingdog, please visit http://www.thedroolingdog.com/
===================================================================

The Silent Report Helper - The Value List

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

Value lists allow you to setup a field to have a list of entry options for the database user. These selection lists are very helpful with users doing data entry or performing a search. Value lists are one of the most powerful methods of adding data to a database quickly without the risk of mistyped information. That is the key benefit for reporting, without the risk of mistyped information. Many times when you run a report, you might see bad data such as a state field being typed in as Illinois, IL or even Illinios (a typo). If these data entry mistakes are a common occurrence in your reports, you may want to find where that problem data is typed in and associate it with a value list.

You can even setup field validation by using value list information. To do this from your field validation dialog box, you simply click the check box next to the words "Member of Value List" and then pick from the value list pull down menu. If the value list is not there, you can create one on the fly by choosing the Define Value Lists selection at the bottom of that list. You can even edit an existing value list the same way. A common way this could be used is for area codes, states, credit card types or job titles.

To add even more power and flexibility to this validation option, a value list can be composed of data within a field. This makes the list dynamic and can be updated constantly. The field used for the value list can even be filtered by a relationship. That is to say, the value list is only made up of data in a field only for the records that match a relationship setting!

So value lists can be great for quick data entry and validation of data in a field, is that it for their advantages in reporting. No, not at all! Most reports have their data sorted in order to present their data in an informative format. For the huge majority of the time, you will be sorting fields in ascending and descending orders. However the ability to sort via a value list can add some very interesting opportunities to your sort routines. A sort by value list can be very helpful in the crafting of reports that might have grouped totals based upon a sub summary part. Here is a link to an example file that I have on the subject ... EXAMPLE: Value List Sorting

There are five different options available for your value list needs and they are pop up list, pull down menu, radio button, check list and pop down calendar. The pop up list and pull down menu options require you to click into the field to see the list of possible choices and you select one of those options. The radio button and check list options show all the available choices on the layout itself and you click to select one or more of them. The pop down calendar option pretty much explains itself. Some FileMaker fields (mainly date fields) can be setup to have a pop up calendar show up. To do data entry you quickly select a date from the current month. You can also quickly navigate within the calendar from month to month or from year to year.

Here are some links to other posts that might be of interest in regards to this topic...
Value List Access
Using A Portal Like A Value List
EXAMPLE: Value Lists and Portals
EXAMPLE: Returning Value Lists
=
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: Fiscal Year Invoice Totals In A Crosstab Report

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

I have a large number of reports that I want to add to my InBizness CRM framework product. That seems to work out well from a productivity standpoint with the blog that I have called FileMaker Reporting Explored. I am purposely trying to create crossfire efforts as I populate content in both areas.

So with that in mind, back on August 26, 2008, I created a blog posting for a cross tab example report for customer aging. A crosstab report is a report that summarizes values in both rows and columns. A week later, I did a report called Invoice Sales By Fiscal Year Quarter. So now it is time to take the next obvious step and blend the two to get a Cross Tab Fiscal Year Report.

Now I used a custom function to get my fiscal report quarter information data. Here is a copy of that calculation FYQ ( invoice_m::g_startmonthFiscalYear ; INVOICE::date_ordered ). The g_startmonthFiscalYear indicates what month of the year the fiscal year starts. In my InBizness solution, the g_startmonthFiscalYear field can be found in a preferences tab area from the main screen.

So when the calculation kicks in, you get data strings such as 4Q08, 3Q08 and 1Q07. The breakdown is the first two characters are the calculated quarter for the even and the last two characters are the year. In a crosstab report, I will need to calculate each quarter separately. So I will need to read just the first two characters of that result to get the quarter information.

I will need 4 calculation fields and 4 summary fields for those calculations. That gives me one summary total for each possible quarter. The calculation for the first quarter is Case(Left(FiscalYearQuarterSold; 2) = "1Q"; c_grand_total; ""). The c_grand_total field is the total of the invoice including line items, shipping and tax charges.

Here you can see some of the new fields I added to create this report.

Again, there are a number of ways to create a crosstab report and the method I'm using is the least intense from a programming aspect but does create more code overall (particularly more fields in the implementation).

After that, it is just a matter of implementing the steps I’ve outlined before ...

- find a layout that closely resembles my needs
- duplicate that layout and rewire it for my report
- move the new report to the area in belongs in the overall layout list
- find a script or set of script steps that closely resembles my needs
- duplicate that coding and rewire it for my report
- add the additional report to my array of custom menus
- test the new report for quality assurance needs
- document the new report feature

Here you can see the original report that show fiscal year data in the traditional way.

Here you can see the new crosstab report that shows the same information but in a more concise way.
=
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 ==============================
Check out the FileMaker Term Of The Day at http://filemakerterm.blogspot.com/ or subscribe via your news reader. ===================================================================