White Paper Recommendation?

[ferdly]

Hi All,

Okay, by hook and by crook and by kludge, I have made FileMaker print out most any kind of report I have ever wanted.

However, it is always "just" a Chapter in any FileMaker book. Where there are several books on some pretty ubiquitous (and IMHO less effective) reporting tools disambiguated from an orderly, repeating arrangement of atoms and molecules.

I am at a juncture where I need to "put it all together" where I have Statements by FamilyID where one or most student may be on a single statement, where the header should be for column continuation and the footer is for page numbering, and the title header needs to fit in a window envelope showing the family address, and the trailing grand summary needs to be the payment tear-away with the company address fitting in an enclosed window envelope, and most-times it is a few charges and one page, but sometimes it can be a lot of charges/payments/credits and a few students and thus a few pages.

I know that I can always re-read some chapters, or actually read the manual, or poke around.

But, this post is to seek a recommendation of perhaps a White Paper/Article/Tutorial/Blog Posting (maybe even a Book I am unaware of) that focuses squarely on the sophisticated reporting features of FileMaker's layouts and so on -- perhaps by a Graphic Designer more than a database geek like myself.

I basically know what I am doing, and have already accomplished the task, so this is NOT a request for help. What I want to do is to find a resource that lets me coalesce what I "kind of" know about how things "kind of" work into a rock-solid understanding of really what is going on.

Thanks in Advance,

Ferdly

(NOTE: I might unabashedly respond to my own post here if I find something as I search about.)

[ferdly]

Well, after lots of reading, and googling, and thinking, and trying things and...

I have come to believe that I cannot meet my requirements without the Extensive Kludge below.

I really hope someone can tell me that I am wrong and that there is a more elegant solution. But this is what I have come up with:

==============================

Extensive Kludge:

Requirements:

1. 279px Top Tri-Fold Invoice To: Address to fit in Window Envelope

2. One or more Line Items (charges, payments etc) including optional memo area that can extent the line item total width

3. Potential for many Line Items (meaning, one, two, or many pages total).

4. Column Headers show at the top of 2nd through Nth page

5. Footer with Page "1 of N", "2 of N" and so on where N is dynamically gathered

6. On Nth (of N pages -- that is the last page) there needs to be a 279 pixel bottom showing the total of the Invoice and the ReturnTo: address as tear-away for fitting into an enclosed return window envelop.

The Kuldge:

Tables:

Customer (with CustomerID and address data etc)

Transactions (with CustomerID and amount data etc)

Layout:

Statment (of Transactions table)

Meeting the Requirements:

1. Title Header 279px with merge fields address from Customer table

2. Body part with all pertinent amount descr data. Memo is Merge Field below these columns and slides up, meaning a second line for Memo only shows/prints when there is a memo of some kind.

3. The found set will be by CustomerID so 1 to unlimited will be the body listing (approx 18px or 32 px with Memo).

4. Header part with Column continuation header (and "STATEMENT cont'd" for good measure above that) approx 38px, as per Title Header will not show on first page.

5. Use '##' for page number, and use a global text "gOfPage" field. dynamically set gOfPage to: " of " & Get(CurrentPageNumber) --> place this merge text on foooter: ##<>

6. Trailing subSummary when sorted by CustomerID, 279 px (less footer) with Company Address block, Total of Charges/Payment and a line for "amount enclosed".

So far so good...

The Kludge comes with the fact that as is, the the trailing subsummary might hang anywhere from at the very top of the page all the way to the bottom (where it belongs). Thus, I had to use "Faux" records and scripting to make sure that this part appears at the bottom of the last page every time.

I add two attributes to the Transactions table: FauxID (number, auto-enter 999) and Faux (text)

I write this script to print an Invoice from the Customer record:

fncPrintInvoice_pCustomerID

===========================

$customerid = Get(ScriptParameter);

EnterFindMode[];

SetField (CustomerID = $customerid);

SetField (FauxID = 999);

PerformFind;

$found_last = Get(FoundCount);

$padrecord_count = 0;

Sort(no dialog; CustomerID, FauxID desc);

EnterPreviewMode[];

GoToPage[last];

$pagecount_orig = Get(CurrentPageNumber);

#

$done = false;

Loop

$padrecord_count = $padrecord_count + 1;

EnterFindMode[]

SetField(FauxID; "<=" $padrecord_count);

ExtendFoundSet;

If[Get(FoundCount) = $found_last]

PeformScript[fncNewFauxRecord; param= $padrecord_count/*see below*/];

EndIf;

$found_last = Get(FoundCount);

ReplaceFieldContent(CustomerID = $customerid);

Sort(no dialog; CustomerID, FauxID desc);

EnterPreviewMode[];

GoToPage[last];

ExitLoopIf[$done];

If[Get(CurrentPageNumber) > $pagecount_orig]

$done = true;

$padrecord_count = $padrecord_count - 2;

EnterFindMode[];

SetField (CustomerID = $customerid);

SetField (FauxID = 999);

PerformFind;

EndIf;

EndLoop;

#

Print[];

#EndOfScript;

fncNewFauxRecord:

=================

$i = Get(ScriptParameter);

NewRecord;

SetField(FauxID = $i);

CommitRecord;

#EndScript;

Additional Notes:

used ConditionalFormatting (FauxID <> 999) to "white out" the faux LineItem body fields

Final Thoughts:

Pros:

it meets the requirements

is fully scriptable/dynamic

Cons:

only one customer at a time

lots of processing overhead

extremely kludgey

Edited February 8, 2010 by Guest