I am currently creating an accounting package for sole proprietor/small companies. The dictionary is completed and also a lot of the GUI.
The structure of the db (sqllite) is that is a tree structure divided into 1.clients(company/ies), 2.documents like vouchers and invoices and 3.document lines. In addition ofcourse the account themselves, which are divided into groups like assets, expenses, revenue etc.
I have enclosed a couple of pictures to show the logic. It is in Norwegian, but I think you get the idea.
Since it it uses double entry accounting, I need to have a corresponding credit for every debet and vice versa for credit.
Any suggestions on how to create a new document line automatically. Every new document/voucher will have corresponding debet/credit lines , so each document has a one-to-many relationship wth the child documentlines (which in a separate table)
I also would like some of the info in documentlines be copied to the main document.
Any suggestions on how to code/embed this. I guess that I need to have the first document line saved automatically when all info has been entered, and then a new document line (table record) would need to be created in code?.
I can’t see your pictures, and probably no one else can either. I’ll give you some pointers about stuff you may already know.
There is a SQLite function called last_insert_rowid(). So if you want to insert a document and then a document_line that has to know the document to which it belongs, then you could do something like set up a “fake” clarion table (using /turbosql) that has just a single LONG column, and do something like:
access:document.insert()
idtable{PROP:SQL} = last_insert_rowid()
docline:doc_id = idtable:idval
! any other columns you want to prime
access:docline.insert
SQLite supports deferrable constraints. That may not help you that much, because it doesn’t support sequences, but it means you can do something like this:
generate an ID for your new document as you enter the document form
Turn off autocommit (Clarion LOGOUT)
Insert any number of document lines from a normal document line browse on your form
Commit the new document with the same ID
Two tricks there: the document lines can be added to the database even tough their parent doesn’t yet exist so long as a) they are not committed and b) the foreign key constraint that normally checks that you are not creating orphans is set as Deferred, so that check is not done until after you have committed the parent.
Third item: Clarion has a TransactionManager template. If, when you create a document you also have to create a debit and a credit transaction, then the transactionmanager can make that atomic, i.e. so that you either create all three rows, or you create none of them.
The application is still a prototype, and yes - I like mdi applications.
Guess I am old fashioned, but I stilll that is the most efficient gui in many cases.
