Personalized Community is here!
Quickly customize your community to find the content you seek.
Have questions on moving to the cloud? Visit the Dynamics 365 Migration Community today! Microsoft’s extensive network of Dynamics AX and Dynamics CRM experts can help.
2021 Release Wave 2Discover the latest updates and new features releasing from October 2021 through March 2022.
2021 release wave 2 plan
The FastTrack program is designed to help you accelerate your Dynamics 365 deployment with confidence.
FastTrack Community | FastTrack Program | Finance and Operations TechTalks | Customer Engagement TechTalks | Upcoming TechTalks | All TechTalks
Hi Techies,We are facing a similar problem to the one discussed in the below linkhttps://community.dynamics.com/crm/f/microsoft-dynamics-crm-forum/179854/lookup-records-with-search-criteria-is-not-working-in-mscrm-2015/444140We 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__] fromProductBase 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 byname asc,"product0".ProductId ascOn 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.
Will rebuilding the free-text catalog help our cause? What are the other things that we need to verify? Thanks in advance.Regards,Clement
It does look like the issue is with the full-text index in SQL Server, so I'd suggest rebuilding the free-text catalog as the first step
Business Applications communities