Dynamics 365 Business Central and DataTransfer: be careful!

Not too long ago, Stephano shared a concern regarding “TryFunctions”. It’s a must read, and you can find it here: Dynamics 365 Business Central and TryFunctions: be careful! – Stefano Demiliani. I think it’s important to warn people about things that act different than people might think – so let me take this opportunity to put yet another thing on the table.

DataTransfer

Since Business Central v21, a new feature has been introduced: the DataTransfer type, which enables for fast moving of data between fields or tables with an insane performance gain over whatever we were able to do before we had this. In short: we’re able to copy the data of an entire table with only one SQL statement.

Here are some resources where you can read up on the DataTransfer:

• DataTransfer Data Type – Business Central | Microsoft Learn
• Dynamics 365 Business Central 2022 Wave 2: introducing the new DataTransfer data type – Stefano Demiliani
• A Business Central “Upgrade Code” saga… (waldo.be)

This week, I had a requirement where I had to apply this in a really big case. I’m going to spare you the details, but I had to move the data of about 200 tables to a new extension (pretty much the same extension, but different ObjectIds and ObjectNames – as I said, I’m going to spare you the details). I even came up with a way to do it generically: only with a few lines of code, it would “datatransfer” all data from all tables to the ones from the other extension. Perfect for DataTransfer! Or so I thought….
Now, at this customer, there are a lot of interfaces .. LOADS of custom API’s. And when you smell “API”, you basically need to put extra attention on the fields that the third parties rely on: the “SystemID” and the “SystemModifiedAt“.

It never occurred to me that these fields would cause a problem in DataTransfer scenarios – although, I must admit that during one of my performance workshops, I once got the question “are systemid’s also copied when using DataTransfer”? And I remembered that I answered with “look, I didn’t test it, but it seems obvious that they are copied, or at least, that you configure it to do so” – and I never really bothered to test it … until this week …

How wrong was I …

It turns out, for the moment:

• It’s NOT possible to copy SystemId
• It’s NOT possible to copy any System-field for that matter
• It’s even NOT possible to run a procedure to correct the SystemId afterwards

What I did to find this out is simply to run some upgrade code with DataTransfer. You can find the project that I used for this here: waldo1001/blog.DataTransferAndSystemFields (github.com). More specifically, you can find the upgrade-codeunit here.

Basically, from the moment I try to copy anything to the SystemId, like this:

When publishing and upgrading the app, it would show me:

Pretty much the same for any System-field. So if I would add the SystemModifiedAt, I get the same:

This made me wonder – can I move the SystemId to a new field though? And yes you can! Apparently, the system-fields can’t be the destination, but they can be the source. So I CAN do this:

Which would perfectly put the old SystemId into field 60149 of the new table. Useful? Well, only if I would be able to copy the original value to the SystemId field later, but I can’t do that either, because it’s not allowed to modify the SystemId at any time.. .

So .. DataTransfer is not an option. 🤷‍♂️
On a sidenote – I had a comment on twitter by Loïc that when you do a CopyCompany, it ALWAYS copies the System-fields … and indeed. These two screenshots are from two different companies:
Original company

Copied company

Strange, isn’t it? We seem to live in a world where data-move is implemented upside down: when you expect it to be copied, it doesn’t, and when you expect it to NOT being copied, it IS copied 🙈.

Why all the fuss, waldo? Why can this be a problem?

Well, integration scenarios to start with! To quote Guido Robben on the twitter thread I had about this:

everything that is linked to the system-id of that table is orphaned after that.

— Guido Robben (@guidorobben) February 7, 2023

I couldn’t put it in a better way 🤷‍♂️. External systems have a connection with that record – and after the upgrade – not anymore. You’re basically (possibly) breaking your interfacing with other systems!

And even in your own code, you could have used the SystemId for different purposes. Like, what if you have code like this?

I mean – changing a SystemId is like changing a primary key. It should not be done. And if we move data to a refactored table, we need to make sure the SystemId is preserved!

Even more – as an ISV – you don’t know who is doing something with any table of your product. From the moment I’m moving data from one table to another, you’re basically breaking possible API’s that somebody created on top of your data.. .

So .. for now .. for me personally .. It’s too dangerous to be using DataTransfer in ANY context.

Only for now, because ..

Microsoft is aware, and is working on a fix!

I’m so grateful Microsoft monitors “ranters” like me sometimes .. . Jesper confirmed here that Microsoft is aware, and is working on a fix!

Bug logged! We’re on it ☺️

— Jesper Schulz-Wedde (MSFT) (@JesperSchulz) February 8, 2023

In the meantime …

What can you do in the meantime? Well – you CAN upgrade without DataTransfer. In a way, if you want to preserve SystemId (and SystemModifiedAt – and even SystemCreatedAt if you will), you ARE able to simply do it with a normal upgrade routine. Sure, it’s slower, but at least, you’re not breaking anything.

You can find a simple example here, where you see I simply take the SystemId from the old table, and copy it to the new table: I CAN provide my own SystemId on insert, I just can’t modify it (on modify).

Do know: I have snippets for that in my VSCode Extension. These will take you a long way:

• tUpgradeCodeunitWaldo
• tUpgradeTableProcedureWaldo (where I even added the SystemId by default!

Conclusion

I’m a big fan of DataTransfer. I really am! It gives a lot of power while refactoring your apps. But we really need the option to be able to copy the system-fields (not only SystemId, but also SystemModifiedAt, and may be even SystemCreatedAt – and why not the UserIds🤷‍♂️) as well. Until it does, it’s simply useless to me (I’m not going to take any risk), so I sure hope Microsoft will provide a solution fast.

I would even extend this to the CopyCompany functionality, where I’d really like the option to NOT copy the system-fields for pretty much the same (opposite) reason (although, that has lower priority for me personally .. ;-)).