Sign in
Microsoft.com
 
Microsoft
 
 
 

locked
FETCH XML Condition for linked entity RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hello,

    I appreciate your great help.

    I am working on Contract view and trying to apply filter from linked entity in quick search textbox and the fetch xml query is not working.

    Example: I want to enter contract name in quick search in the Contract view and it returns the results.

    The requirement is, we want to quick search other entity's attribute in the contract view quick search since contract entity has relationship with order entity.

    Here is my sql query: It is working and returning 1 record

    select ContractName from Contract c join Order o on (c.contractorderid = o.OrderId) where c.ContractName like 'ORDER 17%' or o.OrderName like 'ORDER 17%'

    Here is my Fetch xml query, I tried to execute using fetch xml builder, it is not returning any record.

    <fetch mapping='logical'>
     <entity name='contract'>
      <attribute name='contractname'/>
      <attribute name='contractnumber'/>
      <attribute name='customername'/>
      <attribute name='contractstatus'/>
      <filter type='or'>
       <condition attribute='contractname' operator='like' value='ORDER 17'/>
      </filter>
      <link-entity name='order' from='orderid' to='contractorderid' link-type='inner'>
       <filter type='or'>
        <condition attribute='ordername' operator='like' value='ORDER 17'/>
       </filter>
      </link-entity>
     </entity>
    </fetch>

    Ram

    Thursday, February 9, 2012 10:36 PM

Answers

All replies

  • Question
    Sign in to vote
    2
    Sign in to vote

    Hello Ram,

    I'm afraid that it would be impossible to build such kind of Fetch Xml. You can use 'Or' operator only at one level of entity (like 'master' or 'linked' entity). Possible way out is to execute Fetch message twice and merge retrieved results.

    Microsoft CRM Freelancer

    My blog (english)
    Мой блог (русскоязычный)
    Follow Andriy on Twitter

    Thursday, February 9, 2012 11:09 PM
    Moderator
  • Question
    Sign in to vote
    0
    Sign in to vote

    Thanks Andriy.

    How can i implement the possible solution. Can you tell me the steps involved and how to achieve that?

    Ram

    Friday, February 10, 2012 1:29 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Technically, the fetchxml is correct.  To have it return the same results as the SQL query, include the % wild card in the same places as the SQL query, like this:

    <fetch mapping='logical'>
  <entity name='contract'>
   <attribute name='contractname'/>
   <attribute name='contractnumber'/>
   <attribute name='customername'/>
   <attribute name='contractstatus'/>
   <filter type='or'>
    <condition attribute='contractname' operator='like' value='ORDER 17%'/>
   </filter>
   <link-entity name='order' from='orderid' to='contractorderid' link-type='inner'>
    <filter type='or'>
     <condition attribute='ordername' operator='like' value='ORDER 17%'/>
    </filter>
   </link-entity>
  </entity>
 </fetch>

    The problem you may run into is when you need to edit the view. Since it will have custom FetchXml which can't be rendered by the UI, you will likely have to do all your column adds/subtracts and sorting in the fetchxml as well, instead of the UI.
    Friday, February 10, 2012 2:08 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Thanks Andriy.

    How can i implement the possible solution. Can you tell me the steps involved and how to achieve that?

    Ram

    Steps:

    1. Develop code which will retrieve data based on first criteria.

    2. Develop code which will retrieve data based on second criteria.

    3. Merge results.Is something unclear?

    Microsoft CRM Freelancer

    My blog (english)
    Мой блог (русскоязычный)
    Follow Andriy on Twitter

    Friday, February 10, 2012 1:21 PM
    Moderator
  • Question
    Sign in to vote
    0
    Sign in to vote
    As there is no system relationship between contracts and orders therefore I will suppose you have created a custom relationship. In that case you can do a search in child entity as it will be having the name field of parent entity.

    Regards Faisal

    Friday, February 10, 2012 1:43 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Thanks Andriy, It is clear.

    Ram

    Friday, February 10, 2012 2:43 PM