Modifying data

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

Overview

This section groups the AL topics for writing, changing and deleting data, and for the special table types used when processing it. The core page documents the Insert, Modify, ModifyAll, Delete, DeleteAll and Truncate methods, including syntax, return values and when to prefer Truncate over DeleteAll.

Related pages cover table types: temporary tables (in-memory, reduce network and database load) and virtual tables, with the Date and Integer virtual tables as specific examples. Other pages cover Media and MediaSet data types, runtime filter pages built with FilterPageBuilder, properties that enable Microsoft Dataverse tables for extension development, and schemaUpdateMode settings for retaining test data.

Start with the Insert, Modify and Delete methods page for the basics. Then go to temporary or virtual tables as needed, and to the media, filter page, Dataverse or test data pages for those specific tasks.

Key points

• Insert, Modify, ModifyAll, Delete, DeleteAll and Truncate are the AL methods for maintaining data; Truncate is for high-performance bulk deletion.
• Temporary tables are in-memory and can be set up with the TableType property, temporary record variables, or the SourceTableTemporary page property.
• Virtual tables hold read-only system information computed at runtime and not stored in the database.
• The Date virtual table (ID 2000000007) has Period Type, Period Start, Period End, Period No. and Period Name fields for date ranges.
• The Integer virtual table (ID 2000000026) spans -1,000,000,000 to 1,000,000,000 and is used to control loops in reports.
• Media and MediaSet data types store images and documents in system tables, with better performance and caching than BLOB fields.
• FilterPageBuilder creates runtime filter pages for multiple tables in a modal dialog, using addtable and addrecord.
• schemaUpdateMode in launch.json (Synchronize, Recreate, ForceSync) controls whether test data is kept between publishes.

Learn pages

• Creating filter pages for filtering tables: Using the FilterPageBuilder data type to create a filter page in AL for Business Central.
• Date virtual table: The date virtual table in AL for Dynamics 365 Business Central
• Enabling Microsoft Dataverse Tables for Extension Development: This topic explains how to enable Microsoft Dataverse tables for the extension development process.
• Insert, Modify, ModifyAll, Delete, and DeleteAll Methods: Describes how to use the Insert, Modify, ModifyAll, Delete, and DeleteAll methods in Business Central
• Integer virtual table: The integer virtual table in AL for Dynamics 365 Business Central
• Synchronizing extension test data: Retaining table data after publishing an extension
• Temporary tables: Learn about temporary tables in AL for Business Central.
• Virtual tables: Virtual tables are system tables in AL for Dynamics 365 Business Central
• Working With Media on Records: Learn how to upload media, such as an image, to the database for displaying with records in the client.

Videos, posts and code changes

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

• #10055 Fix: migrating a document attachment deletes shared Tenant Media (code change): “Adds guard to prevent deletion of Tenant Media when other attachments”
• #10276 [master] Renewal Term field on the Create Contract Renewal Quote page does not accept the entered value such as 12M (code change): “Prevents overwriting of just-entered values with persistent record data”
• #10301 [Master]-Bug 646962: Withholding Tax Posting Setup (code change): “Restricts Calculation Base = Net to employee-only withholding taxes”
• #10447 [Bug][Subscription Billing] Price Update Template filters on Subscription Lines are overwritten by the proposal’s own default filters (code change): “Price Update Template filters on Subscription Lines are now correctly preserved”
• #10970 [Master] -SubBilling BillingLine doesn’t consider entries for usage date ranges crossing periods (code change): “Fixed subscription billing to correctly include usage data entries whose charge periods span”
• #11824 [29.x][Subscription Billing] Sales-Explode BOM fails for foreign-currency customers (code change): “Sales Subscription Line creation now correctly handles cached Sales Line data”
• #9712 [master] - Changing the contact of a Customer Subscription Contract doesn’t update the email of the contract. (code change): “Fixed missing email and phone number update when ‘Sell-to Contact No.’ is validated”
• #9850 Fix VariantCode lost during Contract Price Update proposal (code change): “Variant code from subscription header service object is now correctly passed”
• #133 Add TransferFields SkipFieldsNotMatchingType guidance (code change): “TransferFields only errors on type mismatches within the same extension”
• #209 3 AL/BC patterns: Insert/Delete trigger defaults on master data and declined Confirm in OnValidate (code change): “Record.Insert() and Delete() skip OnInsert and OnDelete by default”
• Dynamics 365 Business Central: finally we’ll have TRUNCATE table in SaaS. (community post): “Rec.Truncate AL method to enable efficient bulk deletion of table rows”
• Rec.Truncate in AL (community post): “Rec.Truncate is a new AL method based on SQL Server TRUNCATE”
• BC Friday Tips #68 Always Use Field Validation (community post): “Field validation in Business Central extensions ensures all business logic runs”

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