Cross-tab report needs self-join and/or GTRR?
BACKGROUND — In order to learn FMP and databases in general, I'm using it to track my personal finances. Attached ERD shows the various tables/relationships which follow the similar invoice-solution model. For instance, the NAME table is akin to Customer, JOURNAL (i.e., transaction) is to Invoice, LEDGER (i.e., transaction line items) is to Detail, ACCOUNT is to Product. I've added other tables as necessary for additional reporting (MONTH and CLASS).
I have created many sub-summary and/or cross-tab reports based on the join table, LEDGER. These layouts have the necessary calculation fields, summary fields, and script triggers for sorting break fields, to make these reports — all of which are working as intended. The current layouts are: [Trial Balance], [General Ledger], [Analysis by Account, Name & Dr/Cr], [Analysis by Account, Name & Quarter], [Analysis by Account, Name & Month], [Analysis by Account & Quarter], [Analysis by Account & Month], [Analysis by Account, Month & Dr/Cr]. Dr/Cr indicates Debit or Credit. Each transaction record that I enter must have, at a minimum, two or more detailed line item records, so that "debits equal credits". In this case, positive amounts entered in the database represent debits. Negative amounts represent credits (so a transaction "balances" when the sum of all transaction line items totals to zero). Again, all works as intended.
MY PROBLEM — I'm trying to create one last cross-tab report (I will call it Account Distribution by Account), but not sure whether I should be using a self-join relationship, a Go To Related Record (GTRR) command/button, or something. For the purposes of showing you what I would like to accomplish, the best way is to give another example.
Say I enter a transaction to pay rent for the month (as I do each month). I go in the JOURNAL layout and fill in the date, name (landlord's name), a comment, and in the portal I have to LEDGER, I add the necessary transaction line items, in this case, a positive (debit) amount to the RENT expense account for say $500, and the corresponding line item the same amount but it's negative (credit) to the BANK account. I know my transaction "balances" since the sum of all transaction line items is zero ($500 - $500 = 0). I can run any of the above-mentioned reports on this transaction, and any other transactions I have entered (needless to say, the various reports can be quite powerful depending on the Found Set I have in LEDGER). For any given account, I want the report to tell me, of all records in an account, to what accounts the records related to. To put this in perspective, on the ACCOUNTS layout, I have a portal to LEDGER. The portal shows all related records (i.e., transaction line items) for each account (when I click the Book icon to move to the next account record, the portal records changes accordingly). I want a report that tells me, of the related record listed in the portal, give me a summary of all the accounts the records are related to. (Sure, I could simply go into the JOURNAL layout and look there, but that only gives me the information in detail and not in a nice summary on a report).
The required Account Distribution by Account report, using the above simple example, would look as follows:
Account Name Total Distribution Amount
Account Distribution for Account: BANK
Account Distribution for Account: RENT
In this way, for each account, it gives me an aggregated total of all transactions affecting it, and the corresponding related accounts (since all transactions are related to each other based on the trans_id (i.e, transaction ID) primary key field.
I hope I've given enough information to help you. If you need some clarification, please ask. By the way, I do these Account Distribution by Account, and other reports, often using a Pivot Table in Excel (which I'm trying FMP to do for me instead of Excel). For your information, to do this, I would create the Pivot Table for Account Distribution by defining Account as the column heading, the Transaction ID as the row heading, and the Amount column as the data to sum. For instance (continuing the above example), the Pivot Table would then look as follows:
trans_id / account_name BANK RENT grand_total
1 −500.00 500.00 0.00
grand_total −500.00 500.00 0.00
Since any account will have many records in it, there will be many rows of transactions. I would then sort the BANK column to get all transactions affecting the bank account and delete all rows that did not affect the bank account. Thus, the grand_total will show the total aggregate amount of all transactions affecting bank, and all corresponding totals for all related accounts (in this case, Rent). In this case, the account distribution of the Bank account is $500.00 to RENT (and the account distribution of the Rent account is -$500.00 to Bank).
The report can be very useful to analyze accounts when there's hundreds or thousands of transactions, and many accounts, and you want a summary in aggregate of how each and every account was affected and the corresponding totals of the related accounts.
Thanks very much in advance to help me in the right direction.