User Defined File Format Tab of Bank Codes

The file format information that you enter on the User Defined File Format tab is used to create a text (.txt) file that contains payment information that is processed by a third-party software application.

This tab is available if you select User Defined in the Export to Text Format field on the Bank Codes tab of Bank Settings.

On this tab you match (map) DPS fields and information to your bank's text file requirements. You can set up user-defined file formats for the electronic funds transfer (EFT) requirements of many international banks, including BACS (Bankers Automated Clearing System) files used in the United Kingdom. The text files are created in DPS when you select the Export to Text option for:

• Accounts payable payments

• Employee expense payments

Contents

Field	Description
File Type	Click in this field to select the file type for the file format. The options are: CSV: This is a file with comma-separated values. Commas are used as delimiters between fields in a line. When you select the CSV file type, the Length, Start, Pad Character, and Justify columns are disabled in the Employee and Vendor grids on this tab. Select this option to create text files for other international banking systems, such as United Kingdom BACS payments. Fixed Length: This is a file that contains records with no structured relationships. You define the length of each field. There are no delimiters. You define a starting position and length of each field within the file. Employee When you select the fixed length type, all of the columns in the and Vendor grids on this tab are enabled.
Text Qualifier	From the drop-down list, select a text qualifier to use in addition to comma delimiters in a text file. Text qualifiers are placed at the beginning and end of a field to help distinguish the comma delimiters that are used to separate fields from the commas that are used within text fields. Comma delimiters are placed outside the text qualifier. This is an example of a text file line with a single quotation mark as the text qualifier: 'A','Advanced Mechanical Technologies, Inc.', 'Electrical Engineering', '06/15/2009' Text qualifiers can be used for CSV and fixed length files.
Decimal Places	Select the decimal places to use in the file. The options are: Default: This option uses the number of decimal places that are assigned in Settings > General > Currency for each currency code in the .txt file. Numbers 0, 1, 2, 3, 4, 5, 6, 7, 8, or 9: The number that you select determines the number of decimal places. Decimals are trimmed from the right or padded to the right with zeros as needed. For example, if you select 3 decimal places, the following changes occur for these values: 10.9956 becomes 10.995 10.99 becomes 10.990
Sort by Payment Currency	If you use multiple currencies, select this option to sort payments in the export-to-text file by the payment currency of the cash transaction.
Subtotal by Payment Currency	After you select the Sort by Payment Currency option, select this option to include a totals record layout (Trailer 1 line type) after the last payment record and before the terminal record layout (Trailer 2 line type) in the file.
Line Type	Select a line type from the drop-down list. You must define each line in a text file. First, select the type of line in the Line Type field. Then, in the Employee grid and the Vendor grid, define each piece of information to include in that line type. The Employee grid applies for employee expense payments, and the Vendor grid applies for accounts payable vendor payments. One line is created for each header and trailer line. For detail and settlement or contra lines, the number of lines in the file is equal to the total number of payments processed. For example, if you have 30 payments in a process run, there are 30 detail lines and 30 settlement or contra lines in the file. Some files may require only the payment detail, which means that you define only the fields for the detail line type. Other files may require header and trailer information in addition to the detail line. In this case, you must define the fields for each line type.

Pad Blocks

Field	Description
Pad Last Block	Select this check box to fill (pad) blank spaces at the end of a text file. This does not fill blank spaces at the end of each field within the file. When you select this check box, the Pad Character and Pad Lines in Multiple of fields are enabled.
Pad Character	Enter the characters with which to fill (pad) the blank spaces at the end of the text file layout. This does not pad the blank spaces at the end of the individual fields that make up the file.
Pad Lines in Multiple of	If your bank requires that the text file contain multiples of a specific number of lines, enter that number in this field. DPS automatically adds more pad lines to match the number of lines required.
Hard Returns	Select this check box if you want the file layout to include a carriage return (line feed) when the number of characters in a line is limited to a specific total.
Date Format	Click to open the Format Date/Time dialog box. Enter the date and time settings to use in the file layout. You must use this dialog box if you process payments for different geographical locations that have different time zones and different date and time requirements.
ClieOp	Select this check box if a bank uses the ClieOp format. Most often, this format is used for payments made to recipients in the Netherlands in Euros, or where multiple types of currency need to be supported for a single bank.

File Creation Date

Field	Description
Use File Creation Number	Select this check box if your file format requires a file creation number. It enables the Last File Creation Number field andReset Last File Number Each fields.
Last File Creation Number	Enter the starting creation number for this bank. This number is incremented by one each time an export file is processed through a payment run for an Accounts Payable or Employee Expense payment for this bank. You can modify this number at any time. When you modify the number, entering a previously used number does not recreate the text file that originally used that number. If you want to include the file creation number in the text file, you must define the location of this field. To do this, select File Creation Number in the Data Mapping field in the Employee or Vendor grid on this tab.
Reset Last File Number Each	From the drop-down list in this field, select Day, Month, Year, or None to determine when the file creation number is reset to start again. A file creation number is assigned automatically to each file. The number affects the value of the file identifier if you select File Identifier in the Data Mapping field in the Employee or Vendor grid. The file identifier is the file creation date plus the file creation number.
Last File Creation Date	This field displays the date on which the last file was created.

Employee Grid

After you select a line type in the Line Type field, use the Employee grid rows to enter each piece of information for that line type that your bank requires for an employee expense payment.

Field	Description
Employee Drop-down	Click the drop-down arrow on a grid header to complete any of the following actions: To print grid data, click Print. On the Print Preview form, click File > Print to send the grid data to your default printer. To export grid data to an Excel spreadsheet, click Export to Excel. When Microsoft Excel opens, use its features to modify, print, or email the grid data, or to save the spreadsheet file locally. To turn on grouping for a grid, click Enable Grouping. When a field displays with the instruction: "Drag a column header here to group by that column," drag and drop column headers into the field, in the sequence that you want them to display. Not all options are available on all grids.
Insert	Click this option to add a row to the grid.
Top Up Down Bottom	Use these options to move rows in the grid. Select a row (click anywhere in the row), and then click one of the toolbar options to move a row in the grid. The order of the rows in the grid determines the order of the fields in the text files for both CSV and fixed length files.
Delete	To delete a row in the grid, select the row and click this option.
Field Number	Enter a number to identify this field. Both CSV and fixed length files use the field number to identify fields that are used in calculations. When you select Calculation in the Data Mapping column of this grid, you must enter a field number in the Field Number column.
Length	Enter the number of characters that your bank requires for this field. This field is enabled only if you select Fixed Length in the File Type field.
Start	Enter the number of the starting position of this field in the file. This is based on the number of characters to the right of the first character in the file. Some file formats define an end position. DPS will calculate the end position based on the starting position and the length. This field is enabled only if you select Fixed Length in the File Type field.
Description	Enter a description to help identify the contents of this field. This description is not used in the text file itself. It is for informational purposes only.
Data Mapping	Click in this field to enable the drop-down list, and from the list, select the type of information for the field that you enter in the row. The options are: Constant: Select this option if you want this field to contain specific text that is related to the bank code or company. For example, this field could be used for bank account information. Employee Database Field: Select this option if you want this field to contain a field from the Employees hub. For example, this field could be used for the employee's bank information. Check Database Field: Select this option if you want this field to contain information from the DPS payment. For example, use this field to include the payment amount. Calculation: Select this option if you want this field to be calculated. You can use calculations for control total information, such as entry or addenda counts, entry control totals, total debit entry dollar amount, total credit entry dollar amount, or batch number. File Creation Number: Select this option if you want this field to contain the file creation number that is entered on this tab. File Creation Date: This is available for Header 1, Header 2, Header 3, and Header 4 line type items. The file creation date is generated automatically at the time the file is created. When you select File Creation Date, the Mapping Value and Calculation columns in the grid are disabled. File Identifier: This is the combination of the file creation date plus the file creation number. Payment Currency: This item is available if you use multiple currencies. It is available for all detail lines. Bank Currency: This item is available if you use multiple currencies. It is available for all detail lines. When you select Bank Currency, the Mapping Value and Calculation columns in the grid are disabled. Account ID Sum: This is the sum of the account IDs. Account Sum: This is the sum of the payments in the file. If Subtotal by Payment Currency is selected, it will be the sum of the payments with the same payment currency. Otherwise, it will be the sum of all payments. Record Count: This is the number of payments in the file. Record Counter: Select this option if you want this field to contain a record count number for the record in the file. Each record in the file will have a number value that is one greater than the previous record in the file. You can include a Record Counter field for each line type (header, detail, trailer, and so on).
Mapping Value	Based on the type of information that you selected in the Data Mapping field, enter the specific contents of the field: If you selected this option in the Data Mapping field Then enter the following in the Mapping Value field Constant The specific text that is related to the bank code or company. You can enter any value. Employee Database Field From the drop-down list, select an Employees hub field. This list does not include fields from the Direct Deposit grid on the Accounting tab in the Employees hub. If you need to include direct deposit fields in your file, you must create custom fields on an Employees hub custom tab for the direct deposit information. These custom fields will then be available in the Employee Database Field drop-down list. You must re-enter employees’ direct deposit information in these custom fields. You create custom tabs and fields for the Employees hub in Settings > General > User Defined Components. Check Database Field From the drop-down list, select a payment field. Calculation This field is not enabled. You enter the calculation information in the Calculation field of this grid. File Creation Number This field is not enabled. The file creation number is retrieved from the Last File Creation Number field on this tab.
Calculation	You can use calculations for control total information, such as entry or addenda counts, entry hash totals, total debit entry dollar amount, total credit entry dollar amount, and batch number. Click the Calculation field and then to open the Calculation Details dialog box. In the dialog box, enter the information for the calculation in this field. This field is enabled only if you select Calculation in the Data Mapping field.
Pad Character	Enter the character to use to fill (pad) blank spaces that remain in this field when the field is left or right justified. For example, if a field contains the DPS Vendor Name and a vendor's name takes up only 12 of the 30 spaces that are allotted for the name, the remaining 18 spaces are filled with a character that you choose as the pad character. When you leave this field blank, DPS pads with spaces. This field is enabled only when you select Fixed Length in the File Type field.
Justify	Select the justification for the contents of the field: Left or Right. This field is enabled only when you select Fixed Length in the File Type field.

Vendor Grid

After you select a line type in the Line Type field, use the Vendor grid rows to enter each piece of information for that line type that your bank requires for a vendor payment.

Field	Description
Vendor Drop-down	Click the drop-down arrow on a grid header to complete any of the following actions: To print grid data, click Print. On the Print Preview form, click File > Print to send the grid data to your default printer. To export grid data to an Excel spreadsheet, click Export to Excel. When Microsoft Excel opens, use its features to modify, print, or email the grid data, or to save the spreadsheet file locally. To turn on grouping for a grid, click Enable Grouping. When a field displays with the instruction: "Drag a column header here to group by that column," drag and drop column headers into the field, in the sequence that you want them to display. Not all options are available on all grids.
Insert	Click this option to add a row to the grid.
Top Up Down Bottom	Use these options to move rows in the grid. Select a row (click anywhere in the row), and then click one of the toolbar options to move a row in the grid. The order of the rows in the grid determines the order of the fields in the text files for both CSV and fixed length files.
Delete	To delete a row in the grid, select the row and click this option.
Field Number	Enter a number to identify this field. Both CSV and fixed length files use the field number to identify fields that are used in calculations. When you select Calculation in the Data Mapping column of this grid, you must enter a field number in the Field Number column.
Length	Enter the number of characters that your bank requires for this field. This field is enabled only if you select Fixed Length in the File Type field.
Start	Enter the number of the starting position of this field in the file. This is based on the number of characters to the right of the first character in the file. Some file formats define an end position. DPS will calculate the end position based on the starting position and the length. This field is enabled only if you select Fixed Length in the File Type field.
Description	Enter a description to help identify the contents of this field. This description is not used in the file itself. It is for informational purposes only.
Data Mapping	Click in this field to enable the drop-down list, and from the list, select the type of information that is contained in the field that is entered in the row. The options are: Constant: Select this option if you want this field to contain specific text that is related to the bank code or company. For example, this field might be used for bank account information. Vendor Database Field: Select this option if you want this field to contain a field from the Firms hub. For example, this field might be used for the vendor's bank information. Check Database Field: Select this option if you want this field to contain information from the DPS payment. For example, use this field to include the payment amount. Calculation: Select this option if you want this field to be calculated. File Creation Number: Select this option if you want this field to contain the file creation number that is entered on this tab. File Creation Date: This is available for Header 1, Header 2, Header 3, and Header 4 line type items. The file creation date is generated automatically at the time that the file is created. When you select File Creation Date, the Mapping Value and Calculation columns in the grid are disabled. File Identifier: This is the combination of the file creation date plus the file creation number. Payment Currency: This item is available if you use multiple currencies. It is available for all detail lines. Bank Currency: This item is available if you use multiple currencies. It is available for all detail lines. When you select Bank Currency, the Mapping Value and Calculation columns in the grid are disabled. Account ID Sum: This is the sum of the account IDs. Account Sum: This is the sum of the payments in the file. If Subtotal by Payment Currency is selected, it will be the sum of the payments with the same payment currency. Otherwise, it will be the sum of all payments. Record Count: This is the number of payments in the file. Record Counter: Select this option if you want this field to contain a record count number for the record in the file. Each record in the file will have a number value that is one greater than the previous record in the file. You can include a Record Counter field for each line type (header, detail, trailer, and so on).
Mapping Value	Based on the type of information that you selected in the Data Mapping field, enter the specific contents of the field: If you selected this option in the Data Mapping field Then enter the following in the Mapping Value field Constant The specific text that is related to the bank code or company. You can enter any value. VendorDatabase Field From the drop-down list, select a Firms hub field. Check Database Field From the drop-down list, select a payment field. Calculation This field is not enabled. You enter the calculation information in the Calculation field of this grid. File Creation Number This field is not enabled. The file creation number is retrieved from the Last File Creation Number field on this tab.
Calculation	You can use calculations for control total information, such as entry or addenda counts, entry hash totals, total debit entry dollar amount, total credit entry dollar amount, and batch number. Click the Calculation field and then click the to open the Calculation Details dialog box. In the dialog box, enter the information for the calculation in this field. This field is enabled only if you select Calculation in the Data Mapping field.
Pad Character	Enter the character to use to fill (pad) blank spaces that remain in this field when the field is left or right justified. For example, if a field contains the DPS Vendor Name and a vendor's name takes up only 12 of the 30 spaces that are allotted for the name, the remaining 18 spaces are filled with a character that you choose as the pad character. When you leave this field blank, DPS pads with spaces. This field is enabled only when you select Fixed Length in the File Type field.
Justify	Select the justification for the contents of the field: Left or Right. This field is enabled only when you select Fixed Length in the File Type field.