BCCL Documentation
Index

Mapping Files

Mapping files give you precise control over which fields are exported and how they are named. Instead of exporting every field in a Business Central table, you can select specific fields, rename their output columns, apply formatting, and even add calculated columns. This page explains how to create, customize, and use mapping files.

Generating a Mapping Template

The fastest way to create a mapping file is to let BCCL generate one for you. The mapping task creates a template that includes every field in the specified table:

bccl -t mapping -s table=18 -o customer.mapping.json

This produces a JSON file containing a mapping entry for each field in table 18 (Customer). You then edit this file to keep only the fields you need and make any adjustments.

The mapping task always outputs JSON regardless of the -f parameter.

Restrictions on Which Tables Can Be Mapped

Only tables that the Business Central platform permits opening through RecordRef on the Cloud target can be mapped. Some system and virtual tables are blocked by Microsoft, and generating a mapping for them (or reading them) fails with:

You cannot open record <TableNo> from a RecordRef data type when you are using target Cloud.

This is a platform restriction, not a BCCL limitation, and there is no BCCL setting or workaround. The usual alternative is AL code that copies the data into a normal table, which you then map. See the entry in Common Issues.

Mapping File Structure

A mapping file is a JSON object with the following structure:

{
  "Customer": [
    {
      "FieldNo": 1,
      "FieldName": "No.",
      "MappedName": "No_",
      "Validate": true
    },
    {
      "FieldNo": 2,
      "FieldName": "Name",
      "MappedName": "Name",
      "Validate": true
    },
    {
      "FieldNo": 21,
      "FieldName": "Customer Posting Group",
      "MappedName": "Customer_Posting_Group",
      "Validate": true
    }
  ],
  "ValidateOnInsert": true,
  "ValidateOnModify": true,
  "ValidateOnDelete": true
}

The top-level key (here, "Customer") is the table name with XML-invalid characters replaced by underscores. It contains an array of field definitions. Below the field array are table-level validation settings.

Field Properties

Each field in the mapping array can have the following properties:

Property Type Required Description
FieldNo Integer Yes The Business Central field number
FieldName String No The original BC field name (informational only)
MappedName String Yes The column name in the output
Validate Boolean No Whether to trigger validation on import
Format String No A .NET format string for the field value
Function String No A special function to compute the value
CreateRelatedStub Boolean No Auto-create related records on import
Selected Boolean No Used by the Request Builder UI for field selection

FieldNo

The Business Central field number uniquely identifies a field in a table. You can find field numbers in the table metadata, through the AL development environment, or by looking at the generated mapping template.

FieldName

This is purely informational. BCCL does not use the FieldName property during export or import. It is included in the template so that you can identify fields without looking up field numbers.

MappedName

The MappedName is the column name that appears in your output. By default, it is the field name with XML-invalid characters (such as periods and forward slashes) replaced by underscores. The period in "No." becomes "No_", and "Customer Posting Group" becomes "Customer_Posting_Group".

You can change the MappedName to anything you want:

{
  "FieldNo": 1,
  "FieldName": "No.",
  "MappedName": "CustomerNumber",
  "Validate": false
}

The MappedName must be safe for the output format. For XML output, it must be a valid XML element name. For SQL output, it becomes the column name. Avoid special characters; stick to letters, numbers, and underscores.

Validate

The Validate property controls whether Business Central runs field validation triggers when importing data. For export operations, this property is ignored. It is included in the template for use with import operations.

  • true: Field validation triggers fire during import
  • false: Values are assigned directly without validation

Validate on a single field only controls whether that field's OnValidate logic runs when BCCL assigns it. It cannot influence errors raised by the table's OnInsert trigger, because by default the insert happens before the non-key fields are assigned. For insert-trigger errors, use delayed insert (-d) instead; see "Table-Level Validation Properties" below.

What a Mapping Entry Cannot Do

A mapping entry is a field-to-column assignment with the options listed above. It has no expression or length property: you cannot truncate or transform a value in the mapping file (for example Left(Description,30)). If a value is longer than the target SQL column, widen the column or adjust the source data; see SQL Output Issues. Session options for the SQL connection can be set with --sqlprecommand.

The MappedName convention replaces characters that are invalid in the output with , for example No. becomes No, Fin. Charge Terms Code becomes Fin_ Charge Terms Code, and Prepayment % becomes Prepayment _.

Format

The Format property lets you specify a .NET format string that controls how the field value is formatted in the output. This is primarily useful for CSV exports where you need specific date or number formatting.

Decimal formatting examples:

Format String Input Value Output
"F2" 1234.5 1234.50
"F4" 1234.5 1234.5000
"N2" 1234567.89 1,234,567.89
"C" 1234.50 $1,234.50

Date formatting examples:

Format String Input Value Output
"yyyy-MM-dd" March 25, 2026 2026-03-25
"dd/MM/yyyy" March 25, 2026 25/03/2026
"MM-dd-yyyy" March 25, 2026 03-25-2026
"yyyyMMdd" March 25, 2026 20260325

Example in a mapping entry:

{
  "FieldNo": 59,
  "FieldName": "Balance (LCY)",
  "MappedName": "Balance",
  "Validate": false,
  "Format": "F2"
}

Column Selection

The most common use of mapping files is to limit which fields are exported. To select only certain fields, simply remove the unwanted entries from the field array. Only fields present in the mapping file are included in the output.

Example: Export Only Customer Number and Name

Start with the generated mapping template, then remove all fields except the ones you need:

{
  "Customer": [
    {
      "FieldNo": 1,
      "FieldName": "No.",
      "MappedName": "CustomerNumber",
      "Validate": false
    },
    {
      "FieldNo": 2,
      "FieldName": "Name",
      "MappedName": "CustomerName",
      "Validate": false
    }
  ],
  "ValidateOnInsert": false,
  "ValidateOnModify": false
}

This produces output with only two columns, named "CustomerNumber" and "CustomerName".

Removing a field entry is also a valid troubleshooting workaround: if a specific field causes a conversion or overflow error during export (for example the System.OverflowException on BigInteger fields with 2.x clients) and you do not need that field, delete its entry from the mapping array and the field is skipped entirely. See the overflow entry in Common Issues.

Maintaining Mapping Files Across Upgrades

Mapping files must be maintained across Business Central upgrades. Fields that Microsoft obsoletes must be removed from the mapping; otherwise a GETDATA run (or a Get Data Service Task) fails with an error instead of skipping the field:

An error has occurred: Field IRS 1099 Amount (10021) of table Purchase Header (38) is obsoleted, reason: Moved to IRS Forms App.
  • Review your mapping files after each major Business Central upgrade for fields that have been obsoleted, removed, or moved to separate apps.
  • BCCL offers no option to ignore or tolerate obsoleted fields. Removal from the mapping file is required.
  • Each error reports one field at a time, so you may need to repeat the edit until the task completes.

See the "Field ... is obsoleted" entry in Common Issues.

Renaming Columns

To rename a column, change the MappedName value. The FieldNo determines which Business Central field is read; the MappedName determines what the column is called in the output:

{
  "FieldNo": 1,
  "FieldName": "No.",
  "MappedName": "ID"
},
{
  "FieldNo": 2,
  "FieldName": "Name",
  "MappedName": "CompanyName"
},
{
  "FieldNo": 5,
  "FieldName": "Address",
  "MappedName": "StreetAddress"
}

This is useful when the receiving system expects specific column names that differ from the Business Central field names.

Special Functions

The Function property lets you add computed columns that are not direct field values. When a mapping entry has a Function, the FieldNo typically points to a related field, and the function computes a derived value.

DimensionFromSet

The DimensionFromSet function extracts a specific dimension value from a Dimension Set ID field. This is the most commonly used function for exporting dimension values alongside transaction data.

{
  "FieldNo": 480,
  "FieldName": "Dimension Set ID",
  "MappedName": "Department",
  "Function": "DimensionFromSet(DEPARTMENT)"
}

In this example, field 480 is the Dimension Set ID. The function looks up the dimension set and returns the value for the dimension code "DEPARTMENT". You can add multiple entries for the same FieldNo with different dimension codes:

{
  "FieldNo": 480,
  "FieldName": "Dimension Set ID",
  "MappedName": "Department",
  "Function": "DimensionFromSet(DEPARTMENT)"
},
{
  "FieldNo": 480,
  "FieldName": "Dimension Set ID",
  "MappedName": "Project",
  "Function": "DimensionFromSet(PROJECT)"
}

Const

The Const function outputs a constant value for every record. This is useful for tagging exported data with a fixed identifier:

{
  "FieldNo": 0,
  "FieldName": "",
  "MappedName": "Source",
  "Function": "Const(BusinessCentral)"
}

Every record in the output will have a "Source" column with the value "BusinessCentral". If the constant value is numeric, BCCL treats it as a number; otherwise, it is treated as text.

Limitation: Const and Field Validation on Import

The Function property can conflict with field validation on putdata: "the function fights the validate." If you put "Function":"CONST(<value>)" on a field whose value is already present in the input data, Business Central can report the field as empty even though the input JSON contains a value, for example:

Error: Dimension Code must have a value in Dimension Value: Dimension Code=, Code=<value>. It cannot be zero or empty.

with a stack trace pointing at Table 349.OnInsert(Trigger) and CodeUnit 80806.ImportTableJson.

Rule: if the value for a field is already in the input data, do not use CONST(...) for that field. Map it as a regular field mapping (FieldNo, FieldName, MappedName, optionally Validate). CONST is only for supplying a value that is not in the input data. Removing the Function entry and using a plain field mapping is the recommended fix for this error. See Import (PUTDATA) Errors.

CompanyName

The CompanyName function outputs the name of the current Business Central company:

{
  "FieldNo": 0,
  "FieldName": "",
  "MappedName": "Company",
  "Function": "CompanyName()"
}

NoControlCharacters

The NoControlCharacters function removes control characters (ASCII 9-31) from a text field value. This is useful when exporting to formats that do not handle control characters well:

{
  "FieldNo": 2,
  "FieldName": "Name",
  "MappedName": "Name",
  "Function": "NoControlCharacters()"
}

Custom Functions

BCCL exposes an event that AL developers can subscribe to for implementing custom functions. If a function name does not match any of the built-in functions, BCCL raises the OnFunctionCall event, allowing custom AL code to compute the value.

Using a Mapping File with Export

To use a mapping file during export, pass it with the -m parameter:

bccl -t getdata -s table=18 -f csv -o customers.csv -m customer.mapping.json

BCCL reads the mapping file, and for each record in the table, it exports only the fields listed in the mapping with the column names defined by MappedName.

FlowFields in Mapping Files

Business Central FlowFields are calculated fields whose values are derived from other tables. When a FlowField appears in your mapping file, BCCL automatically calls CalcField() before reading the value. You do not need to take any special action.

Common FlowFields you might include:

Table Field Description
Customer (18) Balance (LCY) Customer balance
Item (27) Inventory Quantity on hand
Vendor (23) Balance (LCY) Vendor balance

Because FlowField calculations require additional database queries, including many FlowFields can significantly slow down large exports. Only include the FlowFields you actually need.

MappedName Restrictions

The MappedName must follow certain rules depending on the output format:

  • XML: Must be a valid XML element name. Cannot start with a number. Cannot contain spaces, periods, or most special characters. BCCL replaces periods and XML-invalid characters with underscores in the default template.
  • CSV: No strict restrictions, but avoid the delimiter character in column names.
  • JSON: Must be a valid JSON property name. Most strings work.
  • SQL: Must be a valid SQL column name. Avoid SQL reserved words and special characters.

As a general rule, use only letters, numbers, and underscores in your MappedName values to ensure compatibility across all formats.

Table-Level Validation Properties

At the root level of the mapping file, alongside the field array, you can set table-level validation flags:

Property Purpose
ValidateOnInsert Run insert triggers when importing new records
ValidateOnModify Run modify triggers when updating existing records
ValidateOnDelete Run delete triggers when deleting records

These properties control whether BCCL runs Business Central's table-level validation (the OnInsert, OnModify, and OnDelete triggers) during import. Disabling them means the values from the input file are written without that business logic being triggered. They only affect import operations and are ignored during export.

When to Turn Insert Validation Off

Some Business Central tables have complex OnInsert logic whose triggers re-validate or re-derive field values. Table 349 Dimension Value is a concrete example. For such tables, an insert can fail with an error of the form <Field> must have a value in <Table>: <Field>=, <Key>=<value>. It cannot be zero or empty. even though the value is in the input file.

Recommended order of remedies:

  1. Check the mapping for a Function entry (such as CONST(...)) on the field named in the error and replace it with a regular field mapping (see the Const limitation above).
  2. Use delayed insert (-d) so all fields are assigned before the record is inserted. This keeps the table's insert validation intact. See Advanced Import Features.
  3. Only if that is not viable, set "ValidateOnInsert": false in the mapping file. This makes the import succeed but skips the business logic in the table's OnInsert trigger entirely, which can leave records in a state the owning app does not expect. Treat it as a last-resort escape hatch.

To confirm that the mapping file is the cause of an import error, re-run the same putdata without -m. If it succeeds, the mapping file is at fault.

Tips and Best Practices

  • Always generate the mapping template with the mapping task instead of writing it by hand. This ensures all field numbers are correct.
  • Keep your mapping files under version control. They are a key part of your data integration configuration.
  • Remove fields you do not need rather than leaving them in the mapping. Fewer fields means faster exports.
  • Use meaningful MappedName values that match what the receiving system expects.
  • When adding DimensionFromSet entries, make sure the dimension code (e.g., "DEPARTMENT") matches exactly what is configured in Business Central, including case.
  • Test your mapping file with a small export before running it against a large dataset.