Data exchange definitions

Data exchange is a standard feature in Business Central. To learn more about data exchange and data exchange definitions, go to Set up data exchange definitions.

Data exchange definitions are fundamental to import and process data in usage-based billing. This article summarizes the key aspects.

The data exchange definition example in this article refers to an import file for usage data. It's a CSV file in UTF-8 encoding with column separators as semicolons. To learn more, go to Import data in usage-based billing.

Definition for data import

For billing, you must first import and process data. To learn more, go to Imports and processing. For Business Central to extract the data from the CSV file, it needs the data's schema. The schema is available on the usage data supplier.

When you set up the field mappings, ensure they cover at least the fields listed under Minimum data requirements. Without these fields, Business Central can't process the usage data.

Note

Usage-based billing provides the USAGEBASED data exchange definition as an example. You can use the definition, but you must adapt it to your setup. You can import and export data exchange definitions on the Data Exchange Definition page by using the Import Data Exchange Definition and Export Data Exchange Definition actions.

The Data Exchange Definitions page has the following FastTabs:

  • General
  • Line Definitions
  • Column Definitions

Note

Although the Line Definitions FastTab is higher on the page, fill in the fields on the Column Definitions FastTab before you enter information on the Line Definitions FastTab.

Settings on the General FastTab

These settings affect the import file. The following table describes the important fields.

FieldDescription
File TypeFor importing usage data, businesses typically choose Variable Text.
TypeFor importing usage data, businesses typically choose Generic Import.
File EncodingThe encoding of unicode characters for the import file.
Column SeparatorSpecifies the character that separates the columns in the import file. This is only relevant for the Variable Text file type.
Header LinesSets how many header lines the import file contains. This ensures that the header data isn't imported.
Reading/Writing XMLportFor importing usage data, choose 1220.

Settings on the Line Definitions FastTab

On this FastTab, you only have to define one line, so that you can use the Field Mapping action to create the mapping between the columns of the import file and the fields of the target table. If you choose Detail in the Line Type field, a unique Code and a Description are assigned and the number of columns to process (import) is specified in Column Count. The default line with the Header line type is deleted.

Settings on the Field Mapping page

After you fill in the fields on the Column Definitions FastTab, and create a line on the Line Definitions FastTab, you can set up the field mappings. On the Line Definitions FastTab, choose the Field Mapping action, and enter the following settings on the Field Mapping page.

On the General FastTab, fill in the fields as follows:

  • The Table ID field should contain 8018. This is the Usage Data Generic Import table.
  • The Name field should contain a name.
  • The Mapping Codeunit field should contain 8030. This is the Generic Import Mappings codeunit to use for the generic import type when you generate imported lines.

On the Field Mapping FastTab, fill in the fields as follows:

  • The Column No. field specifies a column definition, and the Field ID field specifies a target field from the table specified in the Table ID field.
  • The Overwrite Value checkbox indicates that field contents in the target table can be overwritten, if needed. Some fields are automatically filled in from already known usage data suppliers, customers, and subscriptions before the column value for that field is applied: Customer ID fills in Customer Name, and Subscription ID fills in Product ID, Product Name, and Quantity. To ensure that the value from the import file is used instead of the automatically filled-in value, select the Overwrite Value checkbox for the Customer Name, Subscription ID, Product ID, Product Name, and Quantity fields, as described in the following table.

The following table provides a complete example of a setup on the Field Mapping FastTab on the Field Mapping page.

Column No.Column CaptionField IDField CaptionOptionalTransformation RuleOverwrite ValuePriority
2Customer ID7Customer IDNoNo0
3Customer Name8Customer NameNoYes0
7Subscription ID10Subscription IDNoYes0
8Product ID17Product IDNoYes0
10Product Name18Product NameNoYes0
11Subscription Start Date13Subscription Start DateNoNo0
12Subscription End Date14Subscription End DateNoNo0
13Billing Period Start Date15Billing Period Start DateNoNo0
14Billing Period End Date16Billing Period End DateNoNo0
16Cost19Unit CostNoNo0
17Quantity21QuantityNoYes0
18Price20Unit PriceNoNo0
19Amount24AmountNoNo0
22Cost Amount27Cost AmountNoNo0
23Currency25CurrencyNoNo0
24Text150Text1NoNo0

Settings on the Column Definitions FastTab

The following table describes the settings that define the columns to import.

FieldDescription
Column No.Specifies the number of the column in the import file.
NameSpecifies the name of the column. To keep things simple, we recommend that you use the name from the header of the import file.
Data TypeSpecifies whether the data to import is a Text, Date or Decimal.
Data FormatRefers primarily to data of the Date data type. The field specifies how to format dates. For example, for a date in US format, the value should be MM-DD-YYYY.
Data Formatting CultureSpecifies the culture of the data format, if needed. For example, en-US for the Decimal data type indicates that a period is used as decimal separator according to US format.
LengthOnly relevant if the file type is set to Fixed Text.

Imports and processing