User Defined File Format Tab of Banks Form

The file format information that you enter on this 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 Options tab of the Banks form.

On this tab you match (map) Vantagepoint fields and information with your bank's text file requirements. You can set up user-defined file formats for 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 Vantagepoint when you select the Export to Text action on the Printing and Numbering page in Vendor Payments and Employee Payments in the Cash Management application.

Contents

Field	Description
File Type	Select the file type for the file format. The options are: CSV: A comma-separated values file. Commas are used as delimiters between fields in a line. Select this option to create files for other international banking systems, such as the United Kingdom BACS payments. When you select this file type, the Length, Start, Pad Character, and Justify columns are not enabled in the Employee and Vendor grids on this tab. Fixed Length: A data file that contains records with no structured relationships. You define the length of each field. There are no delimiters. For example, with this file type, you would define a starting position and length of each field within the file. When you select the fixed length type, all the columns in the Employee and Vendor grids on this tab are enabled.
Text Qualifier	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 both comma-separated values (.csv) and fixed length file types.
Decimal Places	Select the decimal places to use in the export-to-text file. The options are: Default: This uses the number of decimal places that are assigned on the Global Currency Options form in Settings > Currency > Options 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 Yes to sort payments in the export-to-text file by the payment currency of the cash transaction.
Subtotal by Payment Currency	After you set the Sort by Payment Currency option to Yes, select Yes for 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 export-to-text file.
Line Type	Select a line type from the drop-down list. You must define each line in a text file. First, you select the type of line in the Line Type field. Then, in the Employee grid and the Vendor grid, you 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 output 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 Yes 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 option, the fields in the Pad Block section 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 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. Vantagepoint automatically adds more pad lines to match the number of lines required.
Hard Returns	Select Yes 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 certain total number of characters.
Date Format	Click to open the Format Date/Time dialog box and then 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 Yes if a bank uses the ClieOps 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.
Use File Creation Number	If your file format requires a file creation number, select Yes. You must select this option to enable the Last File Creation Number field and the Reset File Number Each field on this tab.
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, if you enter a previously used number, it 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, set the Data Mapping field to File Creation Number in the appropriate Employee or Vendor grid on this tab. This field is enabled when you select the Use File Creation Number check box.
Reset Last File Number Each	Select Day, Month, Year, or None to determine the time period in which the file creation number for export-to-text files is reset to start again. A file creation number is assigned automatically to each export-to-text file. The number affects the value of the file identifier if you select File Identifier in the Data Mapping field in the Employee and 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 export-to-text 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
Field Number	Enter a number to identify this field. Both .csv and fixed length types 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 No column.
Hidden	Select this check box to hide the row.
Length	Enter the number of characters that your bank requires for this field. This field is enabled only if you set the File Type option to Fixed Length.
Start	Enter the number of the starting position of this field in the text 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. Vantagepoint will calculate the end position based on the starting position and the length. This field is enabled only if you set the File Type option to Fixed Length.
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	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 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 hash 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 system generated at the time the export-to-text 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 the Detail lines. Bank Currency: This item is available if you use multiple currencies. It is available for all the 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 Sum: 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 text file. Each record in the text file will have a number value that is one greater than the previous record in the text 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 in the Mapping Value 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. If you need to include direct deposit fields in your .txt file, you must create custom fields on an Employees hub custom tab for the direct deposit information. These custom fields are then available in this 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 in the desktop application. Check Database Field 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 Calculate in the field to open the Calculation Details dialog box. On the dialog box, enter the information for the calculation in this field. This field is enabled only if you set the Data Mapping field to Calculation.
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 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, Vantagepoint pads with spaces. This field is enabled only when you set the File Type option to Fixed Length.
Justify	Select the justification for the contents of the field: Left or Right. This field is enabled only when you set the File Type option to Fixed Length.
+ Add Employee	Click this option to add a row to the grid.

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
Field Number	Enter a number to identify this field. Both .csv and fixed length file types 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 No column.
Hidden	Select this check box to hide the row.
Length	Enter the number of characters that your bank requires for this field. This field is enabled only when you set the File Type option to Fixed Length.
Start	Enter the number of the starting position of this field in the text 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. Vantagepoint will calculate the end position based on the starting position and the length. This field is enabled only when you set the File Type option to Fixed Length.
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	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 Employees 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 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 system generated at the time the export-to-text 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 the Detail lines. Bank Currency: This item is available if you use multiple currencies. It is available for all the 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 text file. Each record in the text file will have a number value that is one greater than the previous record in the text 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 text file field in the Mapping Value 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 vendor 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 Calculate in the field to open the Calculation Details dialog box and enter the information for the calculation in this field. This field is enabled only if you set the Data Mapping option to Calculation.
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 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, Vantagepoint pads with spaces. This field is enabled only when you set the File Type option to Fixed Length.
Justify	Select the justification for the contents of the field: Left or Right. This field is enabled only when you set the File Type option to Fixed Length.
+ Add Vendor	Click this option to add a row to the grid.