Jeremie
Wed May 05 10:08:07 CDT 2004
Rob,
the fetchXml language is very new and is sometimes very frustrating to work
with when you are used to use a powerful query language such as T-SQL. It
has several limitations and it's inability to return "distinct" records is,
in my opinion, on of the main ones. I reported this problem to Microsoft
when v1.0 first came out, I even met with some of the product manager in
Fargo and Minneapolis at Tech conferences but I could not convince them that
this was a major problem and they eventually decided it was not worth fixing
in v1.2. Their point of view is that since it doesn't affect their
application, it's not a problem they need to fix. It is not a problem in
their application because you can't build advanced queries that may return
the same record multiple times such as "return all accounts that have an
opportunity worth $50k of more". If you build the fetchXml corresponding to
the example I just gave, the same account will be returned twice if it has
two opportunities worth $50k or more. But, again, since you can't build this
query in the standard "advanced find" tool, it's not an issue as far as
Microsoft is concerned. The only workaround Microsoft offered to me is to
manually parse the resulting XML and eliminate the duplicates. I was unable
to convince them that this would adversely affect the performance of our
solution. We can only hope that fetchXml will mature in future versions of
CRM and that if will become more and more powerful with each version.
To make a long story short, there is not way to ask the CRM platform to
return distinct records. The only way to achieve this, is to manually parse
the result.
-Jeremie Desautels
c360 Solutions
"RobS^2" <rathbird@hotmail.com> wrote in message
news:40BBCA0F-BD86-484E-8C4B-F91E3442CF03@microsoft.com...
> I'm using the CRM proxy class in .net to retrieve lists of values for
dropdowns, repeaters, etc, and I get duplicate values, this is especially
frustrating in a dropdown list. Here's an example of one of my queries:
>
> StringBuilder strQuery = new StringBuilder("");
> strQuery.Append("<fetch mapping='logical'>");
> strQuery.Append("<entity name='account'>");
> strQuery.Append("<attribute name='CFPRegionCode'/>");
> strQuery.Append("<filter type='and'>");
> strQuery.Append("<condition attribute='CFPRegionCode' operator='not-null'
/>");
> strQuery.Append("</filter>");
> strQuery.Append("</entity>");
> strQuery.Append("</fetch>");
>
> return RunCRMQuery(strQuery.ToString());
>
> Can anyone tell me how to return only distinct values of CFPRegionCode?
>
> thanks
>
>
>