Use this tab to do the setup steps needed to import bank statements directly into bank statement reconciliation, allow the automatic matching of bank and
DPS records, and make it possible to match any remaining records manually.
Use this tab to define the format for the bank file you will be importing and define matching rules.
Contents
| Field | Description |
|---|
| Import
|
Select this check box to make it possible to import bank statements directly into bank statement reconciliation and enable the fields that are used to set up how
DPS identifies the data in the import file.
|
File Format
| Field | Description |
|---|
| Field Delimiter
|
From the drop-down list, select the character to use as a separator between the fields in the files that you will import.
Options are:
- {Tab}
- @
- #
- $
- ^
- |
- ; (semi colon)
- , (comma)
|
| Text Qualifier
|
From the drop-down list, select the character that you are using as a text qualifier in the files that you will import.
Options are:
-
{None}: No text qualifier
- ": Double quotation mark
- ': Single quotation mark
You can use a text qualifier in addition to delimiters in a file that you import. Text qualifiers are placed at the beginning and end of a value (field), inside the delimiters. Text qualifiers indicate that the content between them is part of one field. For example, if you use comma delimiters, a text qualifier (other than a comma) helps to distinguish commas that are used as delimiters and commas that are used within the value.
Example:
You use comma delimiters and you use a single quotation mark as the text qualifier.
A row of data in the file is as follows:
'John Bartlett', 'Marriott International, Inc', '10/20/2014', '$1,000.00'
Using text delimiters ensures that the commas in Marriott International, Inc and in $1,000.00 are not interpreted as delimiters. These commas are part of the company name and the monetary amount.
|
| Decimal Symbol
|
Enter the symbol that you use for decimals in import files.
|
Date Handling
| Field | Description |
|---|
| Order
|
Select the order in which you want the month (M), day (D), and year (Y) to appear in dates.
Options are:
DMY,
DYM,
MDY,
MYD,
YDM, and
YMD.
|
| Delimiter
|
Enter the character that you want to use as the separator between month, day, and year. Examples are
/ or
-.
|
| Leading Zeros
|
Select this check box if you want leading zeros used for one-digit months or days (for example, 01 instead of 1 for January).
|
| Four Digit Year
|
Select this check box if you want all four digits of the year included for years (for example, 2014 instead of 14).
|
Import Values
| Field | Description |
|---|
| Define Receipt/Payment
|
Use this field to define how
DPS determines which bank records are receipt (deposit) records and which records are payment records. Click the drop-down list to select one of the following options:
- Receipts greater than zero: Select this option if there is not another indicator in the file to define a receipt (deposit) or a payment. Select this option so that
DPS can identify a deposit as a transaction amount greater than zero. Amounts equal to and less than zero will be payment amounts.
- Receipts less than zero: Select this option if there is not another indicator in the file to define a deposit or a payment. Select this option so that
DPS can identify a receipt (deposit) as a transaction amount less than zero. Amounts equal to or greater than zero will be payment amounts.
- Type Mapping: Select this option to define the mapping that the bank uses to identify each record as either a receipt or a payment. Selecting this option enables the
Type Mapping option on the Import Values grid toolbar. Click the
Type Mapping option to display the Bank Transaction Type Matching dialog box, where you can set up the different transaction types.
- Separate Columns: Select this option if the deposits and payments are identified under different columns in the file to be imported.
|
Import Values Grid Toolbar
| Field | Description |
|---|
| Import Values 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
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.
|
| Field | Description |
|---|
| Type Mapping
|
This option is enabled when you select the
Type Mapping option from the
Define Receipt/Payment drop-down list.
Click this option to display the Bank Transaction Type Matching dialog box, where you set up how
DPS identifies whether bank transactions are payment records or receipt records.
|
| Down, Up, and Move Included Items Up
|
Use the these options to re-order a selected field to match the order in your import file.
Click
Move Included Items Up to move all selected fields to the top of the list.
|
Import Values Grid Columns
| Field | Description |
|---|
| Include
|
This column displays a check box for each row in the grid. Select the check box for each field that you include in the import file.
|
| Field Name
|
This column displays the fields that you can import from the file. The predefined fields are:
|
| Label
|
Enter the label for any user-defined field that you include in the import file. You cannot enter labels for predefined fields.
If you have
DPS databases set up in multiple languages, you can log in to each language and enter the appropriate label for each language.
|
| Data Type
|
This field displays the data type for a field, which indicates the type of characters used for the data:
- Varchar: The field can contain both letters and numbers.
- Datetime: The field contains a date.
- Decimal: The field contains numeric amounts.
|
Matching
| Field | Description |
|---|
| Automatically Clear Matched Transactions
|
Select this option to automatically clear any transactions associated with a matching imported bank record.
DPS matches the record on the import tab and updates the
Cleared check box on the Receipts and Payments tabs in
.
If you do not select this option,
DPS matches the bank record with the posted
DPS record according to the matching rules. However, it allows you to review and clear matched transactions as part of the Bank Reconciliation process.
|
| Advanced Matching Rules
|
Click this button to display the Advanced Matching Rules dialog box. Use the dialog box to modify the rules that
DPS uses to match transactions. The available matching rules are based on either amount and reference number or amount and transaction date.
|
| Matching Suggestions
|
Click this button to display the Matching Suggestions dialog box.
DPS uses the values entered in the dialog box to help you manually matching transactions in Bank Reconciliation by providing a list of all unmatched transactions and a list of suggested transactions.
|
