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 ;-).