Hello!

I'm a newbie, sorry! Wondering if anyone could help with advice on how to go about generating reports based on data from multiple tables in Filemaker Pro. Or point me in the right direction if there is information online about this already.

Description of the problem: let's say Table 1 is the main table which includes (1) date of bank transaction; (2) debit amount; (3) credit amount; (4) account code (text). However, sometimes one bank transaction (whether it be debit/credit) needs to be split up in order to then associate it with more than one category code. From a layout which displays the data from Table 1, have set up using a pop-up button visible in each row, a portal to linked data in Table 2. The Table 2 can then display multiple rows of (1) debit amount; (2) credit amount; and (3) category code,  for any one bank transaction in Table 1.

The difficulty I now have is how to generate reports that display the total debits/credits/account codes based on information coming from both Table 1 and Table 2. e.g., Table 1 might have $1000 deposits in total associated with the FRN account code. And Table 2 might have a total of $500 deposits associated with the FRN account code. How do I set up one report to display the overall amount for FRN to be $1500?  I have reports that are already set up to display summaries, overall totals, and subtotals of the information from Table 1, but I now would like to find a way to add the information from Table 2.

Is this possible? If so, could anyone explain how to do this? Or is there a better way to structure the database to make this reporting easier?

I understand one option is to only have one table, and to just add more rows. However I opted for two tables in order to keep the original Table 1 to match as closely as possible to the original bank transaction statements. And also for ease of data entry (sometimes the one transaction in Table 1 needs to be split >10 times which is then easier to view in a separate portal). However I'm open to alternative ways to design the database, if required.

Apologies for the length, hope I haven't over-explained too much. Appreciate any feedback and solutions anyone might have! Thanks in advance. :)

Warm regards,

LP

 

Create your report from the child table, Table 2. You can always include, if you need to, fields from the parent. 

Thanks so much for the message bcooney. Apologies for my ignorance, I don't understand how this will help solve the issue re summing the fields from the two tables. Any chance you might be able to please elaborate for me?

IMHO, your current structure is less than optimal. If a transaction can have many sub-transactions (for lack of a better name), then put ALL your sub-transactions in the  sub-transactions table - even if there is only one. Then you won't have the problem of summarizing the same data from two separate tables (and very likely other problems caused by such anomaly).

 

Okay thank you, I'll reconsider the structure for the sub-transactions.

Btw, the database is actually more complex than my description (I was just trying to simplify it for this question). I have been looking more at the reports that the external consultant developed for us, which we need to tweak a little. Looks like she has used virtual lists to pull data from more than one table (for another reason). The virtual list scripting look a bit complex... hopefully one day I can get my head around it!  

Thanks for your input

The "virtual list" technique can be used to report on more than one table by combining data from two (or more) tables into a single list and using a single table to report on this list. A similar effect could be achieved by importing both sets into a reporting table.

However, when the data can be in the same table to begin with, it is much simpler to do so rather than trying to compensate for the flaw after the fact.

 

Edited May 16, 20214 yr by comment