BCCL Documentation
Index

Filtering Data

When you export data from Business Central with BCCL, you often need only a subset of the records in a table. The view parameter lets you apply filters so that BCCL exports exactly the data you need, reducing transfer time and output file size.

The View Parameter

You pass filters to BCCL using the view setting within the -s parameter. The view parameter uses standard Business Central table view syntax:

bccl -t getdata -s table=18 "view=where(No.=filter(10000..20000))" -f csv -o filtered.csv

The view=where(...) syntax mirrors the SETVIEW function in AL code. Inside the where() clause, you specify one or more field filters separated by commas.

Quoting Rules

Because the view parameter typically contains spaces, parentheses, and special characters, you should wrap it in double quotes on the command line:

"view=where(No.=filter(10000..20000))"

On Windows, you can also use the following form if the entire -s group needs quoting:

-s table=18 "view=where(No.=filter(10000..20000))"

On Linux and macOS, you may need to use single quotes around the entire argument or escape special characters depending on your shell.

Filter Syntax

BCCL uses the standard Business Central filter expressions. If you are familiar with filtering in Business Central pages, the same rules apply here.

Basic Comparison

To filter for an exact value:

view=where(No.=filter(10000))

This exports only the record where No. equals 10000.

Filter Operators

Business Central supports the following filter operators:

Operator Meaning Example Matches
.. Range 10000..20000 Values from 10000 to 20000 inclusive
* Wildcard Corp Any value containing "Corp"
@ Case insensitive @adatum "Adatum", "ADATUM", "adatum"
&#|; Or 10000&#|;20000 Either 10000 or 20000
& And >10000&<20000 Greater than 10000 and less than 20000
<> Not equal <>10000 Any value except 10000
> Greater than >10000 Values greater than 10000
< Less than <10000 Values less than 10000
>= Greater or equal >=10000 Values 10000 and above
<= Less or equal <=10000 Values up to and including 10000

Wildcard Examples

The * wildcard matches any number of characters:

view=where(Name=filter(*Corp*))

This matches any customer whose name contains "Corp", such as "Adatum Corporation" or "CorpNet Systems".

You can use * at the beginning, end, or both:

Pattern Matches
A* Names starting with "A"
*Ltd Names ending with "Ltd"
import Names containing "import"
A*Corp Names starting with "A" and ending with "Corp"

Case-Insensitive Filtering

Prefix your filter value with @ to make the comparison case-insensitive:

view=where(Name=filter(@*adatum*))

This matches "Adatum", "ADATUM", "adatum", and any other casing variation.

Multiple Filters

To filter on more than one field, separate the conditions with commas inside the where() clause:

"view=where(No.=filter(10000..20000),City=filter(London))"

This exports only customers with numbers between 10000 and 20000 whose city is "London". All conditions must be met (they are combined with AND logic).

Example: Filtered Export with Multiple Conditions

bccl -t getdata -s table=18 "view=where(No.=filter(10000..20000),Name=filter(*Corp*),Country_Region_Code=filter(US))" -f csv -o us_corps.csv

This exports customers where:

  • The customer number is between 10000 and 20000
  • The name contains "Corp"
  • The country/region code is "US"

Dynamic Date Expressions

BCCL supports dynamic date expressions in your filters. These are placeholders that BCCL resolves to actual dates and times at runtime. This is especially useful for automated exports that need to run repeatedly with time-relative filters.

Date Expressions

Expression Resolves To Example
{today} Current date March 25, 2026
{today(-7)} 7 days ago March 18, 2026
{today(+1)} Tomorrow March 26, 2026
{today(-30)} 30 days ago February 23, 2026
{today(+90)} 90 days from now June 23, 2026
{workdate} Business Central work date Depends on BC setting

The parameter of {today()} is a Business Central date expression and may carry an explicit unit letter, for example {today(-1D)} (one day back) or {today(-7D)}. This is the form used in the scripts generated by the SQL Bundle Builder. The supported units are W (weeks), D (days), H (hours), M (minutes), and S (seconds), as documented for the Change Log task.

Minute-Based Expressions

For more granular time filtering, use the {minutes()} expression. The parameter is a time expression built from H (hours), M (minutes), and S (seconds) — not a plain minute count. Units can be combined and may be positive or negative:

Expression Resolves To
{minutes(-30M)} 30 minutes ago
{minutes(-1H)} 1 hour ago
{minutes(-1H30M)} 1 hour 30 minutes ago
{minutes(-1H2M13S)} 1 hour, 2 minutes, 13 seconds ago
{minutes(0S)} Current moment
{minutes(1H)} 1 hour from now

Using Date Expressions in Filters

To export all sales invoices posted in the last 7 days:

bccl -t getdata -s table=112 "view=where(Posting_Date=filter({today(-7)}..{today}))" -f csv -o recent_invoices.csv

To export customer ledger entries modified in the last hour:

bccl -t getdata -s table=21 "view=where(Last_Modified_DateTime=filter({minutes(-1H)}..))" -f json -o recent_entries.json

How Date Resolution Works

BCCL processes the date expressions on the client side before sending the request to Business Central. The {today} expression resolves to the current system date on the machine running BCCL, formatted as a date that Business Central can interpret. The {minutes()} expression resolves to a full date-time value.

This means that the date is based on the local time of the machine running BCCL, not the Business Central server time. Keep this in mind if your BCCL client and Business Central server are in different time zones.

Filtering on Date and DateTime Fields

When filtering on date fields, use the date format that Business Central expects. For most configurations, this is the format MM/DD/YYYY or the region-specific format:

"view=where(Posting_Date=filter(01/01/2026..03/25/2026))"

For DateTime fields, you can filter on the date portion:

"view=where(Last_Modified_DateTime=filter(03/01/2026..))"

Using the dynamic date expressions is generally more reliable because you do not have to worry about date format mismatches.

Filtering on System Fields (Incremental Exports)

Business Central system fields can be filtered by their field number in the view parameter. The field name to use is the literal string Field followed by the field number. The most useful one is SystemModifiedAt, field number 2000000003, addressed as Field2000000003.

The filter-group form used in BCCL view strings is FieldName=1(<filter expression>), where 1 is the filter group. These working examples follow the pattern the SQL Bundle Builder generates into RunBCCL.cmd (the builder writes the target as -o [G_L_Account]; the authentication and SQL connection come from a preceding --remember line):

BCCL -t getdata -s table=15 view=WHERE(Field2000000003=1({today(-1D)}..))  -o G_L_Account -m mapping\G_L_Account.mapping.json
BCCL -t getdata -s table=17 view=WHERE(Field2000000003=1({minutes(-2H)}..))  -o G_L_Entry -m mapping\G_L_Entry.mapping.json
  • {today(-1D)}.. is an open-ended range meaning "everything modified from one day ago and onwards".
  • {minutes(-2H)}.. means "everything modified in the last two hours".

This is how GETDATA is turned into an incremental export: without such a filter, GETDATA transfers every record matching the view on every run and upserts it into SQL (0 inserted, N updated). With the filter, only records changed inside the lookback window are transferred.

Choose the lookback window to match the frequency at which you run BCCL. It must always be at least as long as the interval between runs, and preferably longer, so that a delayed or skipped run does not miss changes. Use the "Retention Period Guidelines" table in Scheduling Replication as the reference.

The view=... setting should normally be quoted on the command line. The examples above are shown unquoted as they appear in generated .cmd scripts; quoting is required if your shell would otherwise split or interpret the parentheses or other characters.

Note that the Service Task setting Filter to only entries since last run (ChangeLogFilterSinceLast) is a Service Task and Automation feature only. It is not available as a BCCL.EXE command-line parameter; command-line users achieve the equivalent by supplying the SystemModifiedAt view filter themselves. SystemModifiedAt covers inserts and modifications; deletions are covered by the Change Log task.

Filtering on Option Fields

Option fields in Business Central are stored as integers internally. You can filter using either the text value or the integer:

"view=where(Document_Type=filter(Invoice))"

Or equivalently:

"view=where(Document_Type=filter(2))"

The text values must match exactly what Business Central expects for the option.

Combining Filters with Paging

Filters are applied at the Business Central server level before paging takes effect. This means:

  1. Business Central applies your filter to the table
  2. The filtered record set is split into pages
  3. Each page is sent to BCCL

If your filtered result contains 5,000 records and your page size is 1,000, BCCL makes 5 round-trips to retrieve all the data. The filter ensures that only matching records are counted and transferred.

bccl -t getdata -s table=21 "view=where(Customer_No.=filter(10000))" -f csv -o ledger.csv -z 2000

Combining Filters with Mapping Files

Filters and mapping files work together. The filter determines which records are exported, while the mapping file determines which fields are included:

bccl -t getdata -s table=18 "view=where(No.=filter(10000..20000))" -f csv -o filtered.csv -m customer_mapping.json

This exports only customers 10000 through 20000, and only the fields specified in the mapping file.

Sorting

The view parameter also supports the sorting() clause to control the order of exported records:

"view=sorting(Name) where(Country_Region_Code=filter(US))"

This exports US customers sorted by name. You can combine sorting with filtering in a single view parameter.

Tips and Best Practices

  • Always wrap the view parameter in double quotes to handle special characters correctly.
  • Use dynamic date expressions ({today}, {minutes}) for scheduled or recurring exports instead of hard-coded dates.
  • Test your filter with a small export before applying it to large datasets. Omit -o to see the output on screen.
  • When filtering on field names that contain special characters (periods, slashes), use the exact field name as it appears in the table metadata. Replace spaces with underscores.
  • Combine filters with mapping files to minimize data transfer: filter reduces rows, mapping reduces columns.
  • For complex filtering scenarios that cannot be expressed with the view parameter, consider creating a query object in Business Central or using the RUNCODEUNIT task with custom AL logic.

Common Filter Patterns

Scenario Filter Expression
Records modified today view=where(Last_Modified_DateTime=filter({today}..))
Records changed since the last run (incremental export) view=WHERE(Field2000000003=1({today(-1D)}..))
Records from this month view=where(Posting_Date=filter({today(-25)}..{today}))
Active customers only view=where(Blocked=filter(' '))
Non-blank values view=where(E_Mail=filter(<>''))
Specific set of records view=where(No.=filter(10000&#|;20000&#|;30000))
Range with exclusion view=where(No.=filter(10000..20000&<>15000))