2 Replies Latest reply on Apr 19, 2012 9:10 AM by applelakshan1

    Trail Balance from Filemaker pro

    applelakshan1

      Title

      Trail Balance from Filemaker pro

      Post

      Hello Guys i need to add the extra function for trail balance on our database. 

      01. I have 3 tables and the names are  Transactions , Accounts , and sub_accounts

      Relationship

      Accounts = subaccounts  

      pk_accountfamily_id -----> fk_accountfamily_id (= allowing to create clild records on subaccounts table) (We have only 7 Accounts)

      Transactions = Accounts 

      pk_account_id -----> fk_account_id  (= allowing to create clild records on Transaction  table) 

      * Normally Trail balance based on  Account Families, Lets Say we have  Family called 6 So the Account id is  6  , and 601, 602, 603, etc , those are standard formats and will be clid records of Account 6.

      So when i try to create new records in  transaction table, i can select which account and which sub accounts that transaction is related .

      For instance Lets talk about Account No 6, and family 602 (Sales of services) .

       I have 10 records in transaction table, imagine from that 10 records 6 records are related to 602(Account 6) and 2 Records are related to 603 (again account 6), and another 2 records related to 500 (Account 5). So what i want to get the Total  of this 602 in to one field.

      * So from all the records of transaction table, this should filter Total of  all 602, 603, and so on. 

      The End Result should be Stay in one Calculation Field.
       

        • 1. Re: Trail Balance from Filemaker pro
          philmodjunk

          You appear to have these relationships:

          Transactions>----Accounts-----<Subaccounts

          So when i try to create new records in  transaction table, i can select which account and which sub accounts that transaction is related

          That would suggest that you really need these relationships:

          Accounts----<Subaccounts-----<Transactions

          Accounts::pk_account_id = Subaccounts::fk_account_id
          Subacounts::pk_Subaccount_id = Transactions::fk_SubAccount_id

          There are two options for getting a specified total of all transactions for a given subaccount record (601, 602, 603)

          From a list view layout based on SubAccounts, you can use either a summary field defined in transactions or a calculation field defined in subaccounts as: Sum ( transactions::Amount ) to get the total of all transaction amounts for that sub account.

          From a list view layout based on Transactions, you can define a summary field that computes the "total of" the amount field. You can place this field in a sub summary part "when sorted by fk_subAccount_id" to get a total for each sub account. You can also set up a summary field to get a running total that starts over with each sub account.