action.skip

Configuring Invoice CSV Export

← Accounting System Transfer

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.

Invoice export history

In addition to the actual export file (CSV or SEPA XML, according to the applied export setting), the export operation produces an Invoice Export History record for each exported invoice. The history holds the following information:

  • Export time
  • Effective payment due date (for installment invoices)
  • File name and link to the exported file
  • Flag stating whether a payment balance has been created
  • Flag that controls whether to prevent repeated exports
  • Flag stating whether an invoice has been exported more than once (forced export)

    The Times Exported field on the invoice shows the export count.

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.
    Create Payments true
    false
    Determines whether to create payment balances on the invoices upon exporting, which sets the invoices Paid or Settled.
    Users can overwrite this option manually when starting the export.
    JustOn evaluates this setting only when using the Invoice CSV format.
    Format Invoice CSV Determines the export format and source.
    Select Condition Date__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 invoices in the scope.
    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.

    file_dist_amazon
    Creating an export settings record for producing invoice CSV files

  5. Click Save.

Info

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

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 invoices 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 Examples

Invoice Data

The following code shows an example CSV file configuration for the Invoice CSV format, illustrating the possible features.

{
    "rows":{
        "headerRow":["Custom CSV Export of Open Invoices","TimeStamp","UserName"],
        "footerRow":["Custom footer","NumInvoices","TotalNet__c","TotalTax__c","+TotalNet__c"],
        "invoiceHeaderRow":["Number","Date","Amount"],
        "invoiceRow":["Name","Date__c","GrandTotal__c"],
        "lineItemRow":["Quantity__c","PosTotalNet__c","PosTotalTax__c","invoice.PaymentDueDate__c","GLAccount__c","Center__c"],
        "singleTaxDetailRow":["TaxAmount","TaxRate","TaxRule"],
        "combinedTaxDetailRow":["!COMBINED_TAX", "TaxAmount","TaxRate","TaxRule"]
    },
    "rowOrder":["headerRow","invoiceHeaderRow","invoiceRow","lineItemRow","combinedTaxDetailRow","footerRow"],
    "rowFilter":{
        "lineItemRow":[
            {
                "field":"Type__c",
                "equals":true,
                "value":"Information"
            }
        ]
    },
    "columns":{
        "invoiceRow":{
            "content": {
                "GrandTotal__c":{
                   "align":"right",
                   "length":12
                }
            },
            "index": {
                "1":{
                   "padding":"*",
                   "length":10
                }
            }
        }
    },
    "aggregate":{
        "Invoice__c":["TotalNet__c","TotalTax__c"]
    },
    "decimalPlaces":{
        "calculated":{
            "TaxAmount":2
        },
        "Invoice__c":{
            "GrandTotal__c":3
        },
        "InvoiceLineItem__c":{
            "Quantity__c":0
        }
    },
    "markAsExported":false,
    "columnSeparator":";",
    "useASCII":false,
    "useCRLF":false,
    "fileName":"",
    "options":{
        "decimalSeparator":".",
        "groupingSeparator":"'",
        "dateFormat":"yyyy-MM-dd",
        "timeFormat":"HH:mm",
        "language":"en",
        "signumPlus":"+",
        "signumMinus":"-"
    },
    "filterZeroTaxes":true
}

This produces an invoice CSV file like:

Custom CSV Export of Open Invoices;12:06;ONB49-CT Developer
Number;Date;Amount
2018-00041;2018-06-01;     605.000
1;200.00;42.00;2018-07-01;;
4.00;2.00;R2
38.00;19.00;R19
1;300.00;63.00;2018-07-01;;
6.00;2.00;R2
57.00;19.00;R19
COMBINED_TAX;10.00;2.00;R2
COMBINED_TAX;95.00;19.00;R19
Custom footer;1;500.00;105.00;+

Master Data

You can use JustOn's invoice CSV export to export master data to your accounting system. Master data refers to account-related data like customer name, address, tax ID, debtor/creditor number, etc. Accounting systems usually handle master data separately and can therefore not import it together with invoicing data. For transferring master data, you produce specifically configured CSV files from invoices.

As an example, take the master data exchange format for DATEV. In order to generate a CSV export that is compliant to the DATEV CSV format for debtors/creditors, you must

  • Add custom fields to relevant objects
  • Create a specific JSON export configuration

The JSON configuration for the DATEV debtors/creditors export requires additional custom fields:

Object Field Data Type Possible Value/Description
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).
Invoice Export_DebtorNo__c Formula (Text) ONB2__Account__r.DebtorNo__c
Invoice Export_AccountAccountName__c Formula (Text) IF (ONB2__Account__r.IsPersonAccount, ONB2__Account__r.FirstName + " " + ONB2__Account__r.LastName, ONB2__Account__r.Name)
Invoice Export_AccountVATID__c Formula (Text) ONB2__Account__r.VatNumber__c
Invoice Export_AccountBillingStreet__c Formula (Text) ONB2__Account__r.BillingStreet
Invoice Export_AccountBillingPostalCode__c Formula (Text) ONB2__Account__r.BillingPostalCode
Invoice Export_AccountBillingCity__c Formula (Text) ONB2__Account__r.BillingCity
Invoice Export_Account_IBAN__c Formula (Text) ONB2__Account__r.BankAccount__c

To produce a DATEV debtors/creditors-compliant CSV file, you can use the following JSON export configuration.

Make sure to set the correct start of the fiscal year (field 13 in headerRow1, 20200101 in this example) as well as the correct G/L account length (field 14 in headerRow1, 4 in this example).

{
    "rows":{
        "headerRow1": [
            "EXTF",
            "700",
            "16",
            "Debitoren/Kreditoren",
            "4",
            "TimeStamp",
            "",
            "RE",
            "UserName",
            "",
            "businessentity.ConsultantNumber__c",
            "businessentity.ClientNumber__c",
            "20200101",
            "4",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "" 
        ],
        "headerRow2": [
            "Konto",
            "Name (Adressattyp Unternehmen)",
            "Unternehmensgegenstand",
            "Name (Adressattyp natürl. Person)",
            "Vorname (Adressattyp natürl. Person)",
            "Name (Adressattyp keine Angabe)",
            "Adressattyp",
            "Kurzbezeichnung",
            "EU-Land",
            "EU-UStID",
            "Anrede",
            "Titel/Akad. Grad",
            "Adelstitel",
            "Namensvorsatz",
            "Adressart",
            "Straße",
            "Postfach",
            "Postleitzahl",
            "Ort",
            "Land",
            "Versandzusatz",
            "Adresszusatz",
            "Abweichende Anrede",
            "Abw. Zustellbezeichnung 1",
            "Abw. Zustellbezeichnung 2",
            "Kennz. Korrespondenzadresse",
            "Adresse Gültig von",
            "Adresse Gültig bis",
            "Telefon",
            "Bemerkung (Telefon)",
            "Telefon GL",
            "Bemerkung (Telefon GL)",
            "E-Mail",
            "Bemerkung (E-Mail)",
            "Internet",
            "Bemerkung (Internet)",
            "Fax",
            "Bemerkung (Fax)",
            "Sonstige",
            "Bemerkung (Sonstige)",
            "Bankleitzahl 1",
            "Bankbezeichnung 1",
            "Bank-Kontonummer 1",
            "Länderkennzeichen 1",
            "IBAN-Nr. 1",
            "IBAN1 korrekt",
            "SWIFT-Code 1",
            "Abw. Kontoinhaber 1",
            "Kennz. Hauptbankverb. 1",
            "Bankverb 1 Gültig von",
            "Bankverb 1 Gültig bis",
            "Bankleitzahl 2",
            "Bankbezeichnung 2",
            "Bank-Kontonummer 2",
            "Länderkennzeichen 2",
            "IBAN-Nr. 2",
            "IBAN2 korrekt",
            "SWIFT-Code 2",
            "Abw. Kontoinhaber 2",
            "Kennz. Hauptbankverb. 2",
            "Bankverb 2 Gültig von",
            "Bankverb 2 Gültig bis",
            "Bankleitzahl 3",
            "Bankbezeichnung 3",
            "Bank-Kontonummer 3",
            "Länderkennzeichen 3",
            "IBAN-Nr. 3",
            "IBAN3 korrekt",
            "SWIFT-Code 3",
            "Abw. Kontoinhaber 3",
            "Kennz. Hauptbankverb. 3",
            "Bankverb 3 Gültig von",
            "Bankverb 3 Gültig bis",
            "Bankleitzahl 4",
            "Bankbezeichnung 4",
            "Bank-Kontonummer 4",
            "Länderkennzeichen 4",
            "IBAN-Nr. 4",
            "IBAN4 korrekt",
            "SWIFT-Code 4",
            "Abw. Kontoinhaber 4",
            "Kennz. Hauptbankverb. 4",
            "Bankverb 4 Gültig von",
            "Bankverb 4 Gültig bis",
            "Bankleitzahl 5",
            "Bankbezeichnung 5",
            "Bank-Kontonummer 5",
            "Länderkennzeichen 5",
            "IBAN-Nr. 5",
            "IBAN5 korrekt",
            "SWIFT-Code 5",
            "Abw. Kontoinhaber 5",
            "Kennz. Hauptbankverb. 5",
            "Bankverb 5 Gültig von",
            "Bankverb 5 Gültig bis",
            "Leerfeld",
            "Briefanrede",
            "Grußformel",
            "Kundennummer",
            "Steuernummer",
            "Sprache",
            "Ansprechpartner",
            "Vertreter",
            "Sachbearbeiter",
            "Diverse-Konto",
            "Ausgabeziel",
            "Währungssteuerung",
            "Kreditlimit (Debitor)",
            "Zahlungsbedingung",
            "Fälligkeit in Tagen (Debitor)",
            "Skonto in Prozent (Debitor)",
            "Kreditoren-Ziel 1 Tg.",
            "Kreditoren-Skonto 1 %",
            "Kreditoren-Ziel 2 Tg.",
            "Kreditoren-Skonto 2 %",
            "Kreditoren-Ziel 3 Brutto Tg.",
            "Kreditoren-Ziel 4 Tg.",
            "Kreditoren-Skonto 4 %",
            "Kreditoren-Ziel 5 Tg.",
            "Kreditoren-Skonto 5 %",
            "Mahnung",
            "Kontoauszug",
            "Mahntext 1",
            "Mahntext 2",
            "Mahntext 3",
            "Kontoauszugstext",
            "Mahnlimit Betrag",
            "Mahnlimit %",
            "Zinsberechnung",
            "Mahnzinssatz 1",
            "Mahnzinssatz 2",
            "Mahnzinssatz 3",
            "Lastschrift",
            "Verfahren",
            "Mandantenbank",
            "Zahlungsträger",
            "Indiv. Feld 1",
            "Indiv. Feld 2",
            "Indiv. Feld 3",
            "Indiv. Feld 4",
            "Indiv. Feld 5",
            "Indiv. Feld 6",
            "Indiv. Feld 7",
            "Indiv. Feld 8",
            "Indiv. Feld 9",
            "Indiv. Feld 10",
            "Indiv. Feld 11",
            "Indiv. Feld 12",
            "Indiv. Feld 13",
            "Indiv. Feld 14",
            "Indiv. Feld 15",
            "Abweichende Anrede (Rechnungsadresse)",
            "Adressart (Rechnungsadresse)",
            "Straße (Rechnungsadresse)",
            "Postfach (Rechnungsadresse)",
            "Postleitzahl (Rechnungsadresse)",
            "Ort (Rechnungsadresse)",
            "Land (Rechnungsadresse)",
            "Versandzusatz (Rechnungsadresse)",
            "Adresszusatz (Rechnungsadresse)",
            "Abw. Zustellbezeichnung 1 (Rechnungsadresse)",
            "Abw. Zustellbezeichnung 2 (Rechnungsadresse)",
            "Adresse Gültig von (Rechnungsadresse)",
            "Adresse Gültig bis (Rechnungsadresse)",
            "Bankleitzahl 6",
            "Bankbezeichnung 6",
            "Bank-Kontonummer 6",
            "Länderkennzeichen 6",
            "IBAN-Nr. 6",
            "IBAN6 korrekt",
            "SWIFT-Code 6",
            "Abw. Kontoinhaber 6",
            "Kennz. Hauptbankverb. 6",
            "Bankverb 6 Gültig von",
            "Bankverb 6 Gültig bis",
            "Bankleitzahl 7",
            "Bankbezeichnung 7",
            "Bank-Kontonummer 7",
            "Länderkennzeichen 7",
            "IBAN-Nr. 7",
            "IBAN7 korrekt",
            "SWIFT-Code 7",
            "Abw. Kontoinhaber 7",
            "Kennz. Hauptbankverb. 7",
            "Bankverb 7 Gültig von",
            "Bankverb 7 Gültig bis",
            "Bankleitzahl 8",
            "Bankbezeichnung 8",
            "Bank-Kontonummer 8",
            "Länderkennzeichen 8",
            "IBAN-Nr. 8",
            "IBAN8 korrekt",
            "SWIFT-Code 8",
            "Abw. Kontoinhaber 8",
            "Kennz. Hauptbankverb. 8",
            "Bankverb 8 Gültig von",
            "Bankverb 8 Gültig bis",
            "Bankleitzahl 9",
            "Bankbezeichnung 9",
            "Bank-Kontonummer 9",
            "Länderkennzeichen 9",
            "IBAN-Nr. 9",
            "IBAN9 korrekt",
            "SWIFT-Code 9",
            "Abw. Kontoinhaber 9",
            "Kennz. Hauptbankverb. 9",
            "Bankverb 9 Gültig von",
            "Bankverb 9 Gültig bis",
            "Bankleitzahl 10",
            "Bankbezeichnung 10",
            "Bank-Kontonummer 10",
            "Länderkennzeichen 10",
            "IBAN-Nr. 10",
            "IBAN10 korrekt",
            "SWIFT-Code 10",
            "Abw. Kontoinhaber 10",
            "Kennz. Hauptbankverb. 10",
            "Bankverb 10 Gültig von",
            "Bankverb 10 Gültig bis",
            "Nummer Fremdsystem",
            "Insolvent",
            "Mandatsreferenz 1",
            "Mandatsreferenz 2",
            "Mandatsreferenz 3",
            "Mandatsreferenz 4",
            "Mandatsreferenz 5",
            "Mandatsreferenz 6",
            "Mandatsreferenz 7",
            "Mandatsreferenz 8",
            "Mandatsreferenz 9",
            "Mandatsreferenz 10",
            "Verknüpftes OPOS-Konto",
            "Mahnsperre bis",
            "Lastschriftsperre bis",
            "Zahlungssperre bis",
            "Gebührenberechnung",
            "Mahngebühr 1",
            "Mahngebühr 2",
            "Mahngebühr 3",
            "Pauschalenberechnung",
            "Verzugspauschale 1",
            "Verzugspauschale 2",
            "Verzugspauschale 3"
        ],
        "invoiceRow":[
            "Export_DebtorNo__c",
            "Export_AccountAccountName__c",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "Export_AccountVATID__c",
            "",
            "",
            "",
            "",
            "",
            "Export_AccountBillingStreet__c",
            "",
            "Export_AccountBillingPostalCode__c",
            "Export_AccountBillingCity__c",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "Export_Account_IBAN__c",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            ""
        ]
    },
    "rowOrder":["headerRow1","headerRow2","invoiceHeaderRow","invoiceRow"],
    "columns": {
        "headerRow1": {
            "index": {
                "1":{"forceQuotes":true}
            }
        },
        "invoiceRow": {
            "index": {
                "2":{"forceQuotes":true},
                "3":{"forceQuotes":true},
                "4":{"forceQuotes":true},
                "5":{"forceQuotes":true},
                "6":{"forceQuotes":true},
                "7":{"forceQuotes":true},
                "8":{"forceQuotes":true},
                "9":{"forceQuotes":true},
                "10":{"forceQuotes":true},
                "11":{"forceQuotes":true},
                "12":{"forceQuotes":true},
                "13":{"forceQuotes":true},
                "14":{"forceQuotes":true},
                "15":{"forceQuotes":true},
                "16":{"forceQuotes":true},
                "17":{"forceQuotes":true},
                "18":{"forceQuotes":true},
                "19":{"forceQuotes":true},
                "20":{"forceQuotes":true},
                "21":{"forceQuotes":true},
                "22":{"forceQuotes":true},
                "23":{"forceQuotes":true},
                "24":{"forceQuotes":true},
                "25":{"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":{"forceQuotes":true},
                "40":{"forceQuotes":true},
                "41":{"forceQuotes":true},
                "42":{"forceQuotes":true},
                "43":{"forceQuotes":true},
                "44":{"forceQuotes":true},
                "45":{"forceQuotes":true},
                "46":{"forceQuotes":true},
                "47":{"forceQuotes":true},
                "48":{"forceQuotes":true},
                "49":{"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},
                "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},
                "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},
                "85":{"forceQuotes":true},
                "86":{"forceQuotes":true},
                "87":{"forceQuotes":true},
                "88":{"forceQuotes":true},
                "89":{"forceQuotes":true},
                "90":{"forceQuotes":true},
                "91":{"forceQuotes":true},
                "92":{"forceQuotes":true},
                "93":{"forceQuotes":true},
                "96":{"forceQuotes":true},
                "97":{"forceQuotes":true},
                "98":{"forceQuotes":true},
                "99":{"forceQuotes":true},
                "100":{"forceQuotes":true},
                "102":{"forceQuotes":true},
                "103":{"forceQuotes":true},
                "104":{"forceQuotes":true},
                "107":{"forceQuotes":true},
                "133":{"forceQuotes":true},
                "134":{"forceQuotes":true},
                "136":{"forceQuotes":true},
                "137":{"forceQuotes":true},
                "138":{"forceQuotes":true},
                "139":{"forceQuotes":true},
                "140":{"forceQuotes":true},
                "141":{"forceQuotes":true},
                "142":{"forceQuotes":true},
                "143":{"forceQuotes":true},
                "144":{"forceQuotes":true},
                "145":{"forceQuotes":true},
                "146":{"forceQuotes":true},
                "147":{"forceQuotes":true},
                "148":{"forceQuotes":true},
                "149":{"forceQuotes":true},
                "150":{"forceQuotes":true},
                "151":{"forceQuotes":true},
                "152":{"forceQuotes":true},
                "153":{"forceQuotes":true},
                "154":{"forceQuotes":true},
                "155":{"forceQuotes":true},
                "156":{"forceQuotes":true},
                "157":{"forceQuotes":true},
                "158":{"forceQuotes":true},
                "159":{"forceQuotes":true},
                "160":{"forceQuotes":true},
                "161":{"forceQuotes":true},
                "162":{"forceQuotes":true},
                "165":{"forceQuotes":true},
                "166":{"forceQuotes":true},
                "167":{"forceQuotes":true},
                "168":{"forceQuotes":true},
                "169":{"forceQuotes":true},
                "170":{"forceQuotes":true},
                "171":{"forceQuotes":true},
                "172":{"forceQuotes":true},
                "173":{"forceQuotes":true},
                "176":{"forceQuotes":true},
                "177":{"forceQuotes":true},
                "178":{"forceQuotes":true},
                "179":{"forceQuotes":true},
                "180":{"forceQuotes":true},
                "181":{"forceQuotes":true},
                "182":{"forceQuotes":true},
                "183":{"forceQuotes":true},
                "184":{"forceQuotes":true},
                "187":{"forceQuotes":true},
                "188":{"forceQuotes":true},
                "189":{"forceQuotes":true},
                "190":{"forceQuotes":true},
                "191":{"forceQuotes":true},
                "192":{"forceQuotes":true},
                "193":{"forceQuotes":true},
                "194":{"forceQuotes":true},
                "195":{"forceQuotes":true},
                "198":{"forceQuotes":true},
                "199":{"forceQuotes":true},
                "200":{"forceQuotes":true},
                "201":{"forceQuotes":true},
                "202":{"forceQuotes":true},
                "203":{"forceQuotes":true},
                "204":{"forceQuotes":true},
                "205":{"forceQuotes":true},
                "206":{"forceQuotes":true},
                "209":{"forceQuotes":true},
                "210":{"forceQuotes":true},
                "211":{"forceQuotes":true},
                "212":{"forceQuotes":true},
                "213":{"forceQuotes":true},
                "214":{"forceQuotes":true},
                "215":{"forceQuotes":true},
                "216":{"forceQuotes":true},
                "217":{"forceQuotes":true},
                "220":{"forceQuotes":true},
                "222":{"forceQuotes":true},
                "223":{"forceQuotes":true},
                "224":{"forceQuotes":true},
                "225":{"forceQuotes":true},
                "226":{"forceQuotes":true},
                "227":{"forceQuotes":true},
                "228":{"forceQuotes":true},
                "229":{"forceQuotes":true},
                "230":{"forceQuotes":true},
                "231":{"forceQuotes":true}

            }
        }
    },
    "useASCII":false,
    "markAsExported":false,
    "columnSeparator":";",
    "useCRLF":true,
    "fileName": "EXTF_Export_[SettingName]_[Today].csv",
    "aggregate":{},
    "options":{
            "decimalSeparator":",",
            "groupingSeparator":".",
            "dateFormat":"ddMMyyyy",
            "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 invoice 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: Business Entity (businessentity)
Available calculated fields: basic
Since a header row is written only once per export file, the result is only useful if all involved invoices relate to the same business entity. Otherwise, the result will be unpredictable. You must make sure to configure the export accordingly.
You can define multiple header rows using headerRow1, headerRow2.
Footer row footerRow Is only written once at the end of the CSV file, usually contains aggregated values.
Available objects: none
Available calculated fields: basic, aggregated invoice values
You can define multiple footer rows using footerRow1, footerRow2.
Invoice Header Row invoiceHeaderRow Is written for each invoice.
Available objects: Invoice, Business Entity (businessentity)
Available calculated fields: basic
Invoice Row invoiceRow Is written for each invoice.
Available objects: Invoice, Business Entity (businessentity)
Available calculated fields: basic
Line Item Row lineItemRow Is written for each invoice line item. The invoice line items are sorted by sequence.
Invoice line items of the type Information are omitted.
Available objects: Invoice (invoice), Invoice Line Item (default)
Available calculated fields: basic
Combined Tax Row combinedTaxRow Is written for each individual tax rate (line item). The tax amount is aggregated per tax rate. The tax amount is available as signed or absolute value. Taxes are sorted by tax rate.
Available objects: Invoice (invoice), Invoice Line Item (default)
Available fields: basic, TaxAmount, TaxAmountAbs, TaxRate, TaxRule, TaxCode, TaxProvider
Combined Tax Detail Row combinedTaxDetailRow Is written for each individual tax rate (tax detail). The tax amount is aggregated per tax rate. The tax amount is available as signed or absolute value. Taxes are sorted by tax rate.
Available objects: Invoice (invoice), Invoice Line Item (default)
Available fields: basic, TaxAmount, TaxAmountAbs, TaxRate, TaxRule, TaxCode, TaxProvider
Single Tax Row singleTaxRow Is written for each line item. The tax amount is available as signed or absolute value.
Available objects: Invoice (invoice), Invoice Line Item (default)
Available fields: basic, TaxAmount, TaxAmountAbs, TaxRate, TaxRule, TaxCode, TaxProvider
Single Tax Detail Row singleTaxDetailRow Is written for each tax detail. The tax amount is available as signed or absolute value.
Available objects: Invoice (invoice), Invoice Line Item (default)
Available fields: basic, TaxAmount, TaxAmountAbs, TaxRate, TaxRule, TaxCode, TaxProvider

Info

When using the row keys singleTaxRow or singleTaxDetailRow in the rows configuration, JustOn writes the tax row(s) individually for each invoice line item. That is, the row order configuration does not affect these keys.

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.

Aggregated Values

JustOn can aggregate field values of all exported invoices. The aggregated values are available as calculated fields for the footer row.

Using the aggregate key, you configure the field values to be aggregated.

Aggregate the invoice fields TotalNet__c and TotalTax__c:

"aggregate":{
    "Invoice__c":["TotalNet__c","TotalTax__c"]
}

Info

Only fields of the type Currency are available for aggregation.

Currently, only invoice fields can be configured.

In addition, the following aggregated values are always available and do not need to be configured:

Aggregated Field Description
NumInvoices The number of invoices in the export file.

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

Mark as Exported

The markAsExported key can be used to exclude already exported records from further exports. The option defaults to false.

If set false, JustOn will consequently ignore any Invoice Export History records and export all invoices as defined by the export scope (export period and list selection, as explained in Exporting Invoices) in subsequent exports.

If markAsExported is set true, JustOn will exclude invoices with an Invoice Export History record that refers to the same export setting from further exports. Users can, however, temporarily disable this setting. Selecting the Force Export option on the Export Invoice page allows to export previously exported invoices again.

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.

Filter Empty Tax Rows

If you have configured the output of tax rows (see Rows), JustOn writes the corresponding tax rows to the exported CSV files irrespective of whether there are taxes or not. Your business or your target system may, however, not allow this – you want the tax row to be omitted if the tax is empty. To this end, you can use filterZeroTaxes and set it true.