OCI Exercise 1 – Creating/Using Excel-Based Entry Grids

Note - You can select 720p in the video bar to make the text clearer.

Introduction

During this exercise you will create an entry grid for General Ledger transactions, enter some data, create an import file, and then import it into General Ledger. This process will be divided into two objectives:

  • Creating A Reusable Entry Grid
  • Using The Entry Grid And Importing Data
Note - This tutorial example is designed to work with the Timberline Construction Sample Data.
Step 1 - Create An Entry Grid In Excel

This exercise will demonstrate creating an Excel worksheet that can be used to quickly enter data into the General Ledger.

  1. Create a new Excel workbook.
  2. Click cell A1 of Sheet1.
  3. Click the Add-Ins tab, then click Import Wizard to display the Office Connector Import Wizard.
  4. Select GL Transaction Import File and click Next.
    OCI_Type_Of_Import
  5. Select the column name check boxes listed below, and then click Next:
    • Note - You don't need to select every column; just the columns you will be using.
    • √ Account
    • √ Accounting Date
    • √ Journal
    • √ Reference 1
    • √ Description
    • √ Debit
    • √ Credit
  6. Under Values, click Create An Entry Grid For The Selected Columns and click Finish.
  7. Select Columns F and G and click the Home tab. Under Number, click the comma button to automatically format the values with two decimals and commas.
    • Note - Office Connector Import will automatically reformat all necessary cell data to match any requirements set by Sage 300 CRE. This allows you to format your data for easy viewing without disrupting the import process!

At this point, you have an entry grid that allows you to record General Ledger transactions, create an import file, and upload to Sage 300 CRE!

OCI_Entry_Grid

The above grid was created as an Excel Table which gives it some special abilities. Specifically, as you enter data on a given row and then press Tab on the last column, it will automatically insert and append a new row to the table.This allows the  table to grow automatically based on the number of transactions entered. In addition, if a formula was used in any of the columns, the formula can automatically copy down to all rows (including new rows that are added).

Notice that column H contains a “TSImport Function”. This is an instruction for Office Connector Import so that it knows which columns in the worksheet map to which columns in the import file. It’s not necessary to have this column displayed, so feel free to hide it. Just don’t delete it!

Step 2 - Using The Entry Grid And Importing Data
  1. In this example, we are using the Construction Sample Data. By doing so, we ensure the suggested values (listed below), are valid and can be entered in to the grid. Depending on the version you have installed, you may need to utilize a different Accounting Date.
  2. Click Save Import File on the Office Connector Import toolbar.
  3. The default save location is sufficient for this example.  If it is not already, the first action to be performed after the file has been created should be selected.
    Note - You can specify the location and name of the import file, and the next action to be taken after the file is created.
    OCI_Save_File_Dialog_GL
  4. Click OK to create the file and then launch Import Transactions from General Ledger (it may take a few seconds for this window to come up).
  5. Once Import Transactions – Print Selection appears, click the […] ellipses button for Import File, select the GLTransaction.TXT file, and click Open.
  6. Optionally – Click Printer Setup and change where the report will be sent.
  7. Click Start to import the data.
  8. Review the General Ledger Import Transactions report when the import has finished.

If you need help with this exercise, or have any questions, you may contact us atsupport.sage300cre-oc@event1software.com