Many tables on one report

[maxxex]

Here's my problem. I have made a small accounting database. I have tables: customers (which also includes vendors), invoices (with line items), received invoices from vendors (with line items) and table with payments (what I pay to vendors and what customers pay to me).

I'm trying to create a report which will show me a list of all invoices, received invoices and payments for a selected customer sorted by date. At the end of report there should be

a totals of all coloumns on report (except dates)

English is not my main language, so I hope I wrote what was in my mind.

Thanks in advance for any kind of help.

Edited January 7, 2007 by Guest

[Søren Dyhr]

Where is the table for the ledger?? It should be the one to make the reporting on!

--sd

[maxxex]

There is no Ledger table, I didn't know there should be one. What fields do I need to put in Ledger table ?

[Søren Dyhr]

Account number debit, account number credit and the value of the posting ...well it might be called something else in a less teutonic speaking area, than mine?

These acts as foreign keys in a many2many relation, the question is then if the tables you mention are correctly chosen, since they often just are records in a single table, one to gather things having the same accountnumber.

The issue is that all accounts are similar, and only having slight attributal differences. What I here tries to get thru is that the relational structure behind a book keeping, not nessersarily always can live with what you instinctly feel is the right categories.

--sd

[Fenton]

What Søren is saying (I think), is that most serious accounting systems enter the data into one table, then run a script (or however) to put it also into any separate tables. I don't know if they actually copy the data or not, as they are mostly not FileMaker; and I'm mostly not an accountant.

You could accomplish much the same thing by not using separate tables, ie., not keeping "payments" separate from charges, just entering the data differently. Use a single "line items" table for everything, with either different fields for the amount.

Or only 1 amounts field, and simply a single "type" field to differentiate what each line is. That requires that you keep the positives and negatives straight however during data entry (which can be a little dangerous, IMHO). I think I'd keep the amount fields separate, then add/subtract them as needed for a line subtotal; ie., all data entry is positive.

It is not as difficult as it sounds in practice, as you always enter this data into portals, where the relationship can both add the keys, and filter the data viewed. It makes is fairly easy to do an overall report at the end, as everything is in the same basket.

I can also think of a way to do a report with everything only in its own tables. By using a Custom Function, such as GetRows(), to assemble what is in the portal into a single text field, in the parent record. The advantage of a text field over a portal is that it prints properly over line breaks. Custom Functions listing is at:

briandunning.com/filemaker-custom-functions/list.php

(It sure would be nice if FileMaker could make portals print nicely across line breaks; it would solve these little problems. But I ain't holding my breath; it's obviously a difficult problem, well-known for years.)

[Søren Dyhr]

You could accomplish much the same thing by not using separate tables

This is closer, to what I was trying to get thru! Since the attempted clasifications or categorizations won't work particular well.

--sd