Configuring Payment Entry CSV Import

← Payments in Billing & Invoice Management

JustOn Billing & Invoice Management allows for tracking collections for invoices. To this end, you can import payment entries from CSV files.

The custom setting CSV Import Configuration controls the payment entry import.

CSV Import Configuration Information

The custom setting CSV Import Configuration includes the following information:

Field	Description	Examples
Name	A unique name for the custom setting record, will be selectable when importing a CSV file.	`bank ABC`
CSV Column Separator	Specifies the character that separates the columns in the CSV file.	`;`
Character Encoding	Specifies the character encoding of your CSV file. If empty, defaults to UTF-8.	`ISO-8859-1`
Currency Decimal Separator	Specifies the decimal separator, depends on your locale.	`,`
Date Format	Specifies the order of day, month and year numbers, where `D` = day, `M` = month, `Y` = year. Supported values include `YMD` and `DMY`. The date numbers in the CSV file must be separated by non-digit characters: hyphens for `YMD` and dots for `DMY`.	`YMD` for 2017-12-31 `DMY` for 31.12.2017
Field Mapping	Specifies a field mapping between CSV file columns and payment entry fields. The syntax is `<column> <field>`, that is, a column name (as defined in a CSV header row) or a column number (starting with `1`) + space character + API name of the payment entry field. Multiple mappings are separated by semicolon. Column names in the CSV file must not contain spaces. With column numbers, the CSV file must not contain a header row. For more details, see "Field mapping syntax grammar" below.	`date BookingDate__c;reference Reference__c;debit Debit__c;credit Credit__c` `1 BookingDate__c;2 Reference__c;3 Debit__c;4 Credit__c`
Filter Expression	Specifies an expression to be evaluated for each CSV row. If an expression is true, the row is kept and a payment entry will be created. The syntax of a comparison expression is `$<column> <operator> <value>`. Multiple comparison expressions can be coupled with `&` (logical AND) or `\|` (logical OR), where `&` takes precedence. Column numbers start with `1` or `A`. Strings are enclosed in single quotes, like 'some text'. For more details, see "Filter expression syntax grammar" below.	`$1 = 'IMPORT'` `$'abc' >= 100 & $'abc' <= 200` `$1 > 20 & ($3 = 'S' \| $4 = 'Y')`

Field mapping syntax grammar

The field mapping syntax is based on the following grammar:

Column_Mapping = (Column_Name | Column_Number) " " PaymentEntry_APIFieldName
Mapping = Column_Mapping (";" Column_Mapping)*

Examples:

Field mapping with column names:

date BookingDate__c;reference Reference__c;debit Debit__c;credit Credit__c

Field mapping with column numbers:

1 BookingDate__c;2 Reference__c;3 Debit__c;4 Credit__c

Filter expression syntax grammar

The filter expression syntax is based on the following grammar:

Expr := CompareExpr | '(' Expr ')' | Expr LogOp Expr;
LogOp := '&' | '|'
CompareExpr := Value CompareOp Value;
CompareOp := '=', '<', '>', '!=', '>=', '<=';
Value := String | Number | Column | ColumnName;
Column := '$'Number;
ColumnName := '$'String

Examples:

Import rows where the first column matches with the text value "IMPORT":

$1 = 'IMPORT'

Import rows where the value in the column "abc" is between 100 and 200 (both inclusive):

$'abc' >= 100 & $'abc' <= 200

Import rows where
a) the value of the first column is larger than 20 and
b) the value of the third column is S or the value of the fourth column is Y:

$1 > 20 & ($3 = 'S' | $4 = 'Y')

Defining CSV Import Configuration

Depending on your organization's requirements, you must define one or more specific CSV import configurations.

Click to enter Setup, then open Custom Settings.

In Salesforce Lightning, navigate to Custom Code > Custom Settings.

In Salesforce Classic, navigate to Develop > Custom Settings.
Click Manage in the row of CSV Import Configuration.
Click New.
Specify the details as necessary.

See CSV Import Configuration Information.
Click Save.

CSV Import Configuration Examples

The following examples illustrate possible import configurations. To understand the examples, make sure you are familiar with JustOn's approach to the payment amount calculation.

Be aware that the CSV structure and the corresponding import configuration depend on your environment.

Assume a simple CSV file with just four columns: date, invoice number, credit (absolute amount), debit (absolute amount).

2019-10-12;201900023;150,00;0
2019-10-13;201900045;260,00;0
2019-10-16;201900078;0;80,00

Note that the first two entries represent payments, but the third entry represents a payout. So you need this field mapping to correctly set the corresponding payment entry fields:

1 BookingDate__c;2 Reference__c;3 Credit__c;4 Debit__c

This creates the following payment entries on import:

#	Booking Date	Reference	Credit	Debit
1	2019-10-12	201900023	150,00
2	2019-10-13	201900045	260,00
3	2019-10-16	201900078		80,00

Now assume the same payment operations (two payments and one payout) in a more complex CSV file with headers and more information (which you may not need). Note that there is only one column for the amount with both positive and negative values. So you will, consequently, fill one field with these values – namely the one that handles the same positive or negative values with the same payment or payout effect.

Date;Type;Reference;Recipient/Payer;Account;Amount;Currency
2019-10-12;standing order;201900023;Firma;DE75512108001245126199;150,00;EUR
2019-10-13;direct debit;201900045;Individuel;FR7630006000011234567890189;260,00;EUR
2019-10-16;credit;201900078;Zadruga;BA393385804800211234;-80,00;EUR

To extract the information to produce the same payment or payout effect, you need this field mapping:

Date BookingDate__c;Reference Reference__c;Amount Credit__c

This creates the following payment entries on import:

#	Booking Date	Reference	Credit
1	2019-10-12	201900023	150,00
2	2019-10-13	201900045	260,00
3	2019-10-16	201900078	-80,00