Skip to main content
Skip table of contents

Check Reconciliation File Format Table

The Check Reconciliation File Format table lets you define the way the system reads the files your bank sends to you for check reconciliation. The application accommodates two types of check reconciliation, one for payroll checks and one for accounts payable checks. The check reconciliation options use format records to identify key information in the data file your bank sends you.

Since there are two check reconciliation options, you need to define at least two records in the format table, one for payroll checks and one for accounts payable checks. This is the case even if you use the same bank account for both payroll and accounts payable.

The default file name when printing the Check Reconciliation File Format table to disk is recon_setup.rpt.

Menu Path:  Human Resources > Reference Tables > Payroll > Check Reconciliation File Format

Using Check Reconciliation File Format Records

You use Check Reconciliation File Format records to define key characteristics of the data files received from the bank handling your accounts payable and payroll checking accounts. These records tell your system where and in what form check reconciliation data appears in the bank file.

A bank data file is composed of a series of character strings, each string holding up to 80 characters describing an individual check you issued. For the system to read the bank file and reconcile cleared checks in your database, you use the Check Reconciliation option for payroll checks and Fund Accounting's Reconcile Checks from File option for accounts payable checks.

Each check reconciliation option prompts you to select the correct format record to use for reading the bank file data. The Check Reconciliation File Format record's A/P or Payroll field determines if a selected record is compatible with the check reconciliation option you run.

The system reconciles checks based on the reconciliation option used. If the bank data file contains both payroll and accounts payable checks, you need to run both options to process your cleared checks.

The check reconciliation options are described in Additional Payroll Procedures and the Fund Accounting Vendor Checks section.

Sample Bank File Character String

When you define a Check Reconciliation File Format record, you enter parameters your system will use to locate the following information in each of the bank file's character strings: Check Number, Check Amount, Issue Date, Clear Date, and Account Number. Remember, the bank file contains a series of character strings, each string signifying a processed check.

Before you define your file format records, you need to understand two types of information:

  • The contents of a format record

  • The way this record's fields relate to the data file your bank sends you for check reconciliation.

To better understand the formatting requirements, try reading through the Fields that follow while looking at the data file your bank sends you.

The Check Reconciliation File Formats page's fields are divided into four logical groups: General, Check, Date, and Account Information sections. The General Information fields pertain to the file format record as a whole. The Check, Date, and Account Information sections define how the system identifies the respective data in the actual bank file.

These field descriptions frequently reference the position of information in individual bank file character strings. Assume the system reads from left to right when determining the starting position of information in the bank file.

General Information Section

Field

Description

Bank Format

One-character code identifying the file format record. This code must be unique for each record. The drop-down list includes the letters A through Z and the digits 1 through 9.

A/P or Payroll

Determines whether the record is for Accounts Payable or Payroll check. When you run a reconciliation, the system checks this field to ensure you picked a file format with the option you are using.

Select:

Accounts Payable
Payroll
At minimum, you need to create two records, one defined with the Accounts Payable setting, the other defined with the Payroll setting. This is the case even if you use one checking account for both purposes.

Eliminate Zero Filling

Selection defining how the system should treat leading zeroes when comparing your data with information in the bank file. A leading zero is any zero in a field that comes before any other non-zero character. For example, if a field has a value of 00104A, it contains two leading zeroes.

Select:

Account Number - Ignores leading zeroes in the bank file for the Account Number data but to include leading zeroes for Check Numbers. Use this selection if you do not use leading zeroes for the Account Number in your database, but you do use leading zeroes for Check Numbers. The Account Number corresponds to the value you enter in this table’s Account Number field.
Check # - Ignores leading zeroes in the bank file for the Check Number data but to include leading zeroes for the Account Number. Use this selection if you do not use leading zeroes for Check Numbers in your database, but you do use leading zeroes for Account Number.
Check # and Account # - Ignores leading zeroes in the bank file for both Account Number and Check Number data. This assumes you do not use leading zeroes in your database for either of these fields.
None - Allows leading zeroes when comparing your data to the bank file’s data for both Account Number and Check Number. Use this selection if your database uses leading zeroes for both these fields.

Bank Name

Title of the bank associated with the Check Reconciliation File Format record. Character/30

Check Information Section

Use this section to determine the position and format of check information in the bank file.

Field

Description

Begin Check Number

Position where check number information begins in the bank file’s individual character strings. Integer/3

Check Number Length

Number of characters devoted to the check number in an individual bank file character string. This includes leading zeroes. Integer/3

Zero-Fill Check Number

Checkbox determining whether leading zeroes will be added to check numbers in the bank file. Select the box to allow leading zeroes.

Begin Check Amount

Position where check amount information begins in the bank file’s individual character strings. Integer/3

Check Amount Length

Number of characters devoted to the check amount in an individual bank file character string. This includes leading zeroes and decimal point if decimal points are used. Integer/3

Implied Decimal In Amount

Checkbox that indicates whether the bank file includes a decimal point in the check amount.

  • Leave the field blank if the decimal point appears in the bank file, in which case an amount such as $768.48 displays as 768.48.

  • Select the field if the decimal point does not appear and is therefore implied. In this case, $768.48 would display as 76848.

Date Information Section

Use this section to determine the position and format of date information in the bank file.

Field

Description

Begin Issue Date

Beginning position for the check’s issue date in the bank file’s individual character strings. Integer/3

Issue Date Length

Number of characters dedicated to the check’s issue date in an individual bank file character string. If the issue date displays as 08/25/2017, you would enter 10 in this field to account for the numbers and slash ( / )symbols. Integer/3

Date Format

Format for dates in the bank file, using M, D, Y, and any special characters. For example:

  • If the bank file displays August 25, 2017, as 25082017, enter DDMMYYYY.

  • If the date in the bank file displayed as 08/25/17, enter MM/DD/YY.

  • If the bank file uses Julian dating and you select the Julian Date field, enter YYDDD in the Date Format field. Refer to the description of the Julian Date field for additional details.

Begin Clear Date

Beginning position for the check’s clearance date in the bank file’s individual character strings. You need to enter a value in this field if you want the system to change the Clear Date field for reconciled payroll and accounts payable checks. Integer/3

Clear Date Length

Number of characters dedicated to the check’s clearance date in an individual bank file character string. If the clearance date displays as 09/22/2017, you would enter 10 in this field to account for the numbers and / symbols.

You must enter a value in this field if you want the system to change the Clear Date field for reconciled payroll and accounts payable checks.

Julian Date

Checkbox indicating whether dates display in YYDDD format. In this format, YY stands for the year's last two digits, and DDD for the day’s number within the 365-day year (366 in leap years). For example, January 1, 2017 would appear as 17001, the first day in the year 2017. Select the field if the file uses Julian dating.

Account Information Section

Use this section to determine the position and format of bank account information in the bank file.

Field

Description

Begin Account Number

Beginning position for account number information in the bank file's individual character strings. This includes leading zeroes.

Account Number Length

Number of characters dedicated to the account number information in an individual bank file character string.

Account Number

Your bank account number entered exactly as you want it read by the system. This number appears in the Check Reconciliation report generated when either reconciliation process is run. The system also uses it to compare against the bank file character strings if they store account number information.

  • If you use leading zeroes for the account number, include them in this field.

  • If you set the Eliminate Zero Filling field to None or Check #, you must use leading zeroes in this field, particularly if the bank file uses leading zeroes for the account information.

  • If the bank file does not include account numbers and you do not need to track account numbers in the Check Reconciliation report, enter 0 in this field.


JavaScript errors detected

Please note, these errors can depend on your browser setup.

If this problem persists, please contact our support.