Account Ledger / Transactional Object - Forum - Rollbase - Progress Community

Account Ledger / Transactional Object

 Forum

Account Ledger / Transactional Object

  • Can anyone help my fuzzy brain on a Friday afternoon? I want to setup a basic Accounts Ledger in Rollbase. I'm thinking I'd like to have a 'Transaction' Object with a one-to-one relationship so each Debit transaction/record must have a corresponding Credit transaction/record but as soon as I try to select the same 'Transaction' object I'm only offered a Parent/Child relationship. So, I am thinking, there would need to be an 'Opposite Twin' relationship type, or I should create two Objects: 'Debit Transaction' and 'Credit Transaction' If anyone has QuickBooks/Sage/OtherAccounting experience you'll be familiar with this data structure. Any suggestions from people with more data structure design experience? Is there a best-practice method using what we have or is there a Transaction Object type feature we need to request?
  • Hi surferking,



    could you please elaborate more on object Cardinality? More specifically, how the 'Transaction' object and 'Debit' or 'Credit' transaction types should be organized. In this situation, I'm not sure how a transaction should be related to itself, or how a debit transaction should be related to a credit transaction.



    From my understanding, transactions are usually only debit or credit, but could also be both. There may be such differences in the type of transactions that an object would be needed to represent each one, thus resulting in a total of three objects. The transaction object could simply represent an instance of a transaction and bind a number of transaction types to a transaction record. This record, named 'Transaction', will represent an actual transaction, and the related records, which could exist of each 'Debit' or 'Credit' types, describe the methods of payment that were used to compensate the exchange.



    If differences are minute and only one payment source is
  • Hi Corey,



    Perhaps the best way to explain is with this open source package:



    http://frontaccounting.com/wb3/pages/gallery/journal-entry3.php

    http://frontaccounting.com/wb3/pages/gallery/journal-entry-view4.php

    http://frontaccounting.com/wb3/pages/gallery/general-ledger5.php



    Going to spend the weekend playing with Rollbase ;)



    Thanks, Marcus
  • Think we'll be looking at the data structure by connecting to Front Accounting using the principles in the Rollbase in Action manual Appendix C too as other parts of the functionality will be handy too
  • In accounting terms a transaction must on be a Debit OR a Credit OR Zero.



    For a Double-Accounting Ledger you would select two Accounts. One to Debit and one to Credit. It doesn't matter which is which as long as each is linked to it's equal and opposite and updating one will update the equal and opposite other.



    Both you examples would work but lack the linking of the Debit to the equal and opposite Credit.



    It might actually be a feature request to create a Ledger-type Object so this is all done at the application level rather than in the Application design. With this kind of functionality it would be very quick to be able to create Accounting/Banking/StockMovement management Applications so well-worth the effort to design a best-practice method for this.



    Thanks!
  • I've cracked it! With the help of a couple of good night's sleep and a beer or two :)



    The simplest solution is always the best!



    I'll tidy up the application and post it as an example for people to base simple or complex Accounting Apps on if I get time.



    -------------------------------

    This is a brief summary of the App structure

    -------------------------------



    Objects: Customers, Suppliers, Accounts, Account Type, Transactions.



    Customers: one-to-one relationship with Accounts



    Suppliers: one-to-one relationship with Accounts



    Accounts: many-to-one relationship with Account Types



    Account Types: (automatically created from above relationship) one-to-many relationship with Accounts



    Transactions: 2 x many-to-one relationships with Accounts



    Transaction Fields: Date, Account From, Account To, Name (or Type if you wanted to create a Transaction Types Object), Amount Received, Amount Sent (Formula Field: 0-({!qb_amount_received}). Don't forget the - on the beginning of the { to make the
  • Note, in my example 'Account' is different from the default 'Account' App, it is the naming convention taken from QuickBooks/Sage. Read as 'Transaction Account' if it makes more sense to avoid confusion.
  • Another note to clarify, the 'Amount Received' field is both Credits & Debits. Positive amounts being Credits and Negative Amounts being Debits.



    The calculation of the -'Amount Received' to be the 'Amount Sent' field therefore automatically becomes the equal and opposite Credit/Debit