Custom search in Dynamics CRM 4.0

Question

I have a two related questions.

First: I'm looking to do a full text search against a custom entity in Dynamics CRM 4.0. Has anyone done this before or know how to do it?

I know that I can build QueryExpressions with the web service and sdk but can I do a full text search with boolean type syntax using this method? As far as I can tell that won't do the trick.

Second: Does anyone else feel limited with the searching abilities provided with Dynamics CRM 4.0? I know there are some 3rd pary search products out there but I haven't found one I like yet. Any suggestions would be appreciated.

Answer 1

Searching and filtering via the CRM SDK does take some time to get used to. In order to simulate full text search, you need to use nested FilterExpressions as your QueryExpression.Criteria. SDK page for nested filters The hardest part is figuring out how to build the parent child relationships. There's so much boolean logic going on that it's easy to get lost.

I had a requirement to build a "search engine" for one of our custom entities. Using this method for a complex search string ("one AND two OR three") with multiple searchable attributes was ugly. If you're interested though, I can dig it up. While it's not really supported, if you can access the database directly, I would suggest using SQL's full text search capabilities.

-- ok, here you go. I don't think you'll be able to copy paste this and fulfill your needs. my customer was only doing two to three key word searches and they were happy with the results from this. You can see what a pain it is to just do this in a simple search scenario. I basically puked out code until it was 'working'.

    private FilterExpression BuildFilterV2(string[] words, string[] seachAttributes)
    {
        FilterExpression filter = new FilterExpression();
        List<FilterExpression> allchildfilters = new List<FilterExpression>();

        List<string> andbucket = new List<string>();
        List<string> orBucket = new List<string>();

        // clean up commas, quotes, etc
        words = ScrubWords(words);

        int index = 0;

        while (index < words.Length)
        {
            // if current word is 'and' then add the next wrod to the ad bucket
            if (words[index].ToLower() == "and")
            {
                andbucket.Add(words[index + 1]);
                index += 2;
            }
            else
            {
                if (andbucket.Count > 0)
                {

                    List<FilterExpression> filters = new List<FilterExpression>();
                    foreach (string s in andbucket)
                    {
                        filters.Add(BuildSingleWordFilter(s, seachAttributes));
                    }

                    // send existing and bucket to condition builder 
                    FilterExpression childFilter = new FilterExpression();
                    childFilter.FilterOperator = LogicalOperator.And;
                    childFilter.Filters = filters.ToArray();

                    // add to child filter list
                    allchildfilters.Add(childFilter);

                    //new 'and' bucket
                    andbucket = new List<string>();
                }
                if (index + 1 < words.Length && words[index + 1].ToLower() == "and")
                {
                    andbucket.Add(words[index]);
                    if (index + 2 <= words.Length)
                    {
                        andbucket.Add(words[index + 2]);
                    }
                    index += 3;
                }
                else
                {
                    orBucket.Add(words[index]);
                    index++;
                }
            }
        }

        if (andbucket.Count > 0)
        {
            List<FilterExpression> filters = new List<FilterExpression>();
            foreach (string s in andbucket)
            {
                filters.Add(BuildSingleWordFilter(s, seachAttributes));
            }

            // send existing and bucket to condition builder 
            FilterExpression childFilter = new FilterExpression();
            childFilter.FilterOperator = LogicalOperator.And;
            childFilter.Filters = filters.ToArray();

            // add to child filter list
            allchildfilters.Add(childFilter);

            //new 'and' bucket
            andbucket = new List<string>();
        }
        if (orBucket.Count > 0)
        {
            filter.Conditions = BuildConditions(orBucket.ToArray(), seachAttributes);
        }
        filter.FilterOperator = LogicalOperator.Or;
        filter.Filters = allchildfilters.ToArray();

        return filter;
    }
    private FilterExpression BuildSingleWordFilter(string word, string[] seachAttributes)
    {
        List<ConditionExpression> conditions = new List<ConditionExpression>();

        foreach (string attr in seachAttributes)
        {
                ConditionExpression expr = new ConditionExpression();
                expr.AttributeName = attr;
                expr.Operator = ConditionOperator.Like;
                expr.Values = new string[] { "%" + word + "%" };

                conditions.Add(expr);
        }

        FilterExpression filter = new FilterExpression();
        filter.FilterOperator = LogicalOperator.Or;
        filter.Conditions = conditions.ToArray();

        return filter;
    }

    private ConditionExpression[] BuildConditions(string[] words, string[] seachAttributes)
    {
        List<ConditionExpression> conditions = new List<ConditionExpression>();

        foreach (string s in words)
        {
            foreach (string attr in seachAttributes)
            {
                ConditionExpression expr = new ConditionExpression();
                expr.AttributeName = attr;
                expr.Operator = ConditionOperator.Like;
                expr.Values = new string[] { "%" + s + "%" };

                conditions.Add(expr);
            }
        }

        return conditions.ToArray();
    }

Answer 2

Hm, that's a pretty interesting scenario...

You could certainly do a 'Like' query, and 'or' together the colums/attribute conditions you want included in the search. This seems to be how CRM does queries from the box above entity lists (and they're plenty fast). It looks like the CRM database has a full-text index, although exactly which columns are used to populate it is a bit foggy to me after a brief peek.

And remember LinqtoCRM for CRM query love (I started the project, sorry about the shameless plug).