How to – Filter ‘Case Insensitive’ in the Object Designer (Development Environment)
We all know we can filter on objects in the Object Designer. You can for example search or filter on Type, ID, Name, Modified, Version etc. Now if for example I would to filter the Customer table my Filter could be: *cust*.
This however yields no results. The reason for this is that the database is enabled for Case Sensitivity. If I still would like to find the customer I need to make my filter Case Insensitive by prefixing it with the @ symbol. Like this:
However I don’t want to use a case insensitive filter every time :). For key users or admins it could also lead too confusion why it is not working easier. There is a solution for this by disabling Case Sensitivity.
Tip: Before performing the steps below I recommend to create a FULL SQL backup to create a fallback scenario if problems will occur.
First double check if your database has been configured Case Sensitive. You can check this out for yourself by going to File > Database > Alter. Now go to the Collation tab and check if ‘Case -sensitive’ is enabled.
If you uncheck the option you will get a notification that this is not possible until you first enable ‘Validate Collation’:
Enable the checkbox Validate Collation first and press the OK button.
We can’t disable Case-sensitive yet because we must ensure now that you are the only user who is accessing the database. Therefore we need to enable the Single User option to perform the chosen database alterations. Go to Alter Database and enable Single User and press OK.
Tip: if you are getting an error that single user cannot be set, please ensure all servicetiers are stopped as well as they keep a SQL session maintained thus preventing setting the database in Single User mode.
If your database was set to Single User Mode, finally go to Alter Database and now you can disable the checkbox Case Sensitive. If the process has finished succesfully you will get this message:
Don’t forget to disable single user mode and start your servicetiers after! Uncheck Single user now:
In the end it should like this:
Double check if you can connect to your environment by starting the Windows client (RTC = Role Tailored Client) and good luck with Case insensitive filtering in the Object Designer!
When I started the client I received this message:
Textual: The tenant default in database NAV2017_CU17_CronusNL is not operational because the collation is incorrect. The tenant can be brought to the operational state by running the Sync-NavTenant cmdlet with the Mode parameter set to either Sync or ForceSync.
So the final step is to perform the synchronisation:
To summarize for the skilled users, perform the following steps to enable case insensitive filtering in the Object designer without using a case-insensitive filter:
- Enable Validate Collation
- Enable Single User mode
- Disable Case-sensitive
- Disable Single User mode
- Start service tier (Microsoft Dynamics NAV Service)
Perform Sync-NAVTenant (Powershell Cmdlet)
Microsoft Azure, Microsoft Dynamics and more... 