Sign in
Microsoft.com
 
Microsoft
 
 
 

locked
how to implement follow sql with fetchxml RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote
    SELECT Account.name FROM Account WHERE OwnerIdName=OwnerIdYomiName
    韓建興 http://jamson.cnblogs.com
    Friday, July 3, 2009 2:43 AM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    <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
    Friday, July 3, 2009 3:34 AM
    Moderator
  • Question
    Sign in to vote
    0
    Sign in to vote
    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
    Tuesday, July 7, 2009 3:02 AM
    Moderator
  • Question
    Sign in to vote
    0
    Sign in to vote
     

    <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 AM
    Moderator
  • Question
    Sign in to vote
    0
    Sign in to vote
    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
    Tuesday, July 7, 2009 3:22 AM
    Moderator

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    <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
    Friday, July 3, 2009 3:34 AM
    Moderator
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote
    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.com
    Friday, July 3, 2009 3:42 AM
    Moderator
  • Question
    Sign in to vote
    0
    Sign in to vote
    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.com
    Friday, July 3, 2009 5:32 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    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
  • Question
    Sign in to vote
    0
    Sign in to vote
    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.com
    Friday, July 3, 2009 6:36 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    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
  • Question
    Sign in to vote
    0
    Sign in to vote
    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
  • Question
    Sign in to vote
    0
    Sign in to vote
    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/
    • Marked as answer by Jeff.Han Friday, July 3, 2009 7:03 AM
    • Unmarked as answer by Jeff.Han Tuesday, July 7, 2009 3:27 AM
    Friday, July 3, 2009 6:58 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    i got it.thanks a lot .
    韓建興 http://jamson.cnblogs.com
    Friday, July 3, 2009 7:03 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    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
    Tuesday, July 7, 2009 3:02 AM
    Moderator
  • Question
    Sign in to vote
    0
    Sign in to vote
    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.com
    Tuesday, July 7, 2009 3:08 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
     

    <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 AM
    Moderator
  • Question
    Sign in to vote
    0
    Sign in to vote
    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
    Tuesday, July 7, 2009 3:22 AM
    Moderator