← Back to Blog

CRM Quick search not returning records that exist

CRM Quick search does not return data that you know exists.

Today I was searching a record in CRM and it was returning 0 records found. I could browse to the record so I knew it existed. I thought maybe the filter criteria on the quick find view so I checked that. But the filter conditions should have let me see the record.

So I decided to do a sql trace to figure out what was going on. Below shows the query CRM was using for my search. Underlined shows the issue.  For my test I was searching for a workflow with name “Test”. During a quickfind, it first searches the LoclizedLabelView table for what you are searching for. Then, using the objects returned, it filters out the actual table you are searching as shown on the second underline portion. I searched the LocalizedLabelView table and verified that the label for the workflow I was searching for was something different from the name of the actual workflow.

 

WITH __QuickFind__ as (select top 10001 [WorkflowId] from (SELECT "BDL".[ObjectId] AS
[WorkflowId] FROM [LocalizedLabelView] AS "BDL" WITH (NOLOCK)
 where ( "BDL".Label like N'TEST%' )) as [__QuickFindInternal__])select
top 251 "workflow0".WorkflowId as
"workflowid"

, coalesce("LL0".Label,"workflow0".Name) as "name"
, "workflow0".Category as "category"
, "workflow0".PrimaryEntity as "primaryentity"
, "workflow0".StateCode as "statecode"
, "workflow0".CreatedOn as "createdon"
, "workflow0".OwnerId as "ownerid"
, "workflow0".OwningBusinessUnit as "owningbusinessunit"
, "workflow0".Type as "type"
, convert(bigint, "workflow0".VersionNumber) as "versionnumber"
, "workflow0".OwnerIdYomiName as "owneridyominame"
, "workflow0".OwnerIdName as "owneridname"
, "workflow0".OwnerIdType as "owneridtype"
, "workflow0".OwningBusinessUnitName as "owningbusinessunitname"
, case when (select COUNT(*) from
[__QuickFind__]) = 10001 then 1 else 0 end as [__QuickFindLimitValue__]
from
 Workflow as "workflow0" WITH(NOLOCK)
 left outer join LocalizedLabelView as
"LL0" on ("LL0".ObjectId =
"workflow0".WorkflowId and "LL0".LanguageId
= 1033 and
"LL0".ObjectColumnName = N'Name' )
where
 [workflow0].[WorkflowId]
in (select [WorkflowId] from [__QuickFind__]) and (((("workflow0".RendererObjectTypeCode is
null))
 and (("workflow0".Type = 1 or "workflow0".Type = 3)) and (("workflow0".Category = 0 or "workflow0".Category
= 3
 or (("workflow0".Category
= 1 and
"workflow0".LanguageCode in (1033
, -1)))))))

 

THE FIX

Now to fix the issue all you need to do is find the workflow and just change the name. Save. Then change it back. This should update the LocalizedLabelView to have the proper label.

Also, since we now know what causes the issue we can find the problems with the LocalizedLabelView table that has happened for records we don’t even know about. See query below. Now I’m not sure what other entities this might be helpful on. I checked the sql statements for a few other entities and this would not be an issue for any that I looked at. For example, the appointment records are in the LocalizedLabelView. But the quick find doesn’t use the LocalizedLabelView when searching for appointments.


 

<font face="Times New Roman" size="3">

select

w.name, w.workflowid,v.Label

from FilteredWorkflow w

left outer join LocalizedLabelview v

on v.ObjectId = w.workflowid

 

where

(v.LocalizedLabelId is null or (w.name <> v.Label and v.objectcolumnname
= 'name'))

and parentworkflowid is null

and activeworkflowid is not null

 </font>