7 Replies Latest reply on Jul 30, 2012 9:07 AM by philmodjunk

    Financial Transaction Type

    eibcga

      Title

      Financial Transaction Type

      Post

      I'm a novice FMPA12v2 Mac user using a financial database I designed from scratch.

      I have a one-to-many relationship between my Journal (aka Transaction) parent table, and the Ledger (aka Transaction LineItem) child table, since a transaction can have many line items, but each line item can have one and only one transaction (i.e., one date, one contact name, one memo, one reference number, etc.).  Each Transaction can have one and only one transaction type (i.e., Sales, Cash Receipt, Purchase, Cash Payment, and General) but each transaction type can have many transactions.  Each transaction can have one and only one Contact (i.e., a customer or vendor name), but each Contact can have many Transactions.  Each Transaction LineItem can have one and only one Subaccount, but each Subaccount can have many Transaction LineItems.  Each Transaction LineItem can have one and only one Account, but each Account can have many Transaction LineItems.  An Account Type (i.e., Asset, Liability, Equity, Revenue, Expense) can have many Account Groups (i.e., Cash, Accounts Receivable, Capital Assets, Payables, etc.).  An Account Group can have many Accounts.  All object tables are grey table occurences in the Relationships Graph, Event Tables are in Green, Query Tables are in Red.

      When I'm on the Transaction Layout to enter a new transaction, which has a portal to the Transactions LineItems table, I would like there to be a formula in the Transaction Type foreign key field that decides the following for any given transaction:

      If any of the transaction line items in the portal uses a cash account that is being increased, then the transaction type is Cash Receipt.

      If any of the transaction line items in the portal uses a cash account that is being decreased, then the transaction type is Cash Payment.

      If any of the transaction line items in the portal DOES NOT use a cash account, but is affecting an expense account, then the transaction type is Purchase.

      If any of the transaction line items in the portal DOES NOT use a cash account, but is affecting a revenue account, then the transaction type is Sale.

      If any of the transaction line items in the portal does not meet any of the above critera, then the transaction type is General.

      I'm not sure how to write the case or IF formula so that it considers this.  In the meantime, I have always had to input the transaction type manually when I enter a transaction.

      Everything else works as intended based on my current skill level (although you may find some inefficiencies which I am slowly learning how to resolve).  As you can see from the screenshot, I have over 11,000 transaction details, over 4,000 transactions, and many analysis reports.

      Any guidance to show the way would be very appreciated.  I couldn't have gotten this far already without all the support I have gotten from this Forum and all my reading.

      Screen_Shot_2012-07-28_at_5.27.49_PM.png

        • 1. Re: Financial Transaction Type
          philmodjunk

          Definitely looks like something that will need the case function.

          From the screen shot, you have a single amount field, not debit and credit fields. So a ledger record that descreases an account has a negative value in the amount field?

          Would it be correct that you would never have a Journal record where one cash account is increased in one related ledger record and another related cash account is decreased in a different ledger record? If it Is possible, what would the transaction type be?

          And by "cash account" you mean that it is a ledger record that is related to the Cash account group record?

          Any calculation field defined in Journal that uses such a case function to determining Transaction Type must be unstored and therefore is unindexed. Will that be an issue? If so, a script trigger on the Ledger::account field can perform a set field step with the case function as its calculated result and you can get this value as a stored/indexed field.

          My first cut at such a case function:

          Define this field, cTranTypeFlag in Ledger:

          Case ( Account_Group::Name = "Cash" and Ledger::amount > 0 ; "Cash Receipt";
                    Account_Group::Name = "Cash" ; "Cash Payment" ;
                    Account_Type::Name = "Expense" ; "Purchase" ;
                    Account_Type::Name = "Revenue" ; "Sale" )

          Define this calculation in Journal: (or in a set field step)

          Let ( FlagList = List ( Ledger::cTranTypeFlag ) ;
                  Case ( Not IsEmpty ( FilterValues ( FlagList ; "Cash Receipt" ) ) ; "Cash Receipt" ;
                             Not IsEmpty ( FilterValues ( FlagList ; "Cash Payment" ) ) ; "Cash Payment" ;
                             Not IsEmpty ( FilterValues ( FlagList ; "Purchase" ) ) ; "Purchase" ;                  
                             Not IsEmpty ( FilterValues ( FlagList ; "Sale" ) ) ; "Sale" ;
                             "General"
                           ) // case
                ) // let                

          • 2. Re: Financial Transaction Type
            eibcga

            A positve amount entered in the amount field means debit; a negative amount entered in the amount field means credit.  Whether or not a particular account is increased or decreased will depend on whether the entry is a debit or credit, and what type of account it is (as per the standard double-entry bookkeeping accounting equation Assets = Liab + Equity).  For example, all asset and expense accounts are increased with debits (positive amounts).  All liabilitilies, equity and revenue accounts are increased with credits (negative amounts), and visa-versa.

            The transaction you describe where a related ledger record could increase a cash account, and another related ledger record could decrease another cash account (for example, a transfer of funds from one bank account to another, or a withdrawal of funds from the bank to cash on hand) - would be considered in this database to be a General transaction type (since all you're doing is moving money around that was already received).  For clarity, these two related ledger (aka transaction line items) records are two child records of the same parent transaction record.

            Cash account means an account record in the Account table, that is related to the "Cash" record in the Account Group table.  For example, the Cash account group record could have many accounts related to it (e.g., Cash on hand, Bank A, Bank B, Bank C, etc.), but each account record can only have one and only one account group related to it.

            I also clarify that the a transaction can only be one and only one transaction type (based on the related transaction line items child records), so not sure why the cTranTypeFlag field would be in the Ledger (aka transaction lineitems) table, rather than in the transaction table.  This field being unstored is not an issue.

            I'll take a stab at your suggested solution and try get my head around it.  Thank you very much indeed!

             

             

             

            • 3. Re: Financial Transaction Type
              philmodjunk

              cTranTypeFlag is in Ledger in order to access the needed data to set up things for the calculation in Journal to identify the transaction type. Note that it does not identify a transaction type, instead, it "flags" the records as a possible type so that the calculation in Journal can compare that flag to all other "flags" from the other related ledger records.

              I think the fact that different ledger records related to the same Journal record can both increase and decrease cash accounts will complicate this effort at determining the transaction type. Can there be 3, 4 or more ledger entries for one Journal record that modify cash accounts?

              If so, you'll need to compare the number of increase and decrease ledger entries and use the difference to determine the type.

              If there can only be one of each at most, you can use ValueCount ( FilterValues ( FlagList ; List ( "Cash Receipt" ; "Cash Payment" ) ) ) = 2

              • 4. Re: Financial Transaction Type
                eibcga

                This is what I have so far:

                I have converted your answer to using the ID's (match fields) rather than the record description names:

                In Ledger table I added a calculation field %journal_type, unstored:

                Case (Account_Group::id=10 and amount>0;2;
                Account_Group::id=10 and amount<0;4;
                Account_Type::id=5;3;
                Account_Type::id=4;1)
                In the Journal table I added a calculation field %journal_type, unstored:
                Let (FlagList = List ( Ledger::%journal_type);
                Case (not IsEmpty ( FilterValues ( FlagList ; 2 ) );2;
                not IsEmpty ( FilterValues ( FlagList;4));4;
                not IsEmpty (FilterValues(FlagList;3));3;
                not IsEmpty (FilterValues(FlagList;1));1;
                5
                )//case
                )//let
                The screenshot shows the correspoinding ID's (match fields) for each applicable table.
                I have tested this on the journal layout by changing the accounts entered in the portal to watch the transaction type change accordingly.
                It's almost working.
                The problem now is in the first formula above (repeated below for convenience)
                Case ( Account_Group::Name = "Cash" and Ledger::amount > 0 ; "Cash Receipt";
                          Account_Group::Name = "Cash" ; "Cash Payment" ;
                          Account_Type::Name = "Expense" ; "Purchase" ;
                          Account_Type::Name = "Revenue" ; "Sale" )

                The above case formula does not fully consider the criteria I detailed above in my previous post. The first two lines above regarding whethercash is being received or paid, is working perfectly. The problem lies in the last two lines. What they should figure out if what if there NO cashaccounts used, but one of the account types is an expense account type (when it should be the Purchase transaction type).And, if one of the account types is revenue, then it shoud be the Sale transaction type.Finally, if neither of these criteria are met, then it defaults to the General transaction type.

                ---
                • 5. Re: Financial Transaction Type
                  philmodjunk

                  The first case function is not intended to determine type. The logic that assigns type is in the second case function, not the first. The first just sets up values so that the second can pull them into a list and determine the type from what values are or are not present in the list. And we have already identified one case where this will fail--a case not specified by you at the time that I wrote it.

                  What they should figure out if what if there NO cashaccounts used,

                  That is handled in the second case function. Case functions return the value specified by the first expression that evaluates as True, The 3rd, 4th and 5th values cannot be returned unless there are no "Cash Receipt" and "Cash Payment" values in the list as those will cause either the 1st or 2nd value to be returned.

                  • 6. Re: Financial Transaction Type
                    eibcga

                    cTranTypeFlag is in Ledger in order to access the needed data to set up things for the calculation in Journal to identify the transaction type. Note that it does not identify a transaction type, instead, it "flags" the records as a possible type so that the calculation in Journal can compare that flag to all other "flags" from the other related ledger records.

                    I think the fact that different ledger records related to the same Journal record can both increase and decrease cash accounts will complicate this effort at determining the transaction type. Can there be 3, 4 or more ledger entries for one Journal record that modify cash accounts?

                    If so, you'll need to compare the number of increase and decrease ledger entries and use the difference to determine the type.

                    If there can only be one of each at most, you can use ValueCount ( FilterValues ( FlagList ; List ( "Cash Receipt" ; "Cash Payment" ) ) ) = 2

                    Ok thanks.  It is not likely there could be 3 or 4 more transaction line items (in ledger table) for one transaction record (in journal table) that modify cash accounts -- only 2 transaction line items (for example, increase one bank account, decrease another bank account).  But, there will be many transaction line items for one transaction record that increase ONLY ONE cash account (for eample, recording a pay check -- a Cash Receipt transaction type).

                     

                     

                    • 7. Re: Financial Transaction Type
                      philmodjunk

                      But, there will be many transaction line items for one transaction record that increase ONLY ONE cash account (for eample, recording a pay check -- a Cash Receipt transaction type).

                      And the second case function should handle that situation correctly.

                      Take a look at the functions used inside the case function.

                      List pulls the values returned from cTranTypeFlag for all related values into a list. With my original function, you might get a list that looks like this if you had three related ledger records and you displayed the values returned by list in a large field:

                      Cash Receipt<return>
                      Purchase<return>
                      Sale<return>

                      This is then checked with the expression

                      Filtervalues ( FlagList ; "Cash Receipt" )

                      which will return "Cash Receipt" with the above data. If "Cash Receipt" were not in that list of values, it would return an empty string.

                      Thus,

                      Not IsEmpty ( Filtervalues ( FlagList ; "Cash Receipt" ) )

                      Will be true only if there is at least one "Cash Receipt" value in FlagList. And note that the Case function checks for the two "cash" items first before checking of the other possible types.

                      It is not likely there could be 3 or 4 more transaction line items (in ledger table) for one transaction record (in journal table) that modify cash accounts

                      Hmmm, I'm not real comfortable with the words "not likely". That suggests that it could happen and if it does, the case function will fail to assign the correct type. If you can say "not possible" or "not permitted" then this modified case function should do the job:

                      Let ( FlagList = List ( Ledger::cTranTypeFlag ) ;
                              Case ( Not IsEmpty ( FilterValues ( FlagList ; "Cash Receipt" ) ) AND Not IsEmpty ( FilterValues ( FlagList ; "Cash Payment" ) ) ; "General" ;
                                         Not IsEmpty ( FilterValues ( FlagList ; "Cash Receipt" ) ) ; "Cash Receipt" ;
                                         Not IsEmpty ( FilterValues ( FlagList ; "Cash Payment" ) ) ; "Cash Payment" ;
                                         Not IsEmpty ( FilterValues ( FlagList ; "Purchase" ) ) ; "Purchase" ;                  
                                         Not IsEmpty ( FilterValues ( FlagList ; "Sale" ) ) ; "Sale" ;
                                         "General"
                                       ) // case
                            ) // let