Unify the functionality in lookup fields for standard entity product in base language vs. non-base language setting.

Product records allow field translations of the primary field "name". Regardless of having a translation or not, the behavior is different. Based on the Microsoft explanation, the non-base language environment uses the "SQL Server full-text search" (once again, regardless if it's enabled or disabled) -> this is a feature of the product entity, because the search happens in a different table.

When customers use a multi-language environment they experience the following when using the lookup for product to search for the primary field "name":

• Base language
  
  
  
  • can use wildcard search
  
  
  • use * to search suffix
  
  
  • must use * to search prefixes of each word
  
  
  
  


• Non-base language
  
  
  
  • cannot use wildcard search: * character does nothing
  
  
  • cannot search suffix
  
  
  • can search the prefixes of each word without using *
  
  
  
  

This behavior is said to be caused by the features and limitations of full-text search, for which it holds (source below): "A full-text search considers a word to be a string of characters without spaces or punctuation."

Based on the Microsoft ticket REG:117060115827523 Trouble with Product Wild Card Search

Links:

Query with full-text search

Dashes Ignored in Search with SQL Full-Text