I’ve decided to write this quick post after having spent some hours on supporting a partner in order to discover problems with a Dynamics 365 Business Central online instance.
The partner has moved customer’s data from a Dynamics 365 Business Central v14 C/AL database (onpremise) to Dynamics 365 Business Central online. Migration was successful, but then he had a strange error on code when working on some records. The error was raised by simple GET operations on Code fields, like:
where CustomerNo contains the No. field (primary key) of the record to retrieve.
What happened here?
After investigating, we discover that the data migration migrates data from the original database with Code fields containing non-printing characters, like for example CRLF or TABS. These characters on Code fields were supported on old versions of the ERP, but the current platform does not support them (and a GET on them throws an error).
If you want to perform a clean data migration to the online service, before doing that it’s absolutely recommended to open the Business Central Administration Shell and then to execute the Invoke-NAVSanitizeField Powershell cmdlet.
This cmdlet trims all the unsupported characters from fields of type Code in the tenant database and returns an object containing the number of rows and values modified, the tables impacted and a list of potentially ignored records.
Executing it on the entire database is super easy. Just launch the following command:
$result = Invoke-NAVSanitizeField -ServerInstance YOURSERVERINSTANCE -Tenant YOURTENANT
Here $result is an object containing the results of the sanitize field operation.
As an example, here the execution on our test database for this case:
Result was this:
A Customer record with a CRLF field value on the No. was found in the original database (I think this was mainy caused by copy/paste from Excel cells. Remember that if you copy an entire cell, it has also CRLF characters in the value).
After data sanitization, problem solved (so please remember it). 🙂