locked
SQL Query to FetchXML RRS feed

  • Question

  • Having issues working with a SQL statement and transforming it into a FetchXML query.  Basically, I want to turn this statement into a FetchXML query:

    select

                   invoicedetail.productid,

                   invoicedetail.priceperunit,

                   invoicedetail.quantity,

                   invoicedetail.invoicedetailid,

                   invoicedetail.invoiceid,

                  

                   d3_project.D3_name

    from

                   invoicedetail,

                   invoice,

                   D3_project,

                   product

    where

                   invoicedetail.D3_ProjectId is null and

                   invoicedetail.invoiceid = invoice.invoiceid and

                   invoice.opportunityid = D3_project.D3_OpportunityId and

                   invoicedetail.productid = product.productid and

                   (

                                  (product.d3_commissioncategory = 5) or

                                  (product.producttypecode = 200004) or

                                  (product.d3_category1 = 15) or

                                  (product.d3_autodeskproduct = 48) or

                                  (product.producttypecode = 200010) or

                                  (product.D3_AutodeskProduct = 53)

                   )

    I have come up with this so far, but can't determine how to set filters based on the id's of 2 different entities:

    <fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">

      <entity name="invoicedetail">

        <attribute name="productid" />

        <attribute name="priceperunit" />

        <attribute name="quantity" />

        <attribute name="invoicedetailid" />

        <attribute name="invoiceid" />

        <filter type="and">

          <condition attribute="new_projectid" operator="null" />

        </filter>

        <link-entity name="product" from="productid" to="productid" alias="ab">

          <filter type="and">

            <filter type="or">

              <condition attribute="new_commissioncategory" operator="eq" value="5" />

              <condition attribute="producttypecode" operator="eq" value="200004" />

              <condition attribute="new_category1" operator="eq" value="15" />

              <condition attribute="new_autodeskproduct" operator="eq" value="48" />

              <condition attribute="producttypecode" operator="eq" value="200010" />

              <condition attribute="new_autodeskproduct" operator="eq" value="53" />

            </filter>

          </filter>

        </link-entity>

        <link-entity name="new_project" from="new_projectid" to="new_projectid" visible="false" link-type="outer" alias="aa">

          <attribute name="new_name" />

        </link-entity>

        <link-entity name="invoice" from="invoiceid" to="invoiceid">

        </link-entity>

      </entity>

    </fetch>

    Any help would be appreciated and thank you in advance.

    Shane Collins

    Tuesday, July 17, 2012 6:44 PM

All replies

  • Is this CRM 2011?  If so, here is a trick I use to get the FetchXML.  Go into the CRM interface and write an advanced query that is a close approximation to what you are trying to do, and there is a button to 'export Fetch XML'.  Export that file and review how it works there.   I've done this dozens of times to get the right FetchXML syntax to use.
    Saturday, July 21, 2012 4:07 PM