How do I use ODBC to fill in the Activity Balances and Debit Activity fields on General Ledger Accounts?
Description

Note: The new General Ledger fields added with the 9.6 release still does not allow the rollback of Financial Statements to a date prior to the first period of the last fiscal year. Also, the Year-to Date Ledger (YTD Ledger) function has not changed with the 9.6 added fields.

Cause
Resolution
[BCB:5:Third-party support:ECB]

This solution requires that you have the Timberline ODBC driver installed and that a DSN be defined for the data folder that contains the Sage Timberline Office files that you will use.

Prior to version 9.6, fiscal year-end balances for the past five years were saved and available for reporting. After you upgrade to version 9.6, year-end balances and activity for years six through ten are available. These fields are initially empty when you upgrade and begin to populate as you advance your periods and close your year.

Use the steps below to fill in the activity balances and debit activity balances on general ledger accounts.

Caution: These steps do not update the year-end balances, use Audit Setup Activity to update the year-end balances.

For information on how to set up your Timberline Data Source ODBC DSN, see the link below.

  1. In Access, from the File menu, select New.
  2. Click Blank Database, enter a name for the database, and then click Create.
  3. With Tables highlighted under the Objects section to the left, click New at the top.
  4. Highlight Link Table and click OK.
  5. In the Link box, at the bottom change the Files of Type to ODBC Databases ().
  6. A Select Databases box will open, choose the Timberline Data Source dsn.
  7.  Log in using your Sage Timberline Office User Name and Password.
  8. In the Link Tables box, scroll to find the GLM_Master_Account_Activity_History table, highlight it and click Ok.
  9. In the Database box, double-click the GLM_Master_Account_Activity_History table.
  10. The table is set up with the following column headings:
    Account
    Years_Ago
    X_Years_Ago_Activity
    Year_X_Period_1_Activity (Up to 12 Periods)
    Debit_X_Years_Ago
    Debit_X_Yr_1_Per_Ago (Up to 12 Periods)
    The X represents the number that is in the Years_Ago field. (As an example, if your current period is 12/31/2012, and you want to find balances for 12/31/2009 for Financials, if your current year end date is 7/31/2013, the field you would select is Year 3 period 5).
  11. There are three options to fill in your account history.
  • From the table, you can enter in the details for each of your accounts history.
  • If you have your account totals in an Access database, you can do an Append Query to update the fields in the GLM_Master_Account_Activity_History record.
  • If you have your account totals in an Excel spreadsheet and they are in the correct order (the same order as the fields show in the table), then you can paste the data directly into the table in Access.

 

DocLink: How do I set up an ODBC User DSN for Accounting and Management data?
Defect ID
Steps to duplicate
Related Articles