Electronic Transmissions - Journal Entries

This screen allows you to configure a template that can be used to import journal entries into Compeat from text files created by another accounting system or source.

Once a template has been created, journal entries can be imported from the Import Journal Entries (Templates) screen.

General

• Name This is the name of the template.  It is recommended you name the template for the source or system you will be using the template for.

• Revision You can assign a revision number to the template by entering the revision number here.  This allows you to create multiple templates in the system for the same source or system, and keep track of them.

File Settings

In this section, you define the information about the journal entry files you plan to import.

• File Type This sets the basic file format type.  There are 3 possible selections:

• Delimited Fields of this type are separated by a character, such as a comma, tab, or space. Files of this type will often have extensions such as .txt, or .csv.  Here's what the data in a delimited file may look like:

• Fixed Width In this type, fields are defined by a specific length of characters. Files of this type may also have extensions of .txt.  Here's what the data in a fixed width file may look like:

• Excel Select for Excel spreadsheets.

• Header rows to skip Some data files will have one or two header rows with column names. These should not be included in the import. Specify the number of header rows in your data file, and Compeat will remove this number of rows from the imported data.

• Minimum Row Length Enter the minimum number of characters that Compeat should consider as row with valid data in the file.  Rows in the file comprised of less characters than the minimum amount will be ignored during the import process.  This is mainly used if the file contains section headings, or other rows that do not contain JE data, that are shorter in length than valid rows.

• Text Qualifier For delimited data, specify the text qualifier used. Delimited data use a text qualifier to specify a string of words as one field, even if it contains a delimiter. Typical text qualifiers are single- or double-quotes. You can usually determine what text qualifiers are used by opening your data file in Notepad and examining the data.

• Field Delimiter For delimited file types, specify the delimiter. Delimiters separate different fields in a record (row). You can determine the delimiter type by opening the file in Notepad, and look for column separators.

• Row Delimiter For fixed width and delimited file types, select a row delimiter. Row delimiters separate one record from another.

• Record Format Type This indicates how the invoice data itself is formatted within the invoice file.  There are 3 possible selections:

• Single Row Per Record Select this if all of the data for each single record in the file is contained in one row of data.  In this format, for example, a single row in the file would contain the apply date,GL account number, amount, and description.  Here's what the data may look like in a file with this data type:

• Fixed Number Of Rows  Select this if a single record in the file is comprised of multiple rows.  In this format, for example, one row of the record may contain the GL account number and apply date.  The next row in the file may contain the description, and the row after that may contain the credits and debits. Here's what the data may look like in a file with this record type, where each record is composed of 3 rows:

• Rows Contain A Record Indicator Select this if the file contains a column containing a row indicator.  A row indicator is used to denote the type of data contained in the row.  For example, one row may contain a record indicator of ‘HDR’, denoting that the row contains JE header information such as the apply date, and GL account number.  Another row might have an indicator of ‘DTL’, indicating that the row contains line-item detail information such as the description.  Another may have an indicator of ‘AMT’, indicating the row contains debits and credits.  What the indicators are and what they represent may vary from source to source.  When this format type is selected, a box will appear labeled ‘Indicator Position’.  Enter the column number in the file that contains the row indicator.  For delimited and fixed-width files, the columns are numbered left-to-right beginning with zero.  For Excel files, the column names are used (A, B, C, etc.).  Here's what the data may look like in a file that uses row indicators:

• New Record On This tells Compeat when to begin a new journal entry, based on the changing of information in the records.  There are 3 options:

• Always Compeat will create a new journal entry for each record in the file.

• Apply Date Compeat will create a new journal entry each time it detects a change in the apply date in the records in the file.

• Description Compeat will create a new journal entry each time it detects a change in the description in the records in the file.

• Record Indicator Compeat will create a new journal entry each time it detects the record indicator you specify in the ‘New Rec. Ind.’ Box.  For example, if you want Compeat to create a new JE each time a new header row is encountered, and in the file the record indicator for header row is ‘HDR’, enter ‘HDR’ in the box.  The Record Indicator options only appear if ‘Rows contain a record indicator’ is selected as the record type.

Field Definitions

The Fields section contains a data grid that is used to map fields from the order guide to fields within Compeat. Click the plus symbol in the grid toolbar to open a list of hard-coded Compeat fields.

Select the fields that you wish to map to and then click OK.

Note: The list of available fields may not exactly match those in a data file. For example, a column named "Item ID" in the data field may need to be mapped to the "Item Code" in Compeat.

Description grid options:

• Field This field is non-editable and can be selected from a list of hard-coded options by clicking the plus symbol in the grid toolbar. This forms the list of Compeat fields to be mapped to the order guide fields.  In order to successfully import the invoice file into Compeat, the following fields must be defined: Apply Date, GL Account # or External Account #, Amount or Credit and Debit, and Description.  Some fields expect specific values:

• Debit Indicator This field expects a true or false value, with the true value indicating the amount in the record is a debit, and a false value indicating a credit.  In order for a true value to be detected, the field must contain 'd' or 'debit'.  For a false value to be detected, it must contain 'c' or 'credit'.  If the data in the invoice file does not use one of these indicators, you can use the 'REPLACE' formatting rule to replace the values in the file with one of the accepted ones.

• Credit Indicator This field expects a true or false value, with the true value indicating the amount in the record is a credit, and a false value indicating a debit.  In order for a true value to be detected, the field must contain 'c' or 'credit'.  For a false value to be detected, it must contain 'd' or 'debit'.  If the data in the invoice file does not use one of these indicators, you can use the 'REPLACE' formatting rule to replace the values in the file with one of the accepted ones.

• Location Use this field to indicate which column in the order guide should be mapped to this Compeat field.  Delimited and fixed-width columns are numbered left-to-right, beginning with 0.  For example if you wish to map the Compeat field to the third column in the invoice file, enter "2" in this field.  For Excel files, the column names are used (A, B, C, etc.).

• Length Specify the length of the field for fixed-width data types. This field is not relevant to other file types, and will only appear if ‘Fixed Width’ is selected as the file type.

• Formatting Rules Shows any formatting rules applied to the field. Double-click to create or edit rules.

• Record Ind. This column is visible when ‘Rows Contain A Record Indicator’ is selected as the record format type.  Enter the record indicator here to specify what type of data the column contains in the indicated row.

• Row # This column is visible when  ‘Fixed Number Of Rows’ is selected as the record format type.  Enter the row number within the record here.

Formatting Rules

You can apply functions to transform the data as it is imported. To create rules for a field, select the field and click on Edit Formatting Rules. These rules include functions for concatenating, removing spaces, converting case, basic math functions, and more. A brief description each function will display at the bottom of the dialog when you click on the function in the "Available Rules" list.

Import File Specifications

The Import Template and Export Template buttons can be used to export the current configuration to an xml file or import an existing configuration from an xml file.