Data List Forms Created

3:31 PM - Sunday, May 20, 2012

I was able to create the first 4 forms displaying a list of the information inside the simple accounting system. This is the first step in creating a functional data entry for all tables.

1. Chart of accounts List
2. Authorized Users List
3. Subsidiary Accounts List
4. Transactions List

1. Chart of Accounts list

For the chart of accounts, the list form (window) shows the list of account codes along with the description of the accounts. The last column is the account type. Presently it is still number coded but I'll create a function later that defines the meaning of the codes which are:

Account Type 1 - Asset
Account Type 2 - Liabilities
Account Type 3 - Capital
Account Type 4 - Income
Account Type 5 - Expenses

Instead of displaying the coded number a function should return the description.

Here's the code for the function

function AcctDesc(cAcctType)
local cAcctDesc
cAcctDesc = ""
cAcctType = alltrim(cAcctType)
do case
     case empty(cAcctType)
             cAcctDesc = "Unknown Type"
     case cAcctType == "1"
             cAcctDesc = "Asset"
     case cAcctType == "2"
             cAcctDesc = "Liability"
     case cAcctType == "3"
             cAcctDesc = "Capital"
     case cAcctType == "4"
             cAcctDesc = "Income"
     case cAcctType == "5"
             cAcctDesc = "Expense"
endcase
return (cAcctDesc)

This function will be added to a new procedure file rapidtool.prg and the procedure file will be called inside the main program via set proc to rapidtool.prg

Add New Record in the chart of accounts. 

Since I created the standard list form as a component, I was already able to add in the default action when Add New Record is clicked. All that is needed is to define a property in the form telling it which data entry form to use in adding record.

The property is cEntryForm. The data entry form should still be created (we will just create a new instance of the standard data entry form and revise it to fit the fields of the chart of accounts).

Now it's time to add the search form and the ability to print a hard copy of the chart of accounts.

Here's the subsidiary codes entry screen

 Here's the transaction entry screen

It's Coding Time!

12:38 PM Sunday, May 20, 2012

10 hours ago I finished creating the standard forms for the system, now it's time to implement them. The specific entry screens that I'll create will include:

1. Authorized Users Entry
2. Chart of accounts
3. Subsidiary accounts entry (for defining customers, suppliers, employees, others)
4. Transaction entry

The transaction entry screen will accept 6 different types of transactions that includes:

1. Suppliers invoice
2. Check Voucher
3. Sales Invoice
4. Cash Receipts
5. Debit/Credit Memo
6. Journal Voucher

For full-blown accounting systems, these transactions will be booked using different entry screens and will have more fields to accept the form information from the different types of transactions. However, since I am creating a simplified accounting system. I will only create a single transaction entry form and will just add a dropdown box where the users can choose the type of transaction they will book in the system.

For small businesses, this is alright but for bigger, more complicated business processes with multiple people in their accounting department this won't work well because in large corporations you usually have different accountants handling the different aspects of the accounting process and these accountants are not given blanket authority on all business transactions. In large corporations you have accountants handling the payables, another accountant handling the receivables, the treasury department handling issuance of official receipts and a general accountant. That's aside from the accounting manager and the VP-finance who have blanket authority over all accounting processes.

For companies that fit the above description, the accounting system should have different layers of access security and unlike our simple accounting software, the tool for big companies should control access to data (suppliers invoice, check vouchers, sales invoice, cash receipts) based on the authority level of the accountant.

For my simple accounting, this will not be the case. Every user that logs into the software will see everything. The only thing they cannot do is add new users unless they are given administrative rights.

Ok, now that the plan is laid out and the boundaries are set...It's Coding Time!

Standard Entry Screens

To minimize coding, make it faster and keep everything error free it's a must to build standard components. These will be parts of software that can be programmed once, tested and re-used anywhere with minimal errors and fast implementation. It's one secret in software development. The best and fastest way to develop a software is to build a library of re-usable components and use those instead of continually creating forms, controls and logic.

That's what I'll implement for our simple accounting system. I will create re-usable forms that will be standard across the entire software. For now I'm seeing the following re-usable forms.

1. Data List Form 

It will contain the list of records from any table. For example a list of users, the entire chart of accounts. It will have 5 buttons which will be used for adding new record, editing existing records, deleting records, searching, printing and an exit button to close the data list form.

2. Standard Search Form

It will contain a list of records from any table plus a check box whether users will search for code or description. Then it will allow search for either code or description. It will return the value based on what the user is searching for.

3. Standard Data Entry Form For Non-Relational data


This form will be used for reference files like chart of accounts and subsidiary codes. The standard re-usable form will intially contain an input box for Code and Description but additional fields can be added and the labels of the text boxes are editable depending on the data behind the form.

4. Standard Data Entry Form For Relational Data


This form will be used for transaction files. The upper portion of the form will be used to gather transaction header information (document number, date, description) while the detail will be used to gather the accounting entries.

I will post the screen shot of the standard entry forms when I'm done designing and coding it.

User Login and Verification

I was able to add the menubar, main user interface window and the logo a few hours ago. Now it's time to add a login window that will verify authorized users. It will have a very simple verification. The logic is to simply check if a user provided user name is in the users table and check if the entered password match the one in our record.

However, a thought came to me earlier about having one admin user. At present there's no way to tell in our users table who the admin user will be. So I'll have to add a new field  lAdmin l(1). It will be a logical field that tells the software whether the user is the administrator.

The difference between an ordinary user and an administrator is that the user won't be able to define new users while the administrator will be able to do so. This is still a simple logic design and will still fit my goal of a simplified accounting system. More advanced security features in full-blown accounting systems allows access definition up to data entry level (cash receipts entry, disbursements entry),  function level (add, edit, delete) and up to field level (who can see the price, the customer balance, etc.).

We won't have any advanced security in the simple accounting system but I mentioned it so you'll have an idea of what advanced system security is all about.

Here's the revised structure for our users table. 

username  c(20)
password c(20)
ladmin l(1)

I manually added two records in the users table. One is username: admin password: omar and the other is username: guest password: guest


I added these users manually so that even if there's no facility yet for adding the users, we can already log into the system.

I'll now work on the login screen and post a screenshot at the top of this post once it is done.
~2:26 PM Saturday, 5/19/2012

The login form is now working. I was thinking earlier that I may have to add in the codes in this post and I will..I'll probably do the entire documentation on how things work behind the scenes when I'm done with the entire project  by Monday but for the meantime I added the screenshot of the login form at the top of this post and here's the screen shot of the user validation codes as well as how the login code was called from the main program.

By the way I added a user defined property to the form called lAccess - this is a logical property that I use as a switch. If false it will indicate that the validation failed for the user, if true it will indicate that the user passed the username and password check.

Code when "Login" button is clicked:

Here's the code when the "Cancel" button in the login window is clicked

Here's the code calling the login window in the main program

Simple Accounting System User Interface

4:27 AM-I just finished designing the tables and database. Also done creating it in visual foxpro. I added a few test data so it won't be empty when I start coding the user interface.

My idea for the user interface will be just the traditional looking window with a menubar on top and a logo in the upper left of the screen. The user can select the different features/entry screens from the menu after logging in.

The entry screens will be standard across the entire software. To minimize learning time for users, I will see to it that they will see similar controls (add, edit, delete, print, exit) across all entry screens.

So far I was able to add the main menu and put in the logo. Next task will be to display a user log-in window before the main menu becomes accessible.

Feeling a little sleepy now so I will rest for a few hours and continue working on the user log-in screen when I wake up.

Simple Accounting System Database Design

Based on the diagram that I created earlier, I think I'll need 5 tables all included in a single database. One table will contain allowed users, the second will contain the chart of accounts, the third will be for the transactions and the fourth will be for the details of the transactions, mostly for the accounting entries, the fifth will hold the subsidiary accounts. Here's the detail of the database design:

Database name : SimpleAcc.dbc

Tables:

1. Users 

username c(20)

password c(20) 

2. Charts

acct_code c(8)

acct_desc c(100)

acct_type c(1) - 1 for assets, 2 for liabilities, 3 for capital, 4 for income, 5 for expenses

3. subs 

sub_code c(9)

sub_desc c(100)

sub_type c(1) - 1 customers, 2 suppliers, 3 employees, 4 others.

4. Journal

doc_no c(9)

doc_date d(8)

sub_code c(9) 

trn_type c(1) - 1 suppliers invoice, 2 check voucher, 3 sales invoice, 4 official receipt, 5 debit/credit memo, 6 journal voucher

amount n(10,2)

notes m(4)

5. xfile 

doc_no c(9)

doc_date d(8)

trn_type c(1)

acct_code c(8)

sub_code c(9)

amount n(10,2) - amount will either be positive (debit) or negative (credit)

sign n(1) - 1 means debit, 2 means credit

Ok, it's now time to create the database and table inside Visual Foxpro. The beauty with VFP is it comes with a built in database. You can connect to MS Sql or any other third party databases but since we are doing this quick and dirty, I'll be using the native visual foxpro database. 

~3:11 AM Saturday May 19, 2012

Create An Accounting System in 48 Hours - Introduction

This is my project for the weekend. The idea came to me a few hours ago and it kept me up. It's already 2:00 in the morning here (Saturday) but I think the idea energized me to stay up.

The idea is fueling me up. First because I know I can do it and second because I will be doing it for fun. I know that if I was doing this for money it won't be as motivating.

By the way I will be using Visual Foxpro 7. It's because I've reached some kind of excellence with the tool. Though I haven't touched any VFP codes for a few months now, I'm still confident in using it. There was a time that I can program with VFP even with my eyes closed. Though if you are following along and you are proficient with another programming language like VB, C# or any other programming language that will be fine. Just follow how I'm doing it here. I will include diagrams and screen shots as much as possible.

I'll mostly be using mindmaps instead of flow charts. During the recent months I've been using mindmaps and fell in love with it. Particularly xmind (xmind.net). But for this project I will use a web based mind mapping tool (bubbl.us).

So now, before I start coding I'll first create a mindmap to give me a road map of the modules and functions of the simple accounting system. I'll post the link to the mindmap here right after I finish doing it. Be back in a sec..
~ 2:11 AM Saturday, May 19, 2012

(2:27 AM) I'm now done with the mindmap giving me an overview of the features that will be included in the accounting system. Here's the mindmap I created using Bubbl.us:

This mindmap will be my guide for the next 48 hours. I will design the system menu based on the items listed here and upon completion of the system I expect to fully activate all of the above features listed in this map.