1 2 Previous Next 22 Replies Latest reply on Dec 27, 2011 10:23 AM by eibcga

    Financial Account Analysis

    eibcga

      Title

      Financial Account Analysis

      Post

      Hello all,
      I'm a beginner using FMPAv3 on Mac OS X 10.6.7.  I designed a basic database that helps analyze financial transactions in various ways using standard double-entry bookkeeping journal entries.  It suits my needs so far, but I'd like to add more features, etc. as I learn new skills in FileMaker.
      I'm trying to automate these steps in FileMaker using a script, etc. Here are the steps I currently do manually in FileMaker:
      1) Enter the LEDGER layout in table view (LEDGER tracks JOURNAL details or line items)
      2) Do a Find on all account ID’s that pertain only to revenue and expenses.
      3) Enter the layout called Allocated Revenue/Expense which is based on LEDGER table occurrence. 
      4) The layout will show me all transactions for the period based on the found set in step 2
      5) I’m interested in all transactions in step 4 listed on the layout where the total is zero. (at present the found set from step 2 shows ALL transactions affecting revenue and expense accounts only, whether they total to zero or not)
      6) Based on the “found set” in step 5 of transactions I see with or without zero totals, I do a new Find in the LEDGER layout table view for all transaction ID’s which only had zero totals in step 5.
      I was hoping I could have FileMaker do all this for me automatically in some way, or at least automate most of these steps.
      Below is my relationships graph.  Thanks very much for your help.  If you need more details please ask.

      Screen_shot_2011-05-14_at_9.56.33_PM.png

        • 1. Re: Financial Account Analysis
          philmodjunk

          That all seems very easy to script so that all you need is to click a button or make a few value list selections and you should be on step 6 in short order.

          There are details missing from your description of this process however.

          In step 2, what criteria do you enter to find these records?

          Is there a date or range of dates also to be specified here when you find these records?

          In steps 5 and 6 you talk about wanting only the records where the "total is zero" what kind of field is this total? Presumably, it's a calculation field, but would like to be sure and to know what expression is used in it to calculate the total. (By any chance is it a summary field or a calculation that uses "GetSummary"?)

          • 2. Re: Financial Account Analysis
            eibcga

            Hi PhilModJunk,

            The LEDGER table (a join table, akin to the Invoice line items table in an invoicing solution) stores the child records to the JOURNAL table and the ACCOUNT table, since there's a many-to-many relationship between ACCOUNT (akin to the Product table in an invoicing solution) and JOURNAL (akin to the invoice table in an invoicing solution).  Business rules require that a JOURNAL record must relate to at least two or more LEDGER child records, since many child records can relate to one, and only one parent record.  In addition, the sum of the child records related to a Journal parent record must be zero.  

            You'll note that my database is basically a simple or crude version of most accounting software you'll see in the market today.

            The account ID's in the ACCOUNT table have the following ranges to represent the following financial categories:

            1000-1999 - assets

            2000-2999 - liabilities

            3000-3999 - net assets

            4000-4999 - revenue and gains

            5000-5999 - expenses and losses

            In Step 2 in the LEDGER table view, I just do a find in the Date field for the calendar year "2011-*-*", and the Account ID field ">3999".  This gives me all LEDGER records related to account ID's greater than 3999, and thus, the revenue and expense accounts only for 2011.

            In steps 5 and 6, I'm referring to a Summary Field in the LEDGER table, Total of "amount" field also in the LEDGER table.   The 'amount' field can be a positive number or a negative number.  Since in double-entry bookkeeping, every entry must "balance" (i.e., total on the left side of the accounting equation must equal total on the right side), the sum of all child records in the Ledger table must equate to zero for each and every related journal record.  In other words, basically, the following accounting equation must be satisfied, Assets = Liabilities + Equity.  Equity = Revenue - Expenses:

            A = L + E

            This accounting equation is re-arranged when translating real-world bookkeeping into a database:

            A - L + E = 0

            So, when I record a financial transaction in the Journal table, the Journal record must have at least two journal line items in order for the journal entry to balance. A Journal record (parent) can relate to many Ledger records (child), since there must at least be two records related to each Journal record (one to many relationship between Journal and Ledger).

            For example, I go out for dinner and spend $20.  To record this in the database, I enter the Journal layout and create a new Journal record, then I have a portal to the Ledger table and enter the details in the portal.  I of course reduce the bank account, the asset account, (as a negative $20 in the amount field), and increase the Meals expense account for $20 (as a positive $20 in the amount field).  The equation is satisfied since: −20 − 0 + 20 = 0.  The sum of the amount field in this case would be zero when you consider asset, liability, revenue and expense accounts.  But, the sum of the amount field in this case would not be zero if you only look at revenue and expense accounts.

            But what if I find out later on that I used the wrong expense account, the dinner was not for me, but it was buying dinner for a friend as a gift?  Rather than go back and change the original child record from the Meals expense account to the Gifts expense account,  I would add a new journal record and the details in the portal to decrease the Meals expense account, and increase the Gifts expense account.  The equation is satisfied: 0 − 0 +20 − 20 = 0.  This is an example of where a journal entry was recorded that only affected a revenue account, an expense account, or both (assets and liability accounts were not affected).  Essentially, Step 5 above will give me a report of all entries where this occurred, that is,  when the sum of the amount field in this case would be zero.

            As I mentioned previously, I'm already getting the report I need, but I wanted it to leave out the irrelevant stuff, that is, where the sum of the amount field does not equal to zero.

            Hope this helps.

            • 3. Re: Financial Account Analysis
              philmodjunk

              I'm familiar with double entry accounting methods but in all scripts, the devil is in the details.

              "In Step 2 in the LEDGER table view, I just do a find in the Date field for the calendar year "2011-*-*", and the Account ID field ">3999".  This gives me all LEDGER records related to account ID's greater than 3999, and thus, the revenue and expense accounts only for 2011."

              Go To layout [//select a journal layout as it is journal records that you want here]
              Enter Find mode [] // clear pause checkbox
              #use fields in Journal table for this search wherever possible
              Set field [Ledger::Date ; Year ( Get ( CurrentDate ) ) ]
              Set Field [Journal::Account ID ; "> " & 3999]
              Set Field [Journal::cLedgerTotal ; 0 ]
              Set Error capture [on]
              Perform Find []
              If [Get ( FoundCount ) > 0 ]
                 Go To Related Record [Show only related records; match found set; From table: Ledger; Using layout: "Ledger" (Ledger)]
              Else
                 Show custom dialog ["No records were found"]
              End If

              The more criteria that you can specify in the Journal table, the more quickly you can perform this find. So use a fields in Journal for date and account IDs if you have such fields defined in that table. cLedgerTotal is a calculation defined with the Sum function to compute the same total as your summary field. Unlike a summary field, you can enter search criteria into this calculation field. (And the use of this calculation field is why we are searching the Journal table rather than the ledger.)

              In place of Year ( get ( CurrentDate ) ) , you can also refer to a global field where the user enters or selects a year if you want a bit more flexibility here.

               

              • 4. Re: Financial Account Analysis
                eibcga

                Using the script debugger tool to test the script, I get an error 5 (command is invalid, set field script step does not have a calculation specified) when I run line "Perform Find" and the Journal layout remains in find mode on the screen.  The Perform Find line you have actually says "Perform Find/Replace" on mine.  As well, I changed line three of the script from LEDGER::date to JOURNAL::date, since I have the date field in the Journal table (because the same date would apply to all related child records for any given journal entry).  I also changed line five of the script from Journal::_kf_acct_id to LEDGER::_kf_acct_id, since the Account ID field is actually in the Ledger table (because each child record could have a different account ID).  Any ideas?  Thank you very much for your help so far.  I didn't think of GTRR before.

                • 5. Re: Financial Account Analysis
                  philmodjunk

                  Perform Find[] and Perform Find/Replace are two different script steps. You need Perform Find, not perform Find/Replace.

                  • 6. Re: Financial Account Analysis
                    eibcga

                    Oops.  Fixed.  It worked!  Thank you very much indeed.

                    • 7. Re: Financial Account Analysis
                      eibcga

                      In the Perform Find [] command, the Specify find requested checkbox is blank.  But, when I click the Specify…" button it shows a find requested that was used from another script I have that was previously run.  So when I run the above script, it appears the above find request is adding to the existing as AND… rending the script useless.  Is there some way I can clear the Specify Find Requests panel of all find requests so it doesn't interfere with the above script?  Now the above script won't run properly.

                      • 8. Re: Financial Account Analysis
                        LaRetta_1

                        Hi Erik,

                        Finds manually ran are kept available so that, if you perform a manual find and then want to script it, you can have it automatically there and just check 'specify find requests' to activate it.

                        It will not affect any other finds that you have made.

                        If you uncheck 'specify find requests' then it doesn't matter what may be inside it ... it won't affect your current script.

                        • 9. Re: Financial Account Analysis
                          eibcga

                          Ooh ok, LaRetta.  Thanks very much.

                          • 10. Re: Financial Account Analysis
                            eibcga

                            The above script does not seem to work as intended after I run other scripts have seemed to effected it somehow.  The above script worked the first time.  But since then I have run other scripts that have finds in them.  When I run the above script again, it no longer shows the correct found set (of accounts ID's >3999).  It appears the erroneous found set is now also showing related records that relate to the account record for the bank account.  I have a script that does a find on the bank account.  oh well.  Thanks anyway for the ideas and your help.

                            • 11. Re: Financial Account Analysis
                              philmodjunk

                              That is odd, the above script does not rely in any way on a specific record or group of records being present in the found set. It should produce identical results in each case. Thus, any other finds you might do before running this script should not affect what it pulls up.

                              It also always starts the find from a specified layout--so running the script when the wrong layout is current should also not be a factor.

                              • 12. Re: Financial Account Analysis
                                eibcga

                                To revisit the issue I'm still having, the problem seems to be in this part of the script, based on my review using the Debugger:

                                Set field [Ledger::Date ; Year ( Get ( CurrentDate ) ) ]
                                Set Field [Journal::Account ID ; "> " & 3999]
                                Set Field [Journal::cLedgerTotal ; 0 ]

                                While the script is not giving any errors when I run it, it's not giving the desired results.  The results I get include not only account numbers >3999, but also under <3999.  I was hoping the Find would consider each script step as an 'AND' when doing the search.  Can't figure out why not.  PhilModJunk, you mentioned that the find always starts the find from a specified layout.  I tried re-ordering the "set field" script steps to see if it would impact the result, but no.

                                I point out that the fields used in the 1st and 2nd set field script steps above should actually be JOURNAL::date, and LEDGER::account_id, respectively, since LEDGER table is a child to the JOURNAL parent table (i.e., the ledger detail line items in LEDGER can have one, and only one date for each journal record, but each of the ledger detail line items in LEDGER which are related to a journal record, can have many different account id's).

                                Any ideas?  Thank you very much again for all the guidance.

                                • 13. Re: Financial Account Analysis
                                  philmodjunk

                                  "I point out that the fields used in the 1st and 2nd set field script steps above should actually be JOURNAL::date, and LEDGER::account_id, respectively, since LEDGER table is a child to the JOURNAL parent table (i.e., the ledger detail line items in LEDGER can have one, and only one date for each journal record, but each of the ledger detail line items in LEDGER which are related to a journal record, can have many different account id's)."

                                  That might make all the difference in the world here. Specifying search criteria in a related table (Ledger) can produce results that may be different than what you expect. Is this find intended to find Journal records or account records?

                                  If performed from the Journal layout but specifying an account ID in Ledger, you are telling FileMaker to find all the Journal records that have at least one related record meeting your accountID criteria. Thus, a Journal record may be linked to three Ledger records with Account IDs: 4, 5, and 5000. It will include this Journal record in the found set because one of the related ledger records has an accountID greater than 3999, but your layout may or may not display data from this related record. (It depends on the design of the layout.)

                                  Also, if AccountID is a field of type text rather than number, you may find records you did not expect to find as "4" evaluates as greater than "3999" when the values are stored in a text field.

                                  • 14. Re: Financial Account Analysis
                                    eibcga

                                    The Find is intended to bring up a found set in the LEDGER table of all LEDGER child records where the related ACCOUNT (ID) records are >3999, and where the sum of all LEDGER child records related to each related parent JOURNAL record equal to zero (meaning that debits equal credits and only revenue and/or expense accounts were posted to).  As noted in my initial post above -- I can do the steps manually in FMP which gives me the results in a separate ledger layout, but was trying to automate all the steps into a script I can run that leaves out unnecessary info.  So the find criteria would need to be entered in two layouts, i.e., the JOURNAL and LEDGER (unless I add related fields from the LEDGER able to the JOURNAL table or visa versa?).

                                    I have checked all my primary and foreign key fields in each table and they are all Number, including ACCOUNT::kp_account_id and LEDGER::kf_account_id.  It was worth the look.

                                    Clear as mud?  :)  Thanks again and hope for some guidance.

                                     

                                     

                                    1 2 Previous Next