Remove unsupported characters with Sanitizing Your #msdyn365bc Data.

Hi Readers,

With start of New Year, I saw a new issue and learned something with that.

This reminded about an old article that I have written in past during data upgrade from Classic to RTC Version.

While writing that I thought this will never come up again but to my surprise that it's still there, but this time story is little bit different. This time Microsoft added a process to fix these data issues.

What is the Error Message?

The item cannot be deleted via the User Interface or extension.

Why is the Error Message?

This is due to unsupported characters from fields of type "Code" in the tenant database. 

What are unsupported characters?

Leading and trailing Unicode white-space characters from a field. 

These characters are invisible characters, and represent a keystroke like Enter, TAB or space bar.

How to Fix these Issues?

Last time when we did this with a two-step process.

• Identification - That was done via SQL Query. (Only primary key fields).
• Fix - Via NAV Client to make sure related records are also updated.

** If you are still upgrading from Pre NAV 2013 then please refer old post.

If you / your customer is already in Business Central 14 or higher then Microsoft added a PowerShell cmdlet to fix it.

What is the PowerShell Cmdlet?

• This PowerShell cmdlet is added in Business Central Administration Shell Module. 

• The cmdlet is Invoke-NAVSanitizeField.

What does Invoke-NAVSanitizeField command does?

• This cmdlet deletes all the unsupported characters from fields of type "Code" in the tenant database. 

• It trims leading and trailing Unicode white-space characters from a field. 

• These characters are invisible characters, and represent a keystroke like Enter, TAB or space bar. The cmdlet is similar to the System.Trim method. For a list of the Unicode characters, see IsWhiteSpace method.

• The cmdlet returns an object containing the number of rows and values modified, the tables impacted and a list of potentially ignored records.

What are cmdlet Parameters?

• Server Instance - Specifies the name of a Business Central Server instance.

• Company Name  - Specifies the name of the company that you want to run the operation on. If you do not set this parameter, the operation will run on all companies.

• Tenant - If multitenant then Specifies the ID of the tenant otherwise keep it empty.

• TableId  - Specifies the ID of the table that you want to invoke the operation on. If you do not set this parameter, the operation will run on all tables.

• FieldId - Specifies the ID of the field that you want to invoke the operation on. If you do not set this parameter, the operation will run on all fields of the table.

• RemoveDuplicates - Specifies to delete a record, after it's been sanitized, if it conflicts with an existing record. 

When you Should Run It?

• Before you start replicating your data to Business Central online (SaaS), please make sure to run it. 

• With Execution of this cmdlet, you will be sure that if there are any unsupported characters in data that will be clean.

• I would highly encourage to run this even on prem during upgrade process.

How to Run this PowerShell cmdlet?

To run this simply on complete database to analyze run this with minimum set of parameters.

Invoke-NAVSanitizeField -ServerInstance BC210 

You can surely add parameters if required. The RemoveDupicates switch will delete duplicate records that is only applicable to primary key fields.

 After the query, check data from SQL Server. The command will update the data in database.

Microsoft Article.

Let me know your views as comment to this article.

Hope this helps your future.

Regards,
Saurav Dhyani
www.sauravdhyani.com
https://www.youtube.com/@sauravdhyani