Change Log

The CHANGELOG task queries the Business Central Change Log Entry table to retrieve field-level change history for a specified table. It tracks inserts, updates, and deletes, and outputs the data in SQL format so that changes can be applied incrementally to a SQL Server database. This makes it a core component of the BCCL SQL replication strategy.

When to Use the Change Log Task

The CHANGELOG task is designed for incremental SQL replication, and specifically for the part of it that the GETDATA task cannot do: deletion tracking. GETDATA with a SystemModifiedAt filter picks up inserts and modifications without any Change Log; CHANGELOG reads the Business Central Change Log to discover records that have been deleted and applies the corresponding DELETE statements in SQL.

Scope clarification: enabling the BC Change Log and running -t changelog is not how you stop GETDATA from re-writing unchanged rows on every run. If every GETDATA run reports 0 inserted, N updated, the fix is a SystemModifiedAt (Field2000000003) view filter on the GETDATA command, as generated by the SQL Bundle Builder scripts. See "Filtering on System Fields (Incremental Exports)" in Filtering Data.

Typical use cases include:

Incremental SQL replication -- Keeping a SQL Server mirror of Business Central data up to date with minimal data transfer.
Deletion tracking -- The standard GETDATA task cannot detect deleted records. CHANGELOG reads the Change Log to discover deletions and apply corresponding DELETE statements in SQL.
Audit trail queries -- Retrieving a history of who changed what and when for compliance or debugging purposes.

Prerequisites

Before using the CHANGELOG task, you must enable the Change Log in Business Central for the tables you want to track.

Enabling the Change Log

In Business Central, search for Change Log Setup and open it.
Set Change Log Activated to Yes.
Open the Tables page from the Change Log Setup.
Add entries for each table you want to track:
- Set the Table No. to the target table.
- Choose which operations to log: Insertion, Modification, Deletion.
- For modifications, choose whether to track All Fields or Some Fields.

For SQL replication, you should enable logging for at least Deletions. BCCL can detect inserts and modifications through the SystemModifiedAt timestamp using the GETDATA task, but deletions are only visible through the Change Log.

Verifying Change Log Entries

After enabling the Change Log, make a test change in Business Central and then search for Change Log Entries to confirm that entries are being recorded. If no entries appear, verify that the Change Log Setup is activated and that the correct table is configured.

Required Parameters

Parameter	Description
`-t changelog`	Specifies the CHANGELOG task
`-s table=N`	The Business Central table number to query changes for
`-m mapping.json`	Path to the mapping file for the target table
`-o tablename`	Output SQL table name (used with `-f sql`)
`-f sql`	Output format (CHANGELOG only supports SQL output)
`--sqlconnect`	SQL Server connection string

Optional Parameters

Parameter	Description
`-s view=WHERE(...)`	Filter on the Change Log Entry table
`-z pagesize`	Number of change log entries to process per request (default: 1000)

Output Format

The CHANGELOG task outputs data exclusively in SQL format (-f sql). The output contains SQL statements (INSERT, UPDATE, DELETE) that can be applied directly to a SQL Server table to replicate the changes from Business Central.

For each change log entry, BCCL retrieves:

Change Log metadata -- The type of change, the date and time, the field that changed, the old value, and the new value.
Current record data -- If the record still exists, the current field values are included so that the SQL Server table can be updated with the latest state.

When a record has been deleted, only the Change Log metadata is available. The current record data is empty, and BCCL generates a DELETE statement for SQL Server.

How CHANGELOG Processes Changes

The CHANGELOG task reads from the standard Business Central Change Log Entry table (table 405). It processes entries in chronological order, keyed by Table No. and Date and Time.

For each change log entry, BCCL determines the type of change and generates the appropriate SQL statement:

Change Type	SQL Action	Description
Insertion	INSERT	A new record was created in Business Central
Modification	UPDATE	An existing record was modified
Deletion	DELETE	A record was removed from Business Central

When multiple change log entries exist for the same record (for example, several field-level modifications at the same time), BCCL de-duplicates them so that only one SQL statement is generated per unique record and operation type combination.

Dynamic Date Filtering

The CHANGELOG task supports dynamic date and time expressions in view filters. These expressions are evaluated at runtime, making them ideal for scheduled automation where you always want to process "recent" changes.

The {today()} Expression

Use {today(N)} to calculate a date relative to the current date. The parameter N is a Business Central date expression:

-s "view=where(Date_and_Time=filter({today(-7D)}..{today(0D)}))"

This filters change log entries from the last 7 days through today. The {today(-7D)} expression calculates the date 7 days ago at runtime.

The {minutes()} Expression

Use {minutes(N)} to calculate a date/time relative to the current moment. The parameter supports hours, minutes, and seconds:

-s "view=where(Date_and_Time=filter({minutes(-2H)}..{minutes(0S)}))"

This filters entries from the last 2 hours. Supported time units are:

Unit	Meaning
`W`	Weeks
`D`	Days
`H`	Hours
`M`	Minutes
`S`	Seconds

You can combine units: {minutes(-1H30M)} means "1 hour and 30 minutes ago."

Incremental Sync with Paging

The CHANGELOG task supports a "filter since last run" mechanism through the BCCL paging system. On each run, BCCL remembers where it left off in the Change Log. On the next run, it resumes from that position and only processes new entries.

This is handled automatically when you use the same paging ID across runs. The paging state is stored in the BCCL Cache table inside Business Central.

The Service Task setting Filter to only entries since last run (ChangeLogFilterSinceLast) applies to Service Tasks and Automations only and has no command-line counterpart. Command-line runs must express the window explicitly in the view filter, for example "view=WHERE(Field2=1({minutes(-2H)}..))" for CHANGELOG (field 2 is the Change Log Entry's Date and Time) and view=WHERE(Field2000000003=1({minutes(-2H)}..)) for GETDATA.

How Paging Works

On the first run, BCCL processes change log entries from the beginning (or from your view filter start point) and remembers the last processed entry.
On subsequent runs, BCCL starts from the entry after the last one it processed.
If the page size (-z) is smaller than the number of new entries, BCCL processes one page and remembers the position for the next call.

The default page size is 1000 entries. You can adjust this with the -z parameter:

bccl -t changelog -s table=18 -m customer.json -o Customer -f sql --sqlconnect "..." -z 5000

Complete Example: Incremental Customer Replication

This example demonstrates how to set up incremental replication for the Customer table.

Step 1: Enable Change Log in Business Central

Open Change Log Setup and activate it.
Add table 18 (Customer) with Deletion logging enabled.

Step 2: Generate a Mapping File

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

Edit the mapping file to include only the fields you want to replicate to SQL Server.

Step 3: Create the SQL Table

Use the SQL Bundle Builder to generate a CREATE TABLE script, or create the SQL table manually to match your mapping file.

Step 4: Run the Initial Full Load

Before using CHANGELOG for incremental updates, populate your SQL table with a full export:

bccl -t getdata -s table=18 -f sql -m customer.json -o Customer --sqlconnect "Server=myserver;Database=mydb;Trusted_Connection=true"

Step 5: Run Incremental Updates with CHANGELOG

bccl -t changelog -s table=18 -m customer.json -o Customer -f sql --sqlconnect "Server=myserver;Database=mydb;Trusted_Connection=true"

This processes all change log entries for table 18 and applies the corresponding INSERT, UPDATE, or DELETE statements to the SQL Server Customer table.

Step 6: Schedule Regular Runs

Use Windows Task Scheduler or the Business Central Job Queue to run the CHANGELOG command at regular intervals (for example, every 5 or 15 minutes).

Using CHANGELOG with the SQL Bundle Builder

The SQL Bundle Builder report in Business Central can generate a complete replication package that includes CHANGELOG commands. When you enable the "Add Deletions from Change Log" option in the bundle builder, it generates script lines that run CHANGELOG before GETDATA for each table. This ensures that deletions are captured from the Change Log and then any remaining inserts and modifications are picked up through the normal GETDATA incremental sync.

A typical generated script line looks like:

BCCL -t changelog -s table=18 view=WHERE(Field2=1({today(-7D)}..))  -o [Customer] -m mapping\Customer.mapping.json

Change Log Entry Fields

The CHANGELOG task outputs metadata from the Change Log Entry table alongside the current record data. The Change Log fields included in the output are:

Field	Description
Date and Time	When the change was recorded
Time	The time component of the change
Table No.	The table that was changed
Field No.	The specific field that was changed
Type of Change	Insertion, Modification, or Deletion
Old Value	The previous value of the field (for modifications)
New Value	The new value of the field (for modifications)
Changed Record SystemId	The SystemId of the changed record

Tips and Best Practices

Enable Change Log selectively. Only enable Change Log tracking for tables that need it. Tracking every table generates significant overhead in Business Central.
Always track deletions. If you are replicating to SQL Server, deletion tracking is essential. Without it, records deleted in Business Central will remain in your SQL database as orphans.
Use the SQL Bundle Builder. For multi-table replication, the SQL Bundle Builder automates the generation of CHANGELOG and GETDATA commands, mapping files, and SQL CREATE TABLE scripts.
Schedule frequent runs. For near real-time replication, schedule CHANGELOG runs every few minutes. The paging mechanism ensures that only new changes are processed on each run.
Archive old Change Log entries. Over time, the Change Log Entry table can grow large. Business Central provides tools to delete old entries. Coordinate your retention period with your CHANGELOG run frequency to ensure you do not lose entries before they are processed.
Combine with GETDATA for full coverage. Use CHANGELOG for deletion tracking and GETDATA with a SystemModifiedAt filter for inserts and modifications. This two-pronged approach provides complete incremental replication.
Monitor page sizes. If your tables have high change volumes, increase the page size (-z) to process more entries per run and reduce the number of round trips.

Troubleshooting

No Change Log Entries Returned

Verify that the Change Log is activated in Business Central (Change Log Setup).
Confirm that the specific table is configured for change logging.
Check that changes have actually occurred since your filter start date.

Duplicate SQL Statements

BCCL de-duplicates entries by SystemId and operation type. If you see duplicates, check whether your filter is pulling in overlapping date ranges across runs.

Missing Deletions in SQL

Ensure that Deletion logging is enabled for the table in the Change Log Setup.
Confirm that the Change Log entries have not been purged before BCCL processed them.

Every Run Reports "0 inserted, N updated"

Cause: a GETDATA command without a modification filter transfers the whole table on each run and upserts it. This is expected behaviour of GETDATA, not a Change Log issue.
Fix: add view=WHERE(Field2000000003=1({today(-1D)}..)) (or a {minutes(...)} window matching your schedule) to the GETDATA command, exactly as the SQL Bundle Builder scripts do. The lookback must be at least as long as the interval between runs.
Remember that ChangeLogFilterSinceLast applies to Service Tasks/Automations only; command-line runs need the explicit filter.