Mapping Bank Files to A/R Payments and Distributions

Use these steps to set up the mapping between an electronic A/R payment file from a bank and SyteLine tables, so that the import works for specific bank file formats. For an example of a bank file mapping, see Example: Bank Import File Mapping.

Mapping General Import Settings and File Formats

Use the A/R Payment Import Mappings form to define the import path, contents, and formatting of an incoming A/R payment file from a specific bank:

1. In the header area of the form, specify this information:

   Map ID and Description

   Specify an ID and description that clearly define the bank and the type of file to be used for this import.

   Import Path

   Specify the folder, in UNC format (\\server\folder), where files to be imported with this format are placed for processing.  The folder path must already exist.

   Type

   Specify one of these options:

   • Delimited: There is a delimiter character between each field in the imported file.
   • Non-Delimited: There are no delimiters in the imported file.
   • NACHA CCD: This Map ID is used when you import fixed format files that use the NACHA format. In this case, you cannot further define the mapping. The A/R Payment Import will process incoming files based on the standard NACHA CCD fixed format.
2. If Type is set to Delimited, specify the delimiter character and specify whether the fields are enclosed in quotation marks. If so, the quotation marks are stripped off.
3. If amounts in the imported file do not contain an actual decimal point, but have an implied decimal point, select Amounts Contain Implied Decimal and then specify the number of digits that are included to the right of the decimal.
4. Specify the Date Format used in the file.
5. In the Record Identifier section, indicate whether Record Identifiers Are Used in the file. If yes, then specify this information:

   Field Number

   If Type is set to Delimited, specify the field position of the Record ID field.

   Starting and Ending Position

   If Type is set to Non-Delimited, specify the characters in the row that are the start and end of the Record ID value.

6. Specify the number of header records (initial rows) to skip in the file.
7. In the Mapping Groups section, specify this information:

   Group Name

   Specify the name of the mapping group, which identifies the purpose of field mappings in the group. Currently the options are Payment or Distribution.

   First Record ID of Group

   If you selected Record Identifiers are Used, then an incoming file might have multiple rows for a given record. Each row has a record ID. Specify the record ID that starts the new group.

8. The grid at the bottom of the form is read-only and contains values from the A/R Payment Import Field Mappings form after they are defined.
9. Click Mapping Fields to continue.

To see an example that shows how this mapping is set up for a particular bank file, see Example: Bank Import File Mapping.

Mapping Fields to Tables

Use the A/R Payment Import Field Mappings form to define, for a particular incoming format, the mapping of each imported field to a table and column in the import workbench:

1. If you open this form from the A/R Payment Import Mappings form, the fields at the top of this form are filled in with values defined on that form.
2. For each Map ID and Table Group, specify where field data is located in an incoming row, and the table/column that the field is mapped to:

   Table Name and Column Name

   Specify the workbench table and column that are mapped to this payment data field within a row. The ar_eft_import_arpmt table is used for records in the Payment group, or the ar_eft_import_arpmtd table is used for records in the Distribution group.

   Field Number

   If Type is Delimited, specify the field position of the field within a row to capture.

   Starting Position and Ending Position

   If Type is Non-Delimited, specify the characters in the row that indicate the start and ending positions of the field in the row.

   Record ID

   Specify the Record ID, if any, associated with the imported row containing the field data. If Record IDs are used, they are helpful to determine the row in the file that is being imported.

   Field is Amount

   If you select this field, and Amounts Contain Implied Decimal is selected on the on the A/R Payment Import Mappings form, the value is stored in the database with a decimal point and trailing digits. The number of trailing digits is based on the Digits Following Implied Decimal value on the A/R Payment Import Mappings form.

   Field is Date

   If this field is selected, the Date Format on the A/R Payment Import Mappings form is used to parse the value.

   Value Conversion ID

   If the imported value must be converted to a valid SyteLine value before it is stored in the database, specify the conversion ID to use. Conversions are defined in the A/R Payment Import Conversions form.

3. If you need to return to the A/R Payment Import Mappings form to add another group, click Payment Import Map.
4. If you need to define any value conversions between values in the incoming file and values used in SyteLine, continue with the following section.

Setting up Conversions for Imported Values

Use the A/R Payment Import Conversions form to specify conversions of values from incoming payment files into acceptable Base Prod Name values. For example, the payment types used in the bank file, such as ACH, WIR, etc., probably do not match the SyteLine  payment type values (C, W, etc.).

Related Topics

Importing A/R Payment and Distribution Data from Bank Files

Example: Bank Import File Mapping