Friday Tips #2: Using AutoIncrement for Log Tables in Dynamics 365 Business Central

Hi Readers,

When working with Dynamics 365 Business Central, log tables are a common design pattern used to track changes, operations, or other important actions in the system. A critical aspect of designing an efficient log table is ensuring that each entry is uniquely identified, which is where the AutoIncrement property comes into play.

In this Friday Tips, we’ll explore how to use AutoIncrement to efficiently manage log tables, ensuring data integrity and performance.

What is AutoIncrement?

The AutoIncrement property in Dynamics 365 Business Central is used to automatically generate a unique, incremental value for a specified field when a new record is inserted into a table. This is particularly useful in log tables, where each entry needs a unique identifier (such as an entry number or ID) to ensure the order of events and enable efficient querying.

When enabled, Business Central takes care of generating the unique value, and you don’t need to manually handle this process in your AL code.

Applies to

• Table Field

The fields must be of Integer Data Type and BigInteger Data Type.

Microsoft Document: AutoIncrement Property – Business Central | Microsoft Learn

Using AutoIncrement for a Log Table

Let’s look at a simple example of how to set up a log table with the AutoIncrement property. In this case, we’ll create a table called Log Entry, where each log entry has a unique Entry No. field that is automatically incremented.

In this example:

• The Entry No. field is set as AutoIncrement, which means Business Central will automatically assign a unique number to each new log entry.
• The Log Message field are used to store the message of the log entry.

Advantages of Using AutoIncrement

• Ensures Uniqueness: Using AutoIncrement ensures that every record in the log table has a unique identifier. This is crucial for logging operations where you need to track individual entries, especially in large systems with multiple users and transactions happening simultaneously.
• Simplicity in Code: No need to manually calculate or manage numbering, reducing the risk of conflicts in environments with multiple users or processes inserting records simultaneously.

Example Usage in AL Code

Now that we’ve set up our Log Entry table, let’s see how you can insert a new log entry in AL code:

In this example, the InsertLogEntry procedure initializes a new log record, sets the Log Message, and other fields, and then inserts the record into the table. The AutoIncrement property will automatically assign a value to Entry No.

Remarks

A table can only contain one auto-increment field. The numbers assigned to this field will not always be consecutive for the following reasons:

• If you delete some records from a table, the numbers used for these records are not reused.
• If several transactions are performed at the same time, they will each be assigned a different number. However, if one of these transactions is rolled back, the number that it was assigned is not reused.

If you add an auto-increment field to an existing table, the field automatically generates consecutive values and inserts them into the table. If you enable the AutoIncrement property for a field that already contains data, there must be no zero values in the field.

The AutoIncrement property is designed to always assign numbers automatically. If you want to insert a record, be sure that the value in this field is blank before you insert the record.

The AutoIncrement property does not work with Dynamics 365 Business Central temporary tables.

Conclusion

In this edition of Friday Tips, we explored how using AutoIncrement can simplify and improve the management of log tables in Dynamics 365 Business Central. It provides a simple, effective way to ensure unique identification of log entries while improving query performance and reducing manual effort in code. Happy coding, and stay tuned for more tips next Friday!