action.skip

Configuring Booking Detail CSV Export

Your organization's business may require to export invoices or booking details that are to be imported by external accounting systems like DATEV, SAP or Microsoft Dynamics. Usually, these accounting systems expect specifically formatted CSV files in order to import this data, including invoice line items and tax information. To this end, JustOn Billing & Invoice Management provides a flexible CSV export mechanism. It allows you to configure and execute invoice or booking details exports according to your needs.

Configuring the CSV export comprises two tasks:

Defining Export Settings

Depending on your organization's requirements, you must define an export configuration for CSV exports.

  1. 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.

  2. Click Manage in the row of Export Settings.

  3. Click New.
  4. Specify the details as necessary.

    Field Possible Values Description
    Name SAP_CSV
    DATEV
    A unique name for the custom setting record, will be selectable when exporting invoice data.
    Active true
    false
    Determines whether the configuration is available in the UI.
    Configuration <Salesforce record ID> Specifies the 18-digit, case-safe Salesforce record ID of the JSON configuration file, which is required for CSV exports.
    Format Booking Detail CSV Determines the export format and source.
    Select Condition Type__c IN ('Revenue','Tax') AND BookingDate__c = THIS_YEAR Specifies a condition expression as used in an SOQL WHERE clause to restrict the set of included records. If empty, matches all booking details in the scope.
    Type__c IN ('Revenue','Tax','Deferred') selects all invoice-based booking details.
    Type__c IN ('Payment','Refund','Payout','Prepayment','Provider Fee') selects all balance-based booking details.
    For details, see SOQL WHERE clause in the Salesforce Help.
    Target Export Specifies the Name of a file distribution target for the exported files.
    For exports to cloud storage services, the file distribution target must be the one set up as the storage location with the use case Export and the backend AmazonS3 or GoogleDrive.
    If left empty, the file will only be available on Salesforce's Files tab as of JustOn 2.49.

    The other fields of the export setting are ignored and can be left blank. The corresponding configuration is taken from the JSON configuration.

    file_dist_amazon
    Creating an export settings record for producing booking detail CSV files

  5. Click Save.

Export Settings or File Distribution?

JustOn Billing & Invoice Management uses the custom settings Export Settings and File Distribution to control the output of produced documents. It may be hard to tell which to apply. Remember that the two settings impact different aspects:

  • Export Settings records control what to export – invoice and bookkeeping data CSV files or SEPA XML files.
  • File Distribution settings control the output location of any produced files, be it PDF files for finalized invoices, dunning reminders and account statements, or exported CSV and XML files.

That is, your use cases determine which settings to use:

File export

If you export data to CSV files for accounting systems or to SEPA XML bank transfer orders, you need appropriate Export Settings records.

jo_faq_files_export
Exporting files (applying Export Settings) to the default location

File location

If you want produced PDF files for finalized invoices, dunning reminders and account statements to be output in a specific location (other than the group Owned by me in Salesforce Files), you need a corresponding File Distribution setting.

jo_faq_files_filedist
Defining a specific output location using a File Distribution setting

File export and file location

If you export data to CSV files for accounting systems or to SEPA XML bank transfer orders, you need appropriate Export Settings records. If you want the exported files to be output in a specific location, you need, in addition, a corresponding File Distribution setting.

To combine the two settings, the Name of the file distribution record must be specified as the Target in the export settings record.

jo_faq_files_export+filedist
Exporting files (applying Export Settings) to a specific location (applying File Distribution)

Defining CSV File JSON Configuration

Exporting booking details to CSV files requires a configuration for the CSV file to be produced. The configuration is defined in JSON notation and stored as a Salesforce file. It is referred to by the export configuration using the field Configuration.

Creating JSON Configuration

Depending on your target system, you must configure the CSV output using a JSON configuration document.

  1. Create the configuration and save it as a file with a .json extension.

    For details about the configuration definition, see JSON Configuration Example and JSON Configuration Keys.

  2. Open the Salesforce Files tab.

  3. Click Upload Files.
  4. Select the file using your browser's file selection dialog.
  5. Click Done.

    This makes the configuration file available for the export configuration and thus for the CSV export.

    Note

    Be aware that you need the Salesforce record ID as the value for the Configuration field of the export configuration.

    Find the file record ID in the file URL. To retrieve it, click next to the file record, then select View File Details.

Tip

Use a JSON validator before uploading the mapping to Salesforce.

JSON Configuration Example

Your business may require importing booking details in the DATEV accounting applications like Rechnungswesen pro. In order to generate a CSV export that is compliant to the DATEV CSV format for the posting batch (Buchungsstapel), you must

  • Depending on your business requirements, add custom fields to relevant objects
  • Create a specific JSON export configuration
Required fields for DATEV export configuration

The JSON example configuration requires additional custom fields:

Object Field API Name Formula/Data Type Description
Booking Period FiscalYearStart__c DATE(VALUE( ONB2__Year__c), 1, 1) The start of the fiscal year (Wirtschaftsjahresbeginn).
Booking Detail OffsettingAccountNo__c Text (255) The offsetting account (Gegenkonto, required value).
Booking Detail TaxKey__c from 1 July to 31 December 2020
CASE(TEXT(ROUND(ONB2__TaxRate__c*100, 0)), '0', '1', '5', '2', '16', '3', '7', '4', '19', '5', '49')
as of 1 January 2021
CASE(TEXT(ROUND(ONB2__TaxRate__c*100, 0)), '0', '1', '7', '2', '19', '3', '5', '4', '16', '5', '49')
The tax key (Steuerschlüssel).
With gross value booking, for example, when using automatic accounts (Automatikkonten) in DATEV, the tax key must be empty, that is, the formula must be null.
Business Entity ClientNumber__c Text (255) The client number (Mandantennummer) for this business entity.
Business Entity ConsultantNumber__c Text (255) The number of this business entity's tax consultant (Beraternummer).

For help about creating fields, see Managing Object Fields.

JSON export configuration for DATEV CSV

To produce a DATEV posting batch-compliant CSV file, you can use the following JSON export configuration.

Make sure to set the correct G/L account length (field 14 in headerRow1, 4 in this example).

{
    "rows": {
        "bookingDetailRow": [
            "ONB2__AbsoluteAmount__c",
            "ONB2__DebitCreditFlag__c",
            "",
            "",
            "",
            "",
            "AccountNo__c",
            "ONB2__BpAccountNo__c",
            "TaxKey__c",
            "BookingDate__c",
            "InvoiceNo__c",
            "",
            "",
            "BookingText__c",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            ""
        ],
        "headerRow1": [
            "EXTF",
            "700",
            "21",
            "Buchungsstapel",
            "7",
            "TimeStamp",
            "",
            "SV",
            "Admin",
            "",
            "businessentity.ConsultantNumber__c",
            "businessentity.ClientNumber__c",
            "FiscalYearStart__c",
            "4",
            "StartDate",
            "EndDate",
            "Rechnungen",
            "",
            "1",
            "0",
            "0",
            "EUR",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "JustOn"
        ],
        "headerRow2": [
            "Umsatz (ohne Soll/Haben-Kz)",
            "Soll/Haben-Kennzeichen",
            "WKZ Umsatz",
            "Kurs",
            "Basis-Umsatz",
            "WKZ Basis-Umsatz",
            "Konto",
            "Gegenkonto (ohne BU-Schlüssel)",
            "BU-Schlüssel",
            "Belegdatum",
            "Belegfeld 1",
            "Belegfeld 2",
            "Skonto",
            "Buchungstext",
            "Postensperre",
            "Diverse Adressnummer",
            "Geschäftspartnerbank",
            "Sachverhalt",
            "Zinssperre",
            "Beleglink",
            "Beleginfo - Art 1",
            "Beleginfo - Inhalt 1",
            "Beleginfo - Art 2",
            "Beleginfo - Inhalt 2",
            "Beleginfo - Art 3",
            "Beleginfo - Inhalt 3",
            "Beleginfo - Art 4",
            "Beleginfo - Inhalt 4",
            "Beleginfo - Art 5",
            "Beleginfo - Inhalt 5",
            "Beleginfo - Art 6",
            "Beleginfo - Inhalt 6",
            "Beleginfo - Art 7",
            "Beleginfo - Inhalt 7",
            "Beleginfo - Art 8",
            "Beleginfo - Inhalt 8",
            "KOST1 - Kostenstelle",
            "KOST2 - Kostenstelle",
            "Kost-Menge",
            "EU-Land u. UStID",
            "EU-Steuersatz",
            "Abw. Versteuerungsart",
            "Sachverhalt L+L",
            "Funktionsergänzung L+L",
            "BU 49 Hauptfunktionstyp",
            "BU 49 Hauptfunktionsnummer",
            "BU 49 Funktionsergänzung",
            "Zusatzinformation - Art 1",
            "Zusatzinformation- Inhalt 1",
            "Zusatzinformation - Art 2",
            "Zusatzinformation- Inhalt 2",
            "Zusatzinformation - Art 3",
            "Zusatzinformation- Inhalt 3",
            "Zusatzinformation - Art 4",
            "Zusatzinformation- Inhalt 4",
            "Zusatzinformation - Art 5",
            "Zusatzinformation- Inhalt 5",
            "Zusatzinformation - Art 6",
            "Zusatzinformation- Inhalt 6",
            "Zusatzinformation - Art 7",
            "Zusatzinformation- Inhalt 7",
            "Zusatzinformation - Art 8",
            "Zusatzinformation- Inhalt 8",
            "Zusatzinformation - Art 9",
            "Zusatzinformation- Inhalt 9",
            "Zusatzinformation - Art 10",
            "Zusatzinformation- Inhalt 10",
            "Zusatzinformation - Art 11",
            "Zusatzinformation- Inhalt 11",
            "Zusatzinformation - Art 12",
            "Zusatzinformation- Inhalt 12",
            "Zusatzinformation - Art 13",
            "Zusatzinformation- Inhalt 13",
            "Zusatzinformation - Art 14",
            "Zusatzinformation- Inhalt 14",
            "Zusatzinformation - Art 15",
            "Zusatzinformation- Inhalt 15",
            "Zusatzinformation - Art 16",
            "Zusatzinformation- Inhalt 16",
            "Zusatzinformation - Art 17",
            "Zusatzinformation- Inhalt 17",
            "Zusatzinformation - Art 18",
            "Zusatzinformation- Inhalt 18",
            "Zusatzinformation - Art 19",
            "Zusatzinformation- Inhalt 19",
            "Zusatzinformation - Art 20",
            "Zusatzinformation- Inhalt 20",
            "Stück",
            "Gewicht",
            "Zahlweise",
            "Forderungsart",
            "Veranlagungsjahr",
            "Zugeordnete Fälligkeit",
            "Skontotyp",
            "Auftragsnummer",
            "Buchungstyp",
            "Ust-Schlüssel (Anzahlungen)",
            "EU-Land (Anzahlungen)",
            "Sachverhalt L+L (Anzahlungen)",
            "EU-Steuersatz (Anzahlungen)",
            "Erlöskonto (Anzahlungen)",
            "Herkunft-Kz",
            "Leerfeld",
            "KOST-Datum",
            "Mandatsreferenz",
            "Skontosperre",
            "Gesellschaftername",
            "Beteiligtennummer",
            "Identifikationsnummer",
            "Zeichnernummer",
            "Postensperre bis",
            "Bezeichnung SoBil-Sachverhalt",
            "Kennzeichen SoBil-Buchung",
            "Festschreibung",
            "Leistungsdatum",
            "Datum Zuord.Steuerperiode"
        ]
    },
    "rowOrder": [
        "headerRow1",
        "headerRow2",
        "bookingDetailRow"
    ],
    "columns": {
        "headerRow1": {
            "index": {
                "1": {
                    "forceQuotes": true
                },
                "4": {
                    "forceQuotes": true
                },
                "8": {
                    "forceQuotes": true
                },
                "9": {
                    "forceQuotes": true
                },
                "17": {
                    "forceQuotes": true
                },
                "18": {
                    "forceQuotes": true
                },
                "22": {
                    "forceQuotes": true
                },
                "24": {
                    "forceQuotes": true
                },
                "27": {
                    "forceQuotes": true
                },
                "30": {
                    "forceQuotes": true
                },
                "31": {
                    "forceQuotes": true
                }
            }
        },
        "bookingDetailRow": {
            "index": {
                "1": {},
                "2": {
                    "forceQuotes": true
                },
                "3": {
                    "forceQuotes": true
                },
                "4": {},
                "5": {},
                "6": {
                    "forceQuotes": true
                },
                "7": {},
                "8": {},
                "9": {
                    "forceQuotes": true
                },
                "10": {
                    "options": {
                        "dateFormat": "ddMM"
                    }
                },
                "11": {
                    "forceQuotes": true
                },
                "12": {
                    "forceQuotes": true
                },
                "13": {},
                "14": {
                    "forceQuotes": true,
                    "length": 60
                },
                "15": {},
                "16": {
                    "forceQuotes": true
                },
                "17": {},
                "18": {},
                "19": {},
                "20": {
                    "forceQuotes": true
                },
                "21": {
                    "forceQuotes": true
                },
                "22": {
                    "forceQuotes": true
                },
                "23": {
                    "forceQuotes": true
                },
                "24": {
                    "forceQuotes": true
                },
                "25": {
                    "forceQuotes": true
                },
                "26": {
                    "forceQuotes": true
                },
                "27": {
                    "forceQuotes": true
                },
                "28": {
                    "forceQuotes": true
                },
                "29": {
                    "forceQuotes": true
                },
                "30": {
                    "forceQuotes": true
                },
                "31": {
                    "forceQuotes": true
                },
                "32": {
                    "forceQuotes": true
                },
                "33": {
                    "forceQuotes": true
                },
                "34": {
                    "forceQuotes": true
                },
                "35": {
                    "forceQuotes": true
                },
                "36": {
                    "forceQuotes": true
                },
                "37": {
                    "forceQuotes": true
                },
                "38": {
                    "forceQuotes": true
                },
                "39": {},
                "40": {
                    "forceQuotes": true
                },
                "41": {},
                "42": {
                    "forceQuotes": true
                },
                "43": {},
                "44": {},
                "45": {},
                "46": {},
                "47": {},
                "48": {
                    "forceQuotes": true
                },
                "49": {
                    "forceQuotes": true
                },
                "50": {
                    "forceQuotes": true
                },
                "51": {
                    "forceQuotes": true
                },
                "52": {
                    "forceQuotes": true
                },
                "53": {
                    "forceQuotes": true
                },
                "54": {
                    "forceQuotes": true
                },
                "55": {
                    "forceQuotes": true
                },
                "56": {
                    "forceQuotes": true
                },
                "57": {
                    "forceQuotes": true
                },
                "58": {
                    "forceQuotes": true
                },
                "59": {
                    "forceQuotes": true
                },
                "60": {
                    "forceQuotes": true
                },
                "61": {
                    "forceQuotes": true
                },
                "62": {
                    "forceQuotes": true
                },
                "63": {
                    "forceQuotes": true
                },
                "64": {
                    "forceQuotes": true
                },
                "65": {
                    "forceQuotes": true
                },
                "66": {
                    "forceQuotes": true
                },
                "67": {
                    "forceQuotes": true
                },
                "68": {
                    "forceQuotes": true
                },
                "69": {
                    "forceQuotes": true
                },
                "70": {
                    "forceQuotes": true
                },
                "71": {
                    "forceQuotes": true
                },
                "72": {
                    "forceQuotes": true
                },
                "73": {
                    "forceQuotes": true
                },
                "74": {
                    "forceQuotes": true
                },
                "75": {
                    "forceQuotes": true
                },
                "76": {
                    "forceQuotes": true
                },
                "77": {
                    "forceQuotes": true
                },
                "78": {
                    "forceQuotes": true
                },
                "79": {
                    "forceQuotes": true
                },
                "80": {
                    "forceQuotes": true
                },
                "81": {
                    "forceQuotes": true
                },
                "82": {
                    "forceQuotes": true
                },
                "83": {
                    "forceQuotes": true
                },
                "84": {
                    "forceQuotes": true
                },
                "85": {
                    "forceQuotes": true
                },
                "86": {
                    "forceQuotes": true
                },
                "87": {
                    "forceQuotes": true
                },
                "88": {},
                "89": {},
                "90": {},
                "91": {
                    "forceQuotes": true
                },
                "92": {},
                "93": {},
                "94": {},
                "95": {
                    "forceQuotes": true
                },
                "96": {
                    "forceQuotes": true
                },
                "97": {},
                "98": {
                    "forceQuotes": true
                },
                "99": {},
                "100": {},
                "101": {},
                "102": {
                    "forceQuotes": true
                },
                "103": {
                    "forceQuotes": true
                },
                "104": {},
                "105": {
                    "forceQuotes": true
                },
                "106": {},
                "107": {
                    "forceQuotes": true
                },
                "108": {},
                "109": {
                    "forceQuotes": true
                },
                "110": {
                    "forceQuotes": true
                },
                "111": {},
                "112": {
                    "forceQuotes": true
                },
                "113": {},
                "114": {},
                "115": {},
                "116": {}
            }
        }
    },
    "decimalPlaces": {},
    "markAsExported": false,
    "columnSeparator": ";",
    "useASCII": true,
    "filterLineBreaks": true,
    "useCRLF": true,
    "fileName": "EXTF_Buchungsstapel_[StartDate]_[EndDate].csv",
    "options": {
        "decimalSeparator": ",",
        "groupingSeparator": "",
        "dateFormat": "yyyyMMdd",
        "timeFormat": "yyyyMMddHHmmssSSS",
        "language": "de"
    }
}

JSON Configuration Keys

The JSON keys represent the possible configuration features. If a feature (like invoiceHeaderRow or columnSeparator) is missing from your JSON configuration, JustOn omits the feature during the export or falls back to default values.

Rows

Each row configuration includes a list of cell definitions. The cells can contain a reference to a field, a calculated value or a string (see table below).

The evaluation of a cell works in the following way:

(1) If the cell definition starts with an exclamation mark (like !Name), JustOn writes the string that follows the exclamation mark (Name) into the table cell.

(2) If the cell definition starts with a plus sign (like +GrandTotal__c), JustOn determines whether the value of the specified field (GrandTotal__c) is positive or negative, and writes the corresponding character or string as specified for positive values (signumPlus, for example, + or H) or negative values (signumMinus, for example, - or S) into the table cell. For details, see Debit/Credit Identifier.

The feature to determine whether a value is positive or negative is only available for fields of the type Currency.

(3) If the cell definition holds a valid field name (like GrandTotal__c, invoice.Date__c), JustOn writes the value of the field into the table cell.

  • The field name can be prefixed with an object qualifier (like invoice, lineItem, default). This is useful to refer to, for example, invoice fields in a line item or tax row.
  • If there is no object qualifier, JustOn will output the field of the default object.

(4) If the cell definition holds a valid calculated field name (like TaxAmount, UserName), JustOn writes the content of the calculated value into the table cell.

(5) If the cell definition holds a string that does not match any of the above-mentioned conditions, JustOn copies this string into the table cell. If the cell definition, for example, specifies a field Foo__c (which does not exist), JustOn writes Foo__c into the table cell.

The following table lists the individual row keys that can be included in the rows key for booking detail CSV exports.

Row Name JSON Key Notes
Header row headerRow Is only written once at the start of the CSV file, usually contains column labels.
Available objects: Booking Period (period), Business Entity (businessentity)
Available calculated fields: basic
You can define multiple header rows using headerRow1, headerRow2.
Booking detail row bookingDetailRow Is written for each booking detail.
Available objects: Booking Detail (default), Booking Period (period), Account (account), Invoice (invoice), Business Entity (businessentity)
Available calculated fields: basic

Row Order

The row order key holds all row keys that are to be written to the CSV and defines their sequence. If a row is defined but missing from row order, it will not be created.

Info

Do not forget to include the header row(s) in row order.

Calculated Values

Some fields contain the result of a formula. These values are referred to as calculated values. The following basic calculated fields are always available:

Calculated Field Description
CurrencyIsoCode The currency of the invoice. Works also in orgs with disabled multicurrency feature.
TimeStamp The timestamp of the file creation. It is formatted as DateTime.
Today The current date.
StartDate The export scope start date as selected in the UI.
EndDate The export scope end date as selected in the UI.
UserName The full name of the user who started the export.
OrganizationName The company name as configured in the company profile.
FileName The name of the export file.
PaymentDate The payment date specified in the UI. If empty, defaults to the current date.

Row Filter

The row filter key defines a filter used to exclude rows from the CSV. The filter consists of one or more clauses, where each clause is tested against the default object of the row (like the invoice line item in the line item row). If the clause matches the actual value, the row is not written to the CSV file.

You can define positive filters (value must be equal to be excluded from the CSV) or negative filters (value must be equal to be included in the CSV).

Exclude invoice line items of the type Tax Delta
"rowFilter":{
    "lineItemRow":[
        {
            "field":"Type__c",
            "equals":true,
            "value":"Tax Delta"
        }
    ]
}
Exclude invoice line items of types not equal to Product
"rowFilter":{
    "lineItemRow":[
        {
            "field":"Type__c",
            "equals":false,
            "value":"Product"
        }
    ]
}

Info

Multiple filters are combined using logical OR, that is, at least one filter must match to filter the row.

Columns

Using the columns key, you can, optionally, specify a fixed length, a content alignment setting and a padding character for a column.

"columns":{
    "invoiceRow":{
        "content": {
            "GrandTotal__c":{
               "align":"right",
               "length":12
            }
        },
        "index": {
            "1":{
               "padding":"*",
               "length":10
            }
        }
    }
}

Within the columns key, you first specify the row, like invoiceRow, lineItemRow or bookingDetailRow, for which you want to define a specific column format. The column itself can then be accessed

  • either by its content as defined in the row configuration using the content key and the corresponding cell reference, for example, GrandTotal__c,
  • or by its position using the index key and a position number, starting from 1, which allows you to clearly identify a column even if the row configuration is ambiguous.

The formatting options include:

Option Default Example Description
length 20 Specifies the size of the cell. If the size is too small for the cell text, the text is truncated, otherwise the cell is filled up using the padding character.
align left left
center
right
Specifies the alignment of the cell text.
padding space * Specifies the character used to fill the cell up to the specified length.
forceQuotes false true Determines whether to enclose the field contents in quote characters.
options derived from Date and Number Format Options see Date and Number Format Options Specifies the format options for this column.

Decimal Places

Using the decimalPlaces key, you can configure the number of decimal places to be written. The configuration specifies the object and the field, where the object key is either calculated or the API name of an object (like Invoice__c).

Decimal places configuration example

To display two decimal places for taxes and three decimal places for the grand total:

"decimalPlaces":{
    "calculated":{
        "TaxAmount":2
    },
    "Invoice__c":{
        "GrandTotal__c":3
    }
}

Note

The decimalPlaces key is required, but can be empty. If there is no decimal places configuration for a field, JustOn uses 2 by default.

Change Markers

Some business use cases require to mark the last row of entries that belong together according to a specific criterion. To this end, the CSV export supports the changeMarkers key (as of v2.74). If configured accordingly, JustOn will add the marker in the row before the value in a particular column changes.

The change marker configuration allows to specify the fields to be monitored for changes, the value for the marker, and the name of the marker column. The following table lists the corresponding configuration keys to be included in the changeMarkers key for CSV exports.

Option JSON Key Notes
Output column mark The actual name of the output column for the marker, which must be specified in the rows definition. Must be unique in the list of output columns.
Monitored CSV column cell The column to be monitored for changes. Can be any column name that is specified in the rows definition, regardless of the source of the value.
Marker value value The value to be written to the marker output column when a change in the monitored column is detected. Can be any text. The formatting options as specified in the columns configuration are applied.

You can specify multiple markers. In any case, JustOn will write all used markers in the last row of the CSV output.

Change marker configuration example

This example illustrates the changeMarkers key usage with a booking detail export to be sorted by the invoice number, where an X is set in the output column mark of each last row before the value of InvoiceNo changes:

"rows":{
    "bookingDetailRow": ["InvoiceNo__c","ONB2__Type__c","ONB2__Amount__c","mark"]
},
"changeMarkers":{
    "mark":{
        "cell": "InvoiceNo__c",
        "value": "X"
    }
}

This produces a booking detail CSV file like:

InvoiceNo;Type;Amount;mark
202100365;Revenue;100,00;
202100365;Revenue;100,00;
202100365;Revenue;100,00;X
202100366;Deferred;100,00;
202100366;Revenue;-100,00;X

Info

Using change markers is useful when combined with booking detail sorting (see Defining Booking Detail Sequence).

Mark as Exported

Generally, JustOn will not export booking details where Exported is true and Export Destination is not empty. The markAsExported key can be used to mark exported records accordingly, excluding them from further exports. The option defaults to false.

If set false, JustOn does not select the Exported checkbox upon exporting. Consequently, it will export all booking details of the current booking period in subsequent exports – including the previously exported records, because Exported is false.

If markAsExported is set true for booking detail exports, JustOn sets the Exported checkbox of the booking detail true and the Export Destination field to CSV upon exporting. As a result, subsequent exports will exclude the relevant booking details.

Column Separator

Use the columnSeparator key to specify the column separator for your CSV file. If not set, it defaults to , (comma).

Info

JustOn implements the quoting algorithm as described in RFC 4180. Cells are only quoted if they contain line breaks, quotes or the specified column separator.

Use ASCII

By default, JustOn writes the CSV files in UTF-8 character encoding. If your target system can only work with the ASCII character encoding, you can use useASCII and set it true. In this case, JustOn tries to replace special characters by their nearest ASCII neighbor, like ä becomes ae, ç becomes c and so on. All non-ASCII characters that could not be replaced are substituted by a . (full stop).

Use CRLF

By default, JustOn writes the CSV files with Unix line endings (LF). If your target system can only work with Windows line endings (CR+LF), you can use useCRLF and set it true.

Filter Line Breaks

By default, JustOn writes line breaks in fields. If your target system does not allow this, you can use filterLineBreaks and set it true. This replaces the line breaks with spaces.

Force Filename

By default, JustOn determines the name for the generated CSV file using the following pattern: timestamp + configuration setting name (for example, 20180101102345_Invoices.csv). You can use the key fileName in order to change this behavior.

The following placeholders are available to model the file name: [TimeStamp], [Today], [UserName], [OrganizationName], [StartDate], [EndDate], [SettingName].

A file name pattern like EXP_Invoices_[StartDate]_[EndDate].csv results in the following file name: EXP_Invoices_20180101_20180131.csv.

A file name pattern like EXTF_Buchungsstapel_[StartDate]_[EndDate].csv results in the following file name: EXTF_Buchungsstapel_20180101_20180131.csv.

Debit/Credit Identifier

Your accounting system may require an indicator of whether a currency value is considered a debit or a credit. To this end, you add the relevant field in the corresponding row configuration prefixed with +, like +GrandTotal__c or +Amount__c. In addition, you configure the character or string to be used as the actual indicator using the signumPlus and signumMinus keys as part of the Date and Number Format Options, depending on your target system (for example, + or H, or - or S, respectively).

If set, JustOn determines whether the value of the specified field is positive or negative, and writes the given character or string as specified for credits or debits into the table cell.

Date and Number Format Options

The options key specifies the display formats for date, time and number data types.

Info

If an option or the entire options key is missing, JustOn uses the default values.

Format Option Default Value Example Output Description
decimalSeparator . 12.33 Specifies the character used as the decimal separator for all number fields.
groupingSeparator empty 1 234 567.89 Specifies the character used as the grouping separator for all number fields.
Use SPACE to produce a blank separator.
dateFormat yyyy/MM/dd 2017/11/30 Specifies the format for all date fields.
For details about supported date and time formats, refer to the Simple Date Format syntax in the Java Documentation.
timeFormat hh:mm a 04:35 AM Specifies the format for displaying time values.
For details about supported date and time formats, refer to the Simple Date Format syntax in the Java Documentation.
language en January Specifies the language used to rewrite month names when using long formats, like MMM becomes Nov or MMMM becomes November.
signumPlus + + Specifies the character (or the string) to indicate positive values, for example, + or H, depending on the target system.
Use SPACE to produce a blank character.
Works only with fields of the type Currency.
signumMinus - - Specifies the character (or the string) to indicate negative values, for example, - or S, depending on the target system.
Use SPACE to produce a blank character.
Works only with fields of the type Currency.

Aggregating Booking Details

You can define rules to aggregate multiple booking details into one booking detail using the key aggregationRules.

This key specifies a map with the key bookingDetailRow. The value of bookingDetailRow is a list of aggregation configurations, where each configuration is a map that includes the following keys:

Key Description Example
fieldsToAggregate Specifies the field to be aggregated and the aggregation function. The functions include SUM, MIN, MAX, LAST, CLEAR.
Fields to be aggregated must be defined as a cell in the booking detail row configuration.
The example can be used as is and may be sufficient in most use cases.
{"Amount__c": "SUM"}
conditions Defines the matching conditions for the fields: booking details that match the specified values are aggregated.
Only text values are supported.
{"Type__c": "Revenue"}
groupBy Specifies a list of fields that define a grouping key: booking details with the same values in the specified fields are grouped. ["Center__c"]
Aggregate Revenue booking details, grouped by Center
"aggregationRules": {
    "bookingDetailRow": [{"fieldsToAggregate": {"Amount__c": "SUM"},
                          "conditions": {"Type__c": "Revenue"},
                          "groupBy": ["Center__c"]}]
}

Be aware of the following specifics:

  • The aggregated booking details are written to the end of the CSV file.
  • Booking details that do not match the specified conditions are output as usual.
  • When using more than one rule for bookingDetailRow, they must not capture the same booking details because there is only one rule applied for each booking detail.
  • The condition matching algorithm supports text values only.

Defining Booking Detail Sequence

By default, there is no specific sort order for exported booking details. You can, however, define a custom sequence. To do so, you create the custom field CustomSequence__c on the Booking Detail object. You can then specify your sorting criterion as a formula, which produces a value that is sorted alphabetically or numerically.

  1. Navigate to the fields list of the Booking Detail object.
  2. Create the following new field.

    API Name Data Type Description
    CustomSequence Formula Specifies the sorting criterion, returns a value that is sorted in alphanumeric order.
    Formula(Text) values are sorted alphabetically
    Formula(Date) values are sorted by date

    For help about creating fields, see Managing Object Fields.

The following examples illustrate the behavior:

Criterion Formula Return Type Example Value
Sort by invoice number ONB2__InvoiceNo__c Text 2021-00017
Sort by invoice number, then by type InvoiceNo__c + TEXT(ONB2__Type__c) Text 2021-00017Revenue
Sort by date ONB2__BookingDate__c Date 2021-05-12

Displaying Export File List

By default, the exported files are accessible on Salesforce's Files tab. If required, you can enable the display of the exported CSV files on the Booking Period detail page. To do so:

  1. Navigate to the object management settings of the Booking Period object.
  2. Click Page Layouts.
  3. In the Booking Period Layout row, click to open the action menu, then select Edit.
  4. From the Related Lists palette, drag the Files list to the Related Lists section.
  5. Click Save.