Bank Transmission File Format Table
Use this option to set up the format for providing payroll check information to your bank. The format defines the layout and content of the files you send.
Important
Before using this option, contact your bank for a copy of their file specifications.
Menu Path: Fund Accounting > Reference Tables > Reference Tables > Bank Transmission File Format
Creating Bank Transmission File Formats
Different software systems store records differently. For example, the format of check records in Human Resources is most likely different from the format of checks in your bank's computer system. As a result, you cannot simply send a copy of your check files to the bank. Rather, you need to create files that are formatted so the bank's system can read them.
File Elements
Fund Accounting's Bank File Format Transmission table enables you to set up a format based on the requirements of your bank's software system. Three elements need to be accounted for in a format: files, records, and fields. To understand the formatting process, it may be helpful to review some basics:
- A file is a collection of records. It can also be a collection of files. For example, a bank file can contain multiple files that store checks by their dates. These smaller files are referred to as batch files.
- In addition to records, a file includes header and trailer records. Generally, the header identifies the file, while the trailer summarizes its contents.
- Records are divided into fields. For example, check records in Human Resources include seven fields. However, only three of these might be requested by your bank, for example: Check Number, Payee, and Amount. In addition, the fields must conform with the bank's data requirements.
Record Types
The files used to transmit check data can include up to six types of records:
File Header | Defines the information for the file's header record, such as the date, bank identification number, and your tax number. |
Batch Header | Defines the information for the header records for a batch in the file, such as the check date and bank account number. |
Detail Record | Specifies the information to include from each check, such as the check number, payee name, and check amount. |
Batch Trailer | Summarizes the records of a batch within the file, such as the total number of checks issued and their total amount. |
File Trailers 1 & 2 | Summarize the records in the file, such as the number of batches, total number of checks, and their total amount. |
Your bank's format may not require all of these Record Types. For example, if the bank only accepts one batch at a time, its format could include just three types of records: File Header, Detail Record, and File Trailer.
File Formatting
A file format requires multiple table records, one for each field in each record type. The primary components of these records include:
- Layout Code, which identifies a format and ties all of its records together.
- Record Type, which determines the type of record in the transmission file.
- Field Name, which identifies a field within a record.
Format, which can be one of the following:
Right Justified
Data is stored beginning on the right-hand side of a field. Usually, numeric fields are right justified.
Left Justified
Data is stored beginning on the left-hand side of a field. Usually, character fields are left justified.
Date Format
Dates are stored in a format such as MMDDYY (month-day-year) or YYYYMMDD (year-month-day).
If a field's data does not does not fill the entire field, you can have the system add zeroes or characters to the beginning of the field for right-justified fields and at the end for left-justified fields. For example, $75 could be stored as 0000007500 in a right-justified field that contains 10 positions.
Start/Stop Positions, which define a field's length and determine where the field starts and stops within a record. For example, a check record might include a total of 50 positions:
Start Position
Field Name
Length
Format
Stop Position
01
Check Number
10 numeric
Right justified
10
11
Payee Name
30 character
Left justified
40
41
Check Amount
10 numeric
Right justified
50
- Literal, which can define either filler, blank spaces, or a constant value. For example, a literal can specify a character or number to fill blank spaces in a field or can attach the same information to each check, such as your organization's name. For more information, refer to the Fields.
- Batch Group, which enables you to create separate batches within a file, one for each check date.
Format Example
Following is an example of a bank format, organized by Record Type. The specifications you receive from your bank will differ in layout and content, but will be similar in terms of the type of information provided.
Start | Field | Length | Format | Stop | |
---|---|---|---|---|---|
File Headers | 01 | File Date | 6 numeric | MMDDYY | 06 |
07 | Bank # | 10 numeric | Right justified | 16 | |
17 | Client Name | 30 character | Left justified | 46 | |
Batch Headers | 01 | Account # | 10 numeric | Right justified, 0 fill | 10 |
11 | Check Date | 8 numeric | MMDDYYYY | 18 | |
Detail Records | 01 | Check # | 8 numeric | Right justified, 0 fill | 08 |
09 | Payee Name | 30 character | Left justified, X fill | 38 | |
39 | Check Amt | 10 numeric | Right justified, 0 fill | 48 | |
Batch Trailers | 01 | Total Batch Checks | 6 numeric | Right justified | 06 |
07 | Total Batch Amt | 10 numeric | Right justified, decimal | 16 | |
File Trailer | 01 | Total File Checks | 6 numeric | Right justified | 06 |
07 | Total File Amt | 10 numeric | Right justified, decimal | 16 |
Important
Each line in the chart requires a record. For example, the File Header in the example includes three fields, each of which must have its own record.
In the report, the Batch Group column for the Batch Header record's Check Date field contains a Y, indicating that this format will create separate batch files, one for each check date.
Creating a file format
- Select Fund Accounting > Reference Tables > Reference Tables > Bank Transmission File Format.
- Click Add New.
- In the Bank Layout Field Information section, complete the fields in accordance with your bank's specifications.
Tip
We recommend that you complete the Record Types in numeric order (lowest to highest) and create all records for each Record Type in order of their File Positions. - Click OK to save the record.
Displaying a list of records
- Select Fund Accounting > Reference Tables > Reference Tables > Bank Transmission File Format.
- In the Search Criteria section, enter criteria to select the records you want to display. To display all existing records, leave the section blank.
- Click Find.
- Use the down arrow key to scroll through the records.
At this point, you can select a record to change or delete. You also can print a report that includes the displayed records. Refer to the following procedures for the steps required. - Click Back to return to the Search Criteria section.
Changing a Bank Transmission File Format record
- Select Fund Accounting > Reference Tables > Reference Tables > Bank Transmission File Format.
- In the Search Criteria section, enter criteria to select the records you want to display. To display all existing records, leave the section blank.
- Click Find.
- In the List section, select the record you want to change.
- In the Bank Information Data section, change the desired fields.
- Click OK to save the record.
Deleting a Bank Transmission File Format record
- Select Fund Accounting > Reference Tables > Reference Tables > Bank Transmission File Format.
- In the Search Criteria section, enter criteria to select the records you want to display. To display all existing records, leave the section blank.
- Click Find.
- In the List section, select the record you want to delete.
- Click Delete.
- In the Confirmation dialog, click Yes.
Generating the Bank File Layout List report
- Select Fund Accounting > Reference Tables > Reference Tables > Bank Transmission File Format.
- In the Search Criteria section, enter criteria to select the records you want to include. To display all existing records, leave the section blank.
- Click Find.
- Click Print.
- In the Print window, enter settings for generating the report. The report's default file name is bnk_layout.rpt.
- Click OK.
Fields
Field | Description | ||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Layout Code | Code identifying a bank transmission file. The same code applies to all records in the file. Character/10 You must start the code with one of the following characters: A - Accounts Payable - to indicate that the file contains accounts payable checks generated in Fund Accounting. The Layout Code continues to display as you begin each subsequent record. Do not change the code, unless you want to start a new file format. Also, keep in mind that you cannot change a record’s code once the record is saved. | ||||||||||||||||||||||||
Description | Brief description of the file’s contents, for example, PAYROLL CHECK FORMAT or MIDLAND BANK FORMAT. Changing the description in one record changes it in all records associated with a format. Character/25 | ||||||||||||||||||||||||
Record Type | Code identifying the type of record being created within a file. Select: 1 - File Header - Designates the current record as the header record for the entire file. | ||||||||||||||||||||||||
File Position | Number identifying a field’s location in a particular Record Type. Records should be entered and numbered in sequential order. Integer/4 Example: If the bank’s specifications call for a check detail record with five fields, you should enter a record for each field in the specified order, assigning 3-Detail Record as the Record Type and numbering the records from 1-5 in the File Position field. | ||||||||||||||||||||||||
Delimiter | Optional character for separating fields in a record, such as a comma or semicolon. This tells the system where to start and stop when reading a field. Character/1
| ||||||||||||||||||||||||
Field Name | Name of a check-related field in an accounts payable or payroll record. The drop-down list’s selections apply to either payroll checks or accounts payable checks. Select:
| ||||||||||||||||||||||||
Format | Defines a field’s format. Select: Right Justified - Aligns the last number or character in a value at the right-hand side of the field. If specified in the Literal field, the system fills any empty positions at the beginning of the field with zeros or characters. | ||||||||||||||||||||||||
Literal | Multi-functional field. Character/30 Your entry can include one of the following, depending on your bank’s specifications:
| ||||||||||||||||||||||||
Start Position | Number identifying the position in a record where a field begins. Integer/4 This value must be one greater than the Stop Position of the previous field, unless the current record is a Record Type’s first field, in which case you must enter 1 as the Start Position. You cannot access this field if a Delimiter is being used. | ||||||||||||||||||||||||
Stop Position | Number identifying the position in a record where a field ends. You cannot access this field if a Delimiter is being used. Integer/4 | ||||||||||||||||||||||||
Field Length | Number of positions in a field. Integer/4 If a Delimiter is not being used, the system calculates the length based on the values in the Start Position and Stop Position fields. Otherwise, you must enter the length. | ||||||||||||||||||||||||
Include Decimal Point | Checkbox indicating whether a decimal point should be included in a field’s numeric value.
| ||||||||||||||||||||||||
Batch Group | Checkbox for creating separate batches in your transmission file. This setting only applies if the Record Type is 2 - Batch Header. Leave the checkbox blank if your bank does not want checks grouped. You can group checks by dates, status, or other criteria, depending on your selection in the Field Name field. Example: If a bank wants records grouped by date, select 2 - Batch Header in the Record Type field and HRM Check Date in the Field Name field, and then select the Batch Group checkbox. These settings create Batch Header records for each date. You must also set up a Batch Trailer record to follow each batch. For an example, refer to the Record Type to Count field. | ||||||||||||||||||||||||
Count or Sum | Selection indicating that current record should provide either a record count or total amount of the checks in a batch or file. This field only applies to 4 - Batch Trailer, 5 - File Trailer 1, and 6 - File Trailer 2 records. Select: C - Count Leave the field blank if you are not setting up a count or sum record. | ||||||||||||||||||||||||
Record Types to Count (1-6) | Checkboxes for designating the Record Types that apply to a record count. The boxes correspond to the selections in the Record Types field. Select the boxes to indicate which Record Types to use in the count. | ||||||||||||||||||||||||
Note | If you want the Batch Trailer record to include the number of batches in the file, you must use the field name entered in the Batch Header record. Example: For a count of all checks in the batches created in the previous example (Batch Group field), set up the Batch Trailer record as follows:
In this case, the Field Name field in the Batch Header record was HRM Check Date. Also, keep in mind that to produce multiple batches, the Batch Header’s Batch Group checkbox must be selected. |