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.
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:
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:
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!
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:
tUpgradeTableProcedureWaldo(where I even added the SystemId by default!
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 .. ;-)).
In integration scenarios I’ve always recommended the external system to not store SystemIds and only use them for short procedures. Because a record might be “updated” using a Delete/Insert with an entirely new SystemId while the PK will remain the same.
Dont know if this is useful info in this context but you can specify a custom SystemID when using API Page, see here:
The key part is the page trigger OnInsertRecord:
trigger OnInsertRecord(BelowxRec: Boolean): Boolean
exit(not Insert(true, true));
Yes indeed, thanks for you comment.
But it is NOT (yet?) possible when you are moving data with DataTransfer, which might happen in refactoring scenarios, where it’s VERY important that the systemId is persisted .. 🤷♂️