Use this subtask to define the text and Comma Separated Values (CSV) that correspond to the bank statement's layout. Because bank statements have different formats in different countries, you can use this screen to select needed columns. In addition, you can change the position of selected column headings and insert a text layout of an uploaded file.
You must complete this screen in order to upload your bank statement file. If this screen is not set up or is incorrectly set up, you will be unable to upload your bank statement in the Maintain Bank Statement Info screen.
These non-editable fields display the abbreviation of your bank and its full name. The value for this field is taken from the main screen.
This non-editable field displays the bank account number that is linked to the Bank Abbr that you entered on the main screen.
This non-editable field displays the bank account's currency. For non-multicurrency users, the default for this field is your company's functional currency.
Costpoint Multicurrency users should enter the bank statement's currency in this field.
For each item from your bank statement that you want to upload, you must enter the item's Field No, Length (applicable only for TXT files), and Code. You can enter up to six characters in these fields.
All records in the bank statement file have a code that indicates which type of data is in the record. For example, a Code of 399 could designate a debit transaction while a Code of 699 could denote a credit transaction. This code is located in the same field number/column for all transactions in your bank statement file. For example, the code for both debit and credit transactions could be located in field number/column two (2) of a file.
In addition, you can use the same code more than one time. For example, Code 15 could represent the Statement End Date, Ending Balance, and Beginning Balance.
The program will look at both the Code and the Field No you enter to determine the type of data. For example, Code 15 with a Field No of 4 could designate the Ending Balance. In addition, a Code 15 with a Field No of 6 could signify a Beginning Balance.
For CSV files, it is not necessary to enter the length of the column for the Code, so you should enter "0" in the Length field. The maximum length of the Code column in your bank statement file is 10 digits.
For text files, you must enter a Length for the Code column in your bank statement file. The maximum Length of the Code column in your bank statement file is 10 digits.
This is a required field. If you do not enter a numeric value for this field, you will receive an error message.
This is the account number of your bank statement and is a required field.
The account number in your upload file must be the same as the one that you entered in the main screen of Maintain Company Bank Accounts. If the two numbers are not the same, you will be unable to upload your file into Costpoint.
CSV Files
In the Field No field, enter the column number for the account number in your upload file. The upload program does not need to know the length of a column; therefore, enter a zero (0) into the Length field. Verify that the length of the Account No column in your bank statement file does not exceed 17 characters.
Text Files
In the Field No field, enter the column number for account number in your upload file. In the Length field, enter the length of the Account No column in your bank statement file. Verify that the length of the Account No column in your bank statement file does not exceed 17 characters.
This is your bank statements' end date and is a required field. The date columns of your upload file must be in one of the following date formats: YYYYMMDD, DDMMYYYY, MMDDYYY, YYMMDD, MMDDYY, DDMMYY.
CSV Files
In the Field No field, enter the column number for statement end date in your upload file. The upload program does not need to know the length of a column; therefore, enter a zero (0) into the Length field. Verify that the length of the Statement End Date column in your bank statement file does not exceed eight digits.
Text Files
In the Field No field, enter the column number for the statement end date in your upload file. In the Length field, enter the length of the Statement End Date column in your bank statement file. Verify that the length of the Statement End Date column in your bank statement file does not exceed eight digits.
This is the ending balance of your bank statement and is a required field.
CSV Files
In the Field No field, enter the column number for the ending balance in your upload file. The upload program does not need to know the length of a column; therefore, enter a zero (0) in the Length field. Verify that the Ending Balance column does not exceed 15 digits. The format for the amounts should be (12,2); for example, 999999999999.99.
Text Files
In the Field No field, enter the column number for the ending balance in your upload file. In the Length field, enter the length of the Ending Balance column in your bank statement file. Verify that the Ending Balance column does not exceed 15 digits. The format for the amounts should be (12,2); for example, 999999999999.99.
This is the beginning balance of your bank statement and is a required field.
CSV Files
In the Field No field, enter the column number for the beginning balance in your upload file. The upload program does not need to know the length of a column; therefore, enter a zero (0) in the Length field. Verify that the Beginning Balance column does not exceed 15 digits. The format for the amounts should be (12,2); for example, 999999999999.99.
Text Files
In the Field No field, enter the column number for the beginning balance in your upload file. In the Length field, enter the length of the Beginning Balance column in your bank statement file. Verify that the Beginning Balance column does not exceed 15 digits. The format for the amounts should be (12,2); for example, 999999999999.99.
This is the deposit amount of your bank statement and is a required field.
CSV Files
In the Field No field, enter the column number for the Deposit Amount in your upload file. The upload program does not need to know the length of a column; therefore, enter a zero (0) in the Length field. Verify that the Deposit Amount column does not exceed 15 digits. The format for the amounts should be (12,2); for example, 999999999999.99.
Text Files
In the Field No field, enter the column number for the deposit amount in your upload file. In the Length field, enter the length of the Deposit Amount column in your bank statement file. Verify that the Deposit Amount column does not exceed 15 digits. The format for the amounts should be (12,2); for example, 999999999999.99.
This is the check amount of your bank statement. This is not a required field.
CSV Files
In the Field No field, enter the column number for the check amount in your upload file. The upload program does not need to know the length of a column; therefore, enter a zero (0) in the Length field. Verify that the Check Amount column does not exceed 15 digits. The format for the amounts should be (12,2); for example, 999999999999.99.
Text Files
In the Field No field, enter the column number for the check amount in your upload file. In the Length field, enter the length of the Check Amount column in your bank statement file. Verify that the Check Amount column does not exceed 15 digits. The format for the amounts should be (12,2); for example, 999999999999.99.
Misc Deposit Amt is not a required item. However, you may have an item such as interest earned that is not a regular deposit but was added to your account.
CSV Files
In the Field No field, enter the column number for the miscellaneous deposit amount in your upload file. The upload program does not need to know the length of a column; therefore, enter a zero (0) in the Length field. Verify that the Misc Deposit Amt column does not exceed 15 digits. The format for the amounts should be (12,2); for example, 999999999999.99.
Text Files
In the Field No field, enter the column number for the miscellaneous deposit amount in your upload file. In the Length field, enter the length of the Misc Deposit Amt column in your bank statement file. Verify that the Misc. Deposit Amt. column does not exceed 15 digits. The format for the amounts should be (12,2); for example, 999999999999.99.
Misc Withdrawl Amt is not a required item. However, you may have withdrawals to your account, such as bank charges, that need to be uploaded.
CSV Files
In the Field No field, enter the column number for the miscellaneous withdrawal amount in your upload file. The upload program does not need to know the length of a column; therefore, enter a zero (0) into the Length field. Verify that the Misc Withdrawal Amt column does not exceed 15 digits. The format for the amounts should be (12,2); for example, 999999999999.99.
Text Files
In the Field No field, enter the column number for the miscellaneous withdrawal amount in your upload file. In the Length field, enter the length of the Misc Withdrawal Amt column in your bank statement file. Verify that the Misc Withdrawal Amt column does not exceed 15 digits. The format for the amounts should be (12,2); for example, 999999999999.99.
The transaction date is the date on which the bank processed the transaction to your account. This is a required field.
CSV Files
In the Field No field, enter the column number for the transaction date in your upload file. The upload program does not need to know the length of a column; therefore, enter a zero (0) in the Length field. Verify that the length of the Transaction Date column in your bank statement file does not exceed eight digits. The date column of your upload file must be in one of the following date formats: YYYYMMDD, DDMMYYYY, MMDDYYY, YYMMDD, MMDDYY, DDMMYY.
Text Files
In the Field No field, enter the column number for the Transaction Date in your upload file. In the Length field, enter the length of the Transaction Date column in your bank statement file. Verify that the length of the Transaction Date column in your bank statement file does not exceed eight digits. The date column of your upload file must be in one of the following date formats: YYYYMMDD, DDMMYYYY, MMDDYYY, YYMMDD, MMDDYY, DDMMYY.
The transaction number designates the number of the transaction. The transaction number will usually be a check number or a deposit number.
CSV Files
In the Field No field, enter the column number for the transaction number in your upload file. The upload program does not need to know the length of a column; therefore, enter a zero (0) into the Length field. Verify that the length of the Transaction Number column in your bank statement file does not exceed 20 characters. Although this column can contain up to 20 alphanumeric characters, the upload imports only numeric values. Moreover, the maximum number of digits imported is 10. For example, if you have a check number that reads CHK10050, the import program will upload only the 10050 numeric value.
Text Files
In the Field No field, enter the column number for the transaction number in your upload file. In the Length field, enter the length of the Transaction Number column in your bank statement file. Although this column can up to 20 characters, the upload imports only numeric values. Moreover, the maximum number of digits imported is 10. For example, if you have a check number that reads CHK10050, the import program will upload only the 10050 numeric value.
The bank description denotes the bank's description for the transaction. This field is not required.
CSV Files
In the Field No field, enter the column number for the bank description in your upload file. The upload program does not need to know the length of a column; therefore, enter a zero (0) in the Length field. You can upload up to 45 characters for the Bank Description.
Text Files
In the Field No field, enter the column number for the bank description in your upload file. In the Length field, enter the length of the Bank Description column in your bank statement file. You can upload up to 45 characters for the Bank Description.
Enter the location of the file you are uploading. The location may be on a disk, your hard drive, or on a network drive. This is not a required field and can be left blank. However, for convenience, you may want to enter the location of your file. The value you enter in this field will default into the File Location field in Maintain Bank Statement Info screen when you are ready to upload your bank statement file.
Use this pushbutton to activate the Select File screen. Use this screen to find the location of your upload file.
Select this radio button if the bank data files are in a text format. If your file is in a text format, the length of each item you upload must be defined.
Select this radio button if the bank data files are in a Comma Separated Value (CSV) format.
Enter, or use the drop-down box to select, the date format for date entries in your file. The correct date format must be selected so that the upload file is imported into Costpoint.
Costpoint supports the following date formats; DDMMYYYY, MMDDYYY, YYMMDD, MMDDYY, and DDMMYY.