Help totalling reports

[KellyMc]

I've been chugging along merrily on a solution to track payments to our accounting department, but I seem to have hit a wall. Hopefully it's not a dead end!

We receive payments from customers and need to track the multiple line items that a single payment can cover so that the money gets distributed to the correct accounts. I've got a table that contains information about the receivable (check #, total amount, date received, payer, etc.) Another table contains the accounts with their account numbers and descriptions. And I've got a join table called (perhaps confusingly) "payments" which connects an account and an amount with a receivable. For example, one record there would be

Receivable_ID, amount, account_code_id

Which would tell me, for example, that $50 from check #12345 should go into the sales tax account. Another record might tell me that $25 from that same check goes to membership fees.

I'm stuck though trying to build a report that will break out the subtotals for each account on a line with the receivable and total each column. The accounting department needs this for purposes of depositing the money to the bank and having each account funded correctly.

My plan was to build a list view in the receivables table, display check number and payer and total amount, and then I'd have a separate portal into the payments table, filtered by account code, for each account. I'd label each account name in the header. I hope that makes sense.

And that works fine, but I don't know how to total each account at the bottom of the page. Putting a summary field in the portal doesn't work, I assume because filtering is only for display purposes.

I'm afraid I'm out on the shaky end of my skillset here and I feel like I must be overlooking an obvious solution. Any ideas?

Thanks much.

[comment]

I don't understand the type of report you describe. Filemaker can easily produce a report summarized by account, e.g.

Account 001

• Check 12345 $125.-

• Check 56987 $250.-

Sub Total: $375.-

Account 003

• Check 12345 $100.-

• Check 56987 $200.-

Sub Total: $300.-

etc., or by the received payments:

Check 12345

• Account 001 $125.-

• Account 003 $100.-

Sub Total: $225.-

Check 56987

• Account 001 $250.-

• Account 003 $200.-

Sub Total: $450.-

etc.

In both cases, the report should be produced from the join table you call "payments".

[KellyMc]

I'm wanting something like this:

CHECK Account1 Account2 Account3

#12345 $100 $50 $75

#6543 $60 $75 $25

Totals: $160 $125 $100

It might be that the account subsort in your first example would do it. They're just kind of married to this view from how it's done in the existing system. My view does have the advantage of compactness, and individual checks wouldn't appear more than once.

Edited August 17, 2010 by Guest

[comment]

You might have noticed the word "easily" at the top of my previous post. This type of cross-tab you describe is not so easy to produce: Filemaker is not a spreadsheet, and it's not flexible in the horizontal direction.

However, if the number of your accounts is constant, you could try something similar to:

http://www.fmforums.com/forum/showtopic.php?tid/214642/

[KellyMc]

Thanks for this -- I've been chewing on it all afternoon and will look some more tomorrow. Unfortunately, my account names aren't constant, but I may be able to manage it.