RATING: Intermediate
PLATFORM: Macintosh & Windows
VERSION: FileMaker 5.5 or higher
TECHNIQUE FILES: Abacus_API.fp5, Invoices.fp5, LineItems.fp5, Menu.fp5
DOWNLOAD: < http://collection.filemaker.com/collection_db/ >
(Editor's Note: This article showcases how to integrate two separate framework solutions -- The FileMaker Collection and Abacus accounting engine -- into one workable database system. The author is also the developer of Abacus and is providing his solution as an example of integration.)
Accounting functionality in a FileMaker solution has long been a holy grail for developers. The problem has been the complexity of the task and the degree of specialized knowledge required to implement a general ledger in FileMaker. The new Abacus accounting engine from DataIsland Software is about to change all that.
< http://www.dataisland.com/abacus.html >
Abacus will allow any FileMaker developer to easily add full general ledger reporting to any new or existing solution. Only the most basic accounting knowledge is required and it only takes a few hours.
In this months article, I'll show you how to add Abacus to the FileMaker Solution Framework. The Framework is written by ISO Productions's Matt Petrowsky and is available from the FileMaker web site:
< http://collection.filemaker.com/collection_db/ >
It is a suite of files that can be used as the basis for a complete business solution.
Accounting 101
Basic accounting is really not very complicated; it's just simple algebra.
Assets = Liabilities + Equity
Simply stated, the company is worth what it owes plus what shareholders have invested.
To keep the fundamental accounting equation in balance whatever you do to one side you must do to the other. This is the double entry bookkeeping system.
I think what confuses most people are the terms "Debit" and "Credit". This is because they are used both as nouns and as verbs.
Companies keep track of their income and expenses in a series of accounts (fields). Each account is designated as a Debit account or a Credit account depending on which side of the equation it resides on. To alter the amount in the account it is debited or credited. If the account is on the asset side of the equation it is a debit account and is increase by a debit and decreased by a credit. The reverse is true for accounts on the other side of the equation - they are decreased by a debit and increased by a credit. If you are more comfortable with algebra we can think of accounts in the equation like:
A1+A2+A3 = L1+L2+L3 + E1+E2+E3
Solutions you write use forms (layouts) to collect information from the user. Each form, an invoice for example, has a specific purpose. An invoice is a promise to pay by the customer. The money has not actually been received but you have a reasonable expectation that it will be.
The accounting convention is that the amount you expect to be paid is entered into an account called "Account Receivable". This is an asset account therefore its normal state is debit. We want to increase the amount in the account so we debit Accounts Receivable by the full amount of the invoice.
The equation must be kept in balance so accounts on the other side of the equals sign must be credited. The invoice is composed of various line item amounts, shipping charges and taxes. It is OK to report these amounts separately as long as the total is the same as the amount posted to Accounts Receivable.
Integrating Abacus with Framework
Abacus has no permanent interface of its own and all interaction is performed through the API. The procedure is the same for each type of transaction. Copy the contents of your record values to matching global fields in the API. Call the appropriate scripts in the API, check for errors and flag your record as successfully posted.
Authors Note: Due to the size of the Abacus files (2,2Mb) they are not included in your ISO download. You can download Abacus separately from < http://www.dataisland.com > You should also download separately the FileMaker Solution Framework. The files in your "Technique Files" folder are the ones we describe how to change in the following article.
Begin by dragging the contents of the "FileMaker Solution Framework" folder into the "Abacus" folder. This will give you 14 items in the folder. Then you can either follow along and make the changes outlined below or drag the changed files from your "Technique Files" folder and replace the originals (Invoices.fp5, LineItems.fp5, Menu.fp5).
The Framework file "MENU.FP5" contains a script called "[INT] = Menu File" (26th line from the top) which is called by remote files to invoke the menu. The first script in the file "Abacus_API.FP5" is called "Go To Menu". Point the Perform Script command in "Go To Menu" to "[INT] = Menu File".
The Abacus interface is totally customizable but for this example we'll use the layouts included with the program. The layouts in "Abacus_SetUp.FP5" must be linked into the Framework menu system. Each button on the Framework menu is a record within the "Menu.FP5" file. Use the "File options" button (bottom of the layout) to add a record for each layout we want to access. All the layouts we want to go to are in the same "Abacus_SetUp.FP5" file so the wizard will complain about using the same file name more than once but you can ignore it. We want five buttons named "Chart of Accounts", "Departments", "Year End", "Reports" and "Registration".
Clicking one of the menu buttons moves the current record pointer and triggers the "[BTN] = Main Menu Button" script. Add a new script called "[OPEN] = SETUP" and include this script in the list of scripts contained in "[BTN] = Main Menu Button".
Script:[OPEN] = SETUP
If [ Database = "Abacus_SetUp.FP5" ]
If [ Name = "Chart of Accounts" ]
Perform Script [ Filename: "Abacus_SetUp.FP5", "Go To Account" ]
[ Sub-scripts ]
End If
If [ Name = "Departments" ]
Perform Script [ Filename: "Abacus_SetUp.FP5", "Go To Department" ]
[ Sub-scripts ]
End If
If [ Name = "Reports" ]
Perform Script [ Filename: "Abacus_SetUp.FP5", "Go To Reports" ]
[ Sub-scripts ]
End If
If [ Name = "Year End" ]
Perform Script [ Filename: "Abacus_SetUp.FP5", "Go To Year End" ]
[ Sub-scripts ]
End If
If [ Name = "Registration" ]
Perform Script [ Filename: "Abacus_SetUp.FP5", "Go To Registration" ]
[ Sub-scripts ]
End If
Set Field [ _Error, Status(CurrentError) ]
End If
Abacus uses a different navigation system to Framework so in order to remain at the layouts you select, each of the Abacus_SetUp.fp5 scripts you added above have to have a "Halt Script" added at the end.
Looking at Abacus
With the navigation system working the next step is to add Abacus to the Framework Invoice table.
Add the following six fields to Invoices.FP5:
AccountOne Text
AccountTwo Text
AccountThree Text
Form_Posted Number Auto Enter 0
KEY_Department Number Auto Enter 0
KEY_TransactionID Calc
"INVO" &
Right("00" & Seconds(CreationTime), 2) &
Right("00" & Minute(CreationTime), 2) &
Right("00" & Hour(CreationTime), 2) &
Right("00" & Day(CreationDate), 2) &
Right("00" & Month(CreationDate), 2) &
Right("00" & Year(CreationDate), 2) &
Right("0000" & InvoiceNumber, 4)
You may wonder why we need such a complicated calculation for the transaction ID when the invoice file already includes a serialized Invoice Number field. The reason is to guarantee uniqueness. Many journal files such as invoice, purchase order, checks etc may post transaction to Abacus but the Invoice Number field is just a simple serial number that may be unique within the Invoice.FP5 fie but not necessarily within the solution as a whole. Admittedly the calculation need not be as complicated as the one above but this is something I have developed and used successfully for many years. Just be sure to change the first four characters to reflect the name of the file it is used in.
Add the following two fields to LineItems.FP5
AccountNumber Text
KEY_TransactionID Text
Set up a relationship called "Abacus_API_Constant" from Invoices.FP5 to Abacus_API.FP5 using the fields "_Constant" in Invoices.FP5 to "Constant" in Abacus_API.FP5.
Do the same thing in LineItems.FP5, set up a relationship called "Abacus_API_Constant" from LineItems.FP5 to Abacus_API.FP5 using the fields "_Constant" in LineItems.FP5 to "Constant" in Abacus_API.FP5.
Believe it or not you are almost done. Just a couple of value lists and a few scripts to trigger the posting routines and you will have added accounting functionality to the Framework solution.
Abacus provides many possible value lists that contain accounts of different types. For an invoice the possible choices are Accounts Payable, Revenue and Tax. For your solution you may decide to hard code account numbers or provide a different method of account number selection. For example your people database could contain the names of sales people linked to a particular sales territory and the revenue account number could be automatically entered via a lookup based on the salespersons name.
In this example we will attach a drop down list of possible accounts to each of the account number fields.
Create four value lists in the "Invoice.FP5" file named "Accounts Receivable", "Revenue Accounts", "Departments" and "Tax Collected Accounts". For each list click "Use value list from another file" and specify the value list of the same name in "Abacus_API.FP5"
Add four of the new fields you created to the bottom of the invoice and attach the value lists in the following order:
AccountOne - Accounts Receivable
AccountTwo - Revenue Accounts
KEY_Department - Departments
AccountThree - Tax Collected Accounts
Of course if no accounts are defined in the Chart of Accounts for the particular type don't expect to see them in the value list.
The last step is to add the scripts that post the amounts to the appropriate fields in the Abacus general ledger.
There are four scripts in the Invoice.FP5 file and two in LineItems.FP5. In each case we could combine the four or two scripts into one but we break them apart so that the smaller scripts can be used with other routines.
After the user has selected the account numbers the [Accept] button triggers the Accept[Invoice] script.
Accept {Invoice}
Allow User Abort [ Off ]
Set Error Capture [ On ]
Enter Browse Mode
If [ Form_Posted ]
Show Message [ Buttons: "OK", "", ""; Data: "This invoice has already been posted." ]
Else
Set Field [ InvoiceDate, Status(CurrentDate) ]
# "Check that the invoice has line items amounts"
If [ _Total > 0 and IsValid(LINEITEM_Invoice Number::InvoiceNumber) ]
Perform Script [ "Accept {Common}" ]
[ Sub-scripts ]
# "The Credit/Debit routines use the field "Post_Account""
Set Field [ Abacus_API_Constant::Post_Account, AccountOne ]
Perform Script [ "IsAccountValid{Common}" ]
[ Sub-scripts ]
Set Field [ Abacus_API_Constant::Post_Amount, _Total ]
# "Debit Accounts Receivable"
Perform Script [ Filename: "Abacus_API.fp5", "DEBIT" ]
[ Sub-scripts ]
# "CREDIT each invoice line items"
Go to Related Record [ LINEITEM_Invoice Number ]
[ Show only related records ]
Perform Script [ Filename: "LineItems.FP5", "Post CREDIT to each line item" ]
[ Sub-scripts ]
# "Check for an entry in the tax account field"
If [ not IsEmpty(AccountThree) and not IsEmpty(LINEITEM_Invoice Number::_TaxSummary) ]
# "Post any tax"
Set Field [ Abacus_API_Constant::Post_Account, AccountThree ]
Perform Script [ "IsAccountValid{Common}" ]
[ Sub-scripts ]
Set Field [ Abacus_API_Constant::Post_Amount, LINEITEM_Invoice Number::_TaxSummary ]
# "Credit the tax account"
Perform Script [ Filename: "Abacus_API.fp5", "CREDIT" ]
[ Sub-scripts ]
End If
If [ not IsEmpty(LINEITEM_Invoice Number::_ShipSummary) ]
# "Post any handling fees"
Set Field [ Abacus_API_Constant::Post_Account, "9000" ]
Perform Script [ "IsAccountValid{Common}" ]
[ Sub-scripts ]
Set Field [ Abacus_API_Constant::Post_Amount, LINEITEM_Invoice Number::_ShipSummary ]
# "Credit the handling fee account"
Perform Script [ Filename: "Abacus_API.fp5", "CREDIT" ]
[ Sub-scripts ]
End If
Perform Script [ "Verify&SetPosted" ]
[ Sub-scripts ]
There is a lot of input checking to make sure you only post valid data but the meat of the script simply sets some global fields in the API and calls some API scripts which handle the posting to the general ledger.
Also notice in the 8th line from the bottom "Set Field [ Abacus_API_Constant::Post_Account, "9000" ]" instead of getting an account from the user we are hard coding account 9000 as the account to use for tracking the handling fee. Go to the "Abacus-Chart of Accounts" layout and create account 9000. Name it "Handling fees", make the account type "Expense", the sub type "Other" and the status "Visible"
Three other scripts must be added to "Invoice.FP5".
Script:Accept {Common}
# "Set the global fields in the API file that are the same for each posting."
Set Field [ Abacus_API_Constant::Post_KEY_TransactionID, KEY_TransactionID ]
Set Field [ Abacus_API_Constant::Post_TransactionDate, InvoiceDate ]
Set Field [ Abacus_API_Constant::Post_KEY_Department, KEY_Department ]
Set Field [ Abacus_API_Constant::Post_Reference, InvoiceNumber ]
Script:IsAccountValid{Common}
# "Check the valid account flag in the API"
# "Called for each debit and credit"
If [ not Abacus_API_Constant::ValidAccount ]
Show Message [ Buttons: "OK", "", ""; Data: "Please enter valid account numbers." ]
If [ Abacus_API_Constant::TransactionRecordsExist ]
# "If valid records have been created they must be removed to maintain balance in the GL."
Perform Script [ Filename: "Abacus_API.FP5", "PurgeOrphanRecord" ]
[ Sub-scripts ]
End If
Halt Script
End If
Script:Verify&SetPosted
# " You MUST call "VERIFY" after all postings for the current transaction. UnVerified postings are not included in GL totals."
Perform Script [ Filename: "Abacus_API.FP5", "VERIFY" ]
[ Sub-scripts ]
# "Check the "Verify Successful" flag in API - if true then set the posted flag for the current form."
If [ Abacus_API_Constant::Flag_VerifySuccessful ]
Set Field [ Form_Posted, 1 ]
End If
We also need to add three small scripts in the line items file.
Script: Post CREDIT to each line item
# "Called by "Accept{Invoice}" after isolating line item records"
# "At this point the Account Number field and TransactionID for each line item is blank. Normally the TransactionID is filled in when the record is created and the Account number would come from the products or people database depending on how you are tracking revenue."
Perform Script [ "CopyAccountNumber" ]
[ Sub-scripts ]
# "CREDIT each line item on the invoice or PO to designated sales account"
Go to Record/Request/Page
[ First ]
Loop
Set Field [ Abacus_API_Constant::Post_KEY_TransactionID, KEY_TransactionID ]
Set Field [ Abacus_API_Constant::Post_Account, AccountNumber ]
Perform Script [ "IsAccountValid{Common}" ]
[ Sub-scripts ]
Set Field [ Abacus_API_Constant::Post_Amount, _LineAmount ]
Perform Script [ Filename: "Abacus_API.fp5", "CREDIT" ]
[ Sub-scripts ]
Go to Record/Request/Page
[ Next, Exit after last ]
End Loop
Script: IsAccountValid{Common}
# "Check the valid account flag in the API"
# "Called for each debit and credit"
If [ not Abacus_API_Constant::ValidAccount ]
Show Message [ Buttons: "OK", "", ""; Data: "Please enter valid account numbers." ]
Halt Script
End If
Script: CopyAccountNumber
# "Abacus is set up to process each line item with a separate account number. In this demo each line is sent to the same
account."
Go to Record/Request/Page
[ First ]
Loop
Set Field [ AccountNumber, INVOICE_Invoice Number::AccountTwo]
Set Field [ KEY_TransactionID, INVOICE_Invoice Number::KEY_TransactionID ]
Go to Record/Request/Page
[ Next, Exit after last ]
End Loop
What's Next
There are a couple of things you would need to do in order to make this into a working solution. First, although the [Accept] script checks the "Form Posted" flag and does not allow the invoice to be posted a second time there is nothing to prevent a user from changing invoice details after it has been posted. This must not be permitted and your design should incorporate a method to prevent any changes once the invoice has been posted.
You can do this by editing the user password privileges. In the "Access Privileges >Passwords" dialog. Click the user password and change the "All" menu popup to "Limited" for both "Edit records" and "Delete records". In the calculation dialog you get with each change enter "not Form_Posted". This will prevent anyone using the default user password from making any changes once the Form_Posted flag is set. Don't forget to lock the line items file as well!
Since the Framework only includes the Invoice form, a working solution would need integrated Purchase Orders, Credit Memo's, Cash and Inventory Receipts plus a General Journal form to make adjusting entries to the General Ledger.
Conclusion
If you would like to use Abacus and The FileMaker Collection without adding the above scripts: once you have downloaded each of the above solutions, place all files within a new folder. Place the four files that we have provided (Abacus_API.fp5, Invoices.fp5, LineItems.fp5, and Menu.fp5) in the same folder. Choose to replace the existing files.
While this solution doesn't require you to have an accounting background, it is helpful to fully familarize yourself with all necessary terminology and standards. Abacus is a great example of how to fully integrate a separate database engine into an existing solution and, working with the FileMaker Collection, can help you deliver a customized accounting package to your clients.
Happy FileMaking!
Geoff Wells is the author of ABACUS™ the FileMaker accounting engine available from < http://www..dataisland.com >. Geoff is a Senior Editor at ISO FileMaker Magazine. Email him at webmaster@dataisland.com.