Surrogate Keys – what you can and what not?

In Wave 2 Microsoft introduced Surrogate Keys. What does it mean? When you create a new table one additional field will be created even if you do not have it in the fields. It is the SystemId field. For tests, I created a table with just two fields.

But if you will try to check what fields are in the table you will see that there is new field type GUID added automatically.

Also, the new key has been added to the SQL which is named SystemId.

What you can do and what not?

On this blog https://www.waldo.be/2019/09/20/the-systemid-in-microsoft-dynamics-365-business-central-2019-release-wave-2/ , Waldo explains very well what you get with the SystemId field.  I will try to extend this list with some can and cannot dos and some examples.

Insert, Modify and Rename

You can assign your own SystemId to the record when insert – as Waldo wrote you need to have two parameters. In this example, my record gets the same SystemId as the Customer record.

However, you cannot modify it later. Which is of course the way how it should work.

Remember that the record gets the SystemId only once. Even if you will rename the record it will stay the same. You can test it with a simple code below.

Get Record By SystemId

We all used to function Get(). With unchanged value in the SystemId, you can easily get that record without knowing the primary key. There is a new method which is called GetBySystemId(). It, the same as Get() returns the record but instead of primary hey you need to specify in the parameter the SystemId which you are looking.

It gives a lot of possibilities to simplify code in some cases.

Add SystemId to Page and Page type API

This field is primarily used in the API as the Id (this is why it replaced previous Id fields which you could find across the database). SystemId field you can add to the API pages but when you will try to show it on the standard page you will get an error.

Create Temporary Record with TransferFields

If you want to create a temporary record with function TransferFields you will do not have value in SystemId (I know it is not the “best practice” way but I know it happens sometimes). However, if you would assign the Rec to the Temporary Record the SystemId will not be null.

Using SystemId with RecordRef and FieldRef

When you would like to use the value of the fields with RecordRef and FieldRef you need to know the field number. The number of SystemId field is 2 000 000 000 but you do not need to know that. Microsoft added a new method to RecordRef which is called SystemIdNo() and it returns the SystemId field number.

Where you can find SystemId in Base Application?

At this moment there are two major places where you can find the SystemId in use. One I already mentioned – it replaced the fields Id which you could find. Each such place is commented.

More useful is the second place where you can find the SystemId. In the Purchase and Sales posted documents (so far Posted Sales Invoice, Posted Sales Cr. Memo and Posted Purchase Invoice) there is a new field Draft Invoice SystemId (or Draft Cr. Memo SystemId). During posting it gets value from the Sales Header document which was used for posting (so for example Sales Order or Sales Invoice).

You can use it when you want to check what are the documents which were created from the exact Order or Invoice document or if the sales document still exists.

Examples

All examples for this post you can find on GitHub https://github.com/mynavblog/Examples/tree/master/SurrogateKeys.