Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

Version 1 Next »

Introduction

This documentation outlines a new feature that we created to allow users to import gl statistics for many gl segments and periods all at once. We have two existing forms for importing stat units, but these are designed to require users to import stats for one segment at a time, making the process a bit slow.

With the import, we will be able to aggregate stat values for several gl segments and period at once, and import them into a screen for validation and save. If existing stats already exist, we  can update them from the same import. We will also be able to insert new rows that were not in the file to the screen itself.   

Prerequisites

To perform these imports, we will need:

  • Security permissions to the feature.
  • Affiliation to the companies and divisions we are importing stats for.
  • Opened accounting periods.
  • An Excel file (.xslx) with a header that contains all stat unit import columns.

Stat Feature Setup

Ledger → Maintenance → Segment Descriptions

The stat feature will need to be fully implemented before we can perform imports. One of the most essential setups is that of "Segment Description". Despite already having our gl structure defined, we will need to populate this table with information about the gl segments that we will want to map to.

While not necessary for the import, Stat masters and units (found on the same menu) need to be defined as well, so we can see their descriptions and conversions on reports.


Adding Stat Unit Security

Master → Security → User Profile

To perform stat imports, we'll need security access. To grant this, we'll need to find the category for "Bulk General Ledger Stat Import" on the user profile form. It can be found under "Module Permissions → Ledger → Menu Page: System → Group: Maintenance".

Importing a File

We'll need an excel spreadsheet to perform the import. Any will do, as long as it conforms to the following requirements:

  • The importing sheet must be named "Sheet 1". The file can contain others, but we will import that one.
  • The file must have a header row, with columns for these values, in this order.
    • Company Code
    • Division Code
    • GL Segment Name
    • GL Segment Value
    • Sub Type Code
    • Accounting Period ("FiscalYear-PeriodNumber")
    • Stat Unit 1/2
    • Stat Unit 2/2

The column headers do not need the same names, but it is imperative that they are in the correct order.

When creating the file, we must make sure that string codes like company "01" in our database are not turned into numbers by excel. We can avoid this by typing a ' before the number we are entering. That will enter it as alpha and preserve leading zeroes.


I'll attach a copy of the format here:

Ledger → Maintenance → Stat Import

When we select this feature, we will be prompted to import a file. We'll use a file dialog to find the file. When we are ready to import, we will select "OK".

At this point, it's possible for the import to fail if the spreadsheet does not conform to the requirements above. When that happens, we will receive an error message. Details about these errors can be found in the system's database error log.

One the file is parsed we will be directed to the screen where we will validate and save the import. Nothing has been saved to the database at this point.

If we want to import another file after saving, or import a different one before then, we can click "New" on the ribbon menu to open the file dialog. This will replace the file we have opened with a new one.

Adding and Editing Rows / Validate and Save

Any of the columns in the "Values Imported From The File" band are editable. These are the values that came from the imported sheet. We can enter our own values from the "New Item Row" at the top of the screen.

Whether imported or added, any row can be right-clicked and deleted from the screen. This will cause that row to be removed from the list. It will not be sent to the database when saved. Rows can be multi selected and deleted at once. There is no way to undo a delete once it has been done. If this happens, we will need to import the file again.


Entries are mapped validated after two actions:
  1. When the file is imported,and the records are displayed on screen.
  2. When we attempt a "Save"

When a value fails mapping or validation we'll see error icons next to the value. Some values, like accounting periods, are composites (more than one column makes up a valid entry in the database). In these cases the error Icon will appear next to the first value.

When a value maps and validates, we will see the ids of the database records appear in the "Mapped IDs" column. This is a handy way to identify that the value mapped, and for power users with database access, can proof the mappings before committing.

Only one stat can exist for one segment/period/sub type at a time. If an existing value is found, the "Do Stats Already Exist?" band will be populated. In this band, we can see what value is already entered. This value will be updated to the new values when we save.

Proofing Stat Units

Stat Entry Screens

Ledger → Maintenance → Stats by Segment

Ledger → Maintenance → Stats by Sub Type

We can open the stat entry screens and navigate to the gl segments and fiscal years that we have entered values for. We know the import worked because there are no duplicate periods, and the values match what we had imported.

If we do not enter a sub-type on the import, then the application will enter it into the stats by segment screen, if we do include a sub type, it will enter it into stats by sub type. We must make note of whether we added a sub type to help track down the values we imported.

P&L Front Report

Ledger → Report Wizards → P&L Front

The P&L Front Detail will display account balances along with calculations against stat unit values. Running this report will not show us the stats, but it will show us that they are being applied to the correct segment/period.


P&L Front Expense Detail Report

Ledger → Report Wizards → P&L Front Expense Detail

The P&L Front Detail will break down gl accounts by cost center or department . Running this report will not show us the stats, but it will show us that they are being applied to the correct segment/period.

  • No labels