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 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. Format Booking Detail CSVDetermines the export format and source. Select Condition Type__c IN ('Revenue','Tax') AND BookingDate__c = THIS_YEARSpecifies 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 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.The other fields of the export setting are ignored and can be left blank. The corresponding configuration is taken from the JSON configuration.
Creating an export settings record for producing booking detail CSV files -
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.
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 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.
-
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 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 and custom settings
- Create a specific JSON export configuration
Required fields for DATEV export configuration
The JSON example configuration requires additional custom fields:
| Source | 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 |
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
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
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.
- Navigate to the fields list of the Booking Detail object.
-
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 alphabeticallyFormula(Date)values are sorted by dateFor 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:
- Navigate to the object management settings of the Booking Period object.
- Click Page Layouts.
- In the Booking Period Layout row, click to open the action menu, then select Edit.
- From the Related Lists palette, drag the Files list to the Related Lists section.
- Click Save.