The views and opinions expressed in this blog are those solely of the author(s) and do not necessarily reflect Microsoft’s current policy, position, or branding. For official announcements and guidance on Dynamics 365 apps and services, please visit the Microsoft Dynamics 365 Blog.
Choose your path Increase your proficiency with the Dynamics 365 applications that you already use and learn more about the apps that interest you. Up your game with a learning path tailored to today's Dynamics 365 masterminds and designed to prepare you for industry-recognized Microsoft certifications.
Visit Microsoft Learn
2019 release wave 2 Discover the latest updates and new features to Dynamics 365 planned through March 2020
Release overview guides and videos Release Plan | Preview 2020 Release Wave 1 Timeline
Ace your Dynamics 365 deployment with packaged services delivered by expert consultants. | Explore service offerings
Connect with the ISV success team on the latest roadmap, developer tool for AppSource certification, and ISV community engagements | ISV self-service portal
The FastTrack program is designed to help you accelerate your Dynamics 365 deployment with confidence.
FastTrack Program | Finance TechTalks | Customer Engagement TechTalks | Upcoming TechTalks
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.
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.
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.
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.
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.
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.
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.
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.
All examples for this post you can find on GitHub https://github.com/mynavblog/Examples/tree/master/SurrogateKeys.
Business Applications communities