|
<< Click to Display Table of Contents >> Navigation: DATABASE MAINTENANCE > Validating Your Transactions |
The ACCOUNTS program is extremely careful to never let you create an unbalanced transaction, which in accounting terms is one where the total debits do not equal the total credits. However, in January 2018, after the program had been in existence for six years, one obscure way was found of doing that (and immediately fixed!). So we felt it was appropriate to add a new menu option to determine whether there are any such unbalanced transactions in your database.
To check for this, just run Database ⇒ Validate Transactions. When it is done, it will almost certainly give you a message saying that there were no unbalanced transactions. However, if it does find any unbalanced transactions, it will tell you so, and also tell you to run the report Reports ⇒ Administrative ⇒ Unbalanced Transactions to allow you to find and fix them.
The easiest way to fix such problems will be to just delete and re-enter the transactions shown in that report.
If any unbalanced transactions were found, and they were transactions you created recently, please try to think about exactly how that transaction was entered and perhaps later edited, and report that in as much detail as possible to our technical support, so that we can try to find and fix any further very unusual bugs that could allow such a thing to happen.
Checking for Transactions on Accounts with Sub-Accounts
Only accounts that have no sub-accounts under them should ever be able to be used in transactions in ACCOUNTS. The types of accounts that can be used in transactions are top-level accounts with no sub-accounts, sub-accounts with no sub-sub-accounts, and any sub-sub-accounts. Please see the section on "Grouping and Hierarchy of Accounts" in the topic Accounting Concepts if you need to understand this further.
For the balance of this Help topic, for simplicity an account with other accounts under it will be called a "parent account" and an account that is under another account will be called a "sub-account". (That will mean we won't be referring to them as top-level accounts, sub-accounts or sub-sub-accounts, which are the three levels available.)
When you create a first a sub-account under an account (making it a parent account) you are prompted to change a number of things, most significantly changing any existing transactions that involve the parent account to instead be for the sub-account. If you do not agree to that change, you are not allowed to add the sub-account.
Prior to release 2.41 of ACCOUNTS in January 2021, the types of objects that this checking and changing was done on were transactions, fund accounts with linked income and expense accounts, budget lines, and government form lines. That prevented almost all problems.
In January 2021, however, we realized that four other types of things in the program were not being checked in that way, and could potentially cause problems, which could end up creating transactions using parent accounts, which is not allowed. Those things are memorized transactions, tax code accounts, default accounts for vendors, and bank rules for matching of imported online banking transactions.
The presence of transactions involving parent accounts in your database can cause reports such as the Income Statement and Balance Sheet to be incorrect. So Database ⇒ Validate Transactions now checks for any of these types of objects using parent accounts, and reports on them if they are found. If any such problems are found, they will be displayed to you in a window, from which you can print the results with the Print button.
The following sections explain how you can find and fix each type of error.
Some of these sections will involve the use of the Database ⇒ SQL Select menu option, using a specific SQL that you can cut and paste out of the sections below to help you find what you need to fix. As well as pasting in the SQL to that window, we recommend using the Report Style option of Grid to make it easier to read the results (so individual lines of results cannot be split across different pages!)
Parent Accounts used in Transactions
To find actual transactions using parent accounts, please run the following in Database ⇒ SQL Select:
select (t.trandate) "Date", (t.description) "Payee/Description",
(select a2.name
from accounts a2
join splits s2 on s2.accountid = a2.accountid
where s2.tranid = t.tranid
and s2.seqno =
(select min(s3.seqno)
from splits s3
where s3.tranid = t.tranid)) "Main Account"
from transactions t
join splits s on t.tranid = s.tranid
join accounts a on s.accountid = a.accountid
where a.height > 0
The results of that will give a transaction Date, Payee or Description, and Main Account that the transaction can be found in the register of.
If the Main Account is not shown on the program's main window, that means that it is a parent account itself. Such transactions cannot be fixed as easily using the standard features in the program. Please contact us for assistance.
Otherwise, if you can open a register window for the Main Account, just find that transaction in the register. If it is a transaction without multiple splits, you will see that the displayed Account is just a smallish number rather than an account name. Correct it to be an account name available on the drop-down list, and save the change.
If it is a transaction with splits, so "--- SPLIT ---" is shown in the Account field, open the splits window for the transaction, and again look for Account values that are just a smallish number rather than an account name. Again, correct each such Account value to be an account name available on the drop-down list, then save the splits, and save the transaction.
Parent Fund Accounts with linked Income and Expense Accounts
This problem is extremely unlikely to occur, because the program has always prevented it. However, if you somehow do get a report of parent fund accounts with linked Income and Expense accounts, they should be very easy to find with the Maintenance ⇒ Funds for Accounts menu option. Use the Fund radio button in the window that comes up to sort by fund, then scroll through the list looking for cases where the displayed Fund is just a smallish number rather than a fund account name. Fix them to be a fund account from the drop-down list, and then save the changes.
Budget Lines
This problem is extremely unlikely to occur, because the program has always prevented it. However, if you somehow do get a report of this, please contact us for assistance in fixing it.
Memorized Transactions
This problem could occur in memorized transactions, including ones set up to be recurring. When those memorized transactions were inserted, it would cause problems in actual transactions. To find such transactions, please run the following in Database ⇒ SQL Select:
select (t.name) "Name"
from memorized_transactions t
join memorized_splits s on t.memid = s.memid
join accounts a on s.accountid = a.accountid
where a.height > 0
That will list the names of each memorized or recurring transaction with this type of problem. You can then go to the Maintenance ⇒ Recurring Transactions menu option, to find the ones with those names.
The easiest way to fix such problem memorized transactions will probably be to delete them with the Delete Transaction button on this window, then recreate them by entering a new transaction as desired on the appropriate account's register window, re-memorize it, and if it was recurring, come back to Maintenance ⇒ Recurring Transactions to set up its recurrence appropriately again.
Tax Code Accounts
The easiest way to fix these will be to go to the Maintenance ⇒ Sales Tax Codes menu option. For each displayed code, click the Accounts button to see the list of associated Accounts and Percents. Look for a displayed Account that is just a smallish number rather than an account name. Fix it to be the correct desired account from the drop-down list, and save the change.
Vendors
Vendors can have associated default Expense Accounts for transactions that you create for that vendor. To find vendors where that Expense Account is a parent account, please run the following in Database ⇒ SQL Select:
select (vendors.name) "Name"
from vendors
join accounts on vendors.accountid = accounts.accountid
where accounts.height > 0
That will list the names of the vendors with this problem. Go to the Maintenance ⇒ Vendor List menu option, For each vendor Name that shows up in the SQL Select results above, find the vendor on the list and click the Edit button. In the Edit Vendor widow that comes up, you will see just a smallish number rather than an account name for the Expense Account. Correct that to be the desired account from the drop-down list, and save the change.
Bank Rules
Bank Rules for online banking transaction importing can have an associated account, which could be a parent account. To fix any problems that come up for bank rules, just go to the Maintenance ⇒ Online Banking Bank Rules menu option. Look for rules where the displayed Account is just a smallish number rather than an account name. Correct that to be the desired account from the drop-down list and Save the change.