Link Account entity to Order products

2. května 2012 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>

Upravený chirochino 2. května 2012 15:15

Všechny reakce

3. května 2012 3: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
- Navržen jako odpověď Ashish Mahajan Australia (Architect) 3. května 2012 3:57
- Označen jako odpověď chirochino 10. května 2012 8:38
- Reagovat
- Citovat
3. května 2012 3: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
- Navržen jako odpověď Ashish Mahajan Australia (Architect) 3. května 2012 3:58
- Označen jako odpověď chirochino 10. května 2012 8:38
- Reagovat
- Citovat
3. května 2012 5: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 !!!
- Reagovat
- Citovat
3. května 2012 8: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
- Navržen jako odpověď Ashish Mahajan Australia (Architect) 3. května 2012 8:47
- Označen jako odpověď chirochino 10. května 2012 8:38
- Reagovat
- Citovat
3. května 2012 8: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
- Navržen jako odpověď Ashish Mahajan Australia (Architect) 3. května 2012 8:49
- Reagovat
- Citovat

3. května 2012 8: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

Navržen jako odpověď Ashish Mahajan Australia (Architect) 3. května 2012 8:51
Označen jako odpověď chirochino 10. května 2012 8:37

8. května 2012 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
- Reagovat
- Citovat