Advanced Import Features

BCCL provides several advanced features for importing data into Business Central, including automatic creation of related records, dimension handling, delayed insert patterns, and binary field support. These features help you handle complex data scenarios that go beyond simple field-to-field mapping.

Create Stub Records

When importing data, you may encounter foreign key references to records that do not yet exist in Business Central. For example, importing customers that reference a Customer Posting Group that has not been created. BCCL can automatically create stub records in related tables.

A stub record is a minimal record with only the primary key filled in. It ensures that the foreign key relationship is satisfied during import.

Configuration

Set "CreateRelatedStub": true on the relevant field in your mapping file:

{
  "FieldNo": 21,
  "FieldName": "Customer Posting Group",
  "MappedName": "PostGroup",
  "Validate": "true",
  "CreateRelatedStub": true
}

Example

Given a CSV file with customers referencing posting groups:

Number,Name,ZIP,PostGroup
"4000","The First Customer","92020","GRP1"
"4050","The Second Customer","92030","GRP1"
"5040","And Another Customer","92080","GRP2"

Import command:

BCCL -t putdata -s table=18 -i customers.csv -m mapping.json

Result:

{
  "Table": 18,
  "Inserted": 3,
  "Modified": 0,
  "StubRecords": [
    {
      "Table": 92,
      "Inserted": 2
    }
  ]
}

BCCL inserted three customers and created two stub records (GRP1 and GRP2) in the Customer Posting Group table (table 92).

Limitations

• Stub records only work with tables that have a single-field primary key
• Only the primary key is populated; all other fields remain empty/default
• You should review and complete stub records after import

Dimension Set Handling

Handling dimensions on transactional data in Business Central is one of the more complex areas. Transactions contain a Dimension Set ID field (field 480), which is a numeric identifier that maps to a unique combination of dimension values.

BCCL allows you to treat dimensions as regular columns in your data files using the DimensionFromSet() function.

Export: Dimensions as Columns

When exporting, BCCL can expand the Dimension Set ID into individual dimension columns:

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

This creates a "Department" column in your export that contains the DEPARTMENT dimension value for each record.

Multiple Dimensions

Map multiple dimensions by creating several entries for field 480, each with a different dimension code:

[
  {
    "FieldNo": 480,
    "FieldName": "Dimension Set ID",
    "MappedName": "Department",
    "Function": "DimensionFromSet(DEPARTMENT)",
    "Validate": true
  },
  {
    "FieldNo": 480,
    "FieldName": "Dimension Set ID",
    "MappedName": "Project",
    "Function": "DimensionFromSet(PROJECT)",
    "Validate": true
  },
  {
    "FieldNo": 480,
    "FieldName": "Dimension Set ID",
    "MappedName": "Region",
    "Function": "DimensionFromSet(REGION)",
    "Validate": true
  }
]

Import: Columns to Dimension Set

When importing, BCCL takes the dimension column values and creates or finds the matching Dimension Set ID:

DocNo,PostingDate,Amount,Department,Project
"INV-001","2025-03-15","1500.00","SALES","PROJ-A"
"INV-002","2025-03-16","2300.00","ADMIN","PROJ-B"

BCCL automatically resolves these dimension values into the correct Dimension Set ID in Business Central.

Use Cases

• Exporting general ledger entries with dimension breakdowns for reporting
• Importing journal lines with dimension assignments
• Replicating transactional data to SQL Server with dimension columns

Delayed Insert Pattern

The -d parameter (or --delayedinsert) changes when BCCL inserts a record relative to assigning its field values. It corresponds to the "DelayedInsert": true entry shown in the verbose settings output (default false).

Default Behavior (-d not set)

1. BCCL assigns the primary key fields (validating them if enabled)
2. The record is inserted (Insert(ValidateOnInsert))
3. BCCL assigns the remaining fields (validating each if enabled) and modifies the record

Delayed Insert Behavior (-d)

1. BCCL assigns all mapped fields on the new record (validating each if enabled)
2. The complete record is inserted (Insert(ValidateOnInsert)), or modified if it already exists

When to Use Delayed Insert

Delayed insert is designed for the case where the target table's OnInsert trigger requires a non-primary-key field to be filled with a value. With the default sequence the insert happens before that field is assigned, so the trigger fails with an error such as:

<Field> must have a value in <Table>: <primary key values>. It cannot be zero or empty.

even though the field is clearly present in the payload. Add -d to the putdata command line when you see this error for a field that is in your input data:

BCCL -t putdata -i data.csv -m mapping.json -s table=18 -d

Tables published by third-party apps frequently enforce such mandatory non-key fields in OnInsert; for those tables, run putdata with -d from the start.

-d is orthogonal to the per-field Validate setting and to ValidateOnInsert/ValidateOnModify: it changes when the record is inserted, not whether validation runs. Setting "Validate": true or false on the field named in the error makes no difference to this error, and neither does the order of fields in the mapping file. "ValidateOnInsert": false also makes the import succeed, but it bypasses the table's insert validation entirely and should be a last resort, after -d. See Import (PUTDATA) Errors for the full diagnostic procedure.

Binary Fields

BCCL can handle binary fields (Media, MediaSet, Blob) using Base64 encoding. Enable this with the -b true parameter.

Exporting Binary Fields

BCCL -t getdata -s table=18 -f json -o customers.json -b true

Binary field values are encoded as Base64 strings in the output:

{
  "No.": "10000",
  "Name": "Adatum Corporation",
  "Image": "iVBORw0KGgoAAAANSUhEUgAA..."
}

Importing Binary Fields

Include Base64-encoded binary data in your input file. BCCL decodes it and stores it in the appropriate Media, MediaSet, or Blob field.

Notes

• Binary fields significantly increase data size
• Only enable binary export when you specifically need image or document data
• Base64 encoding increases data size by approximately 33%

Custom Functions

BCCL supports several special functions in mapping files that go beyond simple field mapping.

Const Function

Insert a constant value for every record:

{
  "FieldNo": 5,
  "FieldName": "Source Code",
  "MappedName": "Source",
  "Function": "Const(IMPORT)",
  "Validate": true
}

Every imported record will have "IMPORT" as the Source Code value, regardless of what the input file contains.

Limitation: Const(...) is only for supplying a value that is not in the input data. Do not use it on a field whose value is already present in the input file. The function can conflict with field validation ("the function fights the validate"), and on putdata Business Central then reports the field as empty even though the input contains a value, for example Dimension Code must have a value in Dimension Value: Dimension Code=, Code=<value>. It cannot be zero or empty. Map such fields as regular field mappings instead. See Import (PUTDATA) Errors.

Custom Function

The Custom function is an extensibility point that triggers the OnFunctionCall event in the BCCL AL code. You can subscribe to this event in a custom extension to implement any transformation logic:

{
  "FieldNo": 10,
  "FieldName": "My Field",
  "MappedName": "RawValue",
  "Function": "Custom",
  "Validate": true
}

Your AL extension subscribes to the integration event and processes the value as needed.

DimensionFromSet Function

As described in the Dimension Set Handling section above, DimensionFromSet(CODE) extracts or assigns dimension values from/to Dimension Set IDs.

Format Strings for Import

You can specify format strings in the mapping file to help BCCL parse dates and decimals correctly during import:

Date Parsing

{
  "FieldNo": 20,
  "FieldName": "Posting Date",
  "MappedName": "PostDate",
  "Format": "dd/MM/yyyy",
  "Validate": true
}

This tells BCCL that dates in your input file are formatted as dd/MM/yyyy.

Decimal Parsing

{
  "FieldNo": 21,
  "FieldName": "Amount",
  "MappedName": "Amount",
  "Format": "F2",
  "Validate": true
}

Tips and Best Practices

• Test advanced features with small datasets before running on production data
• Review stub records after import and fill in any required fields
• Use dimension expansion in the SQL Bundle Builder for automated SQL replication with dimensions
• Enable delayed insert (-d) when the target table's OnInsert trigger requires non-key fields, and always for third-party tables with mandatory fields
• Keep binary field exports separate from regular data exports for performance

Related Topics

• Importing Data Overview
• Importing CSV Files
• Mapping Files
• Import (PUTDATA) Errors
• SQL Bundle Builder