Single page/ multi page statements

[Himitsu]

I am sure this problem has been asked and answered many times. I searched and searched but couldn't find anything on it. Anyways, here goes.

What I need to do is print statements for my clients. Some of these are short, 1 page, header, body, footer. But some have a ton of items that will take 2 or more pages. The data is coming from many tables not just one. First, I am running a script to run over to the deposits table and searching for all transactions that haven't been added to a statement before. It jumps into loop mode, setField to a global back on the statement table, with the date, descripition, amount and such. Goes to next record with exit on last set.

Then it jumps over to the properties table and does kind of the same thing, writing to this global.

After returning to the statement table/layout, that global has a lot of info in it, so it passes it into the real field. Now, here is where I go, "WHAT and HOW?" on how to make this info, if needed, to print onto as many pages as needed to print all the info in that field.

Any suggestions would be great.

[Søren Dyhr]

Perhaps is this where it gets complicated?:

The data is coming from many tables not just one.

I should in my humble opinion be done in the transaction table if it exists, if several transaction tables exists is it a flaw in the normalization process ... since all reporting ought to be done in the most atomic table, which honestly needn't be something calling out for loops and sub-loops.

I could be wrong here, but it's however my initial thoughts, report generation is indeed up to the task, provided the data is sliced and diced conveniently ... the bare thought of filling a global text field is obvoiusly not new to me, but should only be considered if the urge is to keep in browsemode, such as:

http://www.kevinfrank.com/download/kf-fast-summary.zip

But you need to keep in mind that there still is a properly structured set of fields behind. To get closer to what I'm about read this:

http://www.fmforums.com/forum/showtopic.php?tid/193262/post/281403/#281403

--sd

[Himitsu]

I understand that normally it would be done in the line item table, but, this is a system for investors who have a bank account, properties, repairs, and collections. Those come from all different table line items. But, I need to print a single statement on one sheet. Some will be short, one page, but sometimes, it will extend further to two or even three pages. That is my problem.

Any ideas would be helpful.

[Søren Dyhr]

But it's the present values of things you are reporting isn't it? Wouldn't it be in a ledger usually??

--sd

[Simon K]

I suspect that what is required is a multi sectioned report - ie the report covers multiple sections of disparate data (but all belonging to the entity in this case an investor) some sections may include data, some may not depending on the client/investor.

I do not think that this is at all an odd requirement and is not really a database design issue (well it shouldn't be and here I agree with Soren).

Its more about efficacy of data presentation in a usable format (ie not multiple reports combined/pasted into a single PDF)

I have the same challenges and would indeed be interested in how to solve this without resorting to multiple reports, third party reporting tools such as Crystal, or looping through the PDF cycle.

Anyway - just thought I would add support to the original post - I don't know much about the global creation method - that sounds fraught too!

Hopefully somebody has an approach to this ...

Simon

[Himitsu]

I understand that layouts can break across borders and that is somewhat where the answer lay. But, I can't seem to wrap my head around the fact that the info is coming from 3 different tables but it's parent is the investor. As I try many things, I think it is strange that a program that is made to store data, do a million other things, ******* near flush your toilet for you, doesn't have a simple way to break a field across page borders. I have done something like this with a portal and I have also done it with a list view. But, now I need this to create a monthly statement broken in two parts, one being the bank account that could be as short as one line or as long as 5 pages or more, second is the properties and it's collection of rent. Once the statement is made, I need to keep it in case I have to send it out again.

Maybe I am over thinking it... How would any of you do it?

[Søren Dyhr]

Alright lets say that all relations are correctly tied to the customer, then should the correct fields be pulled into the reporting and here comes a series of globals which tells what the name or what the column of the field is and these two in handy:

http://www.filemaker.com/help/FunctionsRef-37.html

http://www.filemaker.com/help/FunctionsRef-34.html

This is exactly what you can read in "Special Edition Using Filemaker 8" page 398 thru 406 ...

But there is still a problem, what if the related tables are many of one2many ... this means you need a join table and make the reporting from there, eventhoug you already might have direct linking between the customer and the property.

I think I would make distinguisable RecordID's to every table related, These should then be digested via List( over the relation, split up via a repeating calcfield and imported between internal tables ... since you have the option to make each repetition a new record when importing.

This will produce a common jointable to report from.

--sd

[Himitsu]

I can see how that would work but then how would you tie it in to that statement? If it is equal to the investor, then you would see that info all the time. These statements are new activity for the previous month. And once that statement has been made, it should be stored in case the client needs another copy. So, if we had left something out from last months invoice, no worries, when we produce the next months statement, it will show up with the right date that might have been during the previous statement period. When I set the script to make an email, it can write the email as long as it needs because all the data is set into 2 fields. Top, which is the bank reports and body which is the property activity. The getFeild function, is in a sense what I have done except I am storing all the data in 2 fields, like a line item for that month. Does that make sense? The problem I have is the field contains lengthy data. How does that data spill onto multiple pages when printed?

[Søren Dyhr]

Who says that these join records should be permanent imports between tables are pretty fast ...

I think I for my own purpose needs to see this as a template, which I would share here, when it's done.

--sd

[Søren Dyhr]

On second thought, there is a cheat if we not are quite up to normalize it more correctly:

http://fmcollective.com/2007/08/29/pseudoportals-with-alternating-fill/

Pay attention when the text says:

However, doing so would make it impossible for me to put in the text element down the right side, or to incorporate related data from multiple tables.

[color:brown]"multiple tables"

Eventhough I still would like to give the ad-hoc jointable a stab!

--sd