Configuring Invoice 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.
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 Exportedfield on the invoice shows the export count.
Configuring the CSV export comprises two tasks:
- Defining a custom setting Export Settings
- Defining a configuration (in JSON notation) for the CSV file to be produced
Defining Export Settings
Depending on your organization's requirements, you must define an export configuration for CSV exports.
-
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 Export Settings.
- Click New.
-
Specify the details as necessary.
Field Possible Values Description Name SAP_CSV
DATEVA unique name for the custom setting record, will be selectable when exporting invoice data. Active truefalseDetermines 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 truefalseDetermines whether to create payment balances on the invoices upon exporting, which sets the invoices PaidorSettled.
Users can overwrite this option manually when starting the export.
JustOn evaluates this setting only when using theInvoice CSVformat.Format Invoice CSVDetermines the export format and source. Select Condition Date__c = THIS_YEARSpecifies 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 ExportSpecifies the Nameof 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 caseExportand the backendAmazonS3orGoogleDrive.
If left empty, the file will only be available on Salesforce's Files tab.
Creating an export settings record for producing invoice CSV files -
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.
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.
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
Nameof the file distribution record must be specified as theTargetin the export settings record.
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.
-
Create the configuration and save it as a file with a
.jsonextension.For details about the configuration definition, see JSON Configuration Example and JSON Configuration Keys.
-
Open the Salesforce Files tab.
- Click Upload Files.
- Select the file using your browser's file selection dialog.
-
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
Configurationfield 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
contentkey and the corresponding cell reference, for example,GrandTotal__c, - or by its position using the
indexkey and a position number, starting from1, 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 |
leftcenterright |
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.