Forum Discussion
How to deal with case sensitivity in Dataverse tables without using lower function in queries
Hi
In dataverse build by dynamics365 FNO table2 is child of table1 id field in table1 is uppercase but id field in table2 is lowercase. in query when linking both tables using id field there won't be any data due to case sentivity.
using lower function in everyquery will be expensive. Is there any configuration or setting which could be changed to deal with this permanently.
Hello antoec
In Dataverse, the collation settings—including whether the data comparisons are case-sensitive or case-insensitive—are defined when the environment is created and cannot be changed afterward. This means that if your environment was configured with a case-sensitive collation, you cannot later switch it to be case-insensitive through a configuration change. Therefore, to resolve issues where related fields have mismatched cases, the only practical solutions are to normalize the data—for example, by ensuring that all key values are stored in the same case—or to use computed columns that store a pre-normalized version of the data. This approach typically avoids the performance overhead of using functions like Lower() or Upper() in every query.
there isn’t a built‐in configuration setting in Dataverse to globally disable case sensitivity for text comparisons. Instead, you must ensure that the data used for linking tables is normalized so that both sides of the join use the same case.
If this is helpful please accept the answer
7 Replies
- nilendraFabric
Super User
Hello antoec
In Dataverse, the collation settings—including whether the data comparisons are case-sensitive or case-insensitive—are defined when the environment is created and cannot be changed afterward. This means that if your environment was configured with a case-sensitive collation, you cannot later switch it to be case-insensitive through a configuration change. Therefore, to resolve issues where related fields have mismatched cases, the only practical solutions are to normalize the data—for example, by ensuring that all key values are stored in the same case—or to use computed columns that store a pre-normalized version of the data. This approach typically avoids the performance overhead of using functions like Lower() or Upper() in every query.
there isn’t a built‐in configuration setting in Dataverse to globally disable case sensitivity for text comparisons. Instead, you must ensure that the data used for linking tables is normalized so that both sides of the join use the same case.
If this is helpful please accept the answer
nilendraFabric thank you for the reply. Could guide any documentations or reference where i could follow and create a dataverse which is case-insensitive.
- nilendraFabric
https://learn.microsoft.com/en-us/power-platform/admin/language-collations
key points in the doc
Default Collation Settings:
• When creating a Dataverse environment, administrators select a default language. This choice determines the collation applied to the SQL database that underpins the Dataverse instance.
• Collation settings affect sorting, filtering, and text comparisons, including case sensitivity and accent sensitivity.All languages in Dataverse use case-insensitive collations by default, denoted by `_CI`. For example, “Cafe” and “cafe” are treated as the same word
- v-csrikanth
Community Support
Hi antoec
I sincerely appreciate nilendraFabric for taking the time to provide a thoughtful and insightful solution.
Note: For reference regarding case sensitivity, please refer to the following document.
https://learn.microsoft.com/en-us/power-platform/admin/language-collationsIf the above information helps you, please give us a Kudos and marked the reply Accept as a Solution.
Thanks,
Cheri Srikanth - v-csrikanth
Hi antoec
We haven't heard from you since last response and just wanted to check whether the solution provided has worked for you. If yes, please Accept as Solution to help others benefit in the community.
Thank you,
Cheri Srikanth - v-csrikanth
Hi antoec
It's been a while since I heard back from you and I wanted to follow up. Have you had a chance to try the solutions that have been offered?
If the issue has been resolved, can you mark the post as resolved? If you're still experiencing challenges, please feel free to let us know and we'll be happy to continue to help!
Looking forward to your reply!
Thanks
Cheri Srikanth - v-csrikanth
Hi antoec
We haven't heard from you since last response and just wanted to check whether the solution provided has worked for you. If yes, please Accept as Solution to help others benefit in the community.
Thank you,
Cheri Srikanth.
