The “SystemId” in Microsoft Dynamics 365 Business Central 2019 release Wave 2

I’m returning after a very interesting workshop on DevOps in Berlin .. . At this moment, I’m wasting my time in the very “attractive” TXL airport because of my delayed flight. And how can I better waste my time than to figure out some stuff regarding Business Central.

Figuring out indeed, because I have barely internet, a crappy seat, nearly no access to food, … so for me this is a matter of bury myself so I don’t have to pay attention to my surroundings ;-). Anyway .. It’s not all that bad .. but a delayed flight is never nice. Anyway…..

opic of today: the new systemId!

While I was converting our app to be able to publish on the Wave 2 release .. this was something that I noticed:

All the “integration id’s” are marked for removal, and – most interesting – will be replaced by “the systemID”. What is that? Google, Microsoft Docs .. none of my conventional resources helped me finding out what SystemID is .. luckily, I did come across some information on yammer by Microsoft ;-)..

RecordIDs

You probably all know RecordIDs, right? A single “value” that referred to a specific record in a specific table. We all used them in generic scenarios, right? Also Microsoft – I don’t know if you know “Record Links”? A system table that stores notes and links to specific records? Well, the link to the record is made through RecordID. We have been using it for years .. . Now, a big downside of using RecordIds was the fact when you would rename the record (one of the fields of the keys), it would change its RecordId as well .. and all of a sudden, you could lose the connection in all tables where you stored that specific ID. Long story short – not ideal for integration or generic scenarios…

Surrogate Keys

And this is where “surrogate keys” of my good friend Soren Klemmensen came into place. He came up with a design pattern (well, I don’t know if he came up with it – but he sure advocated it for a long time) that described how to implement having a dedicated unique key of one field for a record. Basically: add a field in a table, and make sure it has a unique GUID. Make it that all these surrogate keys have the same FieldNo, and you are able to generically access the value of any of the keys for any record.

This is something Microsoft actually implemented themselves. And the code is all over the place. Even still in Wave2, we have the code to fill the “Integration Ids” as they call it. Nice system, but a lot of plumbing needed to make it work. I don’t know if there was a design pattern that described what you needed to do to apply this on your own tables – I never did ;-). But definitely interesting to do for many scenarios. Thing is .. quite a lot of work.

The SystemID

Now, as you got for the first screenshot: Microsoft is abandoning this field 8000 (that so-called “integration id”) – their first implementation of the surrogate keys – and will implement “SystemId” from the platform. Meaning: whatever you do: you will ALWAYS have a key called “systemId” for your table, which is a unique GUID in that table that can identify your record, and will never be changed – even when you would rename your record.

How cool is that! Here is an example of a totally useless table I created to show you that I have the systemId in intellisense:

What can we expect from the systemId?

Well, in my understanding – and quite literally what I got from Microsoft (thanks, Nikola 😉 ):

  • It exists on every record
  • But not on virtual/system tables (not yet, at least)
  • You can even set it in rare scenarios where you want to have the same value (e.g. copy from one table to another, upgrade…). Simply assign System Id to the record and do Insert(true,true) – 2x true
  • There is a new keyword – GetBySystemId to fetch by system id
  • It is unique per table, not per DB. Customers and items may have same IDs, though is hard if you are not manipulating it yourself, since guids are unique. Let’s say, they are “probably” unique 😉 – but on SQL, there is a unique key defined on the field, so only guaranteed per table.
  • Integration Record is still there, however the Id of the Integration Record matches the SystemId of the main record (Microsoft has code and upgrade in place)
  • You can only have simple APIs on it (no nesting, like lines). At this point, at least. It should be fixed soon, which is why the APIs are not refactored yet to use SystemId instead of Id.

A few more remarks

IF you would create a field that refers to a systemId, then it makes sense to use the DataClassification “SystemMetadata” for it. Not because I say .. just but because I noticed Microsoft does ;-).

Another not unimportant something I noticed: this is a system-generated field. So if you would need the fieldnumber, you have “recref.SystemIdNo”:

My take on it

From what I understood: there is work to do, but things are looking good:-). In fact, it is exactly what we have been asking for – and Microsoft delivers. Again! Great! I know this will see a lot of use in the (near) future! Within the Base Application, and in lots of apps.

Do know, I didn’t have any documentation about this – so all is based on some small piece of remark on yammer, and things I could see in code… So – if you have anything to add – please don’t hold back ;-). That’s why I have a comment section ;-).

5.00 avg. rating (99% score) - 6 votes

Permanent link to this article: https://www.waldo.be/2019/09/20/the-systemid-in-microsoft-dynamics-365-business-central-2019-release-wave-2/

6 comments

4 pings

Skip to comment form

    • Michael Voigt on September 20, 2019 at 8:47 am
    • Reply

    They didn’t have to look far for inspiration Ax/FFO have had surrogate keys for 20 years, (coincidently called RecordId), an autoincremented system bigint existing at every table. Foreign key references from e.g. a SalesLine to an Item is by Item.RecordId , not the ItemNo. The allows primary keys (e.g. ItemNo) to be easily renamed, and references to long (primary keys will only take up a bigint in the database. Also, the platform offer means to automatically convert the surrogate key to primary key when inserting at pages and reports.

    I kinda liked the surrogate key being a bigint, as sorting by RecordId would tell you the exact order records was inserted for any table, which is always immensily usefull during debugging and problem solving. It raised other issues as RecordId’s is not unique across databases or companies, namely if you tried to import/export data and needed the unique identifier to merge stuff properbly. So I can understand why they will want do a guid instead (but I will still miss the sorting!)

    Off-topic: Thank you for your always interesting articles. It’s a great blog (but you knew that!) 🙂

      • waldo on September 20, 2019 at 8:50 am
        Author

      Thanks for the insight – and the nice words ;-).

    • Heinz Herbeck on October 16, 2020 at 12:27 pm
    • Reply

    Interesting article – I found it on Google after we discovered the hard way that the SystemId is copied from source to destination record when using Rec := Rec2 or Rec.COPY(Rec2). If the destination record is then saved in the database, an error occurs because of the duplicate SystemId. The only solutions I have found so far were to either use TRANSFERFIELDS with second parameter set to FALSE, or save the old SystemId before the copy and assign it back afterwards.

    While this copying of the SystemId might be intended behavior, it poses a real challenge for our modules, which have been converted from C/AL to AL. It is well-nigh impossible to scan thousands of objects for direct record assignments with subsequent INSERT, so we have decided to fix the problem on a case-by-case basis…

    Any other solutions or hints are of course appreciated.

    Kind regards,
    Heinz

      • waldo on November 6, 2020 at 4:27 pm
        Author

      I wasn’t aware of this, thanks for sharing!
      No tips either, sorry 😉

    • Matt on April 28, 2021 at 6:31 am
    • Reply

    What is the benefit of this over a recordid field? Only thing I could see is if you wanted to filter specifically by the table id.

      • waldo on April 28, 2021 at 7:58 am
        Author

      The record-id will change if you rename the record – that’s a massive drawback in many scenarios.. .

  1. […] The “SystemId” in Microsoft Dynamics 365 Business Central 2019 release Wave 2 […]

  2. […] Source : Waldo’s Blog Read more… […]

  3. […] this blog https://www.waldo.be/2019/09/20/the-systemid-in-microsoft-dynamics-365-business-central-2019-release… , Waldo explains very well what you get with the SystemId field.  I will try to extend this list […]

  4. […] this blog https://www.waldo.be/2019/09/20/the-systemid-in-microsoft-dynamics-365-business-central-2019-release… , Waldo explains very well what you get with the SystemId field.  I will try to extend this list […]

Leave a Reply to Michael Voigt Cancel reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.