How to Expose DateTime Fields for Tables That Don't Have Them in Business Central (DIY)
Before attempting to implement any of the solutions described in this guide, ensure you meet the following prerequisites:
Access to Business Central Development Environment:
You need access to a Business Central Sandbox or Production environment where you have the necessary permissions to deploy extensions.
This typically means having a Business Central license that includes development capabilities or access granted by an administrator.
Visual Studio Code (VS Code) with AL Language Extension:
VS Code: Download and install Visual Studio Code.
AL Language Extension: Install the official Microsoft AL Language extension from the VS Code Marketplace. This extension provides the necessary tools for developing in AL.
AL Development Skills:
Basic understanding of AL (Application Language) syntax and concepts, including:
tableextension and pageextension objects.
codeunit and event subscriber concepts.
Basic data types (e.g., DateTime, Record).
Understanding of the Rec and xRec variables in table triggers/events.
Business Central Administration Access (for Publishing Web Services and Change Log):
To publish API pages as web services (Options 1 and 2), you'll need administrative permissions within Business Central.
To configure and enable the Change Log (Option 3), you'll also require appropriate administrative rights.
Microsoft Dynamics 365 Business Central Tenant:
A running instance of Business Central, either cloud-based (SaaS) or on-premise.
Familiarity with OData (for External Consumption):
While not strictly required for AL development, a basic understanding of OData (Open Data Protocol) will be beneficial for consuming the exposed DateTime fields in external tools like Power BI or Postman.
Option 1: Leveraging System Fields (If Available)
Business Central tables often come with a set of hidden, automatically managed system fields. These fields are read-only and provide valuable metadata about record creation and modification.
Available System Fields:
SystemCreatedAt:Records the exact date and time when the record was first created.SystemModifiedAt: Records the exact date and time of the last modification to the record.SystemCreatedBy: Stores the User ID of the user who created the record.SystemModifiedBy: Stores the User ID of the user who last modified the record.
These fields are incredibly useful for auditing and tracking changes without requiring custom development.
Applicability Tip: This option is the simplest and most efficient if the base table already supports these system fields, which is common for most standard Business Central tables.
1.1 Exposing SystemModifiedAt on a Page (for User Interface)
You can make SystemModifiedAt visible directly on a user-facing page using a pageextension. This allows users to quickly see the last modification timestamp for individual records within the UI.
// AL Code: PageExtension to expose SystemModifiedAt
pageextension 50100 CustomerListExt extends "Customer List"
{
// Extend the layout section of the existing "Customer List" page
layout
{
// Add a new control field at the end of the specified control group (e.g., General, Details, etc.)
// 'Control1' is a placeholder name for the control group. Adjust as per the target page's structure.
addlast(Control1)
{
// Define a field to display the SystemModifiedAt value from the current record (Rec)
field(SystemModifiedAt; Rec.SystemModifiedAt)
{
// Specifies that this field should be visible in all application areas
ApplicationArea = All;
// You can add a tooltip for better user experience
// ToolTip = 'Specifies the date and time when the customer record was last modified by the system.';
}
}
}
}Explanation:
pageextension 50100 CustomerListExt extends "Customer List":Creates an extension to the standard "Customer List" page.layout:Defines modifications to the page's visual layout.addlast(Control1):Adds the new field as the last control within a specific content area on the page (e.g., the General tab or a specific group). You might need to inspect the base page to find the correct Control ID or Area ID to place it appropriately.field(SystemModifiedAt; Rec.SystemModifiedAt):Defines the field using the SystemModifiedAt property of the Rec (current record) variable.ApplicationArea = All:Ensures the field is visible across all application areas in Business Central.
1.2 Exposing SystemModifiedAt via an API Page (for External Consumption)
it For external systems (e.g., Power BI, middleware, custom integrations) that need to consume this timestamp, you should expose SystemModifiedAt through a dedicated API page. This provides a clean, OData-enabled endpoint.
// AL Code: API Page to expose SystemModifiedAt for Customers
page 50101 CustomerAPIPage
{
// Defines the page type as an API page
PageType = API;
// Specifies the base table for this API page
SourceTable = Customer;
// API Properties: These are crucial for exposing the API endpoint correctly
APIPublisher = 'MyCompany'; // Your company's name or identifier
APIGroup = 'customer'; // A logical grouping for your APIs (e.g., 'sales', 'inventory')
APIVersion = 'v1.0'; // The version of your API
EntityName = 'customer'; // The singular name of the entity
EntitySetName = 'customers'; // The plural name of the entity collection (used in URL)
// Defines the layout of the API data
layout
{
// All API fields should be within the 'content' area
area(content)
{
// 'repeater' is used for listing multiple records (e.g., a collection of customers)
repeater(Group)
{
// Define the fields to be exposed in the API endpoint
field(no; "No.")
{
// Optionally, specify application area if needed
// ApplicationArea = All;
// ToolTip = 'Specifies the customer number.';
}
field(name; Name)
{
// ApplicationArea = All;
// ToolTip = 'Specifies the customer name.';
}
field(systemModifiedAt; SystemModifiedAt) // Exposing the SystemModifiedAt field
{
// ApplicationArea = All;
// ToolTip = 'Specifies the date and time when the customer record was last modified by the system.';
}
// Add more fields as required for your API
}
}
}
// Optional: Actions can be defined for API interactions (e.g., custom functions)
// actions
// {
// area(Processing)
// {
// action(MyCustomAction)
// {
// ApplicationArea = All;
// trigger OnAction()
// begin
// // Custom logic here
// end;
// }
// }
// }
}Steps to Publish the API Page:
Compile and Deploy: Ensure your AL code is compiled and deployed to your Business Central environment.
Access Web Services: In Business Central, use the search bar to find and open Web Services.
Create New Entry: Click the New action.
Set Object Type to Page.
Enter Object ID as 50101 (matching your API page ID).
Provide a Service Name, for example, customers (this will be part of your OData URL).
Publish: Mark the Published checkbox. Business Central will automatically generate the OData V4 URL for this service.
Option 2: Adding a Custom DateTime Field + Event Subscription
If the standard SystemModifiedAt field is not available or if you require a custom timestamp with specific logic (e.g., tracking changes only for certain fields, or a business-specific "last update" time), you can implement your own LastModifiedDateTime field. This involves adding the field to the table and then updating it via event subscribers.
2.1 Step 1: Add the Custom Field to the Table via tableextension
First, you'll extend the target table (e.g., Item) to include your new LastModifiedDateTime field.
// AL Code: TableExtension to add a custom LastModifiedDateTime field
tableextension 50102 ItemExt extends Item
{
// Define the fields to be added to the Item table
fields
{
// Field ID must be within the custom range (50000-99999)
field(50110; LastModifiedDateTime; DateTime)
{
// Specifies the data classification for compliance (e.g., GDPR)
DataClassification = SystemMetadata;
// Optionally, add a caption for user interface display
Caption = 'Last Modified Date/Time';
// Optionally, add a tooltip
// ToolTip = 'Specifies the last date and time this item record was modified.';
}
}
// You can also add keys, field groups, etc., in a table extension if needed
// keys
// {
// key(PK; "No.")
// {
// Clustered = true;
// }
// }
}
Explanation:
tableextension 50102 ItemExt extends Item:Creates an extension to the standard Item table.field(50110; LastModifiedDateTime; DateTime):Defines a new field with ID 50110, named LastModifiedDateTime, and of DateTime data type.DataClassification = SystemMetadata:This is important for data governance and compliance, indicating that the field contains system-generated metadata.
2.2 Step 2: Create an Event Subscriber to Update the Field
Next, you'll create a codeunit with an event subscriber. This subscriber will listen for a specific event (e.g., OnBeforeModifyEvent on the Item table) and update your LastModifiedDateTime field whenever the record is about to be modified.
// AL Code: Codeunit with Event Subscriber to update LastModifiedDateTime
codeunit 50103 ItemEvents
{
// This attribute defines an event subscriber
// ObjectType::Table: Specifies that the event originates from a table
// Database::Item: Specifies the table (Item) from which the event is published
// 'OnBeforeModifyEvent': The specific event to subscribe to. This event is published just before a record is modified.
// '': The event tag (empty in this case, meaning it applies to all modifications)
// false, false: Specifies if the subscriber should run in a new transaction and if it's for internal use only (typically false for external subscribers)
[EventSubscriber(ObjectType::Table, Database::Item, 'OnBeforeModifyEvent', '', false, false)]
procedure UpdateLastModified(var Rec: Record Item; var xRec: Record Item; RunTrigger: Boolean)
begin
// Validate and update the custom LastModifiedDateTime field with the current system date and time.
// Rec.Validate() ensures that any associated field triggers or logic are executed.
Rec.Validate(LastModifiedDateTime, CurrentDateTime);
end;
// IMPORTANT WARNING:
// Do NOT call Rec.Modify() or Rec.Insert() inside an OnBeforeModifyEvent subscriber.
// Doing so can lead to infinite recursion, causing the system to crash or freeze.
// The purpose of this event is to prepare the record before the actual modification.
// The modification operation itself is handled by the system after this event completes.
}Explanation:
codeunit 50103 ItemEvents:Creates a new codeunit.[EventSubscriber(...)]:This attribute declares the procedure as an event subscriber.ObjectType::Table, Database::Item:Specifies that we're subscribing to an event on the Item table.'OnBeforeModifyEvent':This is a standard Business Central event published just before a record is modified. Other useful events include OnAfterInsertEvent, OnAfterModifyEvent, etc., depending on your exact requirements.Rec.Validate(LastModifiedDateTime, CurrentDateTime):This is the core logic. CurrentDateTime returns the current system date and time. Rec.Validate() assigns this value to the LastModifiedDateTime field of the current record (Rec), ensuring that any associated field triggers or business logic are executed.
2.3 Exposing the Custom Field in an API Page
Just like with system fields, you'll want to expose this custom LastModifiedDateTime field through an API page for external consumption.
// AL Code: API Page exposing the custom LastModifiedDateTime for Items
page 50104 ItemAPI
{
// Defines the page type as an API page
PageType = API;
// Specifies the base table for this API page (the one with your custom field)
SourceTable = Item;
// API Properties: Similar to Option 1, these define your API endpoint
APIPublisher = 'mycompany';
APIGroup = 'item';
APIVersion = 'v1.0';
EntityName = 'item';
EntitySetName = 'items';
// Defines the layout of the API data
layout
{
area(content)
{
repeater(Group)
{
// Expose standard fields
field(no; "No.") { }
field(description; Description) { }
// Expose your custom LastModifiedDateTime field
field(lastModifiedDateTime; LastModifiedDateTime) { }
// Add any other relevant fields for your API
}
}
}
}
Publishing and Consumption: After deploying this AL code, follow the same "Steps to Publish the API Page" as described in Option 1.2 to make this new API accessible via OData.
Option 3: Utilizing the Change Log for Comprehensive Audit Trails
The built-in Change Log feature in Business Central offers a robust, no-code solution for tracking all modifications (inserts, modifications, deletions, renames) to records and fields. It provides a detailed audit trail, including who made the change, what field was changed, the old and new values, and—critically—the exact timestamp of the change.
This option is ideal when you need a complete historical record of all data changes, rather than just the last modification date.
Steps to Configure Change Log:
Search for
"Change Log Setup": In Business Central, use the search bar (Alt+Q) and type Change Log Setup.Enable Change Log: On the Change Log Setup page, select the
Enabled checkbox to activateThe feature.Define Tables and Fields to Log:
Click on the Tables action in the ribbon.
This will open the Change Log Setup (Tables) page.
Click New to add a table.
Select the Table ID of the table you want to monitor (e.g., Item, Customer, Vendor).
In the
Log Modifications, Log Insertions, Log Deletions, Log Renames fields,Choose All Fields or Some Fields based on your requirements.If you select Some Fields, a new window will open where you can specify which individual fields of that table should be logged.
Repeat for all tables you wish to track.
Accessing Change Log Entries:
Once configured, Business Central automatically logs changes. You can view these logs by searching for Change Log Entries. Each entry will include:
Date and Time: The timestamp of the change.
User ID: Who made the change.
Table Name: The table that was affected.
Field Name: The specific field that was modified (if Some Fields or All Fields selected).
Old Value and New Value: The data before and after the change.
Entry Type: Indicates if it was an Insert, Modify, Delete, or Rename.
Considerations:
Performance: While powerful, logging all changes for many tables and fields can impact database performance, especially in high-volume environments. Use it judiciously.
Data Volume: The Change Log Entries table can grow very large over time. Consider a retention policy or archiving strategy.
No Direct Field: The Change Log doesn't add a LastModifiedDateTime field directly to your table; instead, it provides a separate audit trail. You would query the Change Log Entry table to find the last modification for a specific record.
Option 4: Leveraging OData for External Access to DateTime Fields
This option focuses specifically on how to make any of the previously discussed DateTime fields (system or custom) accessible to external tools and platforms using Business Central's OData capabilities. OData (Open Data Protocol) allows for standardized RESTful access to data.
Whether you're using SystemModifiedAt or your custom LastModifiedDateTime field, the process for exposing them via OData is similar, as outlined in Option 1.2 and Option 2.3.
Consuming OData Feeds in External Tools
Once your API page (or a standard page with exposed DateTime fields) is published as a Web Service in Business Central, you can connect to it from various external applications.
1. Connecting from Power BI: Power BI is a popular tool for data visualization and reporting, and it can easily consume OData feeds.
Open Power BI Desktop.
Go to Get Data from the Home tab.
Select OData Feed from the list of data sources, then click Connect.
In the OData feed dialog box, paste the URL of your published API page.
Example URL:
https://<tenant_id>.businesscentral.dynamics.com/ODataV4/Company('<Your_Company_Name>')/itemsReplace
<tenant_id>with your Business Central tenant ID.Replace
<Your_Company_Name>with the exact name of your company in Business Central (e.g., CRONUS US).Replace items with the
EntitySetNameyou defined in your API page (e.g., customers).
Click OK.
You might be prompted for authentication. Choose Organizational account and sign in with your Business Central credentials.
In the Navigator window, select the table you want to load (e.g., items) and click Load or Transform Data for further manipulation.
The LastModifiedDateTime or SystemModifiedAt field will now be available in your Power BI dataset.
2. Querying with OData Filters (e.g., in Power BI or Postman): OData allows you to filter data directly in the URL, which is very efficient for retrieving only the data you need, especially when dealing with large datasets.
Filtering by Date/Time: You can use $filter query option to retrieve records modified after a certain date. Example: Get items modified on or after December 1st, 2024.
https://<tenant>.businesscentral.dynamics.com/ODataV4/Company('<Your_Company_Name>')/items?$filter=lastModifiedDateTime ge 2024-12-01T00:00:00ZlastModifiedDateTime: The field name as exposed in your API.ge:Greater than or equal to. Other operators include gt (greater than), lt (less than), le (less than or equal to), eq (equal to), ne (not equal to).Date and time in ISO 8601 format. T separates date and time, and Z indicates UTC (Coordinated Universal Time).
Ordering Results: You can use
$orderbyto sort the results.Example: Get items ordered by lastModifiedDateTime in descending order.
https://<tenant>.businesscentral.dynamics.com/ODataV4/Company('<Your_Company_Name>')/items?$orderby=lastModifiedDateTime descSelecting Specific Fields: Use $select to retrieve only the fields you need, reducing payload size. Example: Get only No., Description, and LastModifiedDateTime.
https://<tenant>.businesscentral.dynamics.com/ODataV4/Company('<Your_Company_Name>')/items?$select=no,description,lastModifiedDateTime
3. Accessing from Excel: You can also connect to OData feeds directly from Excel to pull in data.
In Excel, go to Data tab > Get Data > From Other Sources > From OData Feed.
Paste your OData URL and follow the authentication prompts as in Power BI.
Select the table and load the data into your Excel sheet.
Last updated