5 Replies Latest reply on Aug 23, 2010 6:40 PM by eibcga

    Cross-tab report needs self-join and/or GTRR?

    eibcga

      Title

      Cross-tab report needs self-join and/or GTRR?

      Post

      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

      BANK                                                -$500.00

      RENT                                                 $500.00

      Account Distribution for Account: RENT

      BANK                                                -$500.00

      RENT                                                 $500.00

      ---

      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.

      erd.jpg

        • 1. Re: Cross-tab report needs self-join and/or GTRR?
          eibcga

          If I may just put my request above in another way which may help show you what I'm looking for:

          Just as an example, lets say I'm using the invoice solution as provided by FileMaker, Inc.  On the Products Layout, I add a portal that shows all related records from the Invoices table listing all invoices the product (say Product A) was sold on and the corresponding total sales amount (e.g., Product A on invoice nos. 1000 & 1001).  These related invoices (i.e., #1000 & 1001) may also sell other products on the same invoices (e.g., invoice #1000, in addition to Product A, also has Product B and Product C.  Invoice #1001 has Product A, Product C and Product D).  I would like a sub-summary or preferably, a cross-tab report that shows the total sales by invoice for ALL the products (including Product A) from all the related invoices.  What must I do?  Hope this clarifies my request a bit more.

          Going back to my request above to my database: On the Accounts Layout, I added a portal that shows all related records from the LEDGER table listing all transaction line items affecting the account (e.g, Rent expense) and the corresponding total rent paid (e.g., Rent expense is related to Transaction ID #1 & 2).  These related transactions (i.e., #1 & 2) will also have other accounts in the same transactions, since each transaction in double-entry bookkeeping must affect at least two different accounts, i.e., more than two transaction line items (e.g., Transaction ID #1, in addition to Rent expense,  also has an entry to the Bank account.  Transaction ID #2 has  an entry to the Rent expense account, and the Prepaid Expense account).  I would like a cross-tab report that shows the total amount by account for ALL the related accounts (including Rent expense) from all the related transactions.

          Thanks again in advance.  I've done lots of reading trying to figure out how to do this on my own using self-joins, GTRR, etc., but not sure how to do the report that will show this information.

          • 2. Re: Cross-tab report needs self-join and/or GTRR?
            eibcga

            OK, this is what I have come up with so far:

            I don't think I'll be needing a new sub-summary or cross-tab report after all, since I have already set that up and it works as intended (i.e., the Trial Balance layout, which is a sub-summary report listing every related account record with a sub-summary part (sorted by account ID as the break field) of the total of the amount field in the Ledger table).  Simply, the Trial Balance report has three columns: the Account ID and Account Name (as a merge field), and the Total of the amount field.  This report changes based on the Find on the transaction date range I have entered (i.e, the date range could be the dates of all transaction line item records in the database, or a date range, e.g., all records with dates including only the year 2009, or only a specified month, or all records dated on or before a specified date, etc.).

            What I essentially want to be able to do here is a dynamic complex Find.  This Find would be based, not on a specified date range, but rather, on all transaction line item records from the Ledger table of Transaction ID numbers that relate to a specified account record.  For any given account record (which could be entered in a global field to allow me to specify which account I want to do the complex Find on), I want to do a Find of all related transaction line item records (based on the Transaction ID field) from the Ledger table.

            How would I do the script steps so that my Find is based on the transaction ID's of all the related transaction line item records of an account?  Needless to say, this is complex, since any account could have a varying number of transaction line items (and thus, many different transaction ID numbers).  Any ideas?  (In an invoice solution, this complex find would be similar to having a listing of total sales for a specified Product record, and for all related Product records that were sold on the related invoices).

            • 3. Re: Cross-tab report needs self-join and/or GTRR?
              eibcga

              Here's the latest...

              I hear that I GTRR script and Constrain/Extend Find may help accomplish my goal.  In the meantime, I currently have a report that lists in the body part all related child records for each Account record [General Ledger sub-summary report]. I also have a portal from the Ledger table on the Accounts table layout, as I mentioned in previous posts.  I can enter the transaction ID for each related record in a Find by adding a new request for each trans ID listed in the portal, but of course I would like FMP to automate this using script steps.  Based on the new Found Set, I can run my existing sub-summary and cross-tab reports.  So, I'm looking for more guidance on how to get the results from the trans_id field of related records from the portal or the report mentioned above, into a new Find to get a new Found Set?

              • 4. Re: Cross-tab report needs self-join and/or GTRR?
                eibcga

                Still plugging along on this problem.  I'm just not sure how to have FM grab the transaction ID from each related record in the found set of the GTRR (I understand that the GTRR script step gives me the same result as if I had a portal of related records on a layout), then enter the transaction ID of each related record into the transaction ID field in a find request, extending the Find for each occurrence of the related record until all related records are in the find.

                It appears to me the script steps would be something like this, but I'm not sure how to put it in the right order or in a format FM will understand

                - GTRR
                - count number of records in GTRR found set
                - loop for each record in found set
                - perform find
                - get field contents of trans_id of recordnumber 1...
                - replace field contents of trans_id field with calculated result (GetNthRecord)
                - extend found set
                - loop end when all recordnumbers have been inserted in trans_id field
                - perform find

                I know what I want, and I can do it manually, but I don't know how to automate the process in FM.  I guess I should hire a developer to consult with.

                • 5. Re: Cross-tab report needs self-join and/or GTRR?
                  eibcga

                  Thought I'd share the crude script I wrote that basically does the Find I was looking for.  Oops seems I can't attach screenshots anymore.