Home | Welcome | Legal Notices | Features and Limitations | Data Model | Installation and Configuration | Operation | Appendix | Screen Shots
Prior to deploying Open Accounting, one must install the database and populate some tables. This section describes these prerequisite tasks. Then one can use the automated forms to post billings and payments.
Open Accounting uses the double type for all numeric calculations. Thus it has no idea what a dollar is, or a euro for that matter. All Transactions for a given organization must be in the same currency. But it’s possible to create multiple Organizations to service Contacts in different currency systems.
The indexes between tables are thirty-two bit integers. One can, for example, change the type name “Cash” to “Geld” and the system will still work as long as the associated ID, 200 by default, does not change. Unfortunately this change impacts all Organizations in the system.
Open Accounting is distributed in two files. One file, called openAccountingTables##-##.mdb, contains the tables themselves. The other, called openAccounting##-##.mdb, contains code, forms, macros, queries, and reports.
The application is linked to the tables. Unfortunately since Access stores absolute and not relative path names, this link must be adapted to every single installation. In Access 2000 and above, it’s possible to use the link table manager for this task. In Access 97, however, one must relink the tables.
Which tables need to be relinked? Those with single word names such as Accounts, Categories, and so on. Leave tables with multiple word names, such as CurrentSession, alone. In the Tables view, first delete the links. Then use Link Tables under Get External Data in the File menu to open the openAccountingTables##-##.mdb file, click the Select All button, and then OK.
If desired, an ODBC compatible database server can host the shared database tables, again those with single word names. We provide scripts written in SQL to create and initially populate the required tables. These scripts were tested in MySql® and may need modification to work with other brands of database servers.
After installation, it’s necessary to populate the Accounts, Contacts, and Organizations tables. When shipped, Organizations contains a bogus company. Accounts and Contacts are empty. Tables used for relational integrity are populated, but one may change them if desired.
Forms for editing existing rows in configuration tables are provided under the Administration menu. New rows must be manually inserted, however: refer to the Data Model section for information about setting values for primary keys. After changing any data item in a configuration table, either close and open the database or use the Change Organization button on the main menu to propagate the update.
Start Open Accounting without configuration and you’ll be using a company named CHANGE ORGANIZATION TABLE!!! with an identifier of FAKE. So the first order of business is modify this table. There is an editor provided under Administration for just this purpose. The required changes should be obvious, except for the slogan column: this is just a tag line printed on the account statements. Once the Organizations table has been updated, use the Change Organization button on the Main menu to log in again.
The Organizations table can have multiple records for tracking different companies or people. For example, a professional association could use one record in this table for each practitioner to allow tracking by care provider. Or each record could represent a separate corporation. Or an accountant could use one record per client. Organizations are groups of related accounts, and Transactions are posted to one organization at a time.
The DetailedBilling column in the Organizations table determines if payments are matched to transactions or not. Some customers may be confused by detailed billing. However internal auditing may be eased with this feature enabled.
By the way, there is nothing that keeps one from using the FAKE organization. Or creating an organization called DEMO. It’s perfectly OK to create bogus Organizations, Contacts, and Accounts for demonstration, training, or other purposes.
The PostYear field allows one to set the fiscal year for posting transactions. This field is read at program start-up and sets a global default for all forms.
There are two ways to populate the contacts table:
Type in contact information one at a time, or
Import this information from another database, such as the Palm Desktop.
Actually Access can import data from Excel spreadsheets, and some programs can export Access tables. But there is one problem with importing data from another program: the priority and role fields are required in Open Accounting, but there may be no analog in the other program.
There are two potential solutions. In the Contacts database table definition, either a) set the required property for both of these columns to No or b) set the default value property to a value appropriate for most contacts, then manually edit the exceptions.
Also please note the Zip code field is digits only.
Unfortunately it’s probably going to be near impossible to import data for this table from another program. But fortunately it takes about ten seconds to populate each record.
Recall from our data model discussion that each record in the Accounts table has a link to both the Organizations and Contacts tables. The data entry form for Accounts present these as drop downs. Of the remaining fields, the hourly rate is used for time billing and it can be different for each account. Also the monthly retainer field is used whenever retainer postings are processed.
Note that even though the field is called monthly retainer, it can be a weekly, quarterly, or annual retainer. You can run retainer postings at any time. The only catch is the posting is done for all accounts with a positive retainer amount at the same time.
The credit limit field is not used by the software in this version. The initial balance field is used to carry over an account from a different system: a positive balance represents an unpaid balance and a negative number indicates the account is overpaid.
We have formatted the Statement Envelopes report to use Letter size paper. Margins are set such that if a COM 10 envelope is loaded, it should format and print correctly. However not all printers load envelopes the same way. It may be necessary to swap the left and right margins depending on the printer model.
To determine if this step is necessary, print one COM 10 envelope. If the return address does not print correctly but the destination address does, swap these two margins and try again.
The software as shipped considers the calendar and fiscal year to be the same: January 1 to December 31 of the current PostYear column in the Organizations table. Some institutions, for example schools, may wish to use a disjoint fiscal year: for example, a school's fiscal year 2007 may start on August 1, 2007 and end on July 31, 2008.
One report, Account History, keys off this PostDate field. To make an adjustment for a disjoint fiscal year, the Visual Basic functions computeStartDate and computeCutoffDate in the Globals module must be modified to compute the appropriate start and stop dates for this report.
Home | Welcome | Legal Notices | Features and Limitations | Data Model | Installation and Configuration | Operation | Appendix | Screen Shots