Tables and data

Path: Development > Programming in the AL language > Tables and data · tier official · system development · narrative reviewed (checked by Opus)

Overview

This section is the AL reference for working with data in Business Central. It starts with table structure (objects, fields, keys, triggers, extensions, relationships) and moves through reading and modifying records, Query objects, and FlowFields and FlowFilters for calculated values that are not stored.

Performance topics sit in their own subtopics. Reading data covers Get, Find, FindSet, partial records, isolation levels, and read scale-out. Indexing covers SIFT and NCCI, including tuning, tracing, and migrating from SIFT to NCCI. Importing and exporting covers XMLport objects and Excel Buffer exports. A Streaming data subtopic is also listed.

The section’s own two pages cover number sequences and DataTransfer. Number sequences use SQL Server sequences and allow gaps without blocking, unlike number series. DataTransfer does bulk copying between tables during upgrade and install. To begin, read Defining table structures, then Reading data and Modifying data. Go to the indexing and DataTransfer pages when performance matters.

Key points

• Defining table structures covers the table object, fields, keys, triggers, system fields, table extensions, relationships, tooltips, and optimized text search.
• Modifying data covers Insert, Modify, Delete and Truncate, temporary tables, virtual tables (Date, Integer), media on records, filter pages, Dataverse table properties, and keeping test data between publishes.
• Reading data covers Get, Find, FindSet and Next, partial records, record isolation levels, SQL performance of database methods, and read scale-out.
• Query objects define dataitems and columns, joins, filters, aggregates, and date parts, and their results can be read in code.
• FlowFields and FlowFilters are virtual fields that calculate values at runtime using CalcFormula, with no data stored.
• SIFT and NCCI speed up sums over numeric columns, with trade-offs in performance and maintenance, and a migration path from SIFT to NCCI.
• Number sequences use SQL Server sequences (NumberSequence type with Insert, Delete, Next, Current, Exists). They allow gaps and are non-blocking, unlike number series.
• DataTransfer bulk-copies data between SQL-based tables during upgrade and install (CopyRows, CopyFields, AddJoin, and others), and is faster than row-by-row record operations. It is listed under 2022 release wave 2.

Subtopics

• Defining table structures (7 pages)
• Modifying data (9 pages)
• Reading data (6 pages)
• Using Query objects (9 pages)
• FlowFields and FlowFilters (3 pages)
• Indexing data for performance (9 pages)
• Streaming data (5 pages)
• Importing and exporting data (6 pages)

More Learn pages

• Number sequences in Business Central: This article describes how to create and use number sequences in AL code in Dynamics 365 Business Central.
• Transferring data between tables using DataTransfer: Learn about the DataTransfer object type and how to use it to move data between tables.

Videos, posts and code changes

Linked by a Haiku matcher with a grounding quote from the item’s summary (link/topics.ts); machine-generated.

• #10255 Add expense activity history foundation (code change): “New append-only activity log table records expense report lifecycle events”
• #10486 [Quality Management] Use Bin code selection for transfer disposition in workflow response (code change): “The Quality Management transfer disposition now uses the bin”
• #203 knowledge(data-modeling): Prices Including VAT decides the basis of sales/purchase/service line amounts (code change): “Prices Including VAT header flag affects the basis of line amount fields in sales, purchase, and service documents”
• Dynamics 365 Business Central: AL Query objects and the new ReadState property. (community post): “AL query objects now support the ReadState property in Dynamics 365 Business Central”
• Record.IsDirty in Business Central 29: a worked example (community post): “Record.IsDirty() returns true when in-memory values differ”
• How to Group and Consolidate General Journal Lines Using Query Object in Business Central. (community post): “consolidate General Journal Lines in Business Central using Query Objects”
• BC Friday Tips #83 Check Whether a Record Has Changed (community post): “Record.IsDirty() method detects whether a record has been modified”
• What’s New: AL Runtime and Database (2024 release wave 1) (video): “number sequence; change log cleanup; alter key; field tooltips; translatable texts”
• FieldExist() and Field() get a text overload (video): “FieldExist() and Field() get a text overload”

Business Central pages and reports

The pages, reports and queries this hub’s Learn pages name (ms.search.form, API reference), joined to the code by exact object id (data/index/docs-objects.json); a page shows the table it runs on.

• Page 16 “Chart of Accounts” · on Table 15 “G/L Account” · via Using Query objects
• Page 22 “Customer List” · captioned “Customers” · on Table 18 “Customer” · via Using Query objects
• Page 25 “Customer Ledger Entries” · on Table 21 “Cust. Ledger Entry” · via Using Query objects
• Page 26 “Vendor Card” · on Table 23 “Vendor” · via Using Query objects
• Page 27 “Vendor List” · captioned “Vendors” · on Table 23 “Vendor” · via Using Query objects
• Page 31 “Item List” · captioned “Items” · on Table 27 “Item” · via Using Query objects
• Page 143 “Posted Sales Invoices” · on Table 112 “Sales Invoice Header” · via Using Query objects
• Page 144 “Posted Sales Credit Memos” · on Table 114 “Sales Cr.Memo Header” · via Using Query objects
• Page 456 “No. Series” · on Table 308 “No. Series” · via Using Query objects
• Page 457 “No. Series Lines” · on Table 309 “No. Series Line” · via Using Query objects
• Page 458 “No. Series Relationships” · on Table 310 “No. Series Relationship” · via Using Query objects
• Page 459 “Sales & Receivables Setup” · on Table 311 “Sales & Receivables Setup” · via Using Query objects
• Page 460 “Purchases & Payables Setup” · on Table 312 “Purchases & Payables Setup” · via Using Query objects
• Page 461 “Inventory Setup” · on Table 313 “Inventory Setup” · via Using Query objects
• Page 9300 “Sales Quotes” · on Table 36 “Sales Header” · via Using Query objects
• Page 9301 “Sales Invoice List” · captioned “Sales Invoices” · on Table 36 “Sales Header” · via Using Query objects
• Page 9303 “Blanket Sales Orders” · on Table 36 “Sales Header” · via Using Query objects
• Page 9304 “Sales Return Order List” · captioned “Sales Return Orders” · on Table 36 “Sales Header” · via Using Query objects
• Page 9305 “Sales Order List” · captioned “Sales Orders” · on Table 36 “Sales Header” · via Using Query objects
• Page 9306 “Purchase Quotes” · on Table 38 “Purchase Header” · via Using Query objects
• Page 9307 “Purchase Order List” · captioned “Purchase Orders” · on Table 38 “Purchase Header” · via Using Query objects
• Page 9309 “Purchase Credit Memos” · on Table 38 “Purchase Header” · via Using Query objects
• Page 9310 “Blanket Purchase Orders” · on Table 38 “Purchase Header” · via Using Query objects
• Page 9311 “Purchase Return Order List” · captioned “Purchase Return Orders” · on Table 38 “Purchase Header” · via Using Query objects

Source: Microsoft Learn (CC BY 4.0). Descriptions are Learn’s own.