CRM FetchXml, how can I dynamically add quicksearch filter that will match related entity's name?

Question

I have a web page outside of CRM that allows users to perform CRM lookups. I need to implement filtering the lookup results using quick search like in the normal CRM UI.

I get the FetchXML for the selected entity/view, then get the FetchXML for the selected entity's quick search view, extract the filter node where isquickfindfields=1, replace {0} with what they typed into the search box, insert the modified node into the selected view's FetchXML, and execute it.

The problem that I'm running into is some of the quick search filters are against a related entity's id field, but the match needs to go against that entity's primary name attribute.

For example, here's the quick search for the account entity:

<fetch version="1.0" output-format="xml-platform" mapping="logical">
<entity name="account">
    <attribute name="name" />
    <attribute name="primarycontactid" />
    <attribute name="address1_city" />
    <attribute name="telephone1" />
    <attribute name="emailaddress1" />
    <order attribute="name" descending="false" />
    <filter type="and">
        <condition attribute="statecode" operator="eq" value="0" />
    </filter>
    <filter type="or" isquickfindfields="1">
        <condition attribute="primarycontactid" operator="like" value="{0}" />
        <condition attribute="telephone1" operator="like" value="{0}" />
        <condition attribute="emailaddress1" operator="like" value="{0}" />
        <condition attribute="accountnumber" operator="like" value="{0}" />
        <condition attribute="name" operator="like" value="{0}" />
    </filter>
    <attribute name="accountid" />
</entity>

And when I plug in the search text and execute the view, I get this error from RetrieveMultiple:

An exception System.FormatException was thrown while trying to convert input value '%acme%' to attribute 'account.primarycontactid'. Expected type of attribute value: System.Guid. Exception raised: Guid should contain 32 digits with 4 dashes (xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx).

If I use the normal CRM interface, do a lookup against accounts and type in the name of a contact in the quick search box, the search works as expected (lists accounts where name of the primary contact is what I entered). Because of this, I had assumed that when CRM queries the database, it would "intelligently" match non-Guid strings againt an entity reference's primary name attribute.

Here's the block of code (pastebin of entire method) that adds the quicksearch filter:

if (string.IsNullOrWhiteSpace(searchString) == false)
{
    var quickSearch = CRMCache.SavedQueries.FirstOrDefault(q => q.ReturnedTypeCode == view.ReturnedTypeCode && q.QueryType == SavedQueryQueryType.QuickFindSearch);
    if (quickSearch != null)
    {
        var quickSearchXml = XElement.Parse(quickSearch.FetchXml);
        var quickSearchFilter = quickSearchXml.XPathSelectElement("//filter[@isquickfindfields=1]");
        foreach (var condition in quickSearchFilter.Elements("condition"))
        {
            condition.SetAttributeValue("value", string.Format(condition.Attribute("value").Value, "%" + searchString + "%"));
        }

        viewEntityNode.Add(quickSearchFilter);
    }
}

While I'm curious how the normal CRM UI handles this, my question is how do I dynamically apply quick search filtering on lookups/views that will correctly filter against a related entity's primary name attribute?

---

[Edit for clarification]

Quick Search (or Quick Find) is one of the view types in CRM. It defines what attributes are searched when a user enters text into the quick search box or when filtering a lookup. All entities have a quick search view and an entity can have only one.

The requirement to match a related entity's name comes from the quick search view. Not all of them include a filter on an entity reference, but when it does, I need to match against the name and not the guid. Since the normal CRM UI correctly applies the search string against the related entity's name, even though the quick search view's FetchXML has the filter against the id, I had thought CRM handled that case internally. Obviously, that's not the case (the error I got shows this). So I need to detect this case and do something different and I want to do this dynamically.

By dynamically, I mean my code doesn't have a bunch of pre-defined FetchXML strings; it's not coded for specific entities, views or search requirements; and it doesn't need to be modified every time a new entity or view is added or altered. Maybe "dynamic" isn't a good term in this context, but I don't know what else to call it. Entity/view-agnostic?

I don't want code like this:

SearchResults ExecuteView(string entityLogicalName, string searchString)
{
    switch(entityLogicalName)
    {
        case "account":
            return searchAccounts(searchString);

        ... all other enties ...

        default:
            throw new Exception("Unknown entity type: " + entityLogicalName);
    }
}


SearchResults searchAccounts(searchString)
{
    var fetchXml = string.Format(@"
<fetch>
      <entity name=""account"">
        <link-entity name=""contact"" from=""contactid"" to=""primarycontactid"">
            <attribute name=""name"" alias=""name"" />
            <filter type=""and"">
                <condition attribute=""name"" operator=""like"" value=""%{0}%"" />
            </filter>
        </link-entity>
      </entity>
</fetch>", searchString);

    return executeSearch(fetchXml);
}

because changes in CRM (add/remove entities, add/remove/update views) would require the code to be updated to match.

Answer 1

I don't know how CRM gets away with presumable using a string to search against a guid, but I suspect it performs some pre-processing or some back end stuff which may not be available to you. (I suppose if you really wanted to know you could try decompiling the CRM .dlls but that might take a while).

So as an alternative I would suggest a stage of processing where you further manipulate the FetchXml, if you do this with the aid of the metadata service you should be able to get away without any hardcoding.

So, taking the account fetch as an example I believe you need to edit the xml so the id attribute has name appended to it, e.g. primarycontactidname. That is the name of the database column in the FilteredView and I believe that it available from FetchXml. (If that doesnt work add a link-entity with a filter for the name and remove the existing condition).

So you know when to append name I would suggest:

1. Retrieve the quick find view search criteria.
2. For each condition use the metadata service to search CRM
3. If the field type is EntityReference then update the quick find view search with the appropriate condition.

Answer 2

I voted up James' answer, cause I do think it is the correct answer to your question. However, perhaps a better solution overall would be to create a special System View for each entity that you want to be searchable from your web page(s). Use a naming convention so your web page knows which System View to look up. In the System View, specify {0} in the criteria fields, then replace it in your code as usual. This way you could create whatever complex FetchXML you want without the overhead of querying the Metadata service. For bonus points, disable this new System View so it isn't visible in the CRM UI.

-Additions after your comments below:

Quick Find doesn't actually "filter lookups/views." When you type a query in the Quick Find box in the CRM UI, it doesn't filter the current view - the results are shown using the "Quick Find" view (see the View dropdown). This view uses special FetchXML, as you've seen - it uses the "isquickfindfields" attribute. This attribute must be a flag to the CRM UI to pre-process this FetchXML and update the node to correct FetchXML for those relationship fields before submitting the query.

If your intent is to show all the System Views in this alternate UI, but allow them all to be filtered by something that looks like the Quick Find search box, then you are talking about something that isn't like the CRM UI at all. :)

If you are talking about just using the same Quick Find view parameters in this alternate UI, then my recommendation is still to create a single hidden System View in CRM that mimics the Quick Find view. The FetchXML behind this view will be "normal" and you will be able to insert the search text without the necessity of calling the Metadata service.

Answer 3

I hope that i understood your question. primarycontactid isn't a name is a guid that represents a record in this case, a contact. Unless your users know the guid of each record searching for this field make no sense. But you want search inside of contact so you have to do a link (equivalent of join in sql), see here how use FetchXml with a example of link entity:

• http://msdn.microsoft.com/en-us/library/gg328117.aspx

Now a example of you want to do:

<fetch>
      <entity name="account">
        <link-entity name="contact" from="contactid" to="primarycontactid">
            <attribute name="name" alias="name" />
            <filter ... />
        </link-entity>
      </entity>
</fetch>

Execute FetchXML in C# dynamically:

string fetchrequest = string.Format(
@"<fetch mapping='logical' aggregate='true'>
    <entity name='queueitem'>
        <attribute name='queueitemid' alias='c' aggregate='count'/>
        <filter type='and'>
            <condition attribute='objecttypecode' operator='ne' value='4406'/>
            <condition attribute='queueid' operator='eq' value='{0}'/>
        </filter>
    </entity>
</fetch>", queueid.ToString());

string fetchresult = crmservice.Fetch(fetchrequest);
XmlDocument document = new XmlDocument();
document.LoadXml(fetchresult);
Console.Writeline("numero de elementos:" + document.SelectSingleNode("//resultset/result/c").InnerText));