Answered by:
how to implement follow sql with fetchxml

Question
-
SELECT Account.name FROM Account WHERE OwnerIdName=OwnerIdYomiName
韓建興 http://jamson.cnblogs.comFriday, July 3, 2009 2:43 AM
Answers
-
<fetch mapping='logical'>
<entity name='account'>
<attribute name='name'/>
<link-entity name='systemuser' from='yomifullname' to='owneridname' alias='aa' link-type='inner'/>
</entity>
</fetch>
Try this!
Ayaz Ahmad Technical Consultant CRM | OA Systems Ltd | http://ayazahmad.wordpress.com- Marked as answer by Ayaz.AhmadModerator Tuesday, July 7, 2009 3:27 AM
Friday, July 3, 2009 3:34 AMModerator -
if you link on the filed that needs to be compared then it will solve your issue with out any concatenations. Just have a look at my above fetchxml in my first reply to your question.
<link-entity name='systemuser' from='yomifullname' to='owneridname' alias='aa' link-type='inner'/>
In this line of FetchXML, it compares yomifullname with owneridname and only returns records where this condition is met.
Ayaz Ahmad Technical Consultant CRM | OA Systems Ltd | http://ayazahmad.wordpress.com- Marked as answer by Ayaz.AhmadModerator Tuesday, July 7, 2009 3:26 AM
Tuesday, July 7, 2009 3:02 AMModerator -
<fetch mapping='logical'>
<entity name='product'>
<attribute name='name'/>
<link-entity name='product' from='FieldA' to='FieldB' alias='aa' link-type='inner'/>
</entity>
</fetch>
Try this and let me know! I have used product as entity name and i have selected only one attribute in select i.e. name.
Ayaz Ahmad Technical Consultant CRM | OA Systems Ltd | http://ayazahmad.wordpress.com- Marked as answer by Jeff.Han Tuesday, July 7, 2009 3:25 AM
Tuesday, July 7, 2009 3:13 AMModerator -
I have just tried on product entity at my crm system and it works. Here is the Fetch XML i am using to fetch products, iam comparing name and description columns because my product entity does not have FieldA and FieldB. This fetchxml selects only products where name = description. Is it what you are looking for?
<fetch mapping='logical'>
<entity name='product'>
<attribute name='name'/>
<attribute name='description'/>
<link-entity name='product' from='description' to='name' link-type='inner'/>
</entity>
</fetch>
Ayaz Ahmad Technical Consultant CRM | OA Systems Ltd | http://ayazahmad.wordpress.com- Marked as answer by Ayaz.AhmadModerator Tuesday, July 7, 2009 3:26 AM
Tuesday, July 7, 2009 3:22 AMModerator
All replies
-
<fetch mapping='logical'>
<entity name='account'>
<attribute name='name'/>
<link-entity name='systemuser' from='yomifullname' to='owneridname' alias='aa' link-type='inner'/>
</entity>
</fetch>
Try this!
Ayaz Ahmad Technical Consultant CRM | OA Systems Ltd | http://ayazahmad.wordpress.com- Marked as answer by Ayaz.AhmadModerator Tuesday, July 7, 2009 3:27 AM
Friday, July 3, 2009 3:34 AMModerator -
Hi
FYI, actually we can use the advance find to construct CRM fetch XML, this can minimize the error when constructing the XML.
Use advance find to query the record, then F11, type the following javascript in URL.
javascript:alert(resultRender.FetchXml.value);
Just copy the Fetch XML generate by CRM.regards,
Toong Yang
http://eastoceantechnical.blogspot.com/Friday, July 3, 2009 3:41 AM -
what about this conditions, is it working for you through advance find OwnerIdName=OwnerIdYomiName
Ayaz Ahmad Technical Consultant CRM | OA Systems Ltd | http://ayazahmad.wordpress.comFriday, July 3, 2009 3:42 AMModerator -
my customer's requirement is :
there a two customized field in entity Product.
Field: new_Mold nvarchar(100)
Field: new_ListType nvarchar(100)
they want to find the records where new_mold=new_ListType in the sample record
those in the sample entity.
韓建興 http://jamson.cnblogs.comFriday, July 3, 2009 5:32 AM -
I try use advance find construct, the result is:
<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="true"> <entity name="account"> <attribute name="name"/> <attribute name="primarycontactid"/> <attribute name="telephone1"/> <attribute name="accountid"/> <order attribute="name" descending="false"/> <link-entity name="systemuser" from="systemuserid" to="owninguser" alias="aa"> <filter type="and"> <condition attribute="yomifullname" operator="eq" value="Yang"/> </filter> </link-entity> </entity> </fetch>
select the related Owner in advance find.
regards,
Toong Yang
http://eastoceantechnical.blogspot.com/- Edited by Toong Yang Friday, July 3, 2009 6:36 AM
Friday, July 3, 2009 6:35 AM -
product entity:
ProductNumber FieldA FieldB
A1 XD VD
A2 VC VC
A3 VC VC
A4 EC KV
A5 UC UC
results:
A2 VC VC
A3 VC VC
A5 UC UC
韓建興 http://jamson.cnblogs.comFriday, July 3, 2009 6:36 AM -
Hi 建興,
I think your requirement can use the related entity find in advance find to achieved it.
regards,
Toong Yang
http://eastoceantechnical.blogspot.com/Friday, July 3, 2009 6:42 AM -
I wondered how to compare two field in fetch xml.
i tried advance find tool in mscrm, failed as the result
in fetch xml, i can compare one or multiple values with a field,
what i want to implement is compare a field with another.
韓建興 http://jamson.cnblogs.com- Edited by Jeff.Han Friday, July 3, 2009 6:56 AM
Friday, July 3, 2009 6:49 AM -
Advance find cannot do dynamic comparison, just can do one way comparison.
I think you can try to query all field B out first and store in array, then pass as parameter to the fetch XML (concatinate string).
then loop through the array.
like <condition attribute="yomifullname" operator="eq" value="+ arrayParam[i].text +"/>"
regards,
Toong Yang
http://eastoceantechnical.blogspot.com/Friday, July 3, 2009 6:58 AM -
i got it.thanks a lot .
韓建興 http://jamson.cnblogs.comFriday, July 3, 2009 7:03 AM -
if you link on the filed that needs to be compared then it will solve your issue with out any concatenations. Just have a look at my above fetchxml in my first reply to your question.
<link-entity name='systemuser' from='yomifullname' to='owneridname' alias='aa' link-type='inner'/>
In this line of FetchXML, it compares yomifullname with owneridname and only returns records where this condition is met.
Ayaz Ahmad Technical Consultant CRM | OA Systems Ltd | http://ayazahmad.wordpress.com- Marked as answer by Ayaz.AhmadModerator Tuesday, July 7, 2009 3:26 AM
Tuesday, July 7, 2009 3:02 AMModerator -
product entity:
ProductNumber FieldA FieldB
A1 XD VD
A2 VC VC
A3 VC VC
A4 EC KV
A5 UC UC
results:
A2 VC VC
A3 VC VC
A5 UC UC
Both them on the same entity.
can you archive it?
韓建興 http://jamson.cnblogs.comTuesday, July 7, 2009 3:08 AM -
<fetch mapping='logical'>
<entity name='product'>
<attribute name='name'/>
<link-entity name='product' from='FieldA' to='FieldB' alias='aa' link-type='inner'/>
</entity>
</fetch>
Try this and let me know! I have used product as entity name and i have selected only one attribute in select i.e. name.
Ayaz Ahmad Technical Consultant CRM | OA Systems Ltd | http://ayazahmad.wordpress.com- Marked as answer by Jeff.Han Tuesday, July 7, 2009 3:25 AM
Tuesday, July 7, 2009 3:13 AMModerator -
I have just tried on product entity at my crm system and it works. Here is the Fetch XML i am using to fetch products, iam comparing name and description columns because my product entity does not have FieldA and FieldB. This fetchxml selects only products where name = description. Is it what you are looking for?
<fetch mapping='logical'>
<entity name='product'>
<attribute name='name'/>
<attribute name='description'/>
<link-entity name='product' from='description' to='name' link-type='inner'/>
</entity>
</fetch>
Ayaz Ahmad Technical Consultant CRM | OA Systems Ltd | http://ayazahmad.wordpress.com- Marked as answer by Ayaz.AhmadModerator Tuesday, July 7, 2009 3:26 AM
Tuesday, July 7, 2009 3:22 AMModerator