Link Account entity to Order products

02 Mayıs 2012 Çarşamba 14:55

How to link account entity to order products entity. I want to use prefiltering so that the report can run only on current record. I am using VS 2008 and fetchxml. I need (quantity, unit price,description) from OrderProducts entity; customer details from Account entity; ordernumber from Order entity. My fetchxml is as follows:

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
  <entity name="salesorderdetail" >
    <attribute name="productid" />
    <attribute name="productdescription" />
    <attribute name="priceperunit" />
    <attribute name="quantity" />
    <attribute name="extendedamount" />
    <attribute name="salesorderdetailid" />
    <attribute name="tax" />
    <attribute name="lineitemnumber" />
    <attribute name="createdby" />
    <attribute name="baseamount" />
    <order attribute="productid" descending="false" />
    <link-entity name="salesorder" from="salesorderid" to="salesorderid" visible="false" link-type="outer" alias="a_6ec0e72e4c104394bc627456c6412838" enableprefiltering="true" prefilterparametername="CRM_FilteredSalesOrderId">
      <attribute name="paymenttermscode" />
      <attribute name="ordernumber" />
      <attribute name="customerid" />	
      <attribute name="shippingmethodcode" />
      <attribute name="shipto_postalcode" />
      <attribute name="shipto_line3" />
      <attribute name="shipto_line2" />
      <attribute name="shipto_line1" />
      <attribute name="shipto_stateorprovince" />
      <attribute name="shipto_telephone" />
      <attribute name="shipto_name" />
      <attribute name="shipto_fax" />
      <attribute name="shipto_country" />
      <attribute name="shipto_contactname" />
      <attribute name="shipto_city" />
      <attribute name="willcall" />
      <attribute name="requestdeliveryby" />
      <attribute name="freighttermscode" />
      <attribute name="freightamount" />
      <attribute name="transactioncurrencyid" />
      <link-entity name="account" from="accountid" to="customerid" visible="false" link-type="outer" alias="a_92b77a4070bd4d1a82d9fa6ce38df2cc" enableprefiltering="true" prefilterparametername="CRM_FilteredAccountId">
      <attribute name="shippingmethodcode" />
      <attribute name="overriddencreatedon" />
      <attribute name="primarycontactid" />
      <attribute name="paymenttermscode" />
      <attribute name="parentaccountid" />
      <attribute name="telephone1" />
      <attribute name="fax" />
      <attribute name="emailaddress1" />
      <attribute name="description" />
      <attribute name="transactioncurrencyid" />
      <attribute name="createdon" />
      <attribute name="createdby" />
      <attribute name="address1_postalcode" />
      <attribute name="address1_line3" />
      <attribute name="address1_line2" />
      <attribute name="address1_line1" />
      <attribute name="address1_stateorprovince" />
      <attribute name="address1_shippingmethodcode" />
      <attribute name="address1_primarycontactname" />
      <attribute name="address1_county" />
      <attribute name="address1_country" />
      <attribute name="address1_city" />
      <attribute name="accountnumber" />
      <attribute name="name" />
    </link-entity>
     </link-entity>
    </entity>
</fetch>

Düzenleyen chirochino 02 Mayıs 2012 Çarşamba 15:15

Tüm Yanıtlar

03 Mayıs 2012 Perşembe 03:57

0
Hi,

Whenever you are dealing with Fetch XML, it is always a good idea to create Fetch XML through Advanced find.

In CRM 2011 you can create advanced find on "Order Products" and add fields from "Order" entity. Then click download Fetch XML to downloadthe Fetch XML. Then you can create advanced find on "Order" and add customer details from "Account" entity. Then click download Fetch XML to download the required Fetch XML. Mix the 2 downloaded Fetch XML to have the required Fetch query.

Also install the 3rd party handy tool "Stunnware Tools for Microsoft Dynamics CRM 4.0". Through this tool you can execute Fetch XML check whether your fetch XML has errors. This tool will connect to your CRM database. Note: even though the name of this tool says CRM 4.0, but it runs on CRM 2011.

I hope this helps. If my response answered your question, please mark the response as an answer and also vote as helpful.
Ashish Mahajan, CRM Developer, CSG (Melbourne)
My Personal Website: http://www.ashishmahajan.com
My Blogs: http://ashishmahajancrm.blogspot.com.au and http://ashishmahajancrm.wordpress.com
My Youtube Channel: http://www.youtube.com/user/ashishmahajanmscrm

My Linkedin:

My Twitter: https://twitter.com/#!/ashishmahajan74
- Yanıt Olarak Öneren Ashish Mahajan Australia (Architect) 03 Mayıs 2012 Perşembe 03:57
- Yanıt Olarak İşaretleyen chirochino 10 Mayıs 2012 Perşembe 08:38
- Yanıt
- Alıntı
03 Mayıs 2012 Perşembe 03:58

0
Forgot to give you the required Fetch XML.

<fetch mapping="logical" version="1.0">
   <entity name="salesorderdetail">
       <attribute name="priceperunit" />
       <attribute name="quantity" />
       <attribute name="salesorderdetailid" />
       <attribute name="uomid" />
       <attribute name="description" />
       <order attribute="priceperunit" />
       <link-entity name="salesorder" from="salesorderid" to="salesorderid" alias="a_6ec0e72e4c104394bc627456c6412838" link-type="outer">
           <attribute name="ordernumber" />
           <link-entity name="account" from="accountid" to="customerid" alias="a_92b77a4070bd4d1a82d9fa6ce38df2cc" link-type="outer">
               <attribute name="name" />
           </link-entity>
       </link-entity>
   </entity>
</fetch>

Modify the above and add required fields as per what you need.

I hope this helps. If my response answered your question, please mark the response as an answer and also vote as helpful.
Ashish Mahajan, CRM Developer, CSG (Melbourne)
My Personal Website: http://www.ashishmahajan.com
My Blogs: http://ashishmahajancrm.blogspot.com.au and http://ashishmahajancrm.wordpress.com
My Youtube Channel: http://www.youtube.com/user/ashishmahajanmscrm

My Linkedin:

My Twitter: https://twitter.com/#!/ashishmahajan74
- Yanıt Olarak Öneren Ashish Mahajan Australia (Architect) 03 Mayıs 2012 Perşembe 03:58
- Yanıt Olarak İşaretleyen chirochino 10 Mayıs 2012 Perşembe 08:38
- Yanıt
- Alıntı
03 Mayıs 2012 Perşembe 05:58

0

Thanx for your response, its working now. One issue left is i need to enable prefiltering, how do i go about it. The one i did seems to be not working. check my fetchxml if its the correct thing.
Good is not good enough when best is expected !!!
- Yanıt
- Alıntı
03 Mayıs 2012 Perşembe 08:47

0
Hi,

Define Pre-Filtering: This is a way to filter your report data using CRM Advance Find Functionality. Pre-Filtering actually makes your report context sensitive.

To get it working, you need to add two attributes to the <entity> tag in your fetch-xml.

<entity name="invoice" enableprefiltering="true" prefilterparametername="InvoiceFilter">

"enableprefiltering", will make the particular entity context sensitive and "prefilterparametername" is optional attribute to provide a name to your pre-filtering parameter. Pre-Filtering adds a Query & Report Parameter to your report, if "prefilterparametername" not provided then the parameter name would be CRM_<EntityName>.

I hope this helps. If my response answered your question, please mark the response as an answer and also vote as helpful.
Ashish Mahajan, CRM Developer, CSG (Melbourne)
My Personal Website: http://www.ashishmahajan.com
My Blogs: http://ashishmahajancrm.blogspot.com.au and http://ashishmahajancrm.wordpress.com
My Youtube Channel: http://www.youtube.com/user/ashishmahajanmscrm

My Linkedin:

My Twitter: https://twitter.com/#!/ashishmahajan74
- Yanıt Olarak Öneren Ashish Mahajan Australia (Architect) 03 Mayıs 2012 Perşembe 08:47
- Yanıt Olarak İşaretleyen chirochino 10 Mayıs 2012 Perşembe 08:38
- Yanıt
- Alıntı
03 Mayıs 2012 Perşembe 08:49

0
To add:

Instead of :
enableprefiltering="true" prefilterparametername="CRM_FilteredAccountId"

Can you do:

enableprefiltering="true" prefilterparametername="AccountId"

Instead of creating a very big query as you have, take my simple small query as example and test it using stunnware tool I have mentioned above.

I hope this helps. If my response answered your question, please mark the response as an answer and also vote as helpful.

Ashish Mahajan, CRM Developer, CSG (Melbourne)
My Personal Website: http://www.ashishmahajan.com
My Blogs: http://ashishmahajancrm.blogspot.com.au and http://ashishmahajancrm.wordpress.com
My Youtube Channel: http://www.youtube.com/user/ashishmahajanmscrm

My Linkedin:

My Twitter: https://twitter.com/#!/ashishmahajan74
- Yanıt Olarak Öneren Ashish Mahajan Australia (Architect) 03 Mayıs 2012 Perşembe 08:49
- Yanıt
- Alıntı

03 Mayıs 2012 Perşembe 08:51

<fetch mapping="logical" version="1.0">
    <entity name="salesorderdetail">
        <attribute name="priceperunit" />
        <attribute name="quantity" />
        <attribute name="salesorderdetailid" />
        <attribute name="uomid" />
        <attribute name="description" />
        <order attribute="priceperunit" />
        <link-entity name="salesorder" from="salesorderid" to="salesorderid" alias="a_6ec0e72e4c104394bc627456c6412838" link-type="outer">
            <attribute name="ordernumber" />
            <link-entity name="account" from="accountid" to="customerid" alias="a_92b77a4070bd4d1a82d9fa6ce38df2cc" link-type="outer" enableprefiltering="true" prefilterparametername="AccountId">
                <attribute name="name" />
            </link-entity>
        </link-entity>
    </entity>
</fetch>

I hope this helps. If my response answered your question, please mark the response as an answer and also vote as helpful.

Ashish Mahajan, CRM Developer, CSG (Melbourne)
My Personal Website: http://www.ashishmahajan.com
My Blogs: http://ashishmahajancrm.blogspot.com.au and http://ashishmahajancrm.wordpress.com
My Youtube Channel: http://www.youtube.com/user/ashishmahajanmscrm

My Linkedin:

My Twitter: https://twitter.com/#!/ashishmahajan74

Yanıt Olarak Öneren Ashish Mahajan Australia (Architect) 03 Mayıs 2012 Perşembe 08:51
Yanıt Olarak İşaretleyen chirochino 10 Mayıs 2012 Perşembe 08:37

08 Mayıs 2012 Salı 13:28

0

Hi,

If the responses answered your question and you are satisfied, please mark the responses as an answer and also vote as helpful. This will help others to search on similar problems.

Thanks in advance.
Ashish Mahajan, CRM Developer, CSG (Melbourne)
My Personal Website: http://www.ashishmahajan.com
My Blogs: http://ashishmahajancrm.blogspot.com.au and http://ashishmahajancrm.wordpress.com
My Youtube Channel: http://www.youtube.com/user/ashishmahajanmscrm

My Linkedin:

My Twitter: https://twitter.com/#!/ashishmahajan74
- Yanıt
- Alıntı