Muokkaa

Jaa

System Fields

  • Artikkeli

System fields are fields that are automatically included in every table object by the platform. Dynamics 365 Business Central includes the following system fields:

  • SystemId
  • Data audit fields
  • Timestamp

System fields are assigned numbers in the range 2000000000-2147483647. This range is reserved for system fields. You'll get a design-time error if you give a field a number in this range.

SystemId field

APPLIES TO: Business Central 2019 release wave 2 and later

The SystemId field is a GUID data type field that specifies a unique, immutable (read-only) identifier for records in the table. The SystemId field has the following characteristics and behavior:

  • All records must have a value in the SystemId field.
  • You can assign your own value when a record is inserted in the database. Otherwise, the platform will automatically generate and assign a value.
  • Once the SystemId has been set, it can't be changed.
  • There's always a unique secondary key on the SystemId field to ensure records don't have identical field values.
  • The SystemId field is given the field number 2000000000.

The SystemId field is exposed in the platform code and for AL code, allowing you to code against it. For example:

  • The Insert(Boolean, Boolean) lets you specify the SystemId value for a record, instead of using one assigned by the platform:

    myRec.SystemId := '{B6666666-F5A2-E911-8180-001DD8B7338E}';  
myRec.Insert(true, true);

  • The GetBySystemId(Guid) uses the SystemId to get a record:

    id := myRec.GetBySystemId('{B6666666-F5A2-E911-8180-001DD8B7338E}';

  • The SystemIdNo() gets the field number used by the SystemId field in the table:

    myRec.Open(DATABASE::MyTable);
SystemIdFieldNo := myRec.SystemIdNo();

  • The TableRelation lets you use the SystemId field to set up table relationships:

    field(1; MyField; Guid)
{
    DataClassification = ToBeClassified;
    TableRelation = Customer.SystemId;
}

  • The SystemId field can be used as a binding key to an API:

    page 50100 "Customer Entity"
{
    PageType = API;
    ApplicationArea = All;
    UsageCategory = Administration;
    SourceTable = Customer;
    ODataKeyFields = SystemId;
    ...

APPLIES TO: Business Central 2020 release wave 2 and later

  • If the SystemId field is specified in a Web Service POST request, the OData stack persists the value in the database.
  • The SystemId field can be used as part of a (non-primary) key.
  • You can show the SystemId field as a field on a page.
  • You can link FactBoxes/page parts using the SystemId field.

Data audit fields

APPLIES TO: Business Central 2020 release wave 2 and later

Every table in Business Central includes the following four system fields, which can be used for auditing records:

Field name (in AL) Column name (in database) Data type Field number Description
SystemCreatedAt $systemCreatedAt DateTime 2000000001 Specifies the data and time that the record was created.
SystemCreatedBy $systemCreatedBy GUID 2000000002 Specifies security ID (SID) of the user that created the record.
SystemModifiedAt $systemModifiedAt DateTime 2000000003 Specifies the data and time that the record was last modified.
SystemModifiedBy $systemModifiedBy GUID 2000000004 Specifies the SID of the user that last modified the record

Runtime characteristics

At runtime, the data audit fields have the following characteristics and behavior:

The platform will automatically generate and assign values according to the following triggers:

Note

You can assign the values, but the values written to the database are always provided by the platform.

Fields are populated as follows:

  • When a new record is created, before calling Insert, the audit fields are given blank GUIDs and blank dates as values.

  • When a record is first inserted, the fields are populated with actual values.

    The $systemCreatedBy and $systemModifiedBy fields are given the same value. So are the $systemCreatedAt and $systemModifiedAt fields.

    The $systemCreatedBy and $systemCreatedAt fields won't change after this point.

  • When a record is updated, the $systemModifiedBy and $systemModifiedAt fields are changed.

The platform won't populate audit field values in these cases:

  • Copying company. The values in the tables of the company being copied stay the same, and the values are copied to the tables of the new company.
  • Synchronizing the table schema with the application.

Note

Audit fields can't be imported with configuration packages.

In AL

The data audit fields are exposed in AL code. As a developer, the audit fields give you an easy and performant way to program against historical data. For example, you can write AL queries that return data changes since a specific date and time.

The following methods are available on the RecordRef data type:

Method Description
SystemCreatedAtNo Gets the field number that is used by the SystemCreatedAt field.
SystemCreatedByNo Gets the field number that is used by the SystemCreatedBy field.
SystemModifiedAtNo Gets the field number that is used by the SystemModifiedAt field.
SystemModifiedByNo Gets the field number that is used by the SystemModifiedBy field.

There are a couple points of interest you should know:

  • If a record is copied into a temporary table, the data audit field values are copied as well. The values aren't changed by the server when calling a modify or insert method. 
  • It's possible to use audit fields in a key. The platform doesn't automatically index these fields in any way.

If you want to translate a user security ID GUID to the corresponding user name, the following AL code might be useful:

procedure GetUserNameFromSecurityId(UserSecurityID: Guid): Code[50]
    var
        User: Record User;
    begin
        User.Get(UserSecurityID);
        exit(User."User Name");
    end;

Timestamp field

The timestamp field contains rowversion numbers for records, as maintained in SQL Server. In SQL server, timestamp is a synonym for the rowversion data type. The value of the timestamp field is an automatically generated, unique binary number. The timestamp is a mechanism for version-stamping table rows.

A typical use of the timestamp field is for synchronizing data changes in tables. It lets you identify records that have changed since the last synchronization. For example, you can read all the records in a table, then store the highest timestamp value. Later, you can query and retrieve records that have a higher timestamp value than the stored value.

In AL

In AL code, the timestamp is accessible through the SystemRowVersion field. However, you can't write to the field.

The following methods are also available on the Database data type:

Method Description
LastUsedRowVersion Gets the last used rowversion from the database. This method does the same as the @@DBTS (Transact-SQL) function.
MinimumActiveRowVersion Gets the lowest active rowversion in the database. This method returns the lowest rowversion of any uncommitted rows. Rows that have a lower timestamp than this returned value are guaranteed to be committed. If there are no active transactions, the value is equal to LastUsedRowVersion + 1. This method does the same as the MIN_ACTIVE_ROWVERSION (Transact-SQL) function.

Expose the timestamp field in Business Central version 20 and earlier

In versions of Business Central earlier than version 21, the timestamp field is hidden. But, you can expose it by using SqlTimestamp Property. You're then able to write code against it, add filters, and so on, similar to any other field in a table.

  1. Add a field that has the data type BigInteger.

    Specify a name for the field, such as Record Time Stamp. You can specify any valid name for field, but you can't use timestamp because it's a reserved name.

  2. Set the SqlTimestamp property to true.

    For example:

    field(3; "Record Time Stamp"; BigInteger)
{
    SqlTimestamp = true;
}

Alternatively, you can use a FieldRef Data Type variable to access the timestamp value of a record, as follows:

  1. Create a RecordRef Data Type variable that references the record in a table for which you want to retrieve its timestamp.

  2. Use the Field Method on the RecordRef variable to get the FieldRef for the field that has the number 0. This field contains the timestamp value.

The following example shows how to retrieve the timestamp value for the first record in the Customer table. RecordRef and FieldRef are RecordRef Data Type and FieldRef Data Type variables, respectively.

RecordRef.Open(DATABASE::Customer);
RecordRef.FindFirst();
FieldRef := RecordRef.Field(0);
Message(Format(FieldRef.Value()));

Table Object
AL Development Environment
Table Overview
Table Extension Object
SqlTimestamp Property
Table Keys
Table Properties