在Dynamics CRM 4.0中自定义搜索

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?

第一:我希望对Dynamics CRM 4.0中的自定义实体进行全文搜索。有没有人以前做过这个或知道怎么做?

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.

我知道我可以使用Web服务和sdk构建QueryExpressions,但是我可以使用此方法使用布尔类型语法进行全文搜索吗?据我所知,这不会成功。

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.

第二:有没有其他人对Dynamics CRM 4.0提供的搜索能力感到受限?我知道有一些第三类的搜索产品,但我还没找到一个我喜欢的产品。任何建议,将不胜感激。

4 个解决方案

#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.

通过CRM SDK进行搜索和过滤确实需要一些时间来习惯。为了模拟全文搜索,您需要使用嵌套的FilterExpressions作为QueryExpression.Criteria。嵌套过滤器的SDK页面最难的部分是弄清楚如何构建父子关系。有很多布尔逻辑,很容易迷失。

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.

我需要为我们的一个自定义实体构建一个“搜索引擎”。将此方法用于具有多个可搜索属性的复杂搜索字符串(“一个AND两个或三个”)是很难看的。如果你感兴趣的话,我可以把它挖出来。虽然它不是真的支持,但如果你可以直接访问数据库,我建议使用SQL的全文搜索功能。

-- 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();
    }

#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.

您当然可以执行“赞”查询,并且“或”将您希望包含在搜索中的列/属性条件放在一起。这似乎是CRM如何从实体列表上方的框中进行查询(并且它们非常快)。看起来CRM数据库有一个全文索引,虽然在短暂的浏览后,我确切地用哪些列填充它对我来说有点模糊。

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

并记住LinqtoCRM的CRM查询爱(我开始了项目,抱歉无耻的插件)。

#3

Second - I can recommend "Global Search" by Akvelon which provides ability to search in all Custom Entities and attributes and Out of Box entities and attributes. Also they are using FTS for search in the attached documents contents. You can find more details in their official site: http://www.akvelon.com/Products/Dynamics%20CRM%20global%20Search/default.aspx

第二 - 我可以推荐Akvelon的“全局搜索”,它提供搜索所有自定义实体和属性以及Out of Box实体和属性的功能。他们还使用FTS在附加的文件内容中进行搜索。您可以在他们的官方网站上找到更多详细信息:http://www.akvelon.com/Products/Dynamics%20CRM%20global%20Search/default.aspx

#4

I would suggest utilizing the Dynamics CRM filtered views provided for you in the database. Then you can utilize all the power of native SQL to do any LIKE's or other logic you need. Plus, the filtered views are security trimmed, so you won't have to worry about users accessing records they do not have permission to.

我建议使用在数据库中为您提供的Dynamics CRM过滤视图。然后,您可以利用本机SQL的所有功能来执行您需要的任何LIKE或其他逻辑。此外,过滤后的视图是安全修整的,因此您不必担心用户访问他们无权访问的记录。