Dynamics Community Forum Thread Details

lookup search not returning expected results

Hi Techies,

We are facing a similar problem to the one discussed in the below link
https://community.dynamics.com/crm/f/microsoft-dynamics-crm-forum/179854/lookup-records-with-search-criteria-is-not-working-in-mscrm-2015/444140

We are on Dynamics CRM 2015 On-premises with UR 0.3.
The search in the lookup works in all environments except the Production.

The lookup column points to the view "Product Family Lookup View".
When the user searches for keyword in the lookup not all elements are shown in the production environment.

The view on the whole works fine. When we don't use keyword search in the lookup we can see all the Items.

We tried comparing the data in the ProductBase in INTEGRATION & PRODUCTION and they are identical.

We tried querying using "Advanced Search" with the same keyword and we get the correct result. We get the correct result from FetchXML. We get the correct result when querying the database with a simple SQL query with LIKE predicate.

We profiled the SQL Query that's used to Filter the lookup using SQL server profiler and we got the below query.

WITH __QuickFind__ as (select top 10001 [ProductId] from (
SELECT "BDL".[ObjectId] AS [ProductId] FROM [BusinessDataLocalizedLabelBase] AS "BDL" WITH (NOLOCK)
where (
contains("BDL".Label, '((("GEODE*" OR FORMSOF(FREETEXT, "GEODE"))) OR ("GEODE"))') and "BDL".LanguageId in (1036)
and "BDL".ObjectColumnNumber = 6 and "BDL".ObjectIdTypeCode = 1024)) as [__QuickFindInternal__])select
top 12 coalesce("LL0".Label,"product0".Name ) as "name"
, "product0".HierarchyPath as "hierarchypath"
, "product0".StateCode as "statecode"
, "product0".ProductStructure as "productstructure"
, "product0".ProductId as "productid"
, "product0".ProductTypeCode as "producttypecode"
, case when (select COUNT(*) from [__QuickFind__]) = 10001 then 1 else 0 end as [__QuickFindLimitValue__]
from
ProductBase as "product0" WITH (NOLOCK)
left outer join BusinessDataLocalizedLabel as "LL0" on
("LL0".ObjectId = "product0".ProductId and "LL0".LanguageId = 1036 and "LL0".ObjectColumnNumber = 6 )
where
[product0].[ProductId] in (select [ProductId] from [__QuickFind__]) and (((("product0".StateCode = 0 and (("product0".ProductStructure = 2)))))) order by
name asc,"product0".ProductId asc

On executing the profiled query directly on the production database we are able to simulate the same issue. The query didn't return all the expected records. In the above query we noticed that full-text search feature of SQL server is being used.

No record found.