Spire Administration - General Ledger
      Back to home
      1. User Manual
      2. Spire Systems - General Knowledge Base
      3. Spire Administration - General Ledger

      Trial Balance report is not balanced

      If the Trial Balance report has numerical information that is different from what's expected, there's a few solutions you can try before contacting support.

      Tips for any database-level changes:

      1. Make a backup (snapshot) of the live database and restore it to a new company/database entry for testing.
        • You can test out any SQL/database changes on a test environment before making changes to the live environment.
        • At a minimum, use the Spire desktop app to test out the problem(s) that should be fixed, as well as common workflows related to any updated tables.

      2. After making changes at the database level, be sure to restart the PostgreSQL and Spire API services (in the Components Services app) in order to refresh any stale data.
        • If your test environment uses the same PostgreSQL and/or Spire server as the live environment, make sure end-users are logged-out of the live system and are aware of the restart.
        • It's recommended to use a separate PostgreSQL and Spire server for the test environment if possible (even if you don't have another PC, perhaps a Hyper-V virtual machine can be used).

      3. When it comes time to update the live database, be sure the end users are aware of this.  Schedule a time when all end users can be logged-out for some relevant duration while you apply the fix.

      Running SQL scripts (on your company database)

      1. On the server PC, run the pgAdmin app.
      2. Double-click/open the server entry, open the list of databases, and select the correct database.
      3. Click the "SQL" button up top to load the query tool (make sure after it loads that the correct database is specified):

      4. Paste/type the SQL script into the "SQL Editor" text area.
      5. Run the script by pressing the F5 key or clicking the "Play" () button.
        • Note that if you only wanted to run a portion of the script, you can highlight the portion desired to be run before running it.  Or, simply comment-out the unwanted lines/parts by prefixing them with two dashes (for example, the line "-- update table..." will not be run and will appear in a different colour).

      Step-by-step guide

      Solution 1 - Run the Re-balancing scripts

      1. See the "Running SQL scripts" section for instructions on applying scripts to the database.
      2. Apply the following SQL script to the company database:

        UPDATE gl_accounts
        SET last_year = '{0.00,0,0,0,0,0,0,0.00,0,0,0,0,0}',
        this_year = '{0.00,0,0,0,0,0,0,0.00,0,0,0,0,0}',
        next_year = '{0.00,0,0,0,0,0,0,0.00,0,0,0,0,0}',
        foreign_last_year = '{0.00,0,0,0,0,0,0,0.00,0,0,0,0,0}',
        foreign_this_year = '{0.00,0,0,0,0,0,0,0.00,0,0,0,0,0}',
        foreign_next_year = '{0.00,0,0,0,0,0,0,0.00,0,0,0,0,0}';

        UPDATE gl_accounts AS a SET
        last_year = r.last_year,
        this_year = r.this_year,
        next_year = r.next_year,
        foreign_last_year = r.frn_last_year,
        foreign_this_year = r.frn_this_year,
        foreign_next_year = r.frn_next_year
        FROM gl_reallocate_balances() AS r
        WHERE a.division = r.division
        AND a.account_no = r.account_no
        AND a.currency = r.currency;

      Solution 2 - Update any incorrect Opening Balances

      1. Run the Spire desktop app and go to the General Ledger.
      2. From the Accounts tab, run/print the "GL Open Balance check" report with the printer icon.  Note that if the numbers are hard to read, preview the report and export it to an MS Excel spreadsheet.
      3. If the base-currency total isn't zero, you'll have to make some changes at the database level.  Most likely, some accounts weren't balanced before closing-off the last fiscal year.
      4. Note that you should contact the company accountant/controller/bookkeeper regarding proper account balances.
      5. Once you know the correct balances, refer to section "Running SQL scripts" regarding running scripts on the database.
      6. You need to change incorrect gl_accounts.opening_balance values to be what they should have been before closing the previous year.  
        • The same goes for gl_accounts.foreign_opening_balance values if you are changing a non-base-currency account.
        • Note that base-currency accounts are denoted by an empty string in the currency column of the gl_accounts table.
        • Example 1 - update simple base-currency account
          update gl_accounts set opening_balance = 5544.33 where account_no = '12321' and currency = '';
        • Example 2 - update non-base-currency account:

          update gl_accounts set opening_balance = 5544.33, foreign_opening_balance = 4654.20 where account_no = '12321' and currency = 'USD';

        • Example 3 - update base-currency account in multi-division company:

          update gl_accounts set opening_balance = 5544.33 where account_no = '12321' and currency = '' and division = '001';
      2. Follow the "Solution 1" section to re-balance any imbalanced accounts.

      Solution 3 - Balance any imbalanced transactions 

      1. Run the Spire desktop app and go to the General Ledger → Transactions tab.
      2. Click the printer icon, check the "GL Journal Transaction" report and click "Preview".  This will run a report for every transaction in the database.
      3. Click the "Find" button at the top and type in "Balance problem" without the quotes, then click "Find Next".
      4. If the text is found then it will be directly below a transaction where the debits and credits do not balance.
      5. Decide what needs to be fixed in the transaction (Spire has no way of knowing) from the Controller, Accountant, Bookkeeper, Owner or source documents.
      6. Print the page and add your notes about what needs to be fixed.
      7. Click "Find Next" and repeat steps 4-7 until no more instances are found.

      8. See the section "Running SQL scripts" for information regarding updating data at the database level.
      9. Correct the value(s) in the database (with update statements) on the applicable row(s) in gl_transactions table.
        • Note that the currency column will have an empty string for base-currency transactions.
        • Base-currency transactions only care about the debit_amt/credit_amt columns.
        • Non-base-currency transactions care about the debit_amt/credit_amt and frn_debit_amt/frn_credit_amt columns.
        • When updating records, try to use the "id" column's value in the "where" clause to ensure you are only updating the one exact row that you intend to update.
        • If you are having trouble with the SQL details here, please contact your partner (for Spire direct-support subscribers) or Spire support if you are a partner/developer.
      1. Follow the "Solution 1" section to re-balance any imbalanced accounts.